EasyPOI导入导出Excel:内含工具类,可直接使用

1 操作步骤

1.0 引入依赖

      <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.0.0</version>
        </dependency>

1.1 引入EasyPOI工具类

package com.gosun.pcms.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.export.styler.ExcelExportStylerBorderImpl;

import org.apache.poi.ss.usermodel.Workbook;
import org.bouncycastle.util.StoreException;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;

public class PoiUtils {

    /**
     * Excel导出工具类
     * @param list 待转换实体集合
     * @param title 表单表头
     * @param sheetName sheet名称
     * @param pojoClass 实体的Class对象
     * @param fileName Excel文件名称
     * @param response 响应:Excel将放入响应的输出流中
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws StoreException {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setStyle(ExcelExportStylerBorderImpl.class);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws StoreException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null)
        downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws StoreException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName+".xls", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new StoreException(e.getMessage(), e);
        }
    }

    /**
     * Excel导入工具类
     * @param file 上传的Excel文件
     * @param classType 待转化的Class对象 
     * @param <T> T 方法独立
     * @return 转化完毕的对象集合 
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> classType) throws Exception {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        return ExcelImportUtil.importExcel(file.getInputStream(), classType, params);
    }
}

1.2 实体类增加@Excel注解

在需要导入导出的实体类上面添加@Excel注解即可,该注解支持属性有name(表单列名)和width(列宽)等

package com.gosun.pcms.bean.vo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.*;


import java.util.Date;

/**
 * @author yiqiang
 * @date 2020/8/10 19:55
 */
@Getter
@Setter
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value = "DetentionAlarmVO",description = "扣车告警信息")
public class AccidentInfoVO {

    @Excel(name = "UUID")
    @ApiModelProperty(value = "主键")
    private String uuid;

    @Excel(name = "事故类型")
    @ApiModelProperty(value = "事故类型ID")
    private String accidentTypeId;

    @Excel(name = "发生时间",format = "yyyy-MM-dd HH:mm:ss")
    @ApiModelProperty(value = "发生时间")
    private Date accidentTime;

    @Excel(name = "事故发生地点")
    @ApiModelProperty(value = "事故发生地点")
    private String accidentLocation;

    @ApiModelProperty(value = "经度")
    private String accidentLongitude;

    @ApiModelProperty(value = "纬度")
    private String accidentLatitude;

    @Excel(name = "涉案车辆A")
    @ApiModelProperty(value = "涉案车辆A")
    private String vehicleA;

    @Excel(name = "涉案车辆B")
    @ApiModelProperty(value = "涉案车辆B")
    private String vehicleB;

    @Excel(name = "主要责任人")
    @ApiModelProperty(value = "主要责任人")
    private String principalResponse;

    @Excel(name = "次要责任人")
    @ApiModelProperty(value = "次要责任人")
    private String secondaryResponse;

    @Excel(name = "事故详情")
    @ApiModelProperty(value = "事故详情")
    private String accidentDetail;

    @Excel(name = "归档时间",format = "yyyy-MM-dd HH:mm:ss")
    @ApiModelProperty(value = "归档时间")
    private Date pigeonholeTime;
}

1.3 controller层测试代码

package com.gosun.pcms.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.gosun.pcms.bean.PageCondition;
import com.gosun.pcms.bean.PageInfo;
import com.gosun.pcms.bean.pojo.PcmsAccidentInfo;
import com.gosun.pcms.bean.pojo.PcmsDetentionAlarm;
import com.gosun.pcms.bean.pojo.PcmsDetentionVehicleInfo;
import com.gosun.pcms.bean.vo.AccidentInfoVO;
import com.gosun.pcms.bean.vo.DetentionAlarmVO;
import com.gosun.pcms.service.AccidentInfoService;
import com.gosun.pcms.util.PoiUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.List;

/**
 * @author yiqiang
 * @date 2020/8/10 17:41
 */
@RequestMapping(value = "/accidentInfo")
@RestController
@Api(value = "事故信息",description = "事故信息")
public class AccidentInfoController {

    @Resource
    private AccidentInfoService accidentInfoService;

    @GetMapping(value = "/exportAccidentInfo")
    @ApiOperation(value = "导出事故信息")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "curPage", value = "当前页", dataType = "String", paramType = "query",required = true),
            @ApiImplicitParam(name = "pageSize", value = "分页大小", dataType = "String", paramType = "query",required = true)
    })
    public void exportAccidentInfo(String curPage, String pageSize, HttpServletResponse response){
        PageCondition<PcmsAccidentInfo> condition= PageCondition.<PcmsAccidentInfo>builder()
                .pageSize(Integer.parseInt(pageSize))
                .curPage(Integer.parseInt(curPage))
                .build();
        PageInfo<List<AccidentInfoVO>> list = accidentInfoService.getAccidentInfoList(condition);
        PoiUtils.exportExcel(list.getRows(),"事故信息表","事故信息",AccidentInfoVO.class,"事故信息表",response);
    }

    @PostMapping(value = "/importAccidentInfo")
    @ApiOperation(value = "导入事故信息")
    public void importAccidentInfo(@RequestBody MultipartFile file) throws Exception {
        PoiUtils.importExcel(file,AccidentInfoVO.class).forEach(System.out::println);
    }

}

2 结果展示

2.1 Excel导出

调用http://localhost:8080/accidentInfo/exportAccidentInfo?currentPage=1&pageSize=10导出Excel

2.2 Excel导入

3 常见问题及解决方案

3.1 Excel导出失败

检查POJO是否存在@Excel注解

3.2 导入的数据,日期全为Null而其他类型数据正常

日期类型需要在@Excel的format属性中定义导入的日期格式,否则很可能会解析不出来

3.3 导入成功而导出失败,提示原因为ClassNotFound

像这种问题应该从两方面排查:

1. 依赖冲突:已存在的依赖是否与EasyPOI的依赖有冲突,而代码实际执行的依赖是你另外导入的依赖版本,而不是poi自带的版本
2. 版本太旧,可以尝试升级版本

4 一些建议

只导入你所需要的依赖,不然很容易冲突
比如EasyPOI的依赖,很多人喜欢按下面这种导入方法

      <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

分析一下上述依赖的包含关系:
1.web包含了base,而base包含了annotation,
2.web里面是在base基础上多了spring的依赖,但多数情况我们都会自定义spring的版本
故如果仅仅需要使用EasyPOI,只需要导入base即可