1.情景：要做一个表格内容导入系统功能，但普通的读取发现excel格式文件会乱码，于是记录一下excel表数据上传读取的方法
2.前端H5：
<div class="modal fade" id="addModal"> <div class="modal-dialog"> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button> <h4 class="modal-title">导入</h4></div> <form class="form-horizontal" name="addDevOrderform" id="addDevOrderform" action="<%=path%>/admin/addXXX.html" method="POST" enctype="multipart/form-data" > <div class="modal-body"> <div class="alert m-b-0" > <h4> <i class="fa fa-pencil-square"></i>设备订单导入</h4> <p> <table class="table table-bordered"> <tbody> <tr> <td style="text-align: center; 18%;">订单文件:</td> <td style="text-align: center; 28%;"> <input type="file" name="orderFile"> </td> </tr> </tbody> </table> </p> </div> </div> </form> <div class="modal-footer"> <a href="javascript:;" class="btn btn-sm btn-success" type="submit" onclick="addDevOrder()" >添加</a> <a href="javascript:;" class="btn btn-sm btn-white" data-dismiss="modal">取消</a></div> </div> </div> </div>
js:
function addDevOrder(){
//省略其他参数验证
//表单提交
$("#addDevOrderform").submit();
}
maven：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
java：
/** * 批量导入订单 * @return * @throws Exception */ @RequestMapping("addXXX.html") public String addDevOrder(@RequestParam MultipartFile orderFile) throws Exception { InputStream inputStream=null; try { Map map = this.getParameterMap(); String path="/opt/xxx/xxx/"; String filename = orderFile.getOriginalFilename(); // 保存备份表格 File file = new File(path + filename); File parent = file.getParentFile(); // 获取父文件 if( !parent.exists() ){ parent.mkdirs();} //创建所有父文件夹 orderFile.transferTo(file.getAbsoluteFile()); //读取表格信息 inputStream=new FileInputStream( file); Workbook workbook=null; if (filename.endsWith("xlsx")){ workbook = new XSSFWorkbook(inputStream);//Excel 2007 }else if (filename.endsWith("xls")){ workbook = new HSSFWorkbook(inputStream);//Excel 2003 } ArrayList<Map<String, Object>> list = new ArrayList<>(); HashMap<String, Object> obj = new HashMap<>(); // 循环工作表Sheet for (int numSheet = 0; numSheet <workbook.getNumberOfSheets(); numSheet++) { //HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); Sheet hssfSheet = workbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // 循环行Row int lastRowNum = hssfSheet.getLastRowNum(); logger.info("lastRowNum:"+lastRowNum); DataFormatter dataFormatter = new DataFormatter(); dataFormatter.addFormat("###########", null); for (int rowNum = 1; rowNum < hssfSheet.getLastRowNum(); rowNum++) { //HSSFRow hssfRow = hssfSheet.getRow(rowNum); Row hssfRow = hssfSheet.getRow(rowNum); if (hssfRow != null) { obj = new HashMap<>(); //内容为空break；我测试的时候多个表格tab的时候后面的表格行数会累加前面的导致报空指针 int cellType = hssfRow.getCell(0).getCellType(); if (cellType==Cell.CELL_TYPE_BLANK){ break; }
//表格数据读取 obj.put("orderNo",hssfRow.getCell(0).toString()); obj.put("shelfLinkNo",hssfRow.getCell(1).toString()); obj.put("productAttributes",hssfRow.getCell(2).toString()); //防止手机号等文本格式被转成数字格式内容发生改变 String devId = dataFormatter.formatCellValue(hssfRow.getCell(3)); obj.put("devId",devId); list.add(obj); } } } int i = devOrderService.addXXX(list); logger.info("addDevOrder num:"+i); } catch (Exception e) { logger.error( e.getMessage(), e ); throw new Exception("出错啦..."); }finally { if (inputStream!=null){ inputStream.close(); } } return "redirect:xxxx.html"; }
大佬链接：https://blog.csdn.net/qq_19734597/article/details/81875224
附：Excel表格导出
//创建Excel
@RequestMapping("/createExcel")
public String createExcel(HttpServletResponse response) throws IOException {
//创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
//建立新的sheet对象（excel的表单）
HSSFSheet sheet=wb.createSheet("成绩表");
//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
HSSFRow row1=sheet.createRow(0);
//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
HSSFCell cell=row1.createCell(0);
//设置单元格内容
cell.setCellValue("学员考试成绩一览表");
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
//在sheet里创建第二行
HSSFRow row2=sheet.createRow(1);
//创建单元格并设置单元格内容
row2.createCell(0).setCellValue("姓名");
row2.createCell(1).setCellValue("班级");
row2.createCell(2).setCellValue("笔试成绩");
row2.createCell(3).setCellValue("机试成绩");
//在sheet里创建第三行
HSSFRow row3=sheet.createRow(2);
row3.createCell(0).setCellValue("李明");
row3.createCell(1).setCellValue("As178");
row3.createCell(2).setCellValue(87);
row3.createCell(3).setCellValue(78);
//.....省略部分代码
//输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=details.xls");
response.setContentType("application/msexcel");
wb.write(output);
output.close();
return null;
}
补充： 解决在处理excel表中数据时无法去除空格的问题
//三种空格unicode(u00A0,u0020,u3000)表示的区别 //1.不间断空格u00A0,主要用在office中,让一个单词在结尾处不会换行显示,快捷键ctrl+shift+space ; //2.半角空格(英文符号)u0020,代码中常用的; //3.全角空格(中文符号)u3000,中文文章中使用; public String trim(String str){ str=str.trim(); str = str.replaceAll("u00A0",""); str = str.replaceAll("u0020",""); str = str.replaceAll("u3000",""); return str; }
结束