excel

Excel

在这里介绍两个框架

poi（apache）

easyExcel（Alibaba）

第一部分 poi

apache提供操作execl文件的工具包

读取方式，将整个excel文档加载到内存中，作为一个对象，开始操作

1. hello poi

先来写一个初遇

① 导入依赖

 <!--这个是用来操作 07版  excel文件后缀为 .xlsx-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

② 实现

public static void main(String[] args) throws Exception {
    // 1.创建工作簿
    Workbook workbook = new XSSFWorkbook();
    // 2.创建工作表
    Sheet sheet = workbook.createSheet();
    // 3.创建第一行(序号从0开始)
    Row row = sheet.createRow(0);
    // 4.创建第一行的第一个单元格(序号从0开始)  单元格坐标为(0,0)
    Cell cell = row.createCell(0);
    // 5.设置一个值
    cell.setCellValue("宗笛");
    
    // 6.定义输出文件
    FileOutputStream fileOutputStream = new FileOutputStream("E:\\JavaCode\\jdk_features\\moduleD\\a.xlsx");
    // 7.将工作簿内容写入文件
    workbook.write(fileOutputStream);
    // 8.关闭工作流
    fileOutputStream.close();
}

第二部分 easyExecl

读取方式：一行一行读，内存中只存在当前一行的内容

一般需要使用集合去保存读取的数据

1.hello easyExecl

① 导入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.7</version>
</dependency>

② 创建读取模板

模板就是一个 实体类 ，表中每一条数据对应一个实体类

以User对象为例

@Data
public class User {

    @ExcelProperty("名称")
	private String name;
	private Integer age;
    
	@DateTimeFormat("yyyy-MM-dd")   // 对日期进行格式化
	private Date br;
}

// 如果不使用@ExcelProperty注解对属性进行标注，那么easyExcel会按照实体类中属性默认顺序赋值，如果类型不匹配则抛出异常

③ 配置读取监听器

用这个东西去读取excel，在这里边我们可以对每一个数据进行操作

public class ListenerExcel<T> extends AnalysisEventListener<T> {

    private final List<T> list = new ArrayList<>();

    // 没读取一条数据 就会执行一次本方法  t就是对应的实体类对象
    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        System.out.println(t);
        list.add(t);
    }

    // 读取完最后一条数据 执行本方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("读取结束");
    }

    // 自定义方法，用于获取 数据集合
    public List<T> getList(){
        return this.list;
    }
}

④ 读取数据

a. 本地

ListenerExecl<Student> listenerExecl = new ListenerExecl<>();  // 创建监听器
EasyExcel.read()     // 读取
        .file("E:\\excel2.xls")   // 指定文件
        .head(Student.class)      // 指定表头信息也就是模板
        .registerReadListener(listenerExecl)  // 注册读取监听器
        .sheet()  
        .doRead();

listenerExecl.getList().forEach(System.out::println);   // 通过监听器说去所有数据

b. web

public void upload(MultipartFile file,HttpServletResponse response) throws IOException {
    ListenerExcel<Student> listenerExcel = new ListenerExcel<>();  // 创建监听器
    EasyExcel.read()     // 读取
        .file(file.getInputStream())   // 指定文件
        .head(Student.class)      // 指定表头信息也就是模板
        .registerReadListener(listenerExcel)  // 注册读取监听器
        .sheet()
        .doRead();
    service.saveBatch(listenerExcel.getList());  // 保存信息
    response.sendRedirect("/list");
}

<form id="upload-form" th:action="@{upload}" method="post" enctype="multipart/form-data" >
    　　　<input type="file" id="upload" name="file" /> <br />
    　　　<input type="submit" value="上传" />
</form>

⑤ 写出数据

a. 本地

void contextLoads() {
    // 使用工具输出execl文件
    EasyExcel.write(new File("E:\\excel2"+ ExcelTypeEnum.XLS.getValue()))  // 指定生成文件
            .sheet("用户列表")      // 指定工作簿名称
            .head(User.class)     // 指定表头
            .doWrite(data());     // 指定数据
}

List<User> data(){
    ArrayList<User> arrayList = new ArrayList<>();
    for (int i = 0; i < 60000; i++) {
        arrayList.add(new User("梁林宗"+i,i,new Date()));
    }
    return arrayList;
}

b. web

public void export(HttpServletResponse response) throws IOException {
    List<Student> list = service.list();
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-disposition","attachment;filename=product.xlsx");

    // 使用工具输出execl文件
    EasyExcel.write(response.getOutputStream())  // 指定生成文件
        .sheet("用户列表")      // 指定工作簿名称
        .head(Student.class)     // 指定表头
        .doWrite(list);     // 指定数据
}

2. 常用注解

easyexcel中为我们提供了很多注解，方便我们开发，为了后文讲述方便，现在这里介绍一下

① @ExcelProperty

一般用于标注列名、顺序等

属性：

1. value （默认属性）标注这个列的标题，也可以实现多级头信息。在读取/写出时会以该值为准对应字段与数据
2. index 标注读取/写出顺序，从0开始,0代表表格中第一列。一般情况下value和index单独使用，不然可能导致读取为null
3. order 标注读取/写出顺序，值越小，输出列越靠前。与index不同的是，index指定是几就是几，order则是指定优先级
4. Converter 标注引用的转换器，当我们需要对读取/写出的内容需要额外操作时，可以创建并引用转换器

// 该字段与‘姓名’列相对；表中数据为名称，但是我需要名称长度，这里引用转换器处理
@ExcelProperty(value = "姓名",converter = CustomStringStringConverter.class)
private Integer nameLength;

// 表中第二列与之对应
@ExcelProperty(index = 1)
private String age;

// 多级头信息，如果头信息一致 导出时会自动合并
@ExcelProperty(value = {"用户信息","性别"})
private String gender;

转换器

// 转换器
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;

public class CustomStringStringConverter implements Converter<String> {
    /**
     * 这里读的时候会调用
     * context.getReadCellData().getStringValue() 获取单元格的值
     *
     * @param context 封装对象
     * @return 结果为字段值
     */
    @Override
    public String convertToJavaData(ReadConverterContext<?> context) {
        return context.getReadCellData().getStringValue();
    }

    /**
     * 这里是写的时候会调用 不用管
     * context.getValue()会获取输出的值 我们可以再次附加操作
     
     * @return
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        return new WriteCellData<>(context.getValue());
    }
}

② @DateTimeFormat

指定读取/写出的日期格式

// 必须使用String接受，不然不生效
@DateTimeFormat("yyyy-MM-dd")
private String bir;

③ @NumberFormat

设置读取/写出数字格式，可以通过这个注解为数字添加后缀

// 一定要用String接受
@NumberFormat("#.##￥")
private String price;

# 代表占位符
    18.125412 -》18.13￥

特殊的后缀会有不同的效果

1. % 18.125412 -》 1812.54%

② @ExcelIgnore

标注忽略此字段

默认情况下，easyexcel会按照对象属性默认排序读取表格值，即使属性不加相关注解，也会去读

标注该字段后，不管是读或者写都不会操作该字段

③ @ExcelIgnoreUnannotated

类注解

表示该类中不加相关注解的字段，在读/写时不在操作，作用与@ExcelIgnore类似，不过是批量添加

② ColumnWith

设置列宽 导出注解

3. 读取

这个模块将来介绍easyexcel怎么去读取excel文件

① 读取 单表

1. 需要一个与表格对应的模板对象
2. 需要创建一个监听器
3. 加载一个Excel文件
4. 调用EasyExcel提供的工具类读取文件

// 模板  在不加任何Excel注解的情况下，easyExcel会按字段默认顺序读取文件列
@Data
public class User {
    private String name;
    private String age;
    private String gender;
}

// 读取监听器
public class UserReadListener implements ReadListener<Object> {

    // 定义数据集合
    private List<Object> listData;

    public UserReadListener() {
        this.listData = new ArrayList<>();
    }

    /**
     * 没读取银行数据就会执行
     *
     * @param o 这一行数据封装的对象
     * @param analysisContext
     */
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        System.out.println("读取一条数据");
        this.listData.add(o);
    }

    /**
     * 数据读取完时会执行该方法
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("数据读取完了");
    }

    /**
     * 数据列表获取方法
     *
     * @return 数据集合
     */
    public List<Object> getListData() {
        return listData;
    }
}

读取方法一

读取有很多种，这一种是最基础的写法，也是最根本的写法-

// 加载文件
String fileName  = "C:\\Users\\97540\\Desktop\\user.xlsx";
// 创建监听器
UserReadListener userReadListener = new UserReadListener();

// 读取数据
	// 写法一 
EasyExcel.read()
        .file("E:\\excel2.xls")   // 指定文件
        .head(Student.class)      // 指定表头信息也就是模板
        .registerReadListener(listenerExecl)  // 注册读取监听器
        .sheet()  				  // 指定sheet，默认0
        .doRead();        		  // 开始读取
	// 写法二 简化写法
EasyExcel.read(fileName,User.class,userReadListener).sheet().doRead();

// 打印数据
userReadListener.getListData().forEach(System.out::println);

方法二

使用匿名内部类的方式实现监听器，可以简单一些

// 定于数据集合
List<User> userList = new ArrayList<>();
// 匿名读取，直接在这个地方指定泛型，就不用转类型了
EasyExcel.read(fileName, User.class, new ReadListener<User>() {
    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        System.out.println(user);
        userList.add(user);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println(userList);
    }
}).sheet().doRead();

方法三

直接使用已定义好的监听器，更加省事

// 默认情况下 没读取100条数据执行一次该方法
EasyExcel.read(fileName,User.class,new PageReadListener<User>(dataList -> {
    System.out.println("我执行了一次");
	dataList.forEach(System.out::println);
})).sheet().doRead();

② 读取 多表

基于上述模板以及监听器，进行一下操作

方法一

最直接简单的读取，数据只会根据模板读取，不一样的地方读取不到，这个方法只能读取全部的sheet

// 创建监听器 其中的doAfterAllAnalysed方法变为每个sheet读完之后执行一次
UserReadListener userReadListener = new UserReadListener();
// 所有的数据都会装到 监听器封装的 dataList中
EasyExcel.read(fileName,User.class,userReadListener).doReadAll();
userReadListener.getListData().forEach(System.out::println);

方法二

读取部分sheet，将表分开一个一个读

ExcelReader excelReader;
try {
    // 构造读取器
    excelReader = EasyExcel.read(fileName).build();
    // sheet0读取
    ReadSheet readSheet0 = EasyExcel.readSheet(0)   // 设置读取表几
                            .head(User.class)               // 设置头模板
                            .registerReadListener(new UserReadListener())  // 设置读取监听器
                            .build();                       // 构建
    // sheet1读取 一般来说两个sheet的模板以及listener是不一样，这里为了方便直接使用一样的
    ReadSheet readSheet1 = EasyExcel.readSheet(1)
                            .head(User.class)
                            .registerReadListener(new UserReadListener())
                            .build();
    
    // 使用读取器读取表
    excelReader.read(readSheet0,readSheet1);
    // 调用各自的监听器获取数据
    ....
}...

③ 读取 多行头表

表中有多行头的读取方法

方法一

在读取前指定开始读取行号

EasyExcel.read(fileName,User.class,new PageReadListener<User>(dataList -> {
    System.out.println("我执行了一次");
    dataList.forEach(System.out::println);
})).sheet().headRowNumber(2).doRead();

// 在读取之前添加方法 .headRowNumber(2)  表示从表中第二行开始读取数据

方法二

使用注解ExcelProperty实现多级表头识别

@ExcelProperty(value = {"用户信息","性别"})

原因：如果在读取表数据时没有给定 从第几行开始读，那么easyexcel会根据ExcelProperty注解的value层数确定从第几行读，所以只需要一个字段加该注解就可以

④ 读取 表头信息

读取表头信息，用的不多

方法一

对监听器进行完善改造，实现其中的invokedHead方法

/**
 * 这里会一行行的返回头
 *
 * @param headMap 头信息
 * @param context 
 */
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
    // 如果想转成成 Map<Integer,String>
    // 方案1： 不要implements ReadListener 而是 extends AnalysisEventListener
    // 方案2： 调用 ConverterUtils.convertToStringMap(headMap, context) 自动会转换
    
    // 转换集合 方便操作
    Map<Integer, String> headStringMap = ConverterUtils.convertToStringMap(headMap, context);
    // 遍历头信息
    for(Map.Entry<Integer,String> entry:headStringMap.entrySet()) {
        System.out.println(entry.getKey() + "====" + entry.getValue());
    }
    System.out.println("下一行");
}

// 遍历结果
0====人员信息表
1====null
2====null
3====null
4====null
下一行
0====姓名
1====年龄
2====性别
3====出生年月
4====pirce        可以看到，合并的单元格只有第一个有值

⑤ 读取 额外内容

例如：表中的超链接、批注、合并的单元格等信息

读取到的信息有点鸡肋，读到的连接并不完整

1. 添加读取器额外内容读取方法
2. 在都之前开启读取额外内容

// 该方法用于读到特殊类型后进行操作
@Override
public void extra(CellExtra extra, AnalysisContext context) {
    log.info("读取到了一条额外信息:{}", JSON.toJSONString(extra));
    System.out.println(JSON.toJSONString(extra));
    switch (extra.getType()) {
        case COMMENT:
            log.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(),
                    extra.getText());
            break;
        case HYPERLINK:
            if ("Sheet1!A1".equals(extra.getText())) {
                log.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(),
                        extra.getColumnIndex(), extra.getText());
            } else if ("Sheet2!A1".equals(extra.getText())) {
                log.info(
                        "额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{},"
                                + "内容是:{}",
                        extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                        extra.getLastColumnIndex(), extra.getText());
            } else {
                log.info("Unknown hyperlink!");
            }
            break;
        case MERGE:
            log.info(
                    "额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
                    extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                    extra.getLastColumnIndex());
            break;
        default:
    }
}


// 读的时候开启额外内容读取
String fileName = "C:\\Users\\97540\\Desktop\\dlc.xlsx";
EasyExcel.read(fileName, Dlc.class,new UserReadListener())     
    // 需要读取批注 默认不读取
    .extraRead(CellExtraTypeEnum.COMMENT)
    // 需要读取超链接 默认不读取
    .extraRead(CellExtraTypeEnum.HYPERLINK)
    // 需要读取合并单元格信息 默认不读取
    .extraRead(CellExtraTypeEnum.MERGE)
    .sheet().doRead();

⑥ 读取 异常处理

对读取数据时的异常捕获

实现监听器中的onException方法

*/***
 ** 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。*
 ***
 ** @param exception*
 ** @param context*
 ** @throws Exception*
 **/*
@Override
public void onException(Exception exception, AnalysisContext context) {
    log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
    *// 如果是某一个单元格的转换异常 能获取到具体行号*
    *// 如果要获取头的信息 配合invokeHeadMap使用*
    if (exception instanceof ExcelDataConvertException) {
        ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
        log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
            excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
    }
}

4. 写出

写出excel包含本地写以及web写等操作

① 本地 写

也就是将文件写道本地

1. 需要一个模板
2. 加载本地文件
3. 需要数据
4. 调用Easyexcel方法写

// 模板
@Data
public class DemoData {
    // 指定头信息，可以在此处配置较为复杂的头信息 order给定输出优先级
    @ExcelProperty(value = {"信息","名称"},order = 2)
    private String string;
    @ExcelProperty(value = {"信息","日期"},order = 1)
    private Date date;
    @ExcelProperty(value = {"信息","数据"})
    private Double doubleData;
}

// 准备数据
List<DemoData> data = data();

// 加载本地文件
String fileName ="E:\\JavaCode\\excel\\demo01\\dlc.xlsx";

// 方法一
EasyExcel.write(fileName,DemoData.class).sheet("表名").doWrite(data());
// 方法二 就是将最后改为函数式接口写法，可以在这一步指定数据
EasyExcel.write(fileName,DemoData.class).sheet().doWrite(()-> {
    //    查数据
    return data();
});

② 多次 写

代码存在bug

③ 特殊类型 写

涉及很多类型，比如超链接，备注，图片等等

特殊类型一般都依赖单元格对象，局限性较大

先来看图片

1. 准备模板
2. 准备文件
3. 准备图片
4. 设置数据（核心）
5. 写出

// 1.模板 六种类型可以写出图片
@Data
public class ImageDemoData {
    private File file;
    private InputStream inputStream;
	// 这里必须使用easyexcel提供的 转换器，不然无法实现
    @ExcelProperty(converter = StringImageConverter.class)
    private String string;
    private byte[] byteArray;
    private URL url;
    private WriteCellData<Void> writeCellData;
}

// 2.准备文件
String fileName ="E:\\JavaCode\\...\\dlc.xlsx";

// 3.准备图片
String imageFilePath = "C:\\Users\\...\\P20508-221246.jpg";

// 4.设置数据
	//分别为多种数据类型设置值
ImageDemoData imageDemoData = new ImageDemoData();
	// url需得是能访问到的图片，前五项比较常规，可以借助Easyexcel提供的工具类FileUtils
imageDemoData.setFile(new File(imageFilePath));
imageDemoData.setInputStream(FileUtils.openInputStream(new File(imageFilePath)));
imageDemoData.setString(imageFilePath);
imageDemoData.setByteArray(FileUtils.readFileToByteArray(new File(imageFilePath)));
imageDemoData.setUrl(new URL("https://gimg2.baidu.com/image_search/...")    
    	// 单元格方式设置数据
            // 1.创建单元格
        WriteCellData<Void> cellData = new WriteCellData<>();
            // 2.创建easyExcel提供的image对象
        ImageData imageData = new ImageData();
            // 3.设置image对象属性
        imageData.setImage(FileUtils.readFileToByteArray(new File(imageFilePath)));
            // 4.创建image数据列表
        ArrayList<ImageData> imageDataList = new ArrayList<>();
            // 5.存放image对象数据
        imageDataList.add(imageData);
            // 6.将image数据列表设置给单元格   是列表的原因就是为了实现一个单元格可以存放多张图片
        cellData.setImageDataList(imageDataList);
                     
imageDemoData.setWriteCellData(cellData);                     
ArrayList<ImageDemoData> dataList = new ArrayList<>();
dataList.add(imageDemoData);
// 以上操作相当于设置了一条数据
                     
// 5. 写出
EasyExcel.write(fileName,ImageDemoData.class).sheet().doWrite(dataList);

其他特殊类型写出，与图片第六种方式，使用单元格方式大同小异

1. 创建单元格
2. 创建目标对象
3. 设置目标对象属性
4. 一层层将对象赋值给模板

// 设置超链接
    // 1.创建单元格，以及指定value(value显示在单元格内)
WriteCellData<String> hyperlink = new WriteCellData<>("百度一下");
    // 2.创建超链接对象
HyperlinkData hyperlinkData = new HyperlinkData();
    // 3.设置对象属性
hyperlinkData.setAddress("https://www.baidu.com/");
    // 4.设置对象数据类型
hyperlinkData.setHyperlinkType(HyperlinkData.HyperlinkType.URL);
    // 5.将对象装入单元格
hyperlink.setHyperlinkData(hyperlinkData);
    // 将单元格数据装给模板
cellData.setHyperlink(hyperlink);

// 设置备注
	// 1.创建单元格，以及指定value
WriteCellData<String> comment = new WriteCellData<>("备注的单元格");
	// 2.创建备注对象
CommentData commentData = new CommentData();
	// 3.设置属性
commentData.setAuthor("我是作者名");
commentData.setRichTextStringData(new RichTextStringData("我是备注内容"));
	// 备注的默认大小是按照单元格的大小 这里想调整到4个单元格那么大 所以向后 向下 各额外占用了一个单元格
commentData.setRelativeLastColumnIndex(1);
commentData.setRelativeLastRowIndex(1);
	// 4.将对象装入单元格
comment.setCommentData(commentData);
	// 5.将单元格装给模板
cellData.setCommentData(comment);

// 还可以通过这种方式设置“公式”但是不推荐使用，就不赘述了

④ 模板写入

使一部分数据按照指定模板导入到目标文件

非常简单的写法

String fileName ="E:\\JavaCode\\excel\\demo01\\src\\main\\java\\com\\llz\\easyexcel\\write\\dlc.xlsx";

String templateFileName ="E:\\JavaCode\\excel\\demo01\\src\\main\\java\\com\\llz\\easyexcel\\write\\dlc2.xlsx";

EasyExcel.write(fileName,CellData.class).withTemplate(templateFileName).sheet().doWrite(dataList());

⑤ 单元格合并

单元格合并有两种方式 注解、自定义

注解：通过@ExcelProperty(value = {}) 实现简单的头信息单元格合并

第三部分 easypoi

1. hello easypoi

① 依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.4.0</version>
</dependency>