一文搞定POI，再也不怕excel导入导出了

合集 - Java后端开发(3)

1.Java中的字符串2023-05-182.IntelliJ IDEA上手这一篇就够了，从入门到上瘾2023-05-22

3.一文搞定POI，再也不怕excel导入导出了03-11

收起

写在前面

在Java日常开发过程中，实现Excel文件的导入导出功能是一项常见的需求。

通过使用相关的Java库，如Apache POI、EasyPoi或EasyExcel，可以轻松地实现Excel文件的读写操作。

而这篇文章将介绍如何在Java中使用Apache POI、EasyPoi 和EasyExcel库来进行Excel文件的导入和导出操作，帮助您快速掌握这一实用的技能。

一、使用场景

下面是excel导入导出的几个最常用的使用场景。

①企业管理系统：企业管理系统通常需要导入员工信息、客户信息、销售数据等大量数据到系统中，以及导出报表、数据分析结果等信息。

②学校教务系统：学校教务系统可能需要导入学生信息、课程表、成绩等数据，以及导出学生成绩单、教师工资表等信息。

③电子商务平台：电子商务平台需要导入商品信息、订单数据等内容，以及导出销售报表、库存清单等信息，方便管理和分析。

④ 人力资源管理系统：人力资源管理系统需要导入员工档案、薪资信息等数据，以及导出薪资条、考勤报表等信息，方便人力资源管理和工资结算。

⑤ 医院信息系统：医院信息系统可能需要导入患者信息、医疗记录等数据，以及导出医疗报告、统计分析等信息，方便医护人员进行医疗服务和管理。

以上仅是一些常见的使用场景，实际上各种系统中的Excel导入导出功能可以根据具体需求进行定制和扩展

二、三个库简介

1、Apache POI

Apache POI是一个流行的Java库，用于处理Microsoft Office格式文件，包括Excel、Word和PowerPoint。它提供了丰富的API，可以创建、读取和修改各种类型的Office文档。

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

优点：

1. 功能强大：支持处理复杂的Excel文件，包括单元格、样式、图表等内容。

2. 稳定性高：作为一个成熟的开源项目，得到广泛支持和持续维护。

3. 灵活性：可以满足各种定制化需求，可以实现复杂的Excel处理功能。

缺点：

1. 学习曲线较陡：对于初学者来说，学习成本可能较高。
2. 性能相对较低：在处理大量数据时，性能可能受到一定影响。

2、EasyPoi

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法

官网： https://gitee.com/lemur/easypoi

优点：

1. 简单易用：EasyPoi 提供了简洁的 API 接口，使得 Excel 操作更加便捷。
2. 功能丰富：支持 Excel 文件的导入导出、模板导出、校验等多种功能。
3. 易于扩展：EasyPoi 基于 Apache POI 和 JexcelApi，可以方便地扩展和定制功能。
4. 文档齐全：EasyPoi 提供了详细的文档和示例，便于开发人员学习和使用。

缺点：

1. 功能有限：相比于 Apache POI，EasyPoi 可能在一些高级功能上有所限制。
2. 可能存在性能问题：由于封装层的存在，EasyPoi 在处理大量数据时可能存在一定的性能损耗。

3、EasyExcel

EasyExcel是一个阿里巴巴基于Apache POI封装的开源框架，专注于Excel文件的读写操作。它提供了简洁易用的API，简化了Excel处理的流程。

官网：https://easyexcel.opensource.alibaba.com/

优点：

1. 简单易用：提供了简洁的API，使用起来非常方便。
2. 高性能：在处理大量数据时具有较高的性能，能够快速导入导出Excel文件。
3. 支持注解：支持使用注解配置Excel文件的导入导出规则，简化了开发过程。

缺点：

1. 功能相对有限：相比Apache POI，功能相对简单，可能无法满足某些复杂的Excel处理需求。
2. 定制化能力较弱：定制化能力不如Apache POI灵活。

三 、各版本Excel 区别

不同版本的Excel在功能和格式上可能会有一些差异。所以后续在处理不同版本的excel时，会有少许不同

以下是一些常见的Excel版本之间的区别

1、Excel 97-2003（.xls）

• 最大行数为65536行，最大列数为256列。
• 支持的最大单元格格式有限。
• 不支持新的Excel特性，如条件格式、表格样式等。
• 文件大小限制为2GB。

2、Excel 2007及以上版本（.xlsx）

• 最大行数和列数均有较大提升，支持数百万行数和16384列。
• 支持更多的单元格格式和样式。
• 引入了新的功能，如条件格式、表格样式、数据透视表等。
• 支持更多的图表类型和图表样式。
• 文件大小限制较大，最多可达16,384 x 1,048,576个单元格。

四 、Excel 基本结构介绍

Java是面向对象的操作语言，万物皆对象。了解了Excel基本结构有助于我们将Excel与Java中对象关联起来

• 工作簿（Workbook）： Excel文件以工作簿的形式存在，一个工作簿可以包含多个工作表（Sheet）
• 工作表（Sheet）： 每个工作表由行（Row）和列（Column）组成，交叉形成单元格（Cell），用于存储数据、文本、公式等内容
• 单元格（Cell）： Excel中的最小单位，用于存储数据或公式。每个单元格有一个唯一的地址，例如A1、B2等
• 行（Row）和列（Column）： 行是水平方向的一组单元格，列是垂直方向的一组单元格。行用数字标识，列用字母标识
• 公式（Formula）： Excel支持使用公式进行计算和数据处理。公式以等号（=）开头，可以引用其他单元格的数值或内容进行运算
• 函数（Function）： Excel提供了大量的内置函数，用于进行各种复杂的计算和数据处理，如SUM（求和）、AVERAGE（平均值）、VLOOKUP（垂直查找）

五、Apache POI基本操作

由于Excel分为03版本和07版本，所以我们在使用的时候需要

注：

• 处理03版本excel时，主要使用HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell等对象来操作Excel文件；

• 处理07版本及之后版本excel时，主要使用XSSFWorkbook、XSSFSheet、XSSFRow、XSSFCell等对象来操作Excel文件

其他操作基本是一样的，了解这个之后，后续操作就很简单了~

5.1 基本写操作

5.1.1 03版本写excel

① 引入依赖

    org.apache.poi
    poi
    4.1.2
     junit
     junit
     4.13.2
     test
    joda-time
    joda-time
    2.10.10

②实例代码

public class AppTest
{
    String filepath="E:\\xiezhrspace\\excel-demo\\fileoutput\\";
    @Test
    public void poiexcel03Test() throws  Exception{
        //1、创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //2、创建一个工作表
        Sheet sheet = workbook.createSheet("第一个工作表");
 
        //3、创建一行
 
        //3.1 创建第一行
        Row row1 = sheet.createRow(0);
        //3.2 创建第二行
        Row row2 = sheet.createRow(1);
        //3.3 创建第三行
        Row row3 = sheet.createRow(2);
 
        //4 创建一个单元格
        //4.1 创建第一行第一个单元格
        Cell cell11 = row1.createCell(0);
        //4.2 创建第一行第二个单元格
        Cell cell12 = row1.createCell(1);
        //4.3 创建第二行第一个单元格
        Cell cell21 = row2.createCell(0);
        //4.4 创建第二行第二个单元格
        Cell cell22 = row2.createCell(1);
        //4.5 创建第三行第一个单元格
        Cell cell31 = row3.createCell(0);
        //4.6 创建第三行第二个单元格
        Cell cell32 = row3.createCell(1);
 
        // 5 设置单元格的值
        //5.1 设置第一行第一个单元格
        cell11.setCellValue("个人公众号");
        //5.2 设置第一行第二个单元格
        cell12.setCellValue("XiezhrSpace");
 
        //5.3 设置第二行第一个单元格
        cell21.setCellValue("个人博客");
        //5.4 设置第二行第二个单元格
        cell22.setCellValue("www.xiezhr.cn");
 
        //5.5 设置第三行第一个单元格
        cell31.setCellValue("当前时间");
        //5.6 设置第三行第二个单元格
        String curdate = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell32.setCellValue(curdate);
 
 
        FileOutputStream fileOutputStream = new FileOutputStream(filepath + "poiexcel03.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        workbook.close();
 
    }
 
}

③ excel写出效果

5.1.2 07版本写excel

07 版本依赖与处理03版本的有点不一样，代码基本上不变

① 所需依赖

    org.apache.poi
    poi-ooxml
    4.1.2

② 代码修改

07版本操作与03版本操作基本没什么变化，我们只需将03版本代码中new HSSFWorkbook() 修改成new XSSFWorkbook()

new FileOutputStream(filepath + "poiexcel03.xls") 修改成new FileOutputStream(filepath + "poiexcel07.xlsx") 即可

 Workbook workbook = new XSSFWorkbook();
 ...省略
 FileOutputStream fileOutputStream = new FileOutputStream(filepath + "poiexcel07.xlsx");
 

③ 最终效果

5.1.3 03版本批量写excel

①代码

@Test
    public void testBigDateExcelTest() throws Exception {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("大文件导出测试");
 
        long begin = System.currentTimeMillis();
 
        for (int rowNum = 0; rowNum <65536 ; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10 ; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue("("+(rowNum+1) + "," + (cellNum+1)+")");
            }
 
        }
        FileOutputStream fileOutputStream = new FileOutputStream(filepath + "03版本批量写入.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        workbook.close();
 
        long end = System.currentTimeMillis();
        System.out.println("耗时："+(double)(end-begin)/1000+"秒");
    }

②最终效果

根据记录时间，耗时：1.663秒

5.1.4 07版本批量写excel

① 代码修改

我们只需将上面new HSSFWorkbook() 修改成new XSSFWorkbook()

new FileOutputStream(filepath + "03版本批量写入.xls") 修改成new FileOutputStream(filepath + "07版本批量写入.xlsx") 即可

Workbook workbook = new XSSFWorkbook();
 ...省略
FileOutputStream fileOutputStream = new FileOutputStream(filepath + "07版本批量写入.xlsx");
 

② 最终效果

由于07及以上版本，没有限制行数，所以在写入数据时耗时相比较长。共耗时：10.959秒

---

注意： 如果03版本写入数据行数超过65536行会报如下错误，而07版本的不会报错

5.1.5 07版本批量写入优化

通过上面的列子，我们可以看出来在07版本中批量写入大数据的时候耗时比较长，这小节，我们就使用apach提供新的类来优化代码

① 代码

代码基本不用变，我们只需要将new XSSFWorkbook() 修改为new SXSSFWorkbook()

并且最终将数据写入过程中产生的缓存文件删除((SXSSFWorkbook) workbook).dispose();

 @Test
    public void batchWriteExcel07optTest() throws Exception {
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("大文件导出优化测试");
 
        long begin = System.currentTimeMillis();
 
        for (int rowNum = 0; rowNum <65536 ; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10 ; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue("("+(rowNum+1) + "," + (cellNum+1)+")");
            }
 
        }
        FileOutputStream fileOutputStream = new FileOutputStream(filepath + "07版本批量写入优化.xlsx");
        workbook.write(fileOutputStream);
        // 清理临时文件
        ((SXSSFWorkbook) workbook).dispose();
        fileOutputStream.close();
        workbook.close();
 
        long end = System.currentTimeMillis();
        System.out.println("耗时："+(double)(end-begin)/1000+"秒");
    }

② 最终效果

同样的数据大小，耗时明显减少了

5.2 基本读操作

5.2.1 基本读excel

① 03版本读取

String filePath = "E:\\xiezhrspace\\excel-demo\\fileinput\\";
 
@Test
public void readExcel03Test() throws  Exception{
 
    FileInputStream fileInputStream = new FileInputStream(filePath + "poiexcel03.xls");
 
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    Sheet sheet = workbook.getSheetAt(0);
    //获取第一行
    Row row1 = sheet.getRow(0);
    //获取第一行的第一个单元格
    Cell cell11 = row1.getCell(0);
    //获取第一行第二个单元格
    Cell cell12 = row1.getCell(1);
    System.out.println("第一行第一个单元格的内容是："+cell11.getStringCellValue());
    System.out.println("第一行第二个单元格的内容是："+cell12.getStringCellValue());
 
    //获取第一行
    Row row2 = sheet.getRow(1);
    //获取第一行的第一个单元格
    Cell cell21 = row2.getCell(0);
    //获取第一行第二个单元格
    Cell cell22 = row2.getCell(1);
    System.out.println("第一行第一个单元格的内容是："+cell21.getStringCellValue());
    System.out.println("第一行第二个单元格的内容是："+cell22.getStringCellValue());
 
}
//结果
第一行第一个单元格的内容是：个人公众号
第一行第二个单元格的内容是：XiezhrSpace
第一行第一个单元格的内容是：个人博客
第一行第二个单元格的内容是：www.xiezhrspace.cn

②07版本读取

只需将new FileInputStream(filePath + "poiexcel03.xls")修改为new FileInputStream(filePath + "poiexcel07.xlsx")

new HSSFWorkbook(fileInputStream) 修改为new XSSFWorkbook(fileInputStream)

String filePath = "E:\\xiezhrspace\\excel-demo\\fileinput\\";
@Test
    public void readExcel07Test() throws  Exception{
 
        FileInputStream fileInputStream = new FileInputStream(filePath + "poiexcel07.xlsx");
 
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //获取第一行
        Row row1 = sheet.getRow(0);
        //获取第一行的第一个单元格
        Cell cell11 = row1.getCell(0);
        //获取第一行第二个单元格
        Cell cell12 = row1.getCell(1);
        System.out.println("第一行第一个单元格的内容是："+cell11.getStringCellValue());
        System.out.println("第一行第二个单元格的内容是："+cell12.getStringCellValue());
 
        //获取第一行
        Row row2 = sheet.getRow(1);
        //获取第一行的第一个单元格
        Cell cell21 = row2.getCell(0);
        //获取第一行第二个单元格
        Cell cell22 = row2.getCell(1);
        System.out.println("第一行第一个单元格的内容是："+cell21.getStringCellValue());
        System.out.println("第一行第二个单元格的内容是："+cell22.getStringCellValue());
 
    }
//结果
第一行第一个单元格的内容是：个人公众号
第一行第二个单元格的内容是：XiezhrSpace
第一行第一个单元格的内容是：个人博客
第一行第二个单元格的内容是：www.xiezhr.cn

注：

如果上面获取单元格数据的时候，取值的类型不对，即String 类型的数据通过cell22.getNumericCellValue() 获取，则会报错

其他类型不匹配情况类似

5.2.2 读取不同类型数据

① 准备数据

现在有一张用户订单信息表，表内容如下，表中包含了各种类型的数据

②书写代码，将表格中的内容读取出来输出到控制台

  @Test
    public void readExcel07ByTypeTest() throws Exception{
        FileInputStream fileInputStream = new FileInputStream(filePath + "用户订单信息表.xlsx");
 
        //根据文件输入流获取excel工作簿（Workbook）
        Workbook workbook = new XSSFWorkbook(fileInputStream);
 
        //根据工作簿获取第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        //获取表头信息
        Row rowtitle = sheet.getRow(0);
        //获取表头有多少列
        int cells = rowtitle.getPhysicalNumberOfCells();
 
        for (int cellNum = 0; cellNum < cells; cellNum++) {
            Cell cell = rowtitle.getCell(cellNum);
            String cellValue = cell.getStringCellValue();
            System.out.print(cellValue + "\t");
        }
        //循环读取数据
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        //从第二行开始读取数据
        for (int rowNum = 1; rowNum < rows; rowNum++) {
            //获取行对象
            Row row = sheet.getRow(rowNum);
            int ofCells = row.getPhysicalNumberOfCells();
            //获取列数
            for (int cellNum = 0; cellNum < ofCells; cellNum++) {
 
                System.out.print("第" + (rowNum + 1) + "行第" + (cellNum + 1) + "列");
                //获取单元格
                Cell cell = row.getCell(cellNum);
                //获取单元格数据类型
                if (cell!=null) {
                    CellType cellType = cell.getCellType();
                    //根据单元格数据类型获取单元格数据
                    String cellvalue ="";
                    switch (cellType) {
 
                        case STRING : //判断是否是字符串类型
                            System.out.print("【String】");
                            cellvalue = cell.getStringCellValue();
                            break;
                        case NUMERIC:  //判断是否是数字类型
                            if (DateUtil.isCellDateFormatted(cell)) {
                                System.out.print("【date】");
                                Date date = cell.getDateCellValue();
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                cellvalue = sdf.format(date);
 
                            } else {
                                System.out.print("【double】");
                                double numericCellValue = cell.getNumericCellValue();
                                cell.setCellType(CellType.STRING);
                                cellvalue = String.valueOf(numericCellValue);
 
                                }
                            break;
                        case BLANK:
                            System.out.print("【BLANK】");
                            break;
                        case BOOLEAN:
                            System.out.print("【BOOLEAN】");
                            boolean bool = cell.getBooleanCellValue();
                            cellvalue = String.valueOf(bool);
                            break;
                        case _NONE:
                            System.out.print("【_NONE】");
                            break;
                        case  ERROR:
                            System.out.print("【ERROR】");
                            byte errorCode = cell.getErrorCellValue();
                            cellvalue = String.valueOf(errorCode);
                            break;
                        case  FORMULA:
                            System.out.print("【FORMULA】");
                            XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
 
                            String formula = cell.getCellFormula();
                            System.out.println("formula:"+formula);
                            CellValue evaluate = formulaEvaluator.evaluate(cell);
                            cellvalue= evaluate.formatAsString();
                            break;
                    }
                    System.out.println(cellvalue);
                }
 
            }
 
        }
 
    }

六、EasyPoi 使用

Easypoi的目标不是替代poi,而是让一个不懂导入导出的快速使用poi完成Excel和word的各种操作,而不是看很多api才可以完成这样工作

Easypoi 为谁而开发？

• 不太熟悉poi的
• 不想写太多重复太多的
• 只是简单的导入导出的
• 喜欢使用模板的

6.1 所需依赖

<dependency>
    <groupId>cn.afterturngroupId>
    <artifactId>easypoi-baseartifactId>
    <version>4.1.0version>
dependency>
<dependency>
    <groupId>cn.afterturngroupId>
    <artifactId>easypoi-webartifactId>
    <version>4.1.0version>
dependency>
<dependency>
    <groupId>cn.afterturngroupId>
    <artifactId>easypoi-annotationartifactId>
    <version>4.1.0version>
dependency>

• 1.easypoi :父包–作用大家都懂得
• 2.easypoi-annotation :基础注解包,作用与实体对象上,拆分后方便maven多工程的依赖管理
• 3.easypoi-base :导入导出的工具包,可以完成Excel导出,导入,Word的导出,Excel的导出功能
• 4.easypoi-web :耦合了spring-mvc 基于AbstractView,极大的简化spring-mvc下的导出功能
• 5.sax :导入使用xercesImpl这个包(这个包可能造成奇怪的问题哈),word导出使用poi-scratchpad,都作为可选包了

如果不使用spring mvc的便捷福利,直接引入easypoi-base 就可以了,easypoi-annotation

6.2 常用注解

EasyPoi 为了方便我们操作，实现了实体和Excel的对应,model--row,filed--col，这样利用注解，我们就可以轻轻松松实现excel的导入导出

• @Excel 作用到filed上面,是对Excel一列的一个描述
• **@ExcelCollection ** 表示一个集合,主要针对一对多的导出,比如一个老师对应多个科目,科目就可以用集合表示
• @ExcelEntity 表示一个继续深入导出的实体,但他没有太多的实际意义,只是告诉系统这个对象里面同样有导出的字段
• @ExcelIgnore 和名字一样表示这个字段被忽略跳过这个导导出
• @ExcelTarget 这个是作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理

6.2.1 @ExcelTarget

限定一个到处实体的注解,以及一些通用设置,作用于最外面的实体

①常用属性

属性	类型	默认值	功能
value	String	null	定义id唯一标识,不能重复
height	double	10	定义单元格高度
fontSize	short	11	设置文字大小

②使用

@ExcelTarget("users")
public class User implements Serializable {
 	//..... 省略属性 相关GET,SET方法
}

6.2.2 @Excel

@Excel这个是必须使用的注解,用在filed(属性)上面,是对Excel一列的一个描述

① 常用属性

属性	类型	默认值	功能
name	String	null	生成Excel表格中列名
needMerge	boolean	fasle	是否需要纵向合并单元格(用于含有list中,单个的单元格,合并list创建的多个row)
orderNum	String	"0"	指定生成Excel中列的顺序,按照数字自然顺序排序
replace	String[]	{}	值得替换 导出是{a_id,b_id} 导入反过来
savePath	String	“upload”	指定导入Excel中图片的保存路径
type	int	1	导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本
width	double	10	指定导出Excel时列的宽度
isImportField	boolean	true	校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,
exportFormat	String	""	导出Excel的时间格式
importFormat	String	""	导入Excel的时间格式
format	String	""	时间格式,相当于同时设置了exportFormat 和 importFormat
imageType	int	1	导出类型 1 从file读取 2 是从数据库中读取 默认是文件 同样导入也是一样的
suffix	String	""	文字后缀,如% 90 变成90%
isWrap	boolean	true	是否换行 即支持\n
mergeVertical	boolean	fasle	纵向合并内容相同的单元格

②使用

@ExcelTarget("user")
public class User {
    @Excel(name = "姓名",orderNum = "1",width = 20 )
    private String name;
    @Excel(name = "年龄",orderNum = "2",width = 20 )
    private  Integer age;
    @Excel(name = "性别",orderNum = "3",width = 20,replace = {"男_1","女_2"})
    private String sex;
    @Excel(name = "生日",orderNum = "4",width = 20,format = "yyyy-MM-dd")
    private Date birthday;
    //...省略GET、SET方法
}

7.2.3 @ExcelEntity

来标记实体类中包含另一个实体类的字段的注解。通过在父实体类的字段上添加 @ExcelEntity 注解，可以将子实体类中的字段映射到 Excel 表格中

① 常用属性

属性	类型	默认值	功能
id	String	null	定义ID

② 使用

@ExcelTarget("user")
public class User {
    //省略GET、SET方法和其他属性
    @ExcelEntity(name = "用户基本信息")
    private UserBaseInfo userBaseInfo;
 
}
 
@ExcelTarget("userBaseInfo")
class UserBaseInfo{
    @Excel(name = "手机号",orderNum = "5",width = 20)
    private String phone;
    @Excel(name = "邮箱",orderNum = "6",width = 20)
	//省略其他字段和方法
}

在上面的示例中， User 类中包含一个 UserBaseInfo 类型的字段 userBaseInfo ，通过在 userBaseInfo 字段上添加 @ExcelEntity 注解，可以将 UserBaseInfo 类中的字段映射到 Excel 表格中，并且在 Excel 表格中会显示为一个包含 手机号 和 邮箱 列的子表格。

6.2.4 @ExcelCollection

一对多的集合注解,来标记实体类中包含集合类型字段的注解。

通过在父实体类的集合字段上添加 @ExcelCollection 注解，可以将集合中的元素映射到 Excel 表格中

①常用属性

属性	类型	默认值	功能
id	String	null	定义ID
name	String	null	定义集合列名,支持nanm_id
orderNum	int	0	排序,支持name_id
type	Class	ArrayList.class	导入时创建对象使用

② 使用

@ExcelTarget("user")
public class User {
   //省略GET、SET 方法和其他属性
    @ExcelCollection(name = "商品列表",orderNum = "10")
    private List products;
 
}
@ExcelTarget("product")
public class Product {
    @Excel(name = "商品名称" )
    private String productName;
    @Excel(name = "商品价格")
    private double productPrice;
}

在上面的示例中， User 类中包含一个 List 类型的字段 products ，通过在 products 字段上添加 @ExcelCollection 注解，可以将 Product 类型的元素映射到 Excel 表格中，并且在 Excel 表格中会显示为一个包含多个商品信息的列表。

6.2.5 @ExcelIgnore

来标记实体类中不需要导出到 Excel 表格的字段的注解

① 使用

public class Product {
    @Excel(name = "商品名称")
    private String name;
    
    @Excel(name = "价格")
    private double price;
    
    @ExcelIgnore
    private String description; // 不需要导出到 Excel 表格的字段
    
    // 其他字段和方法
}

在上面的示例中， Product 类中包含了 name 、 price 和 description 三个字段。通过在 description 字段上添加 @ExcelIgnore 注解，告诉 EasyPoi 在导出 Excel 表格时不导出该字段的内容。

6.3 注解使用导出Excel案例

6.3.1 导出基本数据

① 创建基本对象对象

@Data
@ExcelTarget("user")
public class User {
    @Excel(name = "姓名",orderNum = "1",width = 20,needMerge = true)
    private String name;
    @Excel(name = "年龄",orderNum = "2",width = 20,needMerge = true)
    private  Integer age;
    @Excel(name = "性别",orderNum = "3",width = 20,replace = {"男_1","女_2"},needMerge = true)
    private String sex;
    @Excel(name = "生日",orderNum = "4",width = 20,format = "yyyy-MM-dd",needMerge = true)
    private Date birthday;
 
}

②模拟测试数据

public List testGetTestData(){
        List users = new ArrayList();
        for (int i = 0; i < 5; i++) {
            User user = new User();
            user.setName("小凡"+i);
            user.setAge(i+18);
            user.setSex("1");
            user.setBirthday(new Date());
            users.add(user);
        }
        return users;
    }

③导出数据

@Test
public void testExportExcel() throws Exception {
    ExportParams exportParams = new ExportParams("用户列表", "测试sheet页");
    //创建ExcelExportUtil对象，传入参数为ExportParams对象，User类，testGetTestData()方法
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, testGetTestData());
    //创建FileOutputStream对象，传入参数为文件路径
    FileOutputStream fileOutputStream = new FileOutputStream("D:/test.xls");
    //将workbook写入到文件输出流中
    workbook.write(fileOutputStream);
    //关闭文件输出流
    fileOutputStream.close();
    //关闭workbook
    workbook.close();
    System.out.println("导出成功");
}

④ 查看excel

6.3.2 忽略某个字段

① 添加注解

@ExcelIgnore
private  Integer age;

②查看excel

6.3.3 导出对象中含有对象的数据

① 添加注解

@ExcelTarget("user")
public class User {
    //省略GET、SET方法和其他属性
    @ExcelEntity(name = "用户基本信息")
    private UserBaseInfo userBaseInfo;
}

② 新建UserBaseInfo类

@ExcelTarget("userBaseInfo")
class UserBaseInfo{
    @Excel(name = "手机号",orderNum = "5",width = 20)
    private String phone;
    @Excel(name = "邮箱",orderNum = "6",width = 20)
	//省略其他字段和方法
}

③ 测试数据中添加UserBaseInfo 对象

user.setUserBaseInfo(new UserBaseInfo("15288345678","324355@qq.com","云南省昆明市","651219","532334125689558"));

④ 导出excel数据

6.3.4 导出含list集合数据

①我们新建Product 对象

@ExcelTarget("product")
public class Product {
    //省略GET、SET方法
    @Excel(name = "商品名称" )
    private String productName;
    @Excel(name = "商品价格")
    private double productPrice;
}

② User 类中添加如下list集合

@ExcelCollection(name = "商品列表",orderNum = "10")
private List products;

③添加测试数据

public List testGetTestData(){
    List users = new ArrayList();
    for (int i = 0; i < 5; i++) {
        User user = new User();
        user.setName("小凡"+i);
        user.setAge(i+18);
        user.setSex("1");
        user.setBirthday(new Date());
        user.setUserBaseInfo(new UserBaseInfo("15288345678","324355@qq.com","云南省昆明市","651219","532334125689558"));
        Product pro1 = new Product("冰箱"+i, 5000);
        Product pro2 = new Product("洗衣机"+i, 3000);
        Product pro3 = new Product("空调"+i, 4000);
        List products = Arrays.asList(pro1, pro2, pro3);
        user.setProducts(products);
        users.add(user);
    }
    return users;
}

④ 由于是一对多关系，所以需要添加合并单元格属性needMerge = true

@Excel(name = "姓名",orderNum = "1",width = 20,needMerge = true)
private String name;
@Excel(name = "年龄",orderNum = "2",width = 20,needMerge = true)
//其他属性类似省略。。。

⑤导出excel

6.3.5 导出图片

①User类中定义头像avter字段

@Excel(name = "头像",orderNum = "0",width = 20,type = 2,height = 20,needMerge = true) //type的值一定要指定为2
private String avatar; //定义头像 直接写指定图片路径

② 构造测试数据

    public List testGetTestData(){
        List users = new ArrayList();
        for (int i = 0; i < 5; i++) {
            User user = new User();
            user.setName("小凡"+i);
            user.setAge(i+18);
            user.setSex("1");
            user.setBirthday(new Date());
            user.setUserBaseInfo(new UserBaseInfo("15288345678","324355@qq.com","云南省昆明市","651219","532334125689558"));
            Product pro1 = new Product("冰箱"+i, 5000);
            Product pro2 = new Product("洗衣机"+i, 3000);
            Product pro3 = new Product("空调"+i, 4000);
            List products = Arrays.asList(pro1, pro2, pro3);
            user.setAvatar("https://xiezhrspace.cn/medias/logo.png");
            user.setProducts(products);
            users.add(user);
        }
        return users;
    }

③ 导出excel

6.4 大数据导出

当我们导出得数据量为几十万到几百万时，一次将所有数据加载到内存中，会对cpu、内存都产生巨大压力。

当然了，EasyPoi作者也为我们考虑了，专门提供exportBigExcel 方法来导出大数据

Workbook workbook1 = ExcelExportUtil.exportBigExcel(new ExportParams("用户列表", "测试"), User.class, getUsers());
workbook1.write(outputStream);
ExcelExportUtil.closeExportBigExcel();

6.5 导入excel

6.5.1 导入基本数据

① 准备目标excel

② 定义基本数据对象

@ExcelTarget("student")
public class Student implements Serializable {
    @Excel(name = "编号")
    private String id;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "性别", replace = {"男_1", "女_2"})
    private String gender;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "出生日期", importFormat = "yyyy-MM-dd")
    private Date birth;
    //省略GET、SET方法
}

③ 导入代码

@Test
public void excelImportTest() throws Exception{
    // 创建导入参数配置
    ImportParams importParams = new ImportParams();
    // 设置标题行数
    importParams.setTitleRows(1);
    // 设置表头行数
    importParams.setHeadRows(1);
 
    // 创建文件输入流
    FileInputStream fileInputStream = new FileInputStream("E:\\xiezhrspace\\excel-demo\\fileinput\\学生信息表.xlsx");
    // 调用ExcelImportUtil的importExcel方法，传入文件输入流，实体类，参数配置，返回实体类列表
    List students = ExcelImportUtil.importExcel(fileInputStream, Student.class, importParams);
    // 遍历实体类列表，输出实体类信息
    students.forEach(System.out::println);
    // 关闭文件输入流
    fileInputStream.close();
 
}

② 打印导入结果

Student(id=10001, name=张三, gender=1, age=25, birth=1992-02-23)
Student(id=10002, name=李四, gender=1, age=18, birth=2006-01-01)
Student(id=10003, name=王五, gender=1, age=26, birth=1998-01-01)
Student(id=10004, name=赵六, gender=2, age=26, birth=1998-03-01)

6.5.2 导入带图片数据

①excel数据

②Student类设置图片保存路径

@ExcelTarget("student")
public class Student implements Serializable {
    @Excel(name = "编号")
    private String id;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "性别", replace = {"男_1", "女_2"})
    private String gender;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "出生日期", importFormat = "yyyy-MM-dd")
    private Date birth;
    @Excel(name = "头像", type = 2,  savePath = "src/main/imgs")  //设置导入图片的保存路径
    private String avatar;
    //省略GET、SET 方法
}

③ 导入excel

关键部分

importParams.setNeedSave(false);

importParams.setSaveUrl("src/main/imgs");

@Test
public void excelImportTest() throws Exception{
    // 创建导入参数配置
    ImportParams importParams = new ImportParams();
    // 设置标题行数
    importParams.setTitleRows(1);
    // 设置表头行数
    importParams.setHeadRows(1);
 
    // 设置是否需要保存
    importParams.setNeedSave(false);
    // 设置保存路径
    importParams.setSaveUrl("src/main/imgs");
 
    // 创建文件输入流
    FileInputStream fileInputStream = new FileInputStream("E:\\xiezhrspace\\excel-demo\\fileinput\\学生信息表.xlsx");
    // 调用ExcelImportUtil的importExcel方法，传入文件输入流，实体类，参数配置，返回实体类列表
    List students = ExcelImportUtil.importExcel(fileInputStream, Student.class, importParams);
    // 遍历实体类列表，输出实体类信息
    students.forEach(System.out::println);
    // 关闭文件输入流
    fileInputStream.close();
 
}

④ 导入结果

Student(id=10001, name=张三, gender=1, age=25, birth=1992-02-23,avatar=src/main/imgs\pic33945659303.PNG)
Student(id=10002, name=李四, gender=1, age=18, birth=2006-01-01,avatar=src/main/imgs\pic31457305277.JPG)
Student(id=10003, name=王五, gender=1, age=26, birth=1998-01-01,avatar=src/main/imgs\pic71983821334.PNG)
Student(id=10004, name=赵六, gender=2, age=26, birth=1998-03-01,avatar=src/main/imgs\pic41577097054.PNG)

6.6 小结

以上小节为easypoi的最基本操作，如果大家还想了解更多，可以关注官方文档：http://doc.wupaas.com/docs/easypoi/

作者也给出了大量的实例：https://gitee.com/lemur/easypoi-test

八、EasyExcel使用

8.1 快速入门之写excel

8.1.1 引入依赖

目前最新的依赖是3.3.3，我们这里引入使用最多也最稳定的3.3.2版本

    com.alibaba
    easyexcel
    3.3.2

8.1.2 构建数据对象

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
    @ExcelProperty("员工编号")
    private  Integer id;
    @ExcelProperty("员工姓名")
    private  String name;
    @ExcelProperty("员工年龄")
    private  Integer age;
    @ExcelProperty("员工薪资")
    private  Double salary;
    @ExcelProperty("入职日期")
    private Date inDate;
}

8.1.3 构建测试数据

private List data(long count) {
    List list = ListUtils.newArrayList();
    for (int i = 0; i < count; i++) {
        Employee employee = new Employee();
        employee.setId(i);
        employee.setName("小陈"+i);
        employee.setAge(18+1);
        employee.setSalary(6.66+i);
        employee.setInDate(new Date());
        list.add(employee);
    }
    return list;
}

上面代码根据传入count,构建通用数据，后面不会重复写

8.1.4 写入数据

@Test
public void testbaseWrite() {
    String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, Employee.class).sheet("测试模板").doWrite(data(10));
 
}

8.2 快速入门之读excel

8.2.1 准备目标excel

8.2.2 构建数据对象

这里我们沿用8.1.2 小节中创建的employee对象

8.2.3 读取数据

注： PageReadListener 为默认easyexcel为我们写好的监听类，当然了，我们也可以自己定义，在后面的进阶操作中

我们会说到

@Test
public void testbaseReade(){
    String fileName = "E:\\xiezhrspace\\excel-demo\\fileinput\\" + "员工信息表.xlsx";
    EasyExcel.read(fileName, Employee.class, new PageReadListener(dataList -> {
        for (Employee employee : dataList) {
            System.out.println(employee);
        }
    })).sheet().doRead();
}

8.3 进阶操作

8.3.1 复杂表头写入

① 准备对象

@Data
public class Student {
    @ExcelProperty({"学生信息表","学生ID"})
    private  Integer id;
    @ExcelProperty({"学生信息表","学生姓名"})
    private String name;
    @ExcelProperty({ "学生信息表", "学生年龄" })
    private Integer age;
    @ExcelProperty({ "学生信息表", "生日" })
    private Date birth;
}

②代码

public class UpEasyExcelTest {
    private List data(long count) {
        List list = ListUtils.newArrayList();
        for (int i = 0; i < count; i++) {
            Student student = new Student();
            student.setId(i);
            student.setName("小李" + i);
            student.setAge(22+i);
            student.setBirth(new Date());
            list.add(student);
        }
        return list;
    }
 
    @Test
    public void testUpWrite() {
        String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "upWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, Student.class).sheet("测试模板").doWrite(data(10));
 
    }
}

③excel写入示例

8.3.2 重复多次写入(写到单个或者多个Sheet)

① 准备对象

public class Employee {
    @ExcelProperty("员工编号")
    private  Integer id;
    @ExcelProperty("员工姓名")
    private  String name;
    @ExcelProperty("员工年龄")
    private  Integer age;
    @ExcelProperty("员工薪资")
    private  Double salary;
    @ExcelProperty(value = "入职日期")
    private Date inDate;
    //省略GET、SET 方法
}

②代码

 @Test
public void testmanyDateWriter(){
    // 方法1: 如果写到同一个sheet
    String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "manydataWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去写
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class).build()) {
        // 这里注意 如果同一个sheet只要创建一次
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
        // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来
        for (int i = 0; i < 5; i++) {
            // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
            List data = data(200000);
            excelWriter.write(data, writeSheet);
        }
    }
 
    // 方法2: 如果写到不同的sheet 同一个对象
    fileName =  "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "manydataWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 指定文件
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class).build()) {
        // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
        for (int i = 0; i < 5; i++) {
            // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
            WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).build();
            // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
            List data = data(200000);
            excelWriter.write(data, writeSheet);
        }
    }
 
    // 方法3 如果写到不同的sheet 不同的对象
    fileName =  "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "manydataWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 指定文件
    try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
        // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
        for (int i = 0; i < 5; i++) {
            // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class
            // 实际上可以一直变
            WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(Employee.class).build();
            // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
            List data = data(200000);
            excelWriter.write(data, writeSheet);
        }
    }
}

③Excel示例

除了上面的常用例子外，还有更多玩法，大家可以参照官方文档，里面写的很详细，这里就不一一例举了~

8.4 excel填充

EasyExcel提供了数据填充的功能，可以将指定的数据填充到事先设计好带有样式和格式的Excel模板文，这样我们就可以制作出更加优美的excel

8.4.1 简单的填充

① 准备模板

②准备对象

@Data
public class Student {
    @ExcelProperty({"学生信息表","学生ID"})
    private  Integer id;
    @ExcelProperty({"学生信息表","学生姓名"})
    private String name;
    @ExcelProperty({ "学生信息表", "学生年龄" })
    private Integer age;
    @ExcelProperty({ "学生信息表", "生日" })
    private Date birth;
}

③填充数据

根据对象填充

@Test
public void filebyObjFillTest() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    String templateFileName ="E:\\xiezhrspace\\excel-demo\\fileinput\\" +  "template.xlsx";
 
    // 方案1 根据对象填充
    String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "fillWrite" + System.currentTimeMillis() + ".xlsx";
 
    Student student = new Student();
    student.setId(1);
    student.setName("张三");
    student.setAge(20);
    student.setBirth(new Date());
    // 这里 会填充到第一个sheet， 然后文件流会自动关闭
 
    EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(student);
 
}

根据map填充

@Test
public void filebyMapFillTest() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    String templateFileName ="E:\\xiezhrspace\\excel-demo\\fileinput\\" +  "template.xlsx";
    // 方案2 根据Map填充
    String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "fillWrite" + System.currentTimeMillis() + ".xlsx";
 
    // 这里 会填充到第一个sheet， 然后文件流会自动关闭
    Map map = MapUtils.newHashMap();
    map.put("id", "001");
    map.put("name", "张三");
    map.put("age", 20);
    map.put("birthday", new Date());
    EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);
}

④最终效果

8.4.2 填充列表数据

①准备模板

相比上一小节的模板，列表数据的模板{id}----->{.id} {name}--->{.name} 依此类推

②填充对象

参照上一小节

③ 准备数据

private List data(long count) {
        List list = ListUtils.newArrayList();
        for (int i = 0; i < count; i++) {
            Student student = new Student();
            student.setId(i);
            student.setName("小李" + i);
            student.setAge(22+i);
            student.setBirth(new Date());
            list.add(student);
        }
        return list;
    }

④ 填充数据

@Test
public void listFill() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    // 填充list 的时候还要注意 模板中{.} 多了个点 表示list
    // 如果填充list的对象是map,必须包涵所有list的key,哪怕数据为null，必须使用map.put(key,null)
    String templateFileName ="E:\\xiezhrspace\\excel-demo\\fileinput\\" +  "template-list.xlsx";
 
    // 方案1 一下子全部放到内存里面 并填充
    String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "fillWrite-list" + System.currentTimeMillis() + ".xlsx";
    // 这里 会填充到第一个sheet， 然后文件流会自动关闭
    EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(data(10));
 
}

⑤ 最终效果

8.4.3 复杂填充

当excel模板相对复杂时，也可以填充

① 准备复杂模板

② 准备填充对象

参照8.4.1小节

③准备数据

参照8.4.1小节

④填充数据

@Test
public void complexFill() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    // {} 代表普通变量 {.} 代表是list的变量
    String templateFileName ="E:\\xiezhrspace\\excel-demo\\fileinput\\" +  "template-hard.xlsx";
 
    String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "fillWrite-hard" + System.currentTimeMillis() + ".xlsx";
    // 方案1
    try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
        // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
        // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
        // 如果数据量大 list不是最后一行 参照下一个
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        excelWriter.fill(data(5), fillConfig, writeSheet);
        excelWriter.fill(data(5), fillConfig, writeSheet);
        Map map = MapUtils.newHashMap();
        map.put("date", "2024年03月10日17:28:28");
        map.put("total", 1000);
        excelWriter.fill(map, writeSheet);
    }
}

⑤ 最终效果

九、文章小节

文中只例举出excel 常用操作，如果这些还不满足你的需求，大家可以查看官方文档。

官方文档还是非常详细的，并且都给出了具体的demo。

另外文中例举出的代码已提交到https://gitee.com/xiezhr/excel-demo.git 欢迎大家访问查看

本期内容到此就结束了，希望对你有所帮助，我们下期再见 (●'◡'●)