1.前言

本文章采用抛出需求,之后跟上解决实例的方式

2.实例一

2.1需求

一个对象直接导出成一个Excel表格
①导出的对象

  1. @Data
  2. public class PersonExportVo implements Serializable {
  3. private Integer id;
  4. private String name;
  5. private Date birth;
  6. }

②想导出的表格
image.png

2.2实现

①导出对象加入注解

  1. @Data
  2. public class PersonExportVo implements Serializable {
  3. @Excel(name = "编号")
  4. private Integer id;
  5. @Excel(name = "姓名")
  6. private String name;
  7. @Excel(name = "出生日期")
  8. private Date birth;
  9. }

②工具类(网上搜的,下面的都使用这个工具类)

  1. package com.gao.booteasypoi.utils;
  2. import cn.afterturn.easypoi.excel.ExcelExportUtil;
  3. import cn.afterturn.easypoi.excel.ExcelImportUtil;
  4. import cn.afterturn.easypoi.excel.entity.ExportParams;
  5. import cn.afterturn.easypoi.excel.entity.ImportParams;
  6. import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
  7. import org.apache.commons.lang3.StringUtils;
  8. import org.apache.poi.ss.usermodel.Workbook;
  9. import org.springframework.web.multipart.MultipartFile;
  10. import javax.servlet.http.HttpServletResponse;
  11. import java.io.File;
  12. import java.io.IOException;
  13. import java.io.InputStream;
  14. import java.net.URLEncoder;
  15. import java.util.List;
  16. import java.util.Map;
  17. import java.util.NoSuchElementException;
  18. public class ExcelUtils {
  19. /**
  20. * excel 导出
  21. *
  22. * @param list 数据
  23. * @param title 标题
  24. * @param sheetName sheet名称
  25. * @param pojoClass pojo类型
  26. * @param fileName 文件名称
  27. * @param isCreateHeader 是否创建表头
  28. * @param response
  29. */
  30. public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
  31. ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
  32. exportParams.setCreateHeadRows(isCreateHeader);
  33. defaultExport(list, pojoClass, fileName, response, exportParams);
  34. }
  35. /**
  36. * excel 导出
  37. *
  38. * @param list 数据
  39. * @param title 标题
  40. * @param sheetName sheet名称
  41. * @param pojoClass pojo类型
  42. * @param fileName 文件名称
  43. * @param response
  44. */
  45. public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
  46. defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
  47. }
  48. /**
  49. * excel 导出
  50. *
  51. * @param list 数据
  52. * @param pojoClass pojo类型
  53. * @param fileName 文件名称
  54. * @param response
  55. * @param exportParams 导出参数
  56. */
  57. public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
  58. defaultExport(list, pojoClass, fileName, response, exportParams);
  59. }
  60. /**
  61. * excel 导出
  62. *
  63. * @param list 数据
  64. * @param fileName 文件名称
  65. * @param response
  66. */
  67. public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
  68. defaultExport(list, fileName, response);
  69. }
  70. /**
  71. * 默认的 excel 导出
  72. *
  73. * @param list 数据
  74. * @param pojoClass pojo类型
  75. * @param fileName 文件名称
  76. * @param response
  77. * @param exportParams 导出参数
  78. */
  79. private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
  80. Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
  81. downLoadExcel(fileName, response, workbook);
  82. }
  83. /**
  84. * 默认的 excel 导出
  85. *
  86. * @param list 数据
  87. * @param fileName 文件名称
  88. * @param response
  89. */
  90. private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
  91. Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
  92. downLoadExcel(fileName, response, workbook);
  93. }
  94. /**
  95. * 下载
  96. *
  97. * @param fileName 文件名称
  98. * @param response
  99. * @param workbook excel数据
  100. */
  101. private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
  102. try {
  103. response.setCharacterEncoding("UTF-8");
  104. response.setHeader("content-Type", "application/vnd.ms-excel");
  105. response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
  106. workbook.write(response.getOutputStream());
  107. } catch (Exception e) {
  108. throw new IOException(e.getMessage());
  109. }
  110. }
  111. /**
  112. * excel 导入
  113. *
  114. * @param filePath excel文件路径
  115. * @param titleRows 标题行
  116. * @param headerRows 表头行
  117. * @param pojoClass pojo类型
  118. * @param <T>
  119. * @return
  120. */
  121. public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
  122. if (StringUtils.isBlank(filePath)) {
  123. return null;
  124. }
  125. ImportParams params = new ImportParams();
  126. params.setTitleRows(titleRows);
  127. params.setHeadRows(headerRows);
  128. params.setNeedSave(true);
  129. params.setSaveUrl("/excel/");
  130. try {
  131. return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
  132. } catch (NoSuchElementException e) {
  133. throw new IOException("模板不能为空");
  134. } catch (Exception e) {
  135. throw new IOException(e.getMessage());
  136. }
  137. }
  138. /**
  139. * excel 导入
  140. *
  141. * @param file excel文件
  142. * @param pojoClass pojo类型
  143. * @param <T>
  144. * @return
  145. */
  146. public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
  147. return importExcel(file, 1, 1, pojoClass);
  148. }
  149. /**
  150. * excel 导入
  151. *
  152. * @param file excel文件
  153. * @param titleRows 标题行
  154. * @param headerRows 表头行
  155. * @param pojoClass pojo类型
  156. * @param <T>
  157. * @return
  158. */
  159. public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
  160. return importExcel(file, titleRows, headerRows, false, pojoClass);
  161. }
  162. /**
  163. * excel 导入
  164. *
  165. * @param file 上传的文件
  166. * @param titleRows 标题行
  167. * @param headerRows 表头行
  168. * @param needVerfiy 是否检验excel内容
  169. * @param pojoClass pojo类型
  170. * @param <T>
  171. * @return
  172. */
  173. public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
  174. if (file == null) {
  175. return null;
  176. }
  177. try {
  178. return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
  179. } catch (Exception e) {
  180. throw new IOException(e.getMessage());
  181. }
  182. }
  183. /**
  184. * excel 导入
  185. *
  186. * @param inputStream 文件输入流
  187. * @param titleRows 标题行
  188. * @param headerRows 表头行
  189. * @param needVerfiy 是否检验excel内容
  190. * @param pojoClass pojo类型
  191. * @param <T>
  192. * @return
  193. */
  194. public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
  195. if (inputStream == null) {
  196. return null;
  197. }
  198. ImportParams params = new ImportParams();
  199. params.setTitleRows(titleRows);
  200. params.setHeadRows(headerRows);
  201. params.setSaveUrl("/excel/");
  202. params.setNeedSave(true);
  203. params.setNeedVerfiy(needVerfiy);
  204. try {
  205. return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
  206. } catch (NoSuchElementException e) {
  207. throw new IOException("excel文件不能为空");
  208. } catch (Exception e) {
  209. throw new IOException(e.getMessage());
  210. }
  211. }
  212. /**
  213. * Excel 类型枚举
  214. */
  215. enum ExcelTypeEnum {
  216. XLS("xls"), XLSX("xlsx");
  217. private String value;
  218. ExcelTypeEnum(String value) {
  219. this.value = value;
  220. }
  221. public String getValue() {
  222. return value;
  223. }
  224. public void setValue(String value) {
  225. this.value = value;
  226. }
  227. }
  228. }

