poi之导入Excel(java个人代码)
案例使用的框架:jsp+spring+mybaties
<form id="importForm" name="importForm" method="post" target="win" action="importExcel.ht" enctype="multipart/form-data"> <div class="row"> <table id="tableid" class="table-detail" cellpadding="0" cellspacing="0" border="0"> <tr> <th width="22%">选择文件:</th> <td width="78%"><input type="file" size="40" name="xmlFile" id="xmlFile"/></td> </tr> </table> </div> <input type="hidden" name="rq" value="${rq}"> </form>
后台代码
@RequestMapping("importExcel") @Action(description="导入Excel弹窗") public void importExcel(MultipartHttpServletRequest request,HttpServletResponse response) throws Exception { String rq=request.getParameter("rq"); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); MultipartFile fileLoad = request.getFile("xmlFile"); ResultMessage resultMessage = null; try { String fileType = fileLoad.getOriginalFilename().substring(fileLoad.getOriginalFilename().lastIndexOf(".") + 1, fileLoad.getOriginalFilename().length()); Workbook wb = null; if (fileType.equals("xls")) { wb = new HSSFWorkbook(fileLoad.getInputStream()); } else if (fileType.equals("xlsx")) { wb = new XSSFWorkbook(fileLoad.getInputStream()); } else { throw new Exception("读取的不是excel文件"); } //读取了模板内所有sheet内容 HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0); HSSFCell cell0 = null; HSSFCell cell1 = null; Map<String, String> map = new HashMap<String, String>(); //读取excel,处理自己的业务 StringBuffer buf= new StringBuffer(); for(int i=2;i<=97;i++){ cell0 = sheet.getRow(i).getCell(0); cell1 = sheet.getRow(i).getCell(1); String key = cell0.getStringCellValue().replace(":", ""); String val = cell1.getStringCellValue(); if(key.startsWith("0")){ key = key.substring(1,key.length()); } key = "v"+key; map.put(key, val); } String value = map.toString().replace("=", ":"); LoadDate ld = new LoadDate(); ld.setValue(value); ld.setRq(sdf.parse(rq)); ld.setSbzt("未上报"); for(int i=98;i<=102;i++){ cell0 = sheet.getRow(i).getCell(0); cell1 = sheet.getRow(i).getCell(1); String key = cell0.getStringCellValue(); String val = cell1.getStringCellValue(); if("高峰电量".equals(key)){ ld.setYcgfdl(Long.parseLong(val)); }else if("全天电量".equals(key)){ ld.setYcqdl(Long.parseLong(val)); }else if("错峰电力".equals(key)){ ld.setYccfdLi(Long.parseLong(val)); }else if("错峰电量".equals(key)){ ld.setYccfdLl(Long.parseLong(val)); }else if("低谷电量".equals(key)){ ld.setYcdgdl(Long.parseLong(val)); } } Long id = UniqueIdUtil.genId(); ld.setId(id); ld.setSbzt("未上报"); loadDateService.add(ld); String result="导入数据成功"; if(result.contains("成功")){ resultMessage = new ResultMessage(ResultMessage.Success, "导入成功!"); }else{ resultMessage = new ResultMessage(ResultMessage.Fail, result); } writeResultMessage(response.getWriter(), resultMessage); } catch (Exception ex) { ex.printStackTrace(); String str = MessageUtil.getMessage(); ex.getLocalizedMessage(); if (StringUtil.isNotEmpty(str)) { resultMessage = new ResultMessage(ResultMessage.Fail,"导入失败请检查导入的Excel是否正确:" + str); response.getWriter().print(resultMessage); } else { String message = ExceptionUtil.getExceptionMessage(ex); resultMessage = new ResultMessage(ResultMessage.Fail, "导入失败请检查导入的Excel是否正确"); response.getWriter().print(resultMessage); } } }
个人经验总结
导入报版本不匹配问题
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); MultipartFile fileLoad = request.getFile("xmlFile"); try { String fileType = fileLoad.getOriginalFilename().substring( fileLoad.getOriginalFilename().lastIndexOf(".") + 1, fileLoad.getOriginalFilename().length()); Workbook wb = null; if (fileType.equals("xls")) { try { //07+版本 wb = new HSSFWorkbook(fileLoad.getInputStream()); } catch (Exception e) { //03版 wb = new XSSFWorkbook(fileLoad.getInputStream()); } } else if (fileType.equals("xlsx")) { wb = new XSSFWorkbook(fileLoad.getInputStream()); } else { throw new Exception("读取的不是excel文件"); }
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步