读取页面上传的excel文件解析并储存到后台mysql数据库

1、第一步，先将源文件的文件名拿出来 file.getOriginalFilename(); (后台接收到的file类型为MultipartFile)

2、第二步，拿到excel文件中的每个表里的内容

List<String> idList = excelService.getIdLis tByExcel(file.getInputStream(), fileOriginalName);

通过文件名的后缀去判断创建哪种类型的工作簿，是xls还是xlsx

Workbook work = this.getWorkbook(in, fileName); public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception { Workbook workbook = null; String fileType = fileName.substring(fileName.lastIndexOf(".")); if (".xls".equals(fileType)) { workbook = new HSSFWorkbook(inStr); } else if (".xlsx".equals(fileType)) { workbook = new XSSFWorkbook(inStr); } else { throw new Exception("请上传excel文件！"); } return workbook; }

工作簿跟sheet表是有区别的，一个excel工作簿中可能包含多个sheet表

创建好工作簿后，对里面的sheet进行遍历，并继续遍历每一行的每一个单元格进行读取数据

Sheet sheet = null; Row row = null; Cell cell = null; for (int iSheet = 0; iSheet < work.getNumberOfSheets(); iSheet++) { sheet = work.getSheetAt(iSheet); if (sheet == null) { continue; } for (int iRow = sheet.getFirstRowNum(); iRow <= sheet.getLastRowNum(); iRow++) { row = sheet.getRow(iRow); if (row == null || row.getFirstCellNum() == iRow) { continue; } List<String> list = new ArrayList<>(); for (int iCell = row.getFirstCellNum(); iCell < row.getLastCellNum(); iCell++) { cell = row.getCell(iCell); String value = ""; int cellType = cell.getCellType(); switch (cellType) { case ExcelCellType.STRING: value = cell.getRichStringCellValue().getString(); break; case ExcelCellType.NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = cell.getDateCellValue().toString(); } else { value = cell.getNumericCellValue() + ""; BigDecimal bigDecimal = new BigDecimal(value); value = bigDecimal.toPlainString(); } break; case ExcelCellType.BOOLEAN: value = cell.getBooleanCellValue() + ""; break; case ExcelCellType.FORMULA: value = cell.getCellFormula(); break; case ExcelCellType.BLANK: value = ""; break; default: value = ""; } list.add(value); }

对于这个解析读取是有固定的格式的，它每一种类型的对应关系可以在cell类源码中看到，在此不进行过多撰述。

最后，将两部分的完整代码整理一下

//根据格式创建与之对应的工作簿 public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception { Workbook workbook = null; String fileType = fileName.substring(fileName.lastIndexOf(".")); if (".xls".equals(fileType)) { workbook = new HSSFWorkbook(inStr); } else if (".xlsx".equals(fileType)) { workbook = new XSSFWorkbook(inStr); } else { throw new Exception("请上传excel文件！"); } return workbook; } //拿到工作簿中的所有内容，根据不同的类型有不同的取值方法，这个是固定的 public List<String> getIdListByExcel(InputStream in, String fileName) throws Exception { List<String> allList = new ArrayList<>(); // 创建Excel工作薄 Workbook work = this.getWorkbook(in, fileName); if (null == work) { throw new Exception("创建Excel工作薄为空！"); } Sheet sheet = null; Row row = null; Cell cell = null; for (int iSheet = 0; iSheet < work.getNumberOfSheets(); iSheet++) { sheet = work.getSheetAt(iSheet); if (sheet == null) { continue; } for (int iRow = sheet.getFirstRowNum(); iRow <= sheet.getLastRowNum(); iRow++) { row = sheet.getRow(iRow); if (row == null || row.getFirstCellNum() == iRow) { continue; } List<String> list = new ArrayList<>(); for (int iCell = row.getFirstCellNum(); iCell < row.getLastCellNum(); iCell++) { cell = row.getCell(iCell); String value = ""; int cellType = cell.getCellType(); switch (cellType) { case ExcelCellType.STRING: value = cell.getRichStringCellValue().getString(); break; case ExcelCellType.NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = cell.getDateCellValue().toString(); } else { value = cell.getNumericCellValue() + ""; BigDecimal bigDecimal = new BigDecimal(value); value = bigDecimal.toPlainString(); } break; case ExcelCellType.BOOLEAN: value = cell.getBooleanCellValue() + ""; break; case ExcelCellType.FORMULA: value = cell.getCellFormula(); break; case ExcelCellType.BLANK: value = ""; break; default: value = ""; } list.add(value); } allList.addAll(list); } } work.close(); return allList; }