【Java用法】使用EasyPoi导入与导出Excel文档的解决方案

叁歲伎倆 2022-10-07 10:45 429阅读 0赞

这是另一种写法，与之前的博客[【Java用法】使用poi写Java代码导出Excel文档的解决方案][Java_poi_Java_Excel]，  
最终实现的功能都一样。

## 第一步：添加Maven依赖 ##

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

## 第二步：添加注解 ##

下面是需要导出的实体类，需要添加 **@Excel** 注解；该注解类来自于**cn.afterturn.easypoi.excel.annotation** 包中。以下是我项目中的实体类代码：

package com.iot.wop.hvac.entity;
 
 import cn.afterturn.easypoi.excel.annotation.Excel;
 import com.fasterxml.jackson.annotation.JsonFormat;
 import io.swagger.annotations.ApiModel;
 import io.swagger.annotations.ApiModelProperty;
 import lombok.Data;
 import org.springframework.format.annotation.DateTimeFormat;
 
 import java.io.Serializable;
 import java.util.Date;
 
 /**
 * HvacBrand 此类用于：对接申请实体
 * @author：hujm
 * @date：2021年06月06日 19:43
 * @remark：
 */
 @ApiModel(value = "对接申请实体")
 @Data
 public class HvacJointApply implements Serializable {
 
 @ApiModelProperty(value = "对接申请编号", example = "1")
 @Excel(name = "申请编号", orderNum = "0", width = 15)
 private Integer id;
 
 @ApiModelProperty(value = "品牌名称")
 @Excel(name = "品牌名称", orderNum = "1", width = 20)
 private String brandName;
 
 @ApiModelProperty(value = "暖通类型：1、空调，2、地暖，3、新风")
 @Excel(name = "暖通类型", orderNum = "2", width = 15, replace = {"空调_1", "地暖_2", "新风_3"})
 private Integer hvacType;
 
 @ApiModelProperty(value = "型号")
 @Excel(name = "型号", orderNum = "3", width = 20)
 private String model;
 
 @ApiModelProperty(value = "技术负责人姓名")
 @Excel(name = "技术负责人姓名", orderNum = "4", width = 20)
 private String technicalDirectorName;
 
 @ApiModelProperty(value = "需求时间")
 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
 @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
 @Excel(name = "需求时间", orderNum = "5", width = 20, format = "yyyy-MM-dd HH:mm:ss")
 private Date demandTime;
 
 @ApiModelProperty(value = "联系方式")
 @Excel(name = "联系方式", orderNum = "6", width = 20)
 private String contactWay;
 
 @ApiModelProperty(value = "项目名称")
 @Excel(name = "项目名称", orderNum = "7", width = 20)
 private String projectName;
 
 @ApiModelProperty(value = "审核状态：1、通过，0、未通过")
 @Excel(name = "审核状态：1、通过，0、未通过", orderNum = "8", width = 15, replace = {"通过_1", "未通过_0"})
 private Integer auditStatus;
 
 @ApiModelProperty(value = "是否有现场：1、是，0、否；（暖通设备是否可以正常运行）")
 @Excel(name = "是否有现场（暖通设备是否可以正常运行）", orderNum = "9", width = 15, replace = {"是_1", "否_0"})
 private Integer isPresent;
 
 @ApiModelProperty(value = "申请提交人")
 @Excel(name = "申请提交人手机号", orderNum = "10", width = 20)
 private String createUser;
 
 @ApiModelProperty(value = "申请提交人姓名")
 @Excel(name = "申请提交人姓名", orderNum = "11", width = 15)
 private String createName;
 
 @ApiModelProperty(value = "申请提交时间")
 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
 @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
 @Excel(name = "申请提交时间", orderNum = "12", width = 20, format = "yyyy-MM-dd HH:mm:ss")
 private Date createTime;
 
 @ApiModelProperty(value = "更新申请人")
 private String updateUser;
 
 @ApiModelProperty(value = "更新申请人姓名")
 private String updateName;
 
 @ApiModelProperty(value = "更新时间")
 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
 @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
 private Date updateTime;
 }

## 第三步：引入工具类 ##

在项目中引入 **EasyPoiUtils.java 工具类。**工具类代码详情请查看拓展里的 **EasyPoiUtils.java** 代码。

## 第四步：编写导出导入方法 ##

以下分别贴上Controller、Service与ServiceImpl类中的代码：

**Controller**层中的代码：

@Api(value = "hvacJointApply", tags = "对接申请相关接口")
 @RestController
 @RequestMapping(value = "/api/joint/apply")
 public class HvacJointApplyController extends BaseController {
 
 @Resource
 private HvacJointApplyService hvacJointApplyService;
 
 @ApiOperation(value = "导出对接申请")
 @GetMapping("/exportExcel")
 public RestResponse<Nullable> exportWorkOrder(QueryApplyDTO queryApplyDTO, HttpServletResponse response) {
 
 CurrUserDetails user = getUser();
 RestResponse flag = hvacJointApplyService.exportExcel(user, queryApplyDTO, response);
 
 return flag;
 }
 }

**Service**层中的代码：

public interface HvacJointApplyService {
 
 /**
 * 分页查询对接申请列表，用于分页查询对接申请列表
 *
 * @param user 当前登录用户
 * @param queryApplyDTO 查询参数
 * @return 对接申请列表
 */
 Page<HvacJointApplyDTO> pageList(CurrUserDetails user, QueryApplyDTO queryApplyDTO);
 
 /**
 * 导出对接申请列表
 *
 * @param user 当前登录用户
 * @param queryApplyDTO 查询参数
 * @param response 响应
 * @return 是否导出成功
 */
 RestResponse exportExcel(CurrUserDetails user, QueryApplyDTO queryApplyDTO, HttpServletResponse response);
 }

ServiceImpl实现类中的代码：

@Slf4j
 @Service
 public class HvacJointApplyServiceImpl implements HvacJointApplyService {
 
 @Resource
 private HvacJointApplyMapper hvacJointApplyMapper;
 
 @Override
 public Page<HvacJointApplyDTO> pageList(CurrUserDetails user, QueryApplyDTO queryApplyDTO) {
 
 log.info("E|HvacJointApplyServiceImpl|pageList()|分页对接申请列表，当前登录人【userDetails = {}】", user);
 int pageNum = queryApplyDTO.getPageNum();
 int pageSize = queryApplyDTO.getPageSize();
 
 PageRequestParams pageRequest = PageRequestParams.of(pageNum, pageSize);
 
 log.info("E|HvacJointApplyServiceImpl|pageList()|分页对接申请列表，【查询参数 queryApplyDTO = {}】", queryApplyDTO);
 List<HvacJointApply> hvacJointApplyList = hvacJointApplyMapper.pageList(queryApplyDTO, pageRequest);
 Long pageListCount = hvacJointApplyMapper.pageListCount(queryApplyDTO);
 
 List<HvacJointApplyDTO> jointApplyDTOList = HvacJointApplyDTO.convertToDtoList(hvacJointApplyList);
 
 Pageable pageable = PageRequest.of(pageNum - 1, pageSize);
 Page<HvacJointApplyDTO> dtoPage = new PageImpl<>(jointApplyDTOList, pageable, pageListCount);
 
 log.info("E|HvacJointApplyServiceImpl|pageList()|分页查询品牌列表，【查询结果 dtoPage = {}】", dtoPage);
 
 return dtoPage;
 }
 
 @Override
 public RestResponse exportExcel(CurrUserDetails user, QueryApplyDTO queryApplyDTO, HttpServletResponse response) {
 
 log.info("E|HvacJointApplyServiceImpl|exportExcel()|导出对接申请列表，当前登录人【userDetails = {}】", user);
 
 Page<HvacJointApplyDTO> applyDtoPage = this.pageList(user, queryApplyDTO);
 List<HvacJointApplyDTO> pageContentList = applyDtoPage.getContent();
 // 将pageContentList（Collection类型）的类型转为ArrayList类型
 List<HvacJointApplyDTO> list = new ArrayList<>(pageContentList);
 
 try {
 EasyPoiUtils.exportExcel(list, "对接申请列表", "对接申请列表",
 HvacJointApplyDTO.class, "对接申请" + ".xls", response);
 return RestResponse.success(0, "导出对接申请列表成功！", pageContentList);
 } catch (Exception e) {
 log.error("E|HvacJointApplyServiceImpl|exportExcel()|导出对接申请列表失败！【原因 = {}】", e.getMessage());
 }
 
 return RestResponse.success(-1, "导出对接申请列表失败！", pageContentList);
 }
 }

