Java操作Excel的几种方式(内有示例代码)

Poi

<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>3.17</version>
 </dependency>
 //下面是07(xlsx)版本的，上面是03(xls)
 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>3.17</version>
 </dependency>

Workbook接口的四个实现类

HSSF：Excel97-2003版本，扩展名为.xls。一个sheet最大行数65536，最大列数256。
XSSF：Excel2007版本开始，扩展名为.xlsx。一个sheet最大行数1048576，最大列数16384。
SXSSF：是在XSSF基础上，POI3.8版本开始提供的支持低内存占用的操作方式，扩展名为.xlsx。

生成Excel表格

HSSFWorkbook

 String Path = "D:\\project\\java_project\\idea\\Java_excel\\src\\main";

    @Test
    public void testWriter03() throws Exception {
        //1.创建一个工作簿   HSSFWorkbook  03版    XSSFWorkbook  07版
        Workbook workbook = new HSSFWorkbook();
        //Workbook workbook = new XSSFWorkbook();
        //2.创建一个工作表
        Sheet sheet = workbook.createSheet("carroll学习Java");
        //3.创建行   0代表第一行
        Row row1 = sheet.createRow(0);
        //4.c创建列
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日学习时长");

        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(666);

        //创建第二行
        Row row2 = sheet.createRow(1);
        //创建列
        Cell cell21 = row2.createCell(0);
        cell11.setCellValue("总计时长");

        Cell cell22 = row2.createCell(1);
        cell12.setCellValue(666);

        FileOutputStream outputStream = new FileOutputStream(Path+"test03.xls");
        //FileOutputStream outputStream = new FileOutputStream(Path+"test07.xlsx");
        workbook.write(outputStream);

        outputStream.close();

        System.out.println("文件生成完毕");

    }

结果

读取Excel表格

String Path = "D:\\project\\java_project\\idea\\Java_excel\\src\\";

@Test
public void testReadCellType() throws Exception {

    FileInputStream inputStream = new FileInputStream(Path + "maintest03.xls");

    Workbook workbook = new HSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);
    //获取标题内容
    Row rowTitle = sheet.getRow(0);

    //获取到计算公式
    HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

    if (rowTitle != null) {
        //获得所有列的内容
        int cellCount = rowTitle.getPhysicalNumberOfCells();
        for (int cellNum = 0; cellNum < cellCount; cellNum++) {
            Cell cell = rowTitle.getCell(cellNum);
            if (cell != null) {
                CellType cellType = cell.getCellTypeEnum();
                String cellValue = cell.getStringCellValue();
                System.out.print(cellValue + " | ");
            }
        }
        System.out.println();
    }

    //获取表中的内容
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int rowNum = 1; rowNum < rowCount; rowNum++) {
        Row rowData = sheet.getRow(rowNum);
        if (rowData != null) {
            //读取列
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");

                Cell cell = rowData.getCell(cellNum);
                //匹配列的类型
                if (cell != null) {
                    CellType cellType = cell.getCellTypeEnum();
                    switch (cellType) {
                        case STRING:
                            System.out.print("字符串：" + cell.getStringCellValue());
                            break;
                        case BOOLEAN:
                            System.out.print("布尔：" + cell.getBooleanCellValue());
                            break;
                        case NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                System.out.println("日期格式：" + new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd HH:mm:ss"));
                                break;
                            } else {
                                System.out.print("整形：" + cell.getNumericCellValue());
                                break;
                            }
                        case BLANK:
                            System.out.print("空");
                            break;
                        case ERROR:
                            System.out.print("数据类型错误");
                            break;
                        case FORMULA:
                            String formula = cell.getCellFormula();
                            System.out.println("公式:" + formula);

                            //
                            CellValue evaluate = formulaEvaluator.evaluate(cell);
                            String cellValue = evaluate.formatAsString();
                            System.out.println(cellValue);
                            break;
                        default:
                            break;
                    }
                }
            }

            inputStream.close();

        }
    }
}

EasyExcel

<!--注意它里面自带poi依赖，如果重复带入会报ClassNotfound-->
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>2.2.0-beta2</version>
 </dependency>

• 具体查看文档：https://www.yuque.com/easyexcel/doc/easyexcel
• 官方GItHub：https://github.com/alibaba/easyexcel

你知道的越多，你不知道的越多。
有道无术，术尚可求，有术无道，止于术。
如有其它问题，欢迎大家留言，我们一起讨论，一起学习，一起进步