Java读取Excel文件（支持xls，xlsx，多sheet）

本文主要是介绍Java读取Excel文件（支持xls，xlsx，多sheet），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

---

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);});});}}

这篇关于Java读取Excel文件（支持xls，xlsx，多sheet）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---