欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Java读取Excel文件(支持xls,xlsx,多sheet)

程序员文章站 2022-07-13 12:57:57
...


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);
		 	});
		 });
	}
 }
相关标签: 札记