数据导入导出（POI以及easyExcel）

一.概念：

1.场景需求

        将一些数据库信息导出为Excel表格

        将Excel表格数据导入数据库

        大量数据的导入导出操作

常⽤的解决⽅案为： Apache POI 与阿⾥巴巴 easyExcel

 2.Apache POI介绍

Apache POI 是基于 Office Open XML 标准（ OOXML ）和 Microsoft 的 OLE 2 复合⽂档

格式（ OLE2 ）处理各种⽂件格式的开源项⽬。 简⽽⾔之，您可以使⽤ Java 读写

MS Excel ⽂件，可以使⽤ Java 读写 MS Word 和 MS PowerPoint ⽂件。

 官⽹：https://poi.apache.org/

 模块

1. HSSF － 提供读写 Microsoft Excel XLS 格式 (Microsoft Excel 97 (-2003)) 档案的功

能。

2. XSSF － 提供读写 Microsoft Excel OOXML XLSX 格式 (Microsoft Excel XML (2007+))

档案的功能。

3.SXSSF － 提供低内存占⽤量读写 Microsoft Excel OOXML XLSX 格式档案的功能。

4.HWPF - 提供读写 Microsoft Word DOC97 格式 (Microsoft Word 97 (-2003)) 档案的

功能。

5.XWPF － 提供读写 Microsoft Word DOC2003 格式 (WordprocessingML (2007+)) 档

案的功能。

6.HSLF/XSLF － 提供读写 Microsoft PowerPoint 格式档案的功能。

7.HDGF/XDGF － 提供读 Microsoft Visio 格式档案的功能。

8.HPBF － 提供读 Microsoft Publisher 格式档案的功能。

9.HSMF － 提供读 Microsoft Outlook 格式档案的功能。

POI存在的问题:

        java解析，生成Excel比较有名的框架有 POI ，JXL，但他们有一个严重的问题，就是 非常消耗内存， 也就是说数据量比较大的情况下有的时候会出现OOM（全称“Out Of Memory”，翻译成中文就是“内存用完了”  ）的问题。

但 是POI 有⼀套 SAX 模式的 API 可以⼀定程度的解决⼀些内存溢出的问题，但是依旧没

有完全的解决内存消耗过⼤的问题

OOM详细解析

什么是OOM，为什么会OOM及一些解决方法_Qi_Meng6的博客-CSDN博客_oom

 3.Excel表格 03 与 07 版本区别

POI 可以⽀持不同版本 Excel 表格的操作，对应的版本有 03 和 07+ 版本，那么这两种表

格的区别如下：

03版本：最多⾏数⽀持：65536，并且后缀为.xls

07+版本：理论上没有限制，但实际⽀持⾏数为：1048576，并且后缀为.xlsx

 二：POI操作Excel写

⾸先第⼀件事情，我们需要创建项⽬导⼊对应的 Maven ，这⾥我们需要演示 03 版本

和 07+ 版本，所以两个依赖都需要导⼊

 1.导依赖

    <dependencies>
        <!--   03  -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.1</version>
        </dependency>

        <!-- 07 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.1</version>
        </dependency>
    </dependencies>

2.写 

 接下来我们完成通过POI写⼊Excel表格数据的操作，那么⾸先我们要知道写⼀个表

格需要的步骤：

1.创建工作簿： Workbook

2.创建工作表：sheet

3.创建行：Row

4.创建列（单元格）：Cell

5.具体数据写入

 1.03版本测试：

简单写入  （练习一下即可）

public class ExcelWriteDemo {
    public static void main(String[] args) throws IOException {
        new ExcelWriteDemo().writeExcel03();
    }

    public void writeExcel03() throws IOException {
//      1.  创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
//        2.创建工作表
        HSSFSheet sheet = workbook.createSheet("03版本测试");
//        3.创建行  (创建第一行)
        HSSFRow row1 = sheet.createRow(0);
//        创建单元格 （1,1）
        HSSFCell cell11 = row1.createCell(0);
        cell11.setCellValue("商品ID");

//        创建第一行第二列
        HSSFCell cell12 = row1.createCell(1);
        cell12.setCellValue("商品名称");

//        创建第二行
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        cell21.setCellValue("a1");

        HSSFCell cell22 = row2.createCell(1);
        cell22.setCellValue("精品鼠标");


//        生成一张表  -- IO流
        FileOutputStream outputStream = new FileOutputStream(
                "E:\\java课\\数据导入导出\\练习\\excelPOi\\03版本测试.xls");

//        写入文件
        workbook.write(outputStream);

//        关流 （养成良好习惯）
        outputStream.close();
        System.out.println("文件写入完毕！");
    }
}

 2.07版本测试

//    07 版本测试
    public void writeExcel07() throws IOException {
//      1.  创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
//        2.创建工作表
        Sheet sheet = workbook.createSheet("07版本测试");

        Row row1 = sheet.createRow(0);
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("商品ID");

//        创建表 -- io
        FileOutputStream outputStream = new FileOutputStream(
                "E:\\java课\\数据导入导出\\练习\\excelPOi\\07版本测试.xlsx");
        workbook.write(outputStream);

        outputStream.close();
        System.out.println("07 版本测试完毕！");
    }

 有了上边的基础，这个也简单

 3.实际对象分析

 4.批量写入

那么不管是使⽤ 03 版本还是 07+ 版本，那么我们在实际开发中都会碰到批量写⼊数

据的操作，尤其是数据量⽐较多的时候，那么 03 和 07+ 版本处理的⽅式是不同的

 03版本HSSF：

        最多支持 65536 行数据的写入，超出会报异常。

        操作行为，先将数据放入到缓存中，最后一次写入磁盘，写入速度快

07+版本XSSF

        数据的写入速度会比较慢，因为XSSF是获取全部⾏的数据，因此会消耗⼤

量内存，数据量庞⼤时就有可能会发现内存溢出。

         可以写⼊较⼤的数据，⽐如10W+条数据

 测试： 03测时间和数据

public class ExcelWriteTimeDemo {
    public static void main(String[] args) {
        try {
            new ExcelWriteTimeDemo().textTime03();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

//    03 版本测时间
    public void textTime03() throws IOException {
        long begin = System.currentTimeMillis();
//        创建工作铺
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("03测试");

//        写入数据
        for (int rowNum = 0; rowNum < 65536 ; rowNum++) {//行  多一行就不行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 20; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum+1);
            }
        }
        FileOutputStream outputStream = new FileOutputStream(
                "E:\\java课\\数据导入导出\\练习\\excelPOi\\03版本测时间.xls");
        workbook.write(outputStream);
        outputStream.close();
        long end = System.currentTimeMillis();
        System.out.println("所用时间为：" + (end-begin));
    }
}

 07 测时间和数据

//    07 版本测时间
    public void textTime07() throws IOException {
        long begin = System.currentTimeMillis();
//        创建工作铺
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("03测试");

//        写入数据
        for (int rowNum = 0; rowNum < 65536 ; rowNum++) {//行  多一行就不行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 20; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum+1);
            }
        }
        FileOutputStream outputStream = new FileOutputStream(
                "E:\\java课\\数据导入导出\\练习\\excelPOi\\07版本测时间.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        long end = System.currentTimeMillis();
        System.out.println("所用时间为：" + (end-begin));
    }

07 比03 慢，但能存数据更多

 5.大数据写SXSSF

为了解决⼤数据量写⼊的问题，所以官⽅提供了 SXSSF 来解决⼤⽂件写⼊问题，

所以它可以写⼊⾮常⼤量的数据，⽐如上百万条数据，并且写⼊速度更快，占

⽤内存更少。

官⽅⽂档翻译： SXSSF 在兼容 XSSF 的同时，能够应对⼤数据量和内存空间有限

的情况。 SXSSF 每次获取的⾏数是在⼀个数值范围内，这个范围被称为 “ 滑动窗

⼝ ” ，在这个窗⼝内的数据均存在于内存中，超出这个窗⼝⼤⼩时，数据会被写

⼊磁盘，由此控制内存使⽤，相⽐较⽽⾔， XSSF 则每次都是获取全部⾏。

窗⼝默认⼤⼩为 100 （可以⾃定义），如果数据为 101 ⾏，那么此时超出了窗⼝

限制，索引值最⼩的⾏会被 “ 刷⼊ ” 磁盘，需要注意， SXSSF 会⾃动分配临时⽂

件，这些临时⽂件需要我们⼿动清除，清除的⽅式是使⽤ dispose() ⽅法中。

⾃定义的⽅式： SXSSFWorkbook wb = new SXSSFWorkbook( ⾃定义窗⼝⼤⼩ );

