4

比 poi导入导出更好用的 EasyExcel使用小结 - 香吧香

 1 year ago
source link: https://www.cnblogs.com/zjdxr-up/p/17201440.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

转载请注明出处：

　　官方文档: https://easyexcel.opensource.alibaba.com/docs/current/quickstart/read

　　Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。 ​ easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便

　　优点：节省内存消耗，可大量减少网络开销

2.使用方法

2.1.引入依赖

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

2.2 导入

　　导入对应的表格字段解析实体类：

public class ConfigImportExcelRow implements Serializable {

    @ExcelProperty(value = "姓名", index = 0)
    private String userName;

    /**
     * 昵称
     */
    @ExcelProperty(value = "昵称", index = 1)
    private String nickName;

    @ExcelProperty(value = "年龄", index = 2)
    private Integer age;

}
@PostMapping("/import")
    public JSONResult import(@RequestParam(name = "file") MultipartFile file, HttpServletResponse response) {
    
    List<ConfigImportExcelRow> userRowList = null;
        try {
            userRowList = EasyExcel.read(file.getInputStream()).headRowNumber(2)
                    .head(ConfigImportExcelRow.class).sheet().doReadSync();
        } catch (Exception e) {
            LOGGER.error("导入配置异常", e);
        }
        
        return  "导入结果";
    }

　　head()指定Excel行对应的POJO

　　sheet() 读取第一个sheet

　　doReadSync() ; 同步读取会自动finish

　　headRowNumber 从头的第几行开始读取：可以设置1，因为头就是一行。如果多行头，可以设置其他值。不传入也可以没有指定头，也就是默认1行

　　当有多个 sheet的时候，可以将 read 改为 readSheet 方法：

　　官方示例：

1110857-20230309210526298-1192906820.png

2.3 导出

　　同样对导出的实体字段添加 @ExcelProperty 注解

public class ConfigExportExcelRow implements Serializable {

    @ExcelProperty(value = "姓名", index = 0)
    private String userName;

    /**
     * 昵称
     */
    @ExcelProperty(value = "昵称", index = 1)
    private String nickName;

    @ExcelProperty(value = "年龄", index = 2)
    private Integer age;

}

　　调用示例

@GetMapping("/export")
    @ApiOperation(value = "导出明细")
    public JSONResult importUser(HttpServletResponse response) {
        
        return JSONResult.okResult();
    }
    
    public void exportWorkerPlan(Long cycleId, HttpServletResponse response) {

        try {
            List<ConfigExportExcelRow> excelRowList = new ArrayList<>();
            ConfigExportExcelRow result1 = new ConfigExportExcelRow();
            result1.setUserName("张三");
            result1.setNickName("张三");
            result1.setAge(22);
            excelRowList.add(result1);
            AssessPersonConfigImportResult result2 = new AssessPersonConfigImportResult();
            result2.setUserName("李四");
            result2.setNickName("李四");
            result2.setAge("33");
            excelRowList.add(result2);

            String fileFix = DateUtils.formatDate(System.currentTimeMillis(), DateFormatEnum.YYYYMMDDHHMMSSS);
            String fileName = "导出结果_" + fileFix;
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            fileName = URLEncoder.encode(fileName, "UTF-8").replace("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            // 内容样式策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 垂直居中,水平居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            // 字体策略
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short) 10);
            contentWriteCellStyle.setWriteFont(contentWriteFont);

            //设置输出流和模板信息
            File excelTemplate = ResourceUtils.getFile(EXCEL_TEMPLATE_PATH);
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    .registerWriteHandler(new HorizontalCellStyleStrategy(null, contentWriteCellStyle))
                    .excelType(ExcelTypeEnum.XLSX)
                    .withTemplate(excelTemplate).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            //开启自动换行,自动换行表示每次写入一条list数据是都会重新生成一行空行,此选项默认是关闭的,需要提前设置为true
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(excelRowList, fillConfig, writeSheet);
            excelWriter.finish();
        } catch (Exception e) {
            LOGGER.error("导出Excel失败, errorMessage={}", e.getMessage(), e);
            throw new BusinessException(ActionStatus.PARAMAS_ERROR.inValue(), "人员设置导出Excel失败！");
        }
    }

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK