EasyExcel使用经验《一》

EasyExcel概述
实现下载，并解决文件名称未显示中文的问题
excel文件表头自定义以及数据格式转换

1 EasyExcel概述

EasyExcel是基于POI实现的，更易于使用，且可解决由于下载/加载大文件时导致内存撑爆的问题的一套实用工具；

这套开源工具时阿里的人实现的，文档很详细，很值得参考；

源码地址：https://github.com/alibaba/easyexcel

官网文档地址：https://alibaba-easyexcel.github.io/index.html

本次使用EasyExcel的版本时目前最新的版本（2.2.6）

   <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>easyexcel</artifactId>
       <version>2.2.6</version>
   </dependency>
   <dependency>
       <groupId>org.apache.commons</groupId>
       <artifactId>commons-collections4</artifactId>
       <version>4.1</version>
   </dependency>

下载并写入Excel, 并解决文件名显示不出中文的问题；

 /**
     * 文件下载（失败了会返回一个有部分数据的Excel）
     * <p>1. 创建excel对应的实体对象 参照{@link DownloadData}
     * <p>2. 设置返回的 参数
     * <p>3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系, 用UTF-8'zh_cn'来显示声明文件名称中包含中文
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename*=UTF-8'zh_cn'" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());
    }

    /**
     * 文件上传
     * <p>1. 创建excel对应的实体对象 参照{@link UploadData}
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link UploadDataListener}
     * <p>3. 直接读即可
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener(uploadDAO)).sheet().doRead();
        return "success";
    }

/**
     * 生成文件、下载文件：这个方式支持分多次写入同一个或多个sheet，非常灵活
     * @param request
     * @param response
     */
    @GetMapping("/down.html")
    public void downloadExcel(HttpServletRequest request, HttpServletResponse response){
        List<User> data = getExcelModelData();
        OutputStream out = null;
        ExcelWriter excelWriter = null;
        try {
            out = response.getOutputStream();
            //设置ConetentType CharacterEncoding Header,需要在excelWriter.write()之前设置
            response.setContentType("mutipart/form-data");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition","attachment;filename=test.xlsx");

            excelWriter = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            Sheet sheet = new Sheet(0);
            sheet.setSheetName("sheet");
            sheet.setClazz(data.get(0).getClass());
            excelWriter.write(data,sheet);
            excelWriter.finish();
            out.flush();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                out.close();
            }catch (Exception e){
                e.printStackTrace();
            }

        }

    }

3 excel文件表格式自定义以及数据格式转换

第一步：定义实体：

package com.xxx.service.excel.model;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.xxx.service.excel.fieldhandle.DateHandlerConverter;
import com.xxx.service.excel.fieldhandle.StoreConfirmStatusHandlerConverter;
import com.xxx.service.excel.fieldhandle.StoreTypeHandlerConverter;
import lombok.Data;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

@Data
public class ProveidersMerchantExcelModel implements Serializable {

    private static final long serialVersionUID = 1L;

    @HeadStyle
    @ColumnWidth(15)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.RIGHT,
            borderLeft = BorderStyle.THIN,
            borderRight = BorderStyle.THIN,
            borderBottom = BorderStyle.THIN,
            borderTop = BorderStyle.THIN)
    @ExcelProperty(value = "店铺ID", index = 0)
    Long storeId;

    @HeadStyle
    @ColumnWidth(16)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,
            borderLeft = BorderStyle.THIN,
            borderRight = BorderStyle.THIN,
            borderBottom = BorderStyle.THIN,
            borderTop = BorderStyle.THIN)
    @ExcelProperty(value = "注册人手机号", index = 1)
    String mobilePhone;

    @HeadStyle
    @ColumnWidth(30)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.LEFT,
            borderLeft = BorderStyle.THIN,
            borderRight = BorderStyle.THIN,
            borderBottom = BorderStyle.THIN,
            borderTop = BorderStyle.THIN)
    @ExcelProperty(value = "店铺名称", index = 2)
    String storeName;

    @HeadStyle
    @ColumnWidth(15)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,
            borderLeft = BorderStyle.THIN,
            borderRight = BorderStyle.THIN,
            borderBottom = BorderStyle.THIN,
            borderTop = BorderStyle.THIN)
    @ExcelProperty(value = "店铺类型", index = 3, converter = StoreTypeHandlerConverter.class)
    String storeType;

    @HeadStyle
    @ColumnWidth(15)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,
            borderLeft = BorderStyle.THIN,
            borderRight = BorderStyle.THIN,
            borderBottom = BorderStyle.THIN,
            borderTop = BorderStyle.THIN)
    @ExcelProperty(value = "经营类目", index = 4)
    String storeCategoryName;

    @HeadStyle
    @ColumnWidth(15)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,
            borderLeft = BorderStyle.THIN,
            borderRight = BorderStyle.THIN,
            borderBottom = BorderStyle.THIN,
            borderTop = BorderStyle.THIN)
    @ExcelProperty(value = "审核状态", index = 5, converter = StoreConfirmStatusHandlerConverter.class)
    String status;

    @HeadStyle
    @ColumnWidth(26)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,
            borderLeft = BorderStyle.THIN,
            borderRight = BorderStyle.THIN,
            borderBottom = BorderStyle.THIN,
            borderTop = BorderStyle.THIN)
    @ExcelProperty(value = "提交审核时间", index = 6, converter = DateHandlerConverter.class)
    Long submmitConfirmTime;

    @HeadStyle
    @ColumnWidth(26)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,
            borderLeft = BorderStyle.THIN,
            borderRight = BorderStyle.THIN,
            borderBottom = BorderStyle.THIN,
            borderTop = BorderStyle.THIN)
    @ExcelProperty(value = "开店时间", index = 7, converter = DateHandlerConverter.class)
    Long openStoreTime;
}

