java之通过Excel批量添加数据

来源：互联网发布：linux切换命令行模式编辑：程序博客网时间：2024/05/10 19:16

1.div

<div title="批量添加" data-options="closable:false">   <div id="pnlImport" class="easyui-panel" title=" "data-options="resizable:true,closed:false,fit:true,tools:'#tools_c$#_2'"><form id="updata" method="post" enctype="multipart/form-data"><table><tr><td colspan="2">     <input id="uploadExcel" name="file" type="file"></td></tr></table>    </form></div></div>

2.js

 $('#updata', c$).form('submit', {url :    '${path}/bu/importCatedata',dataType: 'text', contentType : "application/x-www-form-urlencoded;charset=utf-8",success : function(result) {var data = $.parseJSON(result);if(data.code == 0){alert(data.message);}else{alert(data.message);document.getElementById("uploadExcel").value = "";}},error : function() {}});

3.后台处理工具类与实现

package com.eznext.modul.municipal.util;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ImportExcelUtil {private final static String excel2003L =".xls";    //2003- 版本的excelprivate final static String excel2007U =".xlsx";   //2007+ 版本的excel/** * 描述：获取IO流中的数据，组装成List<List<Object>>对象 * @param in,fileName * @return * @throws IOException  */public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{List<List<Object>> list = null;//创建Excel工作薄Workbook work = this.getWorkbook(in,fileName);if(null == work){throw new Exception("创建Excel工作薄为空！");} Sheet sheet = null;Row row = null;Cell cell = null;list = new ArrayList<List<Object>>();//遍历Excel中所有的sheetfor (int i = 0; i < work.getNumberOfSheets(); i++) {sheet = work.getSheetAt(i);if(sheet==null){continue;}//遍历当前sheet中的所有行for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum()+1; j++) {row = sheet.getRow(j);if(row==null||row.getFirstCellNum()==j){continue;}//遍历所有的列List<Object> li = new ArrayList<Object>();for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {cell = row.getCell(y);li.add(cell);}list.add(li);}}work.close();return list;}/** * 描述：根据文件后缀，自适应上传文件的版本  * @param inStr,fileName * @return * @throws Exception */public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{Workbook wb = null;String fileType = fileName.substring(fileName.lastIndexOf("."));if(excel2003L.equals(fileType)){wb = new HSSFWorkbook(inStr);  //2003-}else if(excel2007U.equals(fileType)){wb = new XSSFWorkbook(inStr);  //2007+}else{throw new Exception("解析的文件格式有误！");}return wb;}}

/** * 导入管养数据 * @param file * @param principal * @return * @throws BiffException */@ResponseBody@RequestMapping(value = "/bu/importCatedata")public ActionResult importCatedata(MultipartFile file,Principal principal) throws BiffException {    List<List<Object>> listob = null; try {listob = new ImportExcelUtil().getBankListByExcel(file.getInputStream(),file.getOriginalFilename());tmaintainService.BatChadd(listob);return ActionResult.Succeed();} catch (Exception e) {return ActionResult.Failed(e);}}

0 0