Excel

• 03版xls 最多65536行
• 07版xlsx 没有限制

POI

apache

• HSSF -读写Microsoft Excel 03版本
• XSSF -读写Microsoft Excel OOXML 07版本
• HWPF -读写Microsoft Word
• HSLF -读写Microsoft PowerPoint
• HDGF -读写Microsoft VIsio

---

代码

pom.xml

<dependencies>
        <!--xls(03)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

        <!--xlsx(07)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

        <!--日期格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>

        <!--test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

    </dependencies>

03版本的导出

• 优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
• 缺点：最多处理65536行，否则会抛出异常

/**
     * 03版本测试
     * 工作薄、工作表、行、列
     */
    @Test
    public void testWrite03() throws Exception {
        //1、创建一个工作薄
        Workbook workbook = new HSSFWorkbook();
        //2、创建一个工作表
        Sheet sheet = workbook.createSheet("这是一个工作表03版");
        //3、创建一个行
        Row row1 = sheet.createRow(0);

        //4、创建一个单元格
        Cell cell11 = row1.createCell(0);
        //5、写入数据 (1,1)
        cell11.setCellValue("这是一个单元格");

        Cell cell12 = row1.createCell(1);
        //（1，2）
        cell12.setCellValue(123456);

        Cell cell13 = row1.createCell(2);
        //（1，3）
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell13.setCellValue(time);

        //生成一张表（IO流）
        FileOutputStream fileOutputStream = new FileOutputStream(path+"POI表格测试03版.xls");
        //输出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("excel文件生成完毕");
    }

07版本的导出

• 优点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，数据量太大时，比如1kw条
• 缺点：可以写入比较大的数据量，比如20w条

/**
 * 07版本测试
 * 工作薄、工作表、行、列
 */
@Test
public void testWrite07() throws Exception {
    //1、创建一个工作薄
    Workbook workbook = new XSSFWorkbook();
    //2、创建一个工作表
    Sheet sheet = workbook.createSheet("这是一个工作表07版");
    //3、创建一个行
    Row row1 = sheet.createRow(0);

    //4、创建一个单元格
    Cell cell11 = row1.createCell(0);
    //5、写入数据 (1,1)
    cell11.setCellValue("这是一个单元格");

    Cell cell12 = row1.createCell(1);
    //（1，2）
    cell12.setCellValue(123456);

    Cell cell13 = row1.createCell(2);
    //（1，3）
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell13.setCellValue(time);

    //生成一张表（IO流）
    FileOutputStream fileOutputStream = new FileOutputStream(path+"POI表格测试07版.xlsx");
    //输出
    workbook.write(fileOutputStream);
    //清除临时文件
    ((SXSSFWorkbook )workbook).dispose();
    //关闭流
    fileOutputStream.close();
    System.out.println("excel文件生成完毕");
}

07版本的导出-优化SXSSF

优点：可以写非常大的数据量，比如100w或者更多，写数据更快，占用内存更少

原理：

• 过程中生成临时文件，需要清理临时文件
• 默认由100条记录被保存在内存中，如果超过，则最前面的数据写入临时文件
• 可以自定义数据量new SXSSFWorkbook(数量)
• 如果正在使用合并区域、注释，任然只能存储内存中，如果广泛使用，也可能需要大量内存

/**
     * 07版本测试优化
     * 工作薄、工作表、行、列
     */
    @Test
    public void testWrite07Super() throws Exception {
        //1、创建一个工作薄
        Workbook workbook = new SXSSFWorkbook();
        //2、创建一个工作表
        Sheet sheet = workbook.createSheet("这是一个工作表07优化版");
        //3、创建一个行
        Row row1 = sheet.createRow(0);

        //4、创建一个单元格
        Cell cell11 = row1.createCell(0);
        //5、写入数据 (1,1)
        cell11.setCellValue("这是一个单元格");

        Cell cell12 = row1.createCell(1);
        //（1，2）
        cell12.setCellValue(123456);

        Cell cell13 = row1.createCell(2);
        //（1，3）
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell13.setCellValue(time);

        //生成一张表（IO流）
        FileOutputStream fileOutputStream = new FileOutputStream(path+"POI表格测试07优化版.xlsx");
        //输出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("excel文件生成完毕");
    }