第二步：定义格式转换的实现类

只需要实现 Converter<T> 支持上传和下载字段格式转换处理，目前只实现了下载的格式处理逻辑，亲测有效

DateHandlerConverter.class

package com.xx.service.excel.fieldhandle;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.xx.common.utils.DateUtils;
import org.springframework.util.StringUtils;

public class DateHandlerConverter implements Converter<Long> {

    @Override
    public Class supportJavaTypeKey() {
        return null;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Long convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    @Override
    public CellData convertToExcelData(Long o, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {

        if (o != null) {
            String tmp = DateUtils.longToDateStr(o);
            if(StringUtils.isEmpty(tmp)){
                return new CellData(o);
            }
            return new CellData(tmp);
        }
        return new CellData("");
    }
}

StoreTypeHandlerConverter.class

package com.xx.service.excel.fieldhandle;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

public class StoreTypeHandlerConverter implements Converter<String> {

    /**
     * 店铺类型 1:个人账户、2:企业
     */
    private static final String STATUS_PRIVATE = "1";
    private static final String STATUS_PRIVATE_NAME = "个人账户";
    private static final String STATUS_COMPANY = "2";
    private static final String STATUS_COMPANY_NAME = "企业";

    @Override
    public Class supportJavaTypeKey() {
        return null;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    @Override
    public CellData convertToExcelData(String o, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {

        if (o != null && o.equals(STATUS_PRIVATE)) {
            return new CellData(STATUS_PRIVATE_NAME);
        }else if (o != null && o.equals(STATUS_COMPANY)) {
            return new CellData(STATUS_COMPANY_NAME);
        }
        return new CellData("");
    }
}

StoreConfirmStatusHandlerConverter.class

package com.xx.service.excel.fieldhandle;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.xx.common.enums.StoreConfirmStatusEnum;

public class StoreConfirmStatusHandlerConverter implements Converter<String> {

    @Override
    public Class supportJavaTypeKey() {
        return null;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    @Override
    public CellData convertToExcelData(String o, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        StoreConfirmStatusEnum statusEnum = StoreConfirmStatusEnum.getStoreConfirmStatusEnumByStatus(o);
        if (statusEnum != null) {
            return new CellData(statusEnum.getRemark());
        }else {
            return new CellData(o);
        }
    }
}

文件下载扩展
文件下载还有个方案，那就是通过内网查询，并上传COS等对象/文件存储服务器，然后返回一个文件的下载地址给前端，然后前端通过下载地址去外网去下载文件；
在这个方案中，EasyExceld的另一种写法：

final String fileName = "file" + ".xlsx";
try {
   // 创建一个本地临时文件
   File file = new File(fileName);
   // 查询并将结果写入本地Excel文件
   EasyExcel.write(file, ProviderInfoExcelModel.class).sheet("sheet").doWrite(data(condition));
   // 将文件上传COS服务器
   URL url = cosService.putTmpExcelToCOS(""+DateUtils.getCurrentDate() + ".xlsx",file);
   // 删除临时文件
   if(!file.delete()){
       log.warn("删除本地临时文件失败");
   }
   return url;
}catch (Exception ex){
   throw new BizException("IO异常："+ex.getMessage());
}

导出结果如下图所示：

posted @ 2020-08-11 17:50 OutPointException 阅读(934) 评论(0) 收藏举报

刷新页面返回顶部

OutPointException

EasyExcel使用经验《一》

公告