一、需求说明
通过接口上传一个姓名号码表 ， 返回一个"姓名，号码"格式的的一个String数组 。
二、功能实现
1、pom.xml中引入依赖
org.apache.poi poi 4.1.2 org.apache.poi poi-ooxml 4.1.2 使用poi去解析excel ， poi对应的是excel2003，poi-ooxml对应的是excel2007 。
2、实现上传接口
@RestController@RequestMapping("/testFile")public class FIleUpDownLoadController {@Autowiredprivate FileParserService fileParserService;@ApiOperation(value = "https://www.itzhengshu.com/jiqiao/上传excel文件")@RequestMapping(value = "https://www.itzhengshu.com/target", method = RequestMethod.POST)@ResponseBodypublic 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;}//接收文件流和解析excelList 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);@Overridepublic List splitRows(MultipartFile file) throws FileParserServiceException {// skip validationList 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 = https://www.itzhengshu.com/jiqiao/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 =