③Controller层代码

  1. @GetMapping(value = "/export")
  2. public void exportExcel(HttpServletResponse response) {
  3. //初始化数据
  4. List<PersonExportVo> personList = new ArrayList<>();
  5. for (int i = 0; i < 5; i++) {
  6. PersonExportVo personVo = new PersonExportVo();
  7. personVo.setName("张三" + i);
  8. personVo.setId(i);
  9. personVo.setBirth(new Date());
  10. personList.add(personVo);
  11. }
  12. try {
  13. ExcelUtils.exportExcel(personList, "员工信息表", "员工信息", PersonExportVo.class, "员工信息", response);
  14. } catch (IOException e) {
  15. e.printStackTrace();
  16. }
  17. }

3.实例二(日期格式化)

3.1需求

针对实例一中的日期时间需要格式化。
image.png ===>image.png

3.2代码

  1. @Data
  2. public class PersonExportVo implements Serializable {
  3. @Excel(name = "编号")
  4. private Integer id;
  5. @Excel(name = "姓名")
  6. private String name;
  7. @Excel(name = "出生日期",format = "yyyy-MM-dd HH:mm:ss")
  8. private Date birth;
  9. }

3.3扩展

①数字的格式化使用 numformat
②每一列的单元格宽度使用 width设置

4.实例三

4.1需求

①将对象种某个字段不导出
②列顺序排列
③对于一个状态信息,后端只保存1,2,3这样的标识,所以我需要导出对应的 具体状态描述