POI excel导出与easyexcel导入实践

（一） 准备

1.0 pom依赖

备注：由于easyExcel自带依赖于3.17的poi，所以如果项目其它地方映入了其它版本的poi，就会导致easyExcel导出失败等一系列问题，主要原因是因为poi版本冲突导致的

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
<!--          <version>3.6</version>-->
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
<!--          <version>3.6</version>-->
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
<!--          <version>3.6</version>-->
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>net.sf.saxon</groupId>
    <artifactId>saxon-dom</artifactId>
    <version>8.7</version>
    <scope>compile</scope>
</dependency>

2.0 接口controller层：

 @PostMapping("/export/hasDeliveredOrder")
    public BaseResponse exportHasDeliveredOrder(HttpServletResponse response, @RequestBody HasDeliverExportReq hasDeliverExportReq) {
        log.info("开始导出已发货订单列表");
        List<HasDeliverResp> list = localSpeService.exportHasDeliveredOrder(hasDeliverExportReq);


        // 创建excel
        HSSFWorkbook wk = new HSSFWorkbook();
        // 创建一张工作表
        HSSFSheet sheet = wk.createSheet();
        // 2
        sheet.setColumnWidth(0, 5000);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle style = wk.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
//        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中 POI版本为3.9
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setWrapText(true);//自动换行
        style.setIndention((short) 5);//缩进
        HSSFFont font = wk.createFont();
        font.setFontName("微软雅黑");//设置字体名称
        font.setFontHeightInPoints((short) 10);//设置字号
        style.setFont(font);
        // 创建第一行的第一个单元格
        // 单元格写值
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("订单编号");
        cell = row.createCell((short) 1);
        cell.setCellValue("订单状态");
        cell = row.createCell((short) 2);
        cell.setCellValue("用户id");
        cell = row.createCell((short) 3);
        cell.setCellValue("用户昵称");
        cell = row.createCell((short) 4);
        cell.setCellValue("商品名称");
        cell = row.createCell((short) 5);
        cell.setCellValue("商品套餐");
        cell = row.createCell((short) 6);
        cell.setCellValue("套餐数量");
        cell = row.createCell((short) 7);
        cell.setCellValue("收货地址");
        cell = row.createCell((short) 8);
        cell.setCellValue("收货人");
        cell = row.createCell((short) 9);
        cell.setCellValue("联系方式");
        cell = row.createCell((short) 10);
        cell.setCellValue("发货时间");
        cell = row.createCell((short) 11);
        cell.setCellValue("物流公司");
        cell = row.createCell((short) 12);
        cell.setCellValue("物流单号");
        // 创建第一行
        for (short i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(list.get(i).getOrderNumber());
            row.createCell(1).setCellValue(list.get(i).getOrderStatusName());
            row.createCell(2).setCellValue(list.get(i).getUserId());
            row.createCell(3).setCellValue(list.get(i).getUserName());
            row.createCell(4).setCellValue(list.get(i).getCommodityName());
            row.createCell(5).setCellValue(list.get(i).getGoodsProducts());
            row.createCell(6).setCellValue(list.get(i).getGoodsNum());
            row.createCell(7).setCellValue(list.get(i).getAddress());
            row.createCell(8).setCellValue(list.get(i).getReceiver());
            row.createCell(9).setCellValue(list.get(i).getMobile());
            row.createCell(10).setCellValue(list.get(i).getDeliveryTime());
            row.createCell(11).setCellValue(list.get(i).getExpressName());
            row.createCell(12).setCellValue(list.get(i).getExpressNumber());
        }
        try {
            /**
             * 弹出下载选择路径框
             */
            response.setContentType("application/octet-stream");
            /**
             * 默认Excel名称
             */
            response.setHeader("Content-disposition", "attachment;filename=Opinion.xls");
            response.flushBuffer();
            wk.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        } finally {

        }
        return  null;
    }

/**
 * exsyExcel批量导入物流单号信息
 * @param file
 * @return
 * @throws IOException
 */
@PostMapping("/importExcel")
public BaseResponse importData(@RequestParam("file") MultipartFile file) throws IOException {
    if (file == null) return BaseResponse.buildSuccess("404", null);
    try {
        EasyExcel.read(file.getInputStream(), ImportLogistics.class, new ImportLogisticsExcelListener(localSpeService)).sheet().doRead();
    }catch (Exception e){
        e.printStackTrace();
        logger.error("上传excel失败，失败信息："+e.getMessage());
        return BaseResponse.buildFail("系统繁忙");
    }
    return BaseResponse.buildSuccess("200",  "ok");
}

 

service：List<HasDeliverResp> exportHasDeliveredOrder(HasDeliverExportReq hasDeliverExportReq);

easyexcel读入需要跳过spring   创建新的监听：不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.cxqy.business.modules.localspeorder.request.ImportLogistics;
import com.cxqy.business.modules.localspeorder.service.LocalSpeService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;

