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导入![Excel导入结果](https://i-blog.csdnimg.cn/blog_migrate/e1d559e5603e5ee965ab04184dff2b0d.png)
3 常见问题及解决方案
3.1 Excel导出失败
检查POJO是否存在@Excel注解
3.2 导入的数据,日期全为Null而其他类型数据正常
日期类型需要在@Excel的format属性中定义导入的日期格式,否则很可能会解析不出来
3.3 导入成功而导出失败,提示原因为ClassNotFound
像这种问题应该从两方面排查:
- 依赖冲突:已存在的依赖是否与EasyPOI的依赖有冲突,而代码实际执行的依赖是你另外导入的依赖版本,而不是poi自带的版本
- 版本太旧,可以尝试升级版本
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即可