    //    07 大数据 版本测时间
    public void textBigData07() throws IOException {
        long begin = System.currentTimeMillis();
//        创建工作铺
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("03测试");

//        写入数据
        for (int rowNum = 0; rowNum < 65536 ; rowNum++) {//行  多一行就不行
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 20; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum+1);
            }
        }
        FileOutputStream outputStream = new FileOutputStream(
                "E:\\java课\\数据导入导出\\练习\\excelPOi\\07大数据版本测时间.xlsx");
        workbook.write(outputStream);
        outputStream.close();

//        清除临时文件
        ((SXSSFWorkbook) workbook).dispose();

        long end = System.currentTimeMillis();
        System.out.println("所用时间为：" + (end-begin));
    }

 同样的数据量明显速度要⾼于XSSF，

同时它也可以写⼊更多的数据

 三：POI读数据

其实读取⽤到的对象和写⼊的对象都是⼀样的：

        ⼯作簿：WorkBoot （所有的Excel ⼯作簿的操作通过此类型都可以来进⾏设置）

                03 HSSF

                07+ XSSF

        数据表：Sheet （所以关于数据表的操作通过 Sheet 都可以进⾏设置）

        ⾏：ROW

        单元格：Cell

 具体步骤：

        利用文件流来进行读取

1.获取工作簿

2.获取表

3.获取行

4.获取单元格

5.读取数据

 03版本简单读取：

public class ExcelReadDemo {
    public static void main(String[] args) throws IOException {
        read03();
    }

//    读取Excel
    public static void read03() throws IOException {
        FileInputStream is = new FileInputStream(
                "E:\\java课\\数据导入导出\\练习\\excelPOi\\03版本测试.xls");

//        获取工作簿
        HSSFWorkbook workbook = new HSSFWorkbook(is);
//        获取表
        HSSFSheet sheet = workbook.getSheetAt(0);
//        获取行
        HSSFRow row1 = sheet.getRow(0);
        HSSFCell cell11 = row1.getCell(0);
        String value = cell11.getStringCellValue();
        is.close();
        System.out.println(value);
    }
}

07版本简单读取：

    //    读取Excel
    public static void read07() throws IOException {
        FileInputStream is = new FileInputStream(
                "E:\\java课\\数据导入导出\\练习\\excelPOi\\07版本测时间.xlsx");

//        获取工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(is);
//        获取表
        Sheet sheet = workbook.getSheetAt(0);
//        获取行
        Row row1 = sheet.getRow(0);
        Cell cell11 = row1.getCell(0);
//        String value = cell11.getStringCellValue();
        double value = cell11.getNumericCellValue();
        is.close();
        System.out.println(value);
    }

 批量数据读取

在我们实际开发中，经常会碰到读取不同类型的数据，这也是我们⼯作中最麻烦的

操作，接下来我们来⼀起研究

 

 那么我们想读取这张表，需要⾸先读取标题部分，然后再来读取具体数据部分，这

两步⼀定是要分开的

public class ExcelReadBigDemo {
    public static void main(String[] args) throws IOException {
        piLiangReadDemo();
    }

//    批量数据的读取
    public static void piLiangReadDemo() throws IOException {
        FileInputStream inputStream = new FileInputStream(
                "E:\\java课\\数据导入导出\\练习\\excelPOi\\批量数据读取测试.xls");
//        创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
//        读取第一行的数据
        HSSFRow title = sheet.getRow(0);

        if(title != null){
//            获取单元格的数量
            int numberOfCells = title.getPhysicalNumberOfCells();
            System.out.println("单元格的数量为：" + numberOfCells);
            for (int i = 0; i < numberOfCells; i++) {
//                获取所有单元格
                HSSFCell cell = title.getCell(i);
                if(cell != null){
                    String value = cell.getStringCellValue();
                    System.out.print(value + "|");
                }
            }
            System.out.println();
        }
//        获取标题以下的数据
        int rowNum = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < rowNum; i++) {
            Row row = sheet.getRow(i);
            if(row != null){
                int cellNum = row.getPhysicalNumberOfCells();
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    if(cell != null){
                        CellType cellType = cell.getCellType();
                        String cellVal = "";
                        switch (cellType){
                            case STRING: //字符串
                                cellVal = cell.getStringCellValue();
                                System.out.println("字符串类型");
                                break;
                            case NUMERIC://数值类型
//                                    判断是否为日期
                                if(DateUtil.isCellDateFormatted(cell)){
                                    System.out.println("日期类型");
                                    Date date = cell.getDateCellValue();
                                    cellVal = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                }else {
                                    cellVal = cell.toString();
                                    System.out.println("数值类型");
                                }
                                break;
                            case BLANK:
                                System.out.println("空白类型");
                            case BOOLEAN:
                                cellVal = String.valueOf(cell.getBooleanCellValue());
                                System.out.println("布尔类型");
                                break;
                            case ERROR:
                                System.out.println("错误格式");
                                break;
                        }
                        System.out.println(cellVal);
                    }
                }
            }
        }
        inputStream.close();
    }

}

 以上写代码太复杂，可以找工具类，直接复制粘贴

 四：Excel操作--EasyExcel

 1.概述：

EasyExcel 是⼀个基于 Java 的简单、省内存的读写 Excel 的开源项⽬。在尽可能节约内

存的情况下⽀持读写百 M 的 Excel 。

github 地址： https://github.com/alibaba/easyexcel

官⽅⽹站： https://easyexcel.opensource.alibaba.com/

总结：快速、简单避免 OOM 的 Java 处理 Excel ⼯具

 注意：这个⼯具操作⽅法官⽅⽂档提供的⾮常详细，甚⾄连不通的需求如何写的代

码都有对应案例，所以这⾥我们只讲⼀下基础的使⽤写⼊和读取的操作，剩下的请

参考官⽅⽂档

 2.具体操作：

1.引入依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.75</version>
        </dependency>

 简单的写数据

/**
 * @author Zzhenhua
 * @project kuang-poi
 * @date 2023/1/13 15:05
 * @description TODO
 */
public class EasyDemo {
    public static void main(String[] args) {
        new EasyDemo().simpleWrite();
    }
    private List<DemoData> data() {
        List<DemoData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }



    /**
     * 最简单的写
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 直接写即可
     */
    public void simpleWrite() {
        // 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入

        // 写法1 JDK8+
        // since: 3.0.0-beta1
//        String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        String fileName = "E:\\java课\\数据导入导出\\练习\\excelPOi\\easyExcel.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class)
                .sheet("模板")
                .doWrite(data());

//        // 写法2
//        fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
//        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
//        // 如果这里想使用03 则 传入excelType参数即可
//        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
//
//        // 写法3
//        fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
//        // 这里 需要指定写用哪个class去写
//        try (ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build()) {
//            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
//            excelWriter.write(data(), writeSheet);
//        }
    }

}

@Data
@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;

    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

 简单的读

package com.hua.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.hua.pojo.DemoData;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;

public class EasyExcelImportMain {
 
    public static void main(String[] args) throws Exception {
 
        // 创建一个输入流，将 Excel 文件读取出来
        InputStream inputStream = new FileInputStream(
                "E:\\java课\\数据导入导出\\练习\\excelPOi\\easyExcel.xlsx");
 
        List<DemoData> tmpList = EasyExcel.read(inputStream)
                // 设置与Excel表映射的类
                .head(DemoData.class)
                // 设置sheet,默认读取第一个
                .sheet()
                // 设置标题所在行数
                .headRowNumber(1)
                // 异步读取
                .doReadSync();
 
 
        for (DemoData tmpDate : tmpList) {
            System.out.println(tmpDate);
        }
    }
}

案例给前端返回excel数据

 

   @GetMapping("/hotel/export")
    public void export(HttpServletResponse response) throws IOException {
        //获取需要导出的数据
        List<Hotel> hotels = hotelService.find();
        //excel文件名
        final String FILENAME = "酒店信息";
        //sheetName
        final String SHEETNAME = "酒店信息表";
        //获取model对象类
        Class hotel = Hotel.class;

        try {
            //表头样式策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置头居中
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
            //初始化表格样式
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

//                response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(FILENAME, "UTF-8").replaceAll("\\+", "%20");
            //响应首部 Access-Control-Expose-Headers 就是控制“暴露”的开关，它列出了哪些首部可以作为响应的一部分暴露给外部。
            //此处设置了开放Content-Disposition，前端可获取该响应参数获取文件名称
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), hotel).autoCloseStream(Boolean.FALSE)
                    .registerWriteHandler(horizontalCellStyleStrategy).sheet(SHEETNAME).doWrite(hotels);

        } catch (IOException e) { //下载失败情况的处理
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

最后postman测试返回数据

 

 

 

完成！