/**
 * @Author yjl
 * @Date 2021/11/9 16:06
 * @Version 1.0
 */



public class ImportLogisticsExcelListener extends AnalysisEventListener<ImportLogistics> {




    private static final Logger LOGGER = LoggerFactory.getLogger(ImportLogisticsExcelListener.class);


    /**
     * 因为DataListener 不能被spring管理，所以每次读取excel都要new DataListener,如果DataListener里面用到spring管理的对象，比如操作需要数据库的话Service和Dao可以通过构造方法传进来
     */
    private LocalSpeService localSpeService;

    /**
     * 每读取1000条数据进行校验身份证，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;

    List<ImportLogistics> list = new ArrayList<>();


    public ImportLogisticsExcelListener(LocalSpeService localSpeService) {
        this.localSpeService = localSpeService;
    }

    /**
     * 这个方法每一条数据解析都会来调用-easyExcel会自动过滤掉excel中的第一行(默认第一行为列名)
     * 只有解析到数据才会调用，如果上传的是空excel的话该方法不会调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(ImportLogistics data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", data.toString());
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     * 也就是每次请求接口就会调用一次该方法，空excel也会调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据检测完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        LOGGER.info("----->"+list);
        if (list.size() > 0){
            localSpeService.saveBatch(list);
        }
        LOGGER.info("存储数据库成功！");
    }

}

实体类对应excel表格：

@Data
public class ImportLogistics {

    @ExcelProperty(value = "订单编号",index = 0)
    private String orderNumber;

//    @DateTimeFormat("yyyy/mm/dd/HH/mm/ss")
    @ExcelProperty(value = "发货日期",index = 3)
    private String createTime;

    @ExcelProperty(value = "物流公司",index = 1)
    private String logcop;

    @ExcelProperty(value = "物流单号",index = 2)
    private String expressNumber;

}

实现类：

@Override
public boolean saveBatch(List<ImportLogistics> list) {
    if(list != null){
        TOrderLogistics tOrderLogistics = new TOrderLogistics();
        for (ImportLogistics importLogistics: list){
            if (importLogistics.getExpressNumber() !=null){
                BeanUtils.copyProperties(importLogistics, tOrderLogistics);
                int i = tOrderLocalSpeMapperExt.addExpresss(tOrderLogistics);
            }
        }
        return true;
        }
    return false;
}

3.0 后续补上导出：
...

完整pom文件：

<dependencies>
    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-starter-netflix-eureka-client</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.2.10</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-security</artifactId>
    </dependency>
    <dependency>
        <groupId>com.cxqy.common</groupId>
        <artifactId>aliyun-rocketmq-spring-boot-starter</artifactId>
        <version>1.1.0-SNAPSHOT</version>
    </dependency>
    <dependency>
        <groupId>com.cxqy.common</groupId>
        <artifactId>netease-sms-spring-boot-starter</artifactId>
        <version>1.1.2-RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-redis</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>tk.mybatis</groupId>
        <artifactId>mapper</artifactId>
    </dependency>
    <dependency>
        <groupId>com.belerweb</groupId>
        <artifactId>pinyin4j</artifactId>
        <version>2.5.0</version>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.2</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>4.1.2</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-openfeign</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>commons-validator</groupId>
<artifactId>commons-validator</artifactId>
<version>1.6</version>
</dependency>
<!--支付宝支付-->
    <!-- https://mvnrepository.com/artifact/com.alipay.sdk/alipay-easysdk -->
<dependency>
<groupId>com.alipay.sdk</groupId>
<artifactId>alipay-easysdk</artifactId>
<version>2.0.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alipay.api/k12-alipay-sdk -->
<dependency>
<groupId>com.alipay.api</groupId>
<artifactId>k12-alipay-sdk</artifactId>
<version>0.0.1-beta</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.62</version>
</dependency>
<!--文本安全检测-->

<dependency>
<groupId>com.aliyun</groupId>
<artifactId>aliyun-java-sdk-green</artifactId>
<version>3.4.1</version>
</dependency>
<dependency>
<groupId>com.aliyun</groupId>
<artifactId>aliyun-java-sdk-core</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>com.aliyun.oss</groupId>
<artifactId>aliyun-sdk-oss</artifactId>
<version>2.8.3</version>
</dependency>
<dependency>
<groupId>com.aliyun.openservices</groupId>
<artifactId>ons-client</artifactId>
<version>1.8.0.Final</version>
</dependency>
<dependency>
<groupId>com.auth0</groupId>
<artifactId>java-jwt</artifactId>
<version>3.4.1</version>
</dependency>
<dependency>
<groupId>com.cxqy.common</groupId>
<artifactId>netease-sms-spring-boot-starter</artifactId>
<version>1.1.2-RELEASE</version>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.3</version>
</dependency>
<dependency>
<groupId>com.cxqy.common</groupId>
<artifactId>aliyun-rocketmq-spring-boot-starter</artifactId>
<version>1.1.0-SNAPSHOT</version>
</dependency>