《5分钟Java》实现excel文件上传并解析

一、需求说明

通过接口上传一个姓名号码表，返回一个"姓名，号码"格式的的一个String数组。

二、功能实现

1、pom.xml中引入依赖

<dependency>

 <groupId>org.apache.poi</groupId>

 <artifactId>poi</artifactId>

 <version>4.1.2</version>

</dependency>

<dependency>

 <groupId>org.apache.poi</groupId>

 <artifactId>poi-ooxml</artifactId>

 <version>4.1.2</version>

</dependency>

使用poi去解析excel，poi对应的是excel2003，poi-ooxml对应的是excel2007。

2、实现上传接口

@RestController

@RequestMapping("/testFile")

public class FIleUpDownLoadController {

  @Autowired

  private FileParserService fileParserService;

  @ApiOperation(value = "上传excel文件")

  @RequestMapping(value = "/target", method = RequestMethod.POST)

  @ResponseBody

  public ResponseEntity importTarget(@RequestParam("file") MultipartFile file, HttpServletResponse response) {

    ResponseEntity res = new ResponseEntity();

    try {

      String fileName = file.getOriginalFilename();

      if (!fileName.endsWith(".xls")) {

        res.setCode(ReturnCodeMsgEnum.IMPORT_EXCEL_FORMAT.getCode());

        res.setMsg(ReturnCodeMsgEnum.IMPORT_EXCEL_FORMAT.getMsg());

        return res;

      }

      //接收文件流和解析excel

      List<String> rows = fileParserService.splitRows(file);

      //无错误，不需要返回下载文件，且执行保存数据库

      res.setCode(ReturnCodeMsgEnum.IMPORT_EXCEL_SUCCESS.getCode());

      res.setMsg(ReturnCodeMsgEnum.IMPORT_EXCEL_SUCCESS.getMsg());

      res.setData(rows);

      return res;

    }catch(Exception e){

      logger.error("importTarget error", e);

      res.setCode(ReturnCodeMsgEnum.IMPORT_EXCEL_ERROR.getCode());

      res.setMsg(ReturnCodeMsgEnum.IMPORT_EXCEL_ERROR.getMsg());

      res.setData(e.getMessage());

      return res;

    }

  }

}

主要就是使用MultipartFile类型的参数进行文件流的上传。通过file.getOriginalFilename()去获取上传文件的名称。

3、流的处理和excel表格的解析

@Service("fileParserService")

public class FileParserServiceImpl implements FileParserService {

  public static final String EXCEL_2003 = ".xls";

public static final String EXCEL_2007 = ".xlsx";

public static final String COMMA = ",";

private static final Logger log = LoggerFactory.getLogger(FileParserServiceImpl.class);

@Override

public List<String> splitRows(MultipartFile file) throws FileParserServiceException {

  // skip validation

  List<String> rows = null;

  try (InputStream inputStream = file.getInputStream()) {

    String fileName = file.getOriginalFilename();

    Workbook workbook = null;

    if (fileName.endsWith(EXCEL_2003)) {

      workbook = new HSSFWorkbook(inputStream);

    } else if (fileName.endsWith(EXCEL_2007)) {

      workbook = new XSSFWorkbook(inputStream);

    }

    if (workbook != null) {

      rows = new ArrayList<>();

      //sheet页数

      int numOfSheet = workbook.getNumberOfSheets();

      if(numOfSheet>1){

        throw new FileParserServiceException("不支持多个sheet上传");

      }

      for (int i = 0; i < numOfSheet; i++) {

        Sheet sheet = workbook.getSheetAt(i);

        if (sheet == null) continue;

        //行数

        int lastRowNum = sheet.getLastRowNum();

        if (lastRowNum == 0) continue;

        Row row;

        //列数

        short lastCellNum = sheet.getRow(1).getLastCellNum();

        for (int j = 1; j <= lastRowNum; j++) {

          StringBuilder sb = new StringBuilder();

          row = sheet.getRow(j);

          if (row == null) {

            throw new FileParserServiceException("当前文件存在空行,请重新上传");

          }

          String mNum = "";

          Cell cellA = row.getCell(0);

          if (cellA != null) {

            cellA.setCellType(CellType.STRING);

            mNum = cellA.getStringCellValue().trim();

          }

          sb.append(mNum).append(COMMA);

          //遍历每一行

          for (int k = 1; k < lastCellNum; k++) {

            String res = "";

            Cell cell = row.getCell(k);

            if (cell == null) {

              continue;

            }else if(cell.getCellTypeEnum() == CellType.BLANK){

              continue;

            }

            if (cell.getCellTypeEnum() == CellType.NUMERIC) {

              if (DateUtil.isCellDateFormatted(cell)) {

                Date theDate = cell.getDateCellValue();

                short s = cell.getCellStyle().getDataFormat();

                if (s == 0x16) {

                  res = new SimpleDateFormat(Constants.DATE_FORMAT_B).format(theDate);

                } else if (s == 0x12 || s == 0x14) {

                  res = new SimpleDateFormat(Constants.DATE_FORMAT_C).format(theDate);

                } else if (s == 0x13 || s == 0x15 || s == 181 || s == 0x2e || s == 176 || s == 177) {

                  res = new SimpleDateFormat(Constants.DATE_FORMAT_D).format(theDate);

                } else if (s == 0x2d) {

                  res = new SimpleDateFormat(Constants.DATE_FORMAT_E).format(theDate);

                } else {

                  res = new SimpleDateFormat(Constants.DATE_FORMAT_A).format(theDate);

                }

              } else {

                double value = cell.getNumericCellValue();

                if (isInt(value)) {

                  res = String.valueOf(new Double(value).intValue());

                } else if (isLong(value)) {

                  res = new BigDecimal(value).toString();

                } else {

                  res = String.valueOf(value);

                }

              }

              sb.append(res).append(COMMA);

              continue;

            }

            cell.setCellType(CellType.STRING);

            res = cell.getStringCellValue();

            if(res.contains(":")||res.contains(",")){

              throw new FileParserServiceException("当前文件存非法字符(,或:),请重新上传");

            }

            if (res.isEmpty())

              continue;

            sb.append(res).append(COMMA);

          }

          rows.add(sb.substring(0, sb.length() - 1));

        }

      }

    }

  } catch (IOException e) {

    log.error("splitRows error", e);

  }

  return rows;

}

}

（1）流的获取

file.getInputStream()

（2）流转换成Workbook

Workbook workbook = null;

if (fileName.endsWith(EXCEL_2003)) {

workbook = new HSSFWorkbook(inputStream);

} else if (fileName.endsWith(EXCEL_2007)) {

workbook = new XSSFWorkbook(inputStream);

}

（3）解析excel

主要就是获取sheet页，获取行数，获取列数，遍历获取每个单元格。其中还涉及到单元格数字和字符串的解析问题。

三、测试验证

使用postman工具调用接口验证，符合需求。

localhost:8089/testFile/target