03版的读取

 /**
     * 03版本测试
     * 工作薄、工作表、行、列
     */
    @Test
    public void testWrite03() throws Exception {
        //1、创建一个工作薄
        Workbook workbook = new HSSFWorkbook();
        //2、创建一个工作表
        Sheet sheet = workbook.createSheet("这是一个工作表03版");
        //3、创建一个行
        Row row1 = sheet.createRow(0);

        //4、创建一个单元格
        Cell cell11 = row1.createCell(0);
        //5、写入数据 (1,1)
        cell11.setCellValue("这是一个单元格");

        Cell cell12 = row1.createCell(1);
        //（1，2）
        cell12.setCellValue(123456);

        Cell cell13 = row1.createCell(2);
        //（1，3）
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell13.setCellValue(time);

        //生成一张表（IO流）
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"POI表格测试03版.xls");
        //输出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("excel文件生成完毕");
    }

07版本的读取

/**
     * 07版本测试
     * 工作薄、工作表、行、列
     */
    @Test
    public void testWrite07() throws Exception {
        //1、创建一个工作薄
        Workbook workbook = new XSSFWorkbook();
        //2、创建一个工作表
        Sheet sheet = workbook.createSheet("这是一个工作表07版");
        //3、创建一个行
        Row row1 = sheet.createRow(0);

        //4、创建一个单元格
        Cell cell11 = row1.createCell(0);
        //5、写入数据 (1,1)
        cell11.setCellValue("这是一个单元格");

        Cell cell12 = row1.createCell(1);
        //（1，2）
        cell12.setCellValue(123456);

        Cell cell13 = row1.createCell(2);
        //（1，3）
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell13.setCellValue(time);

        //生成一张表（IO流）
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"POI表格测试07版.xlsx");
        //输出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("excel文件生成完毕");
    }

读取表中所有内容

public void testCell()throws Exception{
        //获取文件流
        FileInputStream inputStream = new FileInputStream(PATH+"POI表格测试07版.xlsx");

        //1、创建一个工作薄
        Workbook workbook = new XSSFWorkbook(inputStream);

        //拿到表中的计算
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

        int sheetNum = workbook.getNumberOfSheets();

        for (int i=0;i<sheetNum;i++){
            Sheet sheet = workbook.getSheetAt(i);
            int rowNum = sheet.getPhysicalNumberOfRows();
            for (int rowIndex =0; rowIndex<rowNum;rowIndex++){
                Row row = sheet.getRow(rowIndex);
                int cellNum = row.getPhysicalNumberOfCells();
                for (int cellIndex=0;cellIndex<cellNum;cellIndex++){
                    Cell cell = row.getCell(cellIndex);
                    String cellValue = getCellValueStringWithCell(cell,formulaEvaluator);
                    System.out.println(cellValue);
                }
            }
        }
        inputStream.close();
    }

    /**
     * 获取表格的内容
     * @param cell - cell
     * @param formulaEvaluator - 公式转换
     * @return -返回结果都转换成string
     */
    public String getCellValueStringWithCell(Cell cell,FormulaEvaluator formulaEvaluator){
        String cellValue = "";
        switch (cell.getCellType()) {
            //0 数字（日期、普通数字）
            case Cell.CELL_TYPE_NUMERIC :{
                //日期
                if (HSSFDateUtil.isCellDateFormatted(cell)){
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = simpleDateFormat.format(cell.getDateCellValue());
                }
                else {
                    //不是日期格式，防止数字过长
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cellValue =  String.valueOf(cell.toString());
                }
                break;
            }
            //1 字符串
            case Cell.CELL_TYPE_STRING:{
                cellValue = cell.getStringCellValue();
                break;
            }
            //2  公式
            case Cell.CELL_TYPE_FORMULA:{
                String formual =  cell.getCellFormula()+"";
                System.out.println(formual);
                CellValue value = formulaEvaluator.evaluate(cell);
                cellValue = value.formatAsString();
                break;
            }
            //3 空
            case Cell.CELL_TYPE_BLANK:{
                System.out.println("空");
                break;
            }
            //4 布尔值
            case Cell.CELL_TYPE_BOOLEAN:{
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            }
            //5 错误
            case Cell.CELL_TYPE_ERROR:{
                System.out.println("数据类型错误");
                break;
            }
        }
        return cellValue;
    }

EasyExcel

API文档 https://www.yuque.com/easyexcel/doc/easyexcel

在poi的基础上封装，不会出现内存溢出，使用更简单

具体用法可以看API文档

引用

<dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
        </dependency>

        <!--日期格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.20</version>
        </dependency>

        <!--test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

对比

image-20200530092505594.png