Alibaba的EasyExcel使用心得-摩杜云开发者社区

前言:

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)

开发前准备：

1、导入依赖

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

2、导出实体类

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(){}
}

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、模板文件

Alibaba的EasyExcel使用心得_java

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();

最终效果：

Alibaba的EasyExcel使用心得_ide_02

项目实战：

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();
    }

转自：https://blog.csdn.net/m0_71239320/article/details/129087986