关于java读取excel文件

工作一年多，仿佛一直在文件上传下载上研究。。。今天把读取excel文件相关（包括根据读取的数据报错要把该单元格标注，显示错误信息）整理一下，希望能够帮到别人，自己也能时不时的回顾一下。

首先用到的是springMVC框架，用的也是POI，大体流程写一下。

1.考虑到页面的美观性，前台jsp没有直接用input标签中的type="file"，而是将上传按钮跟文本框分开，具体代码如下：

①画面上显示的是普通文本框：

<input type="text" name="filePath" id="filePath" class="form form-size__full" readonly="readonly"/>

②隐藏显示文本框，用来获取文件路径

<form:form action="${basePath }" method="post"  enctype="multipart/form-data" id="fileUploadForm" name="fileUploadForm" commandName="user">

<input type="file" name="proInfoFile" id="proInfoFile" style="display: none;" class="form form-size__full"
      οnchange="document.fileUploadForm.filePath.value=this.value" />

③点击“确定”/“提交”按钮将流传入后台controller

④Controller层相关代码：

@RequestMapping("/access/proInfoIntr/uploadExcel")
 public ModelAndView uploadExcel(HttpServletRequest request, User user,
   Errors errors) throws Exception {
  ModelAndView mv = new ModelAndView();

// 创建一个通用的多部分解析器
  CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
    request.getSession().getServletContext());

try {
   // 判断 request 是否有文件上传
   if (multipartResolver.isMultipart(request)) {
    // 转换成多部分request
    MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
    MultipartFile file = multipartRequest.getFile("proInfoFile");

// 未传入文件
    if (file.getSize() <= 0) {

//此处根据项目具体需要抛错
     throw new Exception();
    }

//service层实现，具体参照⑤

proInfoUploadService.fileCheck(file, projectInfo,
      userCode);

}catch() {

}

mv.setViewName("跳转页面");
return mv;

}

⑤Service实现类

 @Override
 public boolean fileCheck(MultipartFile file, String projectInfo,
   String userCode) throws Exception {
  // 判断文件内容是否没有错误
  boolean readFinished = true;
  String cellVal;
  // 判断文件类型
  String fileType = file.getOriginalFilename().substring(
    file.getOriginalFilename().lastIndexOf("."));
  if ((!excel2003L.equals(fileType)) && (!excel2007U.equals(fileType))) {
   throw new AppException(messageUtil.getMessage("ERRC0019",
     new String[] { "" }));
  } else {
   // 返回保存的文件路径
   filePath = new ImportExcel().importExcel(file);
   // 获取读入的Excel表
   FileInputStream excelFileInputStream = new FileInputStream(filePath);

   Workbook wb = new ImportExcel().getWorkbook(file.getInputStream(),
     filePath);
   excelFileInputStream.close();
   Sheet sheet = wb.getSheetAt(0);

   String[] headers = projectInfo.split(",");

   // 创建单元格样式
   int firstRowNum = sheet.getFirstRowNum();// 获取表第一行非空num
   int totalRowNum = sheet.getPhysicalNumberOfRows();// 获取总行数
   sheet.setColumnWidth(sheet.getRow(firstRowNum)
     .getPhysicalNumberOfCells(), 256 * 50);// 设置错误信息单元格的宽度
   int titleCellNum = sheet.getRow(firstRowNum).getLastCellNum();//获取表头的单元格数
   for (int rowNum = firstRowNum; rowNum < totalRowNum; rowNum++) {
    Row row = sheet.getRow(rowNum);
    if (CommonUtil.isRowEmpty(row)) {
     continue;
    } else {
     Cell errMsgCell = row.createCell(titleCellNum);// 存放errorMessage的单元格
     String errMsg = "";// 错误信息
     // 获取表头
     if (firstRowNum == rowNum) {
      for (int i = 1, j = 0; i < titleCellNum
        && j < headers.length; i++, j++) {
       // 获取单元格的值
       Cell cell = row.getCell(i);
       cellVal = String.valueOf(CommonUtil
         .getCellValue(cell));
       if ("null".equals(cellVal)
         || StringUtils.isBlank(cellVal)) {
        throw new AppException(messageUtil.getMessage(
          "ERRC0017", new String[] { file
            .getOriginalFilename() }));
       }
       if (headers[j].equals(cellVal)) {
        continue;
       } else {
        // 读出的项目号文件的表头和导入的文件表头不一致
        wb.close();
        throw new AppException(messageUtil.getMessage(
          "ERRC0017", new String[] { file
            .getOriginalFilename() }));
       }
      }
     } else {// 读取信息
      // 项目类型check
      String[] errMsgArr = new String[250];// 存放每条数据的errMsg
      int num = 0;// errMsg[]下标
      for (int i = 1; i < titleCellNum; i++) {
       // 获取单元格的值
       Cell cell = row.getCell(i);
       if (null == cell) {// 单元格为空
        cell = row.createCell(i);
        CommonUtil.changeCellStyle(wb, row, i, cell);
        errMsgArr[num] = messageUtil.getMessage(
          "ERRC0002", new String[] { sheet
            .getRow(firstRowNum).getCell(i)
            + "" });
        num++;
       } else {
        switch (cell.getCellType()) {
        // 单元格为空
        case Cell.CELL_TYPE_BLANK:
         CommonUtil
           .changeCellStyle(wb, row, i, cell);
         errMsgArr[num] = messageUtil.getMessage(
           "ERRC0002", new String[] { sheet
             .getRow(firstRowNum)
             .getCell(i)
             + "" });
         num++;
         break;
        // 单元格数值为String类型
        case Cell.CELL_TYPE_STRING:
         if (StringUtils.isBlank(cell
           .getStringCellValue())) {
          CommonUtil.changeCellStyle(wb, row, i,
            cell);
          errMsgArr[num] = messageUtil
            .getMessage(
              "ERRC0002",
              new String[] { sheet
                .getRow(firstRowNum)
                .getCell(i)
                + "" });
          num++;
         } else {
          num = typeSelect(wb, row, headers,
            errMsgArr, num, i, cell);
         }
         break;
        // 非空、非String
        default:
         num = typeSelect(wb, row, headers,
           errMsgArr, num, i, cell);
         break;
        }
       }
      }
      // 将errMsg[]转换为String串（方便给每条errMsg添加换行）
      for (String str : errMsgArr) {
       if (null != str) {
        errMsg = errMsg + str + "\r\n";// 错误信息换行
       }
      }
     }
     // 设置错误信息单元格格式
     CellStyle errCellStyle = wb.createCellStyle();
     errCellStyle.setWrapText(true);// 设置换行
     Font font = wb.createFont();// 设置错误信息字体颜色：红色
     font.setColor(HSSFColor.RED.index);
     errCellStyle.setFont(font);
     errMsgCell.setCellStyle(errCellStyle);
     // 放置错误信息
     errMsgCell.setCellValue(errMsg);
     // 数据内容有错误
     if (StringUtils.isNotBlank(errMsg)) {
      readFinished = false;
     }
    }
   }
   FileOutputStream excelFileOutPutStream = new FileOutputStream(
     filePath);
   wb.write(excelFileOutPutStream);
   excelFileOutPutStream.flush();
   excelFileOutPutStream.close();
   wb.close();
   // 如果文件没有错误，则将数据写入数据库
   if (readFinished) {
    List<List<Object>> proInfoList = new ArrayList<List<Object>>();
    List<Object> proInfo = new ArrayList<Object>();
    for (int rowNum = 1; rowNum < totalRowNum; rowNum++) {
     Row row = sheet.getRow(rowNum);
     proInfo = new ArrayList<Object>();
     DateFormat format = new SimpleDateFormat(
       "yyyy-MM-dd HH:mm:ss");
     for (int i = 1; i < titleCellNum; i++) {
      Cell cell = row.getCell(i);
      if (0 == cell.getCellType()) {// 数值类型
       String mid = String.valueOf(cell);
       cellVal = mid.replace(".0", "");
      } else {
       cellVal = String.valueOf(cell);
      }
      // 项目开始日期格式转换
      if ((row.getLastCellNum() - 2) == i) {
       String[] proBeginDate = cellVal.split("-");
       cellVal = proBeginDate[2] + "-" + proBeginDate[1]
         + "-" + proBeginDate[0];
      }
      // 状态转换
      if (Constant.projectInfo_status_0.equals(cellVal)) {
       proInfo.add("0");
      } else if (Constant.projectInfo_status_1
        .equals(cellVal)) {
       proInfo.add("1");
      } else {
       proInfo.add(cellVal);
      }
     }
     // 获取当前用户工号
     proInfo.add(userCode);
     // 获取系统时间
     proInfo.add(format.format(new Date()));// 创建时间
     proInfo.add(format.format(new Date()));// 更新时间
     proInfoList.add(proInfo);
    }
    // 删除数据
    try {
     pro.lockTblProjects();// 锁表
     deleteAllPro();
     // 存数据
     if (proInfoList != null && proInfoList.size() > 0) {
      insertProInfo(proInfoList);
     }
    } finally {
     pro.unlockTblProjects();// 解锁
    }
   }
   return readFinished;
  }
 }

⑥数据库用的是mysql，框架用的mybatis，具体sql语句就不体现了，就是一般流程