Alibaba的EasyExcel使用心得

╰+哭是因爲堅強的太久メ 2024-03-26 10:31 62阅读 0赞

**目录**

前言:

开发前准备：

1、导入依赖

2、导出实体类

1）常用注解

2）注意内容

3、监听器

ExcelMapper

Handler

4、模板文件

5、测试demo

1）调整单元格样式

2）插入图片

最终效果：

项目实战：

1、读Excel

Controller

Service

ServiceImpl

2、写Excel

Controller

Service

ServiceImpl

## 前言: ##

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便。

官网：[EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel (alibaba.com)][EasyExcel_ - _Java_Excel_ _ Easy Excel _alibaba.com]

## 开发前准备： ##

### 1、导入依赖 ###

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

### 2、导出实体类 ###

import com.alibaba.excel.annotation.ExcelIgnore;
    import com.alibaba.excel.annotation.ExcelProperty;
    import com.baomidou.mybatisplus.annotation.TableName;
    import lombok.Data;
    
    import java.util.Date;
    
    @Data
    @TableName("user")
    public class ExcelUserEntityImport {
        private static final long serialVersionUID = 1L;
    
        @ExcelProperty(value = "用户名")
        private String username;
        @ExcelProperty(value = "密码")
        private String password;
    
        @ExcelProperty(value = "手机号")
        private String phone;
        @ExcelProperty("性别标签")
        private Integer sex;
    
        @ExcelProperty({"基础信息","年龄"})
        private Integer age;
    
        @ExcelProperty({"基础信息","性别"})
        private String gender;
    
        @ExcelProperty(value ={"基础信息","地址"})
        private String address;
    
        @ExcelIgnore
        //@ExcelProperty(converter = DateConverter.class,value = "生日")
        private Date birthday;
    
        //忽略导出字段
        @ExcelIgnore
        private Long id;
        @ExcelIgnore
        private String hobby;
        @ExcelIgnore
        private Date creatTime;
        @ExcelIgnore
        private Date updateTime;
        @ExcelIgnore
        private Integer isDeleted;
    }

#### 1）常用注解 ####

@ExcelProperty(value = "用户名",index=0) ->导出属性名

@ExcelIgnore ->忽略导出的字段

#### 2）注意内容 ####

如果在@ExcelProperty中不指定index的值，index会默认从0开始排版，如果指定顺序，导出的表格则会按照index顺序进行排版。

### 3、监听器 ###

import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.alibaba.fastjson.JSON;
    import lombok.extern.slf4j.Slf4j;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    
    import java.util.ArrayList;
    import java.util.List;
    
    // 有个很重要的点 UserDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    @Slf4j
    public class UserDataListener extends AnalysisEventListener<ExcelUserEntityImport> {
    
        private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUserEntityImport.class);
        /**
         * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
         */
        private static final int BATCH_COUNT = 100;
        /**
         * 缓存的数据
         */
        private List<ExcelUserEntityImport> list = new ArrayList<>(BATCH_COUNT);
        @Override
        public void invoke(ExcelUserEntityImport data, AnalysisContext analysisContext) {
            LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
            String gender = data.getGender();
            Integer sex = getSexByGender(gender);
            data.setSex(sex);
            /**
             * 自己要完成的逻辑
             * 进行自己功能的实现
             */
    
            list.add(data);
            if (list.size() >= BATCH_COUNT) {
                //            saveData();
                // 存储完成清理 list
                list = new ArrayList<>(BATCH_COUNT);
            }
        }
    
        public Integer getSexByGender(String gender) {
            switch (gender){
                case "男":
                    return 1;
                case "女":
                    return 2;
                case "未知":
                    return 3;
                default:
                    throw new RuntimeException("性别异常");
            }
    
        }
    
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            LOGGER.info("所有数据解析完成！");
        }
    
        /**
         * 自己要完成的逻辑
         * 进行自己功能的实现
         */
        public void doSome(){}
    }

#### ExcelMapper ####

public interface ExcelMapper extends BaseMapper<ExcelUserEntityImport> {
    }

#### Handler ####

import com.alibaba.excel.write.handler.SheetWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
    import org.apache.poi.ss.usermodel.DataValidation;
    import org.apache.poi.ss.usermodel.DataValidationConstraint;
    import org.apache.poi.ss.usermodel.DataValidationHelper;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddressList;
    
    import java.util.HashMap;
    import java.util.Map;
    
    //要在导出时添加到对应的handler中
    public class ExcelTemplateHandler implements SheetWriteHandler {
    
        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    
        }
    
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    
            String[] sexTypes = new String[]{"男", "女", "未知"};
            Map<Integer, String[]> mapDropDown = new HashMap<>();
            // 3 为导出表格索引，索引冲0开始。 如不在excel导出实体类指定，则按照先后顺序
            mapDropDown.put(3, sexTypes);
            Sheet sheet = writeSheetHolder.getSheet();
            ///开始设置下拉框
            DataValidationHelper helper = sheet.getDataValidationHelper();//设置下拉框
            for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
                /***起始行、终止行、起始列、终止列**/
                CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, entry.getKey(), entry.getKey());
                /***设置下拉框数据**/
                DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
                DataValidation dataValidation = helper.createValidation(constraint, addressList);
                /*处理Excel兼容性问题
    			if (dataValidation instanceof XSSFDataValidation) {
    				dataValidation.setSuppressDropDownArrow(true);
    				dataValidation.setShowErrorBox(true);
    			} else {
    				dataValidation.setSuppressDropDownArrow(false);
    			}*/
                sheet.addValidationData(dataValidation);
            }
        }
    }

### 4、模板文件 ###

![ea19f3249a0d47f3bc5f02732771909b.png][]

### 5、测试demo ###

@Test
        @SneakyThrows
        public void test() {
            /**
             * 1、进行模板填充
             */
            // 生成Excel路径
            String filePath = "a.xlsx";
            //模板路径
            String templatePath = "temp.xlsx";
            ExcelWriter excelWriter = EasyExcel.write(filePath).withTemplate(templatePath).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            //代表表格每次都会重新生成新的一行，而不是使用下面的空行。
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            // 填充数据
            ExcelTemplate template = new ExcelTemplate();
            template.setEaddress("河南省xxxx")
                    .setEbegintime(new Date())
                    .setEdesc("摸鱼ing")
                    .setEendtime(new Date())
                    .setEidcard("41xxxxxxxxxxx")
                    .setEmajor("电子商务")
                    .setEmarriage("未婚")
                    .setEname("小张")
                    .setEnation("汉")
                    .setEbirthday(new Date())
                    .setEpost("天才少女")
                    .setEgradeationtime(new Date())
                    .setEschool("xxxx")
                    .setEsex("女")
                    .setEworkcom("天庭六部")
                    .setEsalary(10000000.0)
                    .setEnative("未知");
    
            //进行字段的填充
            excelWriter.fill(template, writeSheet);
            //进行模板表格的填充 datal和模板相互对应
            //excelWriter.fill(new FillWrapper(template, template, fillConfig, writeSheet);
            // 别忘记关闭流
            excelWriter.finish();

进行测试后发现，单元格相关没有对应格式。

#### 1）调整单元格样式 ####

/**
             * 2、进行单元格合并 样式调整
             */
            //注意：一定要先把文件创建完后再进行单元格样式设置
            FileInputStream inputStream = new FileInputStream(new File(filePath));
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);
            // 合并列 列和行均为0开始
            sheet.addMergedRegion(new CellRangeAddress(16, 16, 1, 2));
            sheet.addMergedRegion(new CellRangeAddress(16, 16, 3, 4));
            sheet.addMergedRegion(new CellRangeAddress(16, 16, 5, 6));
            sheet.addMergedRegion(new CellRangeAddress(16, 16, 7, 8));
            sheet.addMergedRegion(new CellRangeAddress(16, 16, 9, 10));
            sheet.addMergedRegion(new CellRangeAddress(16, 16, 11, 13));
            //进行下边框的设置
            RegionUtil.setBorderBottom(BorderStyle.THIN,new CellRangeAddress(15, 15, 1, 13),sheet);

#### 2）插入图片 ####

/**
             * 3、插入图片
             */
            // 转换成流
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            BufferedImage bufferImg = ImageIO.read(new File("note.jpg"));
            ImageIO.write(bufferImg, "jpg", byteArrayOut);
            XSSFDrawing patriarch = sheet.createDrawingPatriarch();
            /**
             * new XSSFClientAnchor(0, 0, 0, 0, (short) 11, 2, (short) 12, 6);
             * 前4个参数是偏移量，设置为0即可 row1为起始行 row2结束行 前到后不到 (short) 11为起始列 (short) 12 为结束列
             * 设置好这些参数就能在指定位置插入图片
             */
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 11, 3, (short) 14, 14);
            anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
            patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
    
            String mergeExcelPath="merge.xlsx";
            FileOutputStream outputStream = new FileOutputStream(mergeExcelPath);
            workbook.write(outputStream);
            outputStream.flush();

#### 最终效果： ####

![16e42934db1446969914b90b006cdd6f.png][]

##  ##

## 项目实战： ##

### 1、读Excel ###

#### Controller ####

@GetMapping("/exportExcel")
        @ApiOperation(value = "批量导出基础字段文件", notes = "导出file")
        public void exportExcel(HttpServletResponse response, @RequestParam String ids) throws IOException {
            List<Long> list = Func.toLongList(ids);
            userService.exportExcel(response, list);
        }

#### Service ####

void exportExcel(HttpServletResponse response, List<Long> list);

#### ServiceImpl ####

@SneakyThrows
        @Override
        public void exportExcel(HttpServletResponse response, List<Long> list) {
            //导出所需数据(自己要的数据)
            List<User> userList = baseMapper.selectBatchIds(list);
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("用户数据", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), ExcelUserEntityImport.class)
                    .registerConverter(new LongStringConverter())
                    //样式的处理类 需要就注册进来.registerWriteHandler(new ExcelTemplateHandler())
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .sheet("数据导出").doWrite(userList);
        }

### 2、写Excel ###

#### Controller ####

/**
         * 上传文件
         * @param file 文件
         */
        @PostMapping("/uploadFile")
        @ApiOperation(value = "上传文件", notes = "传入file")
        public R uploadFile(@RequestParam("file") MultipartFile file) throws IOException {
            return userService.uploadFile(file);
        }

#### Service ####

R uploadFile(MultipartFile file);

#### ServiceImpl ####

@Override
        public R uploadFile(MultipartFile file) {
            List<ExcelUserEntityImport> excelList=null;
            try {
                excelList = read(file.getInputStream(), ExcelUserEntityImport.class, new UserDataListener()).sheet().doReadSync();
            } catch (IOException e) {
                e.printStackTrace();
            }
            excelService.saveBatch(excelList);
            return R.ok();
        }

这样下来就大功告成了！

[EasyExcel_ - _Java_Excel_ _ Easy Excel _alibaba.com]: https://easyexcel.opensource.alibaba.com/
[ea19f3249a0d47f3bc5f02732771909b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/6d7d0b0b7ca343889bc055fadc6d6cce.png
[16e42934db1446969914b90b006cdd6f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/6f1bdc6e55fc404dbffa7ff48c76a325.png