拓展资料：

**EasyPoiUtils.java 工具类**

package com.iot.daily.common.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.entity.enmus.ExcelType;
 import lombok.extern.slf4j.Slf4j;
 import org.apache.commons.lang3.StringUtils;
 import org.apache.poi.ss.usermodel.Workbook;
 import org.springframework.web.multipart.MultipartFile;
 
 import javax.servlet.http.HttpServletResponse;
 import java.io.File;
 import java.io.IOException;
 import java.io.InputStream;
 import java.io.OutputStream;
 import java.util.LinkedHashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.NoSuchElementException;
 
 /**
 * EasyPoiUtils.java此类用于Excel导入导出
 * @author:lzy
 * @date:2019年1月29日
 * @remark:
 */
 @Slf4j
 public class EasyPoiUtils {
 
 /**
 * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头
 *
 * @param list 导出的实体类
 * @param title 表头名称
 * @param sheetName sheet表名
 * @param pojoClass 映射的实体类
 * @param isCreateHeader 是否创建表头
 * @param fileName
 * @param response
 * @return
 */
 public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
 String fileName, boolean isCreateHeader, HttpServletResponse response) {
 ExportParams exportParams = new ExportParams(title, sheetName);
 exportParams.setCreateHeadRows(isCreateHeader);
 defaultExport(list, pojoClass, fileName, response, exportParams);
 }
 
 
 /**
 * 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头
 *
 * @param list 导出的实体类
 * @param title 表头名称
 * @param sheetName sheet表名
 * @param pojoClass 映射的实体类
 * @param fileName
 * @param response
 * @return
 */
 public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
 String fileName, HttpServletResponse response) {
 defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
 }
 
 /**
 * 功能描述：Map 集合导出 多Sheet页 导出
 *
 * @param list 实体集合
 * @param fileName 导出的文件名称
 * @param response
 * @return
 */
 public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
 defaultExport(list, fileName, response);
 }
 
 /**
 * 功能描述：默认导出方法
 *
 * @param list 导出的实体集合
 * @param fileName 导出的文件名
 * @param pojoClass pojo实体
 * @param exportParams ExportParams封装实体
 * @param response
 * @return
 */
 private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,
 HttpServletResponse response, ExportParams exportParams) {
 Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
 if (workbook != null) {
 downLoadExcel(fileName, response, workbook);
 }
 }
 
 /**
 * 功能描述：Excel导出
 *
 * @param fileName 文件名称
 * @param response
 * @param workbook Excel对象
 * @return
 */
 private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
 OutputStream os = null;
 try {
 
 // 解决文件名中文乱码
 String exportName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
 // 输出Excel文件
 os = response.getOutputStream();
 response.reset();
 response.setContentType("application/vnd.ms-excel");
 response.setHeader("Content-disposition", "attachment; filename=" + exportName);
 response.setHeader("Pragma", "No-cache");
 workbook.write(os);
 
 os.close();
 } catch (IOException e) {
 log.error("Excel导出", e);
 throw new RuntimeException(e);
 } finally {
 if (os != null) {
 try {
 os.close();
 } catch (IOException e) {
 e.printStackTrace();
 log.error("IO异常：", e);
 }
 }
 }
 }
 
 /**
 * 功能描述：默认导出方法
 *
 * @param list 导出的实体集合
 * @param fileName 导出的文件名
 * @param response
 * @return
 */
 private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
 Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
 if (workbook != null) {
 downLoadExcel(fileName, response, workbook);
 }
 }
 
 
 /**
 * 功能描述：根据文件路径来导入Excel
 *
 * @param filePath 文件路径
 * @param titleRows 表标题的行数
 * @param headerRows 表头行数
 * @param pojoClass Excel实体类
 * @return
 */
 public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
 //判断文件是否存在
 if (StringUtils.isBlank(filePath)) {
 return null;
 }
 ImportParams params = new ImportParams();
 params.setTitleRows(titleRows);
 params.setHeadRows(headerRows);
 List<T> list = null;
 try {
 list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
 } catch (NoSuchElementException e) {
 e.printStackTrace();
 log.error("导入模板不能为空", e);
 throw new RuntimeException("模板不能为空");
 } catch (Exception e) {
 e.printStackTrace();
 log.error("根据文件路径来导入Excel", e);
 }
 return list;
 }
 
 /**
 * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
 *
 * @param file 上传的文件
 * @param titleRows 表标题的行数
 * @param headerRows 表头行数
 * @param pojoClass Excel实体类
 * @return
 */
 public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
 if (file == null) {
 return null;
 }
 ImportParams params = new ImportParams();
 params.setTitleRows(titleRows);
 params.setHeadRows(headerRows);
 List<T> list = null;
 try {
 list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
 } catch (NoSuchElementException e) {
 log.error("根据接收的Excel文件来导入Excel:NoSuchElementException,并封装成实体类", e);
 throw new RuntimeException("excel文件不能为空");
 } catch (Exception e) {
 log.error("根据接收的Excel文件来导入Excel,并封装成实体类", e);
 throw new RuntimeException(e.getMessage());
 }
 return list;
 }
 
 /**
 * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
 *
 * @param is 上传的文件
 * @param titleRows 表标题的行数
 * @param headerRows 表头行数
 * @param pojoClass Excel实体类
 * @param verfiy 是否开启验证
 * @return
 */
 public static <T> List<T> importExcel(InputStream is, Integer titleRows, Integer headerRows, Class<T> pojoClass, boolean verfiy) {
 if (is == null) {
 return null;
 }
 ImportParams params = new ImportParams();
 params.setTitleRows(titleRows);
 params.setHeadRows(headerRows);
 params.setNeedVerfiy(verfiy);
 List<T> list = null;
 try {
 list = ExcelImportUtil.importExcel(is, pojoClass, params);
 } catch (NoSuchElementException e) {
 log.error("根据接收的Excel文件来导入Excel:NoSuchElementException,并封装成实体类", e);
 throw new RuntimeException("excel文件不能为空");
 } catch (ClassNotFoundException e) {
 log.error("根据接收的Excel文件来导入Excel,并封装成实体类ClassNotFoundException", e);
 throw new RuntimeException(e.getMessage());
 } catch (Exception e) {
 log.error("根据接收的Excel文件来导入Excel,并封装成实体类", e);
 throw new RuntimeException(e.getMessage());
 }
 return list;
 }
 
 
 /**
 * 多sheet封装数据
 *
 * @param data
 * @param entity
 * @param sheetName
 * @return
 */
 public static Map<String, Object> getTestMap(List<?> data, Class<?> entity, String sheetName) {
 Map<String, Object> map = new LinkedHashMap<>();
 map.put("entity", entity);
 ExportParams ep = new ExportParams();
 ep.setSheetName(sheetName);
 map.put("title", ep);
 map.put("data", data);
 return map;
 }
 }

完结！

[Java_poi_Java_Excel]: https://blog.csdn.net/weixin_44299027/article/details/116307517