同时支持xls与xlsx后缀
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-compress</artifactId> <version>1.18</version> </dependency> <dependency> <groupId>org.apache.ant</groupId> <artifactId>ant</artifactId> <version>1.9.7</version> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.ant</groupId> <artifactId>ant</artifactId> <version>1.9.7</version> <scope>compile</scope> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.8.1</version> </dependency> public class ExcelReadUtil { /** * excel读取 * @param file 文件 * @param ignoreRow 忽略行数 * @return */ public static Map<String, List<List<String>>> readExcel(File file, Integer ignoreRow) { try { return readExcel(file, ignoreRow, null); } catch (Exception e) { e.printStackTrace(); return new HashMap<>(); } } /** * excel读取 * @param file 文件 * @param ignoreRow 忽略行数 * @param limit 读取行数 * @return */ public static Map<String, List<List<String>>> readExcel(File file, int ignoreRow, Integer limit) { try { Workbook workbook = WorkbookFactory.create(new FileInputStream(file)); return readExcel(workbook, ignoreRow, limit); } catch (Exception e) { return new HashMap<>(); } } /** * 读取excel 文件 * * @param workbook * @param ignoreRow * @return */ private static Map<String, List<List<String>>> readExcel(Workbook workbook, int ignoreRow, Integer limit) throws Exception { Map<String, List<List<String>>> map = new HashMap<>(); int rowSize = 0; try { Cell cell = null; for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) { Sheet sheet = workbook.getSheetAt(sheetIndex); List<List<String>> lists = new ArrayList<>(); int rowNum; if (limit == null) { rowNum = sheet.getLastRowNum(); } else { rowNum = 1; } for (int rowIndex = ignoreRow; rowIndex <= rowNum; rowIndex++) { Row row = sheet.getRow(rowIndex); if (null == row) { continue; } int tempRowSize = row.getLastCellNum() + 1; if (tempRowSize > rowSize) { rowSize = tempRowSize; } List<String> list = new ArrayList<>(); int col = 0; for (int colIndex = 0; colIndex <= row.getLastCellNum(); colIndex++) { cell = row.getCell(colIndex); String value = ""; if (cell != null) { CellType cellType = cell.getCellType(); switch (cellType) { case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = String.valueOf(cell.getDateCellValue()); } else { DecimalFormat df = new DecimalFormat("0.000000000000"); value = subZeroAndDot(String.valueOf(df.format(cell.getNumericCellValue()))); } break; case STRING: value = String.valueOf(cell.getStringCellValue()); break; case FORMULA: value = String.valueOf(cell.getCellFormula()); break; case BLANK: value = ""; break; case BOOLEAN: value = String.valueOf(cell.getBooleanCellValue()); break; case ERROR: value = String.valueOf(cell.getErrorCellValue()); break; default: value = ""; } if (StringUtils.isNotBlank(value)) { list.add(value); } else { col++; } } } if (col == row.getRowNum()) { continue; } if (list.size() > 0) { lists.add(list); } } map.put("sheet" + sheetIndex, lists); } } catch (Exception e) { e.printStackTrace(); } return map; } private static String subZeroAndDot(String s) { if (s.indexOf(".") > 0) { s = s.replaceAll("0+?$", "");//去掉多余的0 s = s.replaceAll("[.]$", "");//如最后一位是.则去掉 } return s; } }