记录java上传Excel读取数据

在pom文件里导入依赖

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.16</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16</version> </dependency>

前端页面随便写个file导入

<div class="modal fade bs-example-modal-lg" tabindex="-1" role="dialog" aria-labelledby="myLargeModalLabel" id="myModal"> <div class="modal-dialog modal-lg" role="document"> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button> <h4 class="modal-title" id="myModalLabel"><b>导入</b></h4> </div> <div class="modal-body"> <div> <form class="form-inline" id="form1" enctype="multipart/form-data" method="post"> <table> <tr> <td></td> <td><input style="width:100%;" type="file" name="file" id="file" class="form-control"></td> </tr> </table> </form> </div> </div> <div class="modal-footer"> <button type="button" class="btn btn-default" data-dismiss="modal">取消</button> <button type="button" class="btn btn-success" onclick="file_submit()">提交</button> </div> </div> </div> </div>

记得导入js

<script src="../../js/jquery-form.min.js"></script>

链接：https://pan.baidu.com/s/1nLSrcpV_wyDII7pjGhQeXQ  提取码：op3c

 

然后写ajax调用接口

function file_submit(){ var fileName=$("#file").val(); if(fileName.indexOf("xls")==-1){ toastr.error("请选择表格") return; } var options={ url:'/QualityJiPei/importExcel', data:{}, type:'POST', dataType: 'json', contentType : "application/x-www-form-urlencoded; charset=gbk", success:function(json){ if(json.flag>0){ alert(json.resultMsg); }else{ alert(json.resultMsg); } } } $("#form1").ajaxSubmit(options); }

后台Controller

@RequestMapping("/importExcel") public String importExcel(@RequestParam("file") MultipartFile file) throws Exception{ JSONObject jsonObject=new JSONObject(); String name=file.getOriginalFilename(); if(name.length()<6|| !name.substring(name.length()-5).equals(".xlsx")){ jsonObject.put("msg","文件格式错误"); } Map<String,Object> list = ExcelUtils.excelToShopIdList(file.getInputStream()); int i=dao.addList(list); String daoMsg = MsgFormatUtils.getMsgByResult(i, "新增"); jsonObject.put("resultMsg",daoMsg); jsonObject.put("flag",i); return jsonObject.toJSONString(); }

导入工具类

package com.ldxx.util; import com.ldxx.bean.DicPeifangjipei; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.ss.usermodel.*; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.*; public class ExcelUtils { public static Map<String,Object> excelToShopIdList(InputStream inputStream) { Map<String,Object> map=new HashMap<>(); List<Object> list = new ArrayList<>(); //创建Excel工作薄 Workbook work=null; try { work = WorkbookFactory.create(inputStream); } catch (Exception e) { e.printStackTrace(); } Sheet sheet = null; Row row = null; Cell cell = null; // 循环工作表Sheet for (int i = 0; i < work.getNumberOfSheets(); i++) { sheet = work.getSheetAt(i); if (sheet == null) { continue; } // 循环行Row for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) { row = sheet.getRow(j); if (row == null || row.getFirstCellNum() == j) { continue; }else{ //获取固定单元格 cell = row.getCell(0); cell = row.getCell(1); cell = row.getCell(2); cell = row.getCell(3); cell = row.getCell(4); cell = row.getCell(5); cell = row.getCell(6); //根据业务写.. } List<Object> li = new ArrayList<>(); //循环获取本行每个单元格 for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { cell = row.getCell(y); li.add(cell); //根据业务写.. } list.add(li); } } map.put("obj",list); return map; } public static String getCellValue(Cell cell) { String cellValue = ""; if (cell == null) { return cellValue; } //判断数据的类型 //判断数据的类型 switch (cell.getCellTypeEnum()) { case NUMERIC: //数字 cellValue = stringDateProcess(cell); break; case STRING: //字符串 cellValue = String.valueOf(cell.getStringCellValue()); break; case BOOLEAN: //Boolean cellValue = String.valueOf(cell.getBooleanCellValue()); break; case FORMULA: //公式 cellValue = String.valueOf(cell.getCellFormula()); break; case BLANK: //空值 cellValue = ""; break; case ERROR: //故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } return cellValue; } public static String stringDateProcess(Cell cell) { String result = new String(); if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式 SimpleDateFormat sdf = null; if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { sdf = new SimpleDateFormat("HH:mm"); } else {// 日期 sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); } Date date = cell.getDateCellValue(); result = sdf.format(date); } else if (cell.getCellStyle().getDataFormat() == 58) { // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58) SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); double value = cell.getNumericCellValue(); Date date = org.apache.poi.ss.usermodel.DateUtil .getJavaDate(value); result = sdf.format(date); } else { double value = cell.getNumericCellValue(); CellStyle style = cell.getCellStyle(); DecimalFormat format = new DecimalFormat(); String temp = style.getDataFormatString(); // 单元格设置成常规 if (temp.equals("General")) { format.applyPattern("#"); } result = format.format(value); } return result; } }