Java读取Excel文件(支持xls,xlsx,多sheet)
1. pom.xml依赖
<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>
2. 工具类封装
public class ExcelReadUtil { private static Logger logger = LoggerFactory.getLogger(ExcelReadUtil.class); public static HashMap<String, ArrayList<ArrayList<String>>> readExcel(File file, int ignoreRow) { if (file.getName().toLowerCase().endsWith(".xlsx")) { return readExcelForXlsx(file, ignoreRow); } else if (file.getName().toLowerCase().endsWith(".xls")) { return readExcelForXls(file, ignoreRow); } return null; } /** * 读取Excel xlsx后缀名文件数据 * * @param file */ private static HashMap<String, ArrayList<ArrayList<String>>> readExcelForXlsx(File file, int ignoreRow) { HashMap<String, ArrayList<ArrayList<String>>> map = new HashMap<>(); if (!file.exists()) { logger.error("{}文件不存在", file.getName()); return null; } int rowSize = 0; try (BufferedInputStream in = new BufferedInputStream(new FileInputStream(file))) { XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(in); } catch (IOException e) { e.printStackTrace(); } XSSFCell cell = null; for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) { XSSFSheet sheet = workbook.getSheetAt(sheetIndex); ArrayList<ArrayList<String>> lists = new ArrayList<>(); for (int rowIndex = ignoreRow; rowIndex <= sheet.getLastRowNum(); rowIndex++) { XSSFRow row = sheet.getRow(rowIndex); if (null == row) { continue; } int tempRowSize = row.getLastCellNum() + 1; if (tempRowSize > rowSize) { rowSize = tempRowSize; } ArrayList<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 { value = String.valueOf(new DecimalFormat("0").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; } /** * 读取excel xls后缀名文件 * * @param file * @param ignoreRow * @return */ private static HashMap<String, ArrayList<ArrayList<String>>> readExcelForXls(File file, int ignoreRow) { HashMap<String, ArrayList<ArrayList<String>>> map = new HashMap<>(); if (!file.exists()) { logger.error("{}文件不存在", file.getName()); return null; } int rowSize = 0; try { BufferedInputStream bufferedInputStream = new BufferedInputStream(new FileInputStream(file)); HSSFWorkbook workbook = new HSSFWorkbook(bufferedInputStream); HSSFCell cell = null; for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) { HSSFSheet sheet = workbook.getSheetAt(sheetIndex); ArrayList<ArrayList<String>> lists = new ArrayList<>(); for (int rowIndex = ignoreRow; rowIndex < sheet.getLastRowNum(); rowIndex++) { HSSFRow row = sheet.getRow(rowIndex); if (null == row) { continue; } int tempRowSize = row.getLastCellNum() + 1; if (tempRowSize > rowSize) { rowSize = tempRowSize; } ArrayList<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 { value = String.valueOf(new DecimalFormat("0").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; } }
3. 测试类
@Test public void testExcelRead(){ HashMap<String, ArrayList<ArrayList<String>>> excelReadMap = ExcelReadUtil.readExcel(new File(excelFilePath), 1); if(excelReadMap != null){ excelReadMap.entrySet().stream().forEach(entry -> { entry.getValue().stream().forEach(col -> { col.stream().forEach(System.out::println); }); }); } }
原文链接:
https://blog.csdn.net/superbeyone/article/details/89158908
优秀不够,你是否无可替代
软件测试交流QQ群:721256703,期待你的加入!!
欢迎关注我的微信公众号:软件测试君
![](https://www.cnblogs.com/images/cnblogs_com/longronglang/1061549/o_QQ%E6%88%AA%E5%9B%BE20190728134401.jpg)