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文件");
           }