一个通用的EXCEL生成下载方法

Excel是一个Java开发中必须会用到的东西，之前博主也发过一篇关于使用Excel的文章，但是最近工作中，发现了一个更好的使用方法，所以，就对之前的博客进行总结，然后就有了这篇新的，万能通用的方法说明书了
好了，闲话不多说，直接上干货了
控制器代码如下

@Operation(summary = "Excel导入", description = "Excel导入")@GetMapping("/commPointManagement/importExcel")public ResultBody importExcel(@RequestParam MultipartFile excel,@RequestParam(value = "creatorId") Long creatorId,HttpServletResponse response){try {return commPointManagementService.importExcel(excel,creatorId,response);} catch (Exception e) {return ResultBody.failed("导入失败"+e.getMessage());}}/*** excel导出* @param dto*/@Operation(summary = "Excel导出", description = "Excel导出")@PostMapping("/commPointManagement/downloadInfoExcel")public void downloadInfoExcel(@RequestBody CommPointManagementDTO dto, HttpServletResponse response){commPointManagementService.downloadInfoExcel(dto,response);}/*** 下载模板*/@GetMapping("/commPointManagement/downloadExcelTemplate")@Operation(summary = "下载模板excel模板", description = "下载模板excel模板")public void exportExcelFormat(HttpServletResponse response) {commPointManagementService.exportExcelFormat(response);}

上面总结了三个方法，都是不使用模板的，代码也有注释，大家可根据注释获取自己需要的方法

service接口就不粘贴了，直接上实现类了

public void exportExcelFormat(HttpServletResponse response) {List<List<String>> customTitle = new ArrayList<>();List<String> titles = Lists.newArrayList();titles.add("字段1");titles.add("字段2");titles.add("字段3");customTitle.add(titles);try {EasyPoiUtil.exportExcel("模板名称.xlsx", null, null, Lists.newArrayList(), CommPointManagementVO.class, customTitle, Lists.newArrayList(), response);} catch (Exception e) {throw new RuntimeException(e);}}@Overridepublic ResultBody importExcel(MultipartFile excel,Long creatorId, HttpServletResponse response) throws Exception {//获取Excel中的数据集合ExcelImportResult<CommPointManagementBean> importExcel = EasyPoiUtil.importExcel(excel, 1, 0, 0, false, CommPointManagementBean.class);List<CommPointManagementBean> importList = importExcel.getList();int failCount = 0;int insertCount = 0;if (CollUtil.isEmpty(importList)) {return ResultBody.ok().msg("导入excel成功");}Timestamp time = new Timestamp(new Date().getTime());for (int i = 0; i < importList.size(); i++) {CommPointManagementBean date = importList.get(i);}return ResultBody.ok().data().msg("导入excel成功");}@Overridepublic void downloadInfoExcel(CommPointManagementDTO dto, HttpServletResponse response) {//获取数据库数据IPage<CommPointManagementVO> page = commPointManagementMapper.findPage(new PageParams(dto.getPage(), dto.getLimit()), dto);List<CommPointManagementVO> records = page.getRecords();List<List<String>> customTitle = new ArrayList<>();List<String> titleList = new ArrayList<>();titleList.add("字段1");titleList.add("字段2");titleList.add("字段3");customTitle.add(titleList);try {EasyPoiUtil.exportExcel("表名.xlsx", "表头", "Excel中的页名", records,CommPointManagementVO.class, customTitle, new ArrayList<>(), response);} catch (Exception e) {throw new RuntimeException(e);}}

上面方法中的CommPointManagementVO.class是接口返回的实体类，虽然没有注解，但是细心一点看还是能看出来的，里面还有一个工具类EasyPoiUtil，这个就是咱们今天的主角了，主角介绍如下：

package com.secondcloud.system.server.util;import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** easyPoi excel通用导入导出*/
public class EasyPoiUtil {private static final String SUFFIX = ".xlsx";/*** exportExcel 通用导出方法(bean加注解)** @param fileName    文件名* @param title       标题* @param sheetName   表单名* @param list        数据* @param pojoClass   数据类* @param customTitle 自定义表头* @param mergeList   自定义合并* @param response    响应* @throws Exception*/public static void exportExcel(String fileName, String title, String sheetName, List<?> list, Class<?> pojoClass,List<List<String>> customTitle, List<CellRangeAddress> mergeList, HttpServletResponse response)throws Exception {defaultExport(list, pojoClass, fileName, response, simpleParams(title, sheetName, fileName), customTitle,mergeList, null);}/*** exportExcelEntity 通用导出方法(自定义ExcelExportEntity)** @param fileName    文件名* @param title       标题* @param sheetName   表单名* @param list        数据* @param entityList  数据配置* @param customTitle 自定义表头* @param mergeList   自定义合并* @param response    响应* @throws Exception*/public static void exportExcelEntity(String fileName, String title, String sheetName, List<?> list, List<ExcelExportEntity> entityList,List<List<String>> customTitle, List<CellRangeAddress> mergeList, HttpServletResponse response)throws Exception {defaultExport(list, null, fileName, response, simpleParams(title, sheetName, fileName), customTitle,mergeList, entityList);}/*** exportExcel 多表单导出方法(bean加注解)** @param fileName   文件名* @param configList title:标题 data:数据 entity:数据类* @param response   响应* @throws Exception*/public static void exportExcelMultiSheet(String fileName, List<Map<String, Object>> configList,HttpServletResponse response) throws Exception {// 根据fileName后缀确定excel文件是xls还是xlsxExcelType type = ExcelType.HSSF;if (StrUtil.endWith(fileName, SUFFIX)) {type = ExcelType.XSSF;}Workbook workbook = ExcelExportUtil.exportExcel(configList, type);if (workbook != null) {try {if (CollectionUtil.isNotEmpty(configList)) {int sheetIndex = 0;for (Map<String, Object> map : configList) {List<List<String>> customTitle = (List<List<String>>) map.get("customTitle");List<CellRangeAddress> mergeList = (List<CellRangeAddress>) map.get("mergeList");String sheetTitle = Convert.toStr(map.get("sheetTitle"));Sheet sheet = workbook.getSheetAt(sheetIndex);if (sheet != null) {// 自定义合并boolean mergeFlag = CollectionUtil.isNotEmpty(mergeList);if (mergeFlag) {for (CellRangeAddress merge : mergeList) {sheet.addMergedRegion(merge);}}// 合并多列标题(暂时支持第一行向左合并 第二行向上合并)int begin = 0;int end = 0;String tmpTitle = null;// 已合并区域标识 i&jList<String> mergedList = new ArrayList<>();int i = 0;if (sheetTitle != null) {i++;}int line = i;CellStyle cellStyle = null;for (List<String> strings : customTitle) {Row row = sheet.getRow(i);if (row == null) {row = sheet.createRow(i);}int j = 0;for (String s : strings) {Cell cell = row.getCell(j);if (cell == null) {cell = row.createCell(j);cell.setCellStyle(cellStyle);} else if (cellStyle == null) {cellStyle = cell.getCellStyle();}cell.setCellValue(s);if (!mergeFlag) {// 第一行合并规则if (i == line) {if (StrUtil.isEmpty(s)) {end = j;}if (tmpTitle != null && StrUtil.isNotEmpty(s) && !tmpTitle.equals(s)) {if (end > begin) {sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));for (int f = begin; f < end; f++) {mergedList.add(i + "&" + f);}}begin = j;tmpTitle = s;}if (tmpTitle == null) {begin = j;tmpTitle = s;}} else if (i == line + 1) {// 第二行向上合并if (StrUtil.isEmpty(s) && !mergedList.contains((i - 1) + "&" + j)) {sheet.addMergedRegion(new CellRangeAddress(i - 1, i, j, j));mergedList.add((i - 1) + "&" + j);mergedList.add(i + "&" + j);}}}j++;}if (!mergeFlag) {// 第一行合并规则(换行前)if (i == line) {if (strings.size() > 0 && end > begin) {sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));for (int f = begin; f < end; f++) {mergedList.add(i + "&" + f);}}}}i++;}}sheetIndex++;}}} catch (Exception e) {}downLoadExcel(fileName, response, workbook);}}/*** importExcel 通用导入方法(bean加注解)** @param file       导入文件* @param titleRows  标题行数* @param headerRows 头部行数* @param keyIndex   主键列 如果这个cell没有值,就跳过，默认0* @param needVerfiy 是否需要校验，默认false* @param pojoClass  数据类* @return* @throws Exception*/public static <T> ExcelImportResult<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,Integer keyIndex, Boolean needVerfiy, Class<T> pojoClass) throws Exception {if (file == null) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);params.setKeyIndex(keyIndex);params.setNeedVerify(needVerfiy);ExcelImportResult<T> rst = null;try {rst = ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);} catch (Exception e) {e.printStackTrace();throw e;}return rst;}/*** importExcel 指定表单导入方法(bean加注解)** @param file            导入文件* @param titleRows       标题行数* @param headerRows      头部行数* @param startSheetIndex 表单序号* @param sheetNum        表单数量* @param keyIndex        主键列 如果这个cell没有值,就跳过\* @param needVerfiy      是否需要校验，默认false* @param pojoClass       数据类* @return* @throws Exception*/public static <T> ExcelImportResult<T> importExcelCommon(MultipartFile file, Integer titleRows, Integer headerRows,Integer startSheetIndex, Integer sheetNum, Integer keyIndex, Boolean needVerfiy, Class<T> pojoClass)throws Exception {if (file == null) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);params.setStartSheetIndex(startSheetIndex);params.setSheetNum(sheetNum);params.setKeyIndex(keyIndex);params.setNeedVerify(needVerfiy);ExcelImportResult<T> rst = null;try {rst = ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);} catch (Exception e) {e.printStackTrace();throw e;}return rst;}/*** importExcel 根据文件路径导入excel(bean加注解)** @param filePath* @param titleRows* @param headerRows* @param pojoClass* @param <T>* @return* @throws Exception*/public static <T> ExcelImportResult<T> importExcel(String filePath, Integer titleRows, Integer headerRows,Class<T> pojoClass) throws Exception {if (StrUtil.isEmpty(filePath)) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);ExcelImportResult<T> rst = null;try {rst = ExcelImportUtil.importExcelMore(new File(filePath), pojoClass, params);} catch (Exception e) {e.printStackTrace();throw e;}return rst;}private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,ExportParams exportParams, List<List<String>> customTitle, List<CellRangeAddress> mergeList,List<ExcelExportEntity> entityList) throws Exception {exportParams.setFixedTitle(false);Workbook workbook;int titleLine = 0;if (CollectionUtil.isNotEmpty(customTitle)) {titleLine = customTitle.size();}// 自定义标题需要在标题下插入行List<Object> t = new ArrayList<>();while (titleLine > 1 && CollectionUtil.isNotEmpty(list)) {t.add(pojoClass.getDeclaredConstructor().newInstance());titleLine--;}t.addAll(list);if (pojoClass == null) {workbook = ExcelExportUtil.exportExcel(exportParams, entityList, t);} else {workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, t);}if (workbook != null) {// 自定义标题if (CollectionUtil.isNotEmpty(customTitle)) {Sheet sheet = workbook.getSheetAt(0);if (sheet != null) {// 自定义合并boolean mergeFlag = CollectionUtil.isNotEmpty(mergeList);if (mergeFlag) {for (CellRangeAddress merge : mergeList) {sheet.addMergedRegion(merge);}}// 合并多列标题(暂时支持第一行向左合并 第二行向上合并)int begin = 0;int end = 0;String tmpTitle = null;// 已合并区域标识 i&jList<String> mergedList = new ArrayList<>();int i = 0;if (exportParams.getTitle() != null) {i++;}if (exportParams.getSecondTitle() != null) {i++;}int line = i;CellStyle cellStyle = null;for (List<String> strings : customTitle) {Row row = sheet.getRow(i);if (row == null) {row = sheet.createRow(i);}int j = 0;for (String s : strings) {Cell cell = row.getCell(j);if (cell == null) {cell = row.createCell(j);cell.setCellStyle(cellStyle);} else if (cellStyle == null) {cellStyle = cell.getCellStyle();}cell.setCellValue(s);if (!mergeFlag) {// 第一行合并规则if (i == line) {if (StrUtil.isEmpty(s)) {end = j;}if (tmpTitle != null && StrUtil.isNotEmpty(s) && !tmpTitle.equals(s)) {if (end > begin) {sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));for (int f = begin; f < end; f++) {mergedList.add(i + "&" + f);}}begin = j;tmpTitle = s;}if (tmpTitle == null) {begin = j;tmpTitle = s;}} else if (i == line + 1) {// 第二行向上合并if (StrUtil.isEmpty(s) && !mergedList.contains((i - 1) + "&" + j)) {sheet.addMergedRegion(new CellRangeAddress(i - 1, i, j, j));mergedList.add((i - 1) + "&" + j);mergedList.add(i + "&" + j);}}}j++;}if (!mergeFlag) {// 第一行合并规则(换行前)if (i == line) {if (strings.size() > 0 && end > begin) {sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));for (int f = begin; f < end; f++) {mergedList.add(i + "&" + f);}}}}i++;}}}downLoadExcel(fileName, response, workbook);}}private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)throws Exception {OutputStream ouputStream = null;try {response.setCharacterEncoding("UTF-8");response.setHeader("content-Type", "application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));response.setHeader("Pragma", "No-cache");ouputStream = response.getOutputStream();workbook.write(ouputStream);} catch (Exception e) {e.printStackTrace();throw e;} finally {if (ouputStream != null) {try {ouputStream.flush();ouputStream.close();} catch (Exception e) {}}}}/*** 获取表单配置项** @param title* @param sheetName* @param fileName* @return*/public static ExportParams simpleParams(String title, String sheetName, String fileName) {// 根据fileName后缀确定excel文件是xls还是xlsxExcelType type = ExcelType.HSSF;if (StrUtil.endWith(fileName, SUFFIX)) {type = ExcelType.XSSF;}ExportParams exportParams = new ExportParams(title, sheetName, type);exportParams.setFixedTitle(false);return exportParams;}/*** 获取表单配置对象** @param title* @param sheetName* @param fileName* @param pojoClass* @param list* @param customTitle 自定义表头* @param mergeList   自定义合并* @return*/public static Map<String, Object> simpleParamsMap(String title, String sheetName, String fileName, List<?> list,Class<?> pojoClass, List<List<String>> customTitle, List<CellRangeAddress> mergeList) throws Exception {Map<String, Object> map = new HashMap<String, Object>();map.put("title", simpleParams(title, sheetName, fileName));map.put("entity", pojoClass);List<Object> array = new ArrayList<>();if (CollectionUtil.isNotEmpty(customTitle)) {int size = customTitle.size();while (size > 1) {array.add(pojoClass.getDeclaredConstructor().newInstance());size--;}}if (list != null) {array.addAll(list);}map.put("data", array);map.put("customTitle", customTitle);map.put("mergeList", mergeList);map.put("sheetTitle", title);return map;}/*** 生成ExcelExportEntity** @param name  名称* @param value 属性* @param merge 是否合并列* @param list  下级entity* @return*/public static ExcelExportEntity getExportEntity(String name, String value, Boolean merge, Double width, Integer orderNum,String format, List<ExcelExportEntity> list) {ExcelExportEntity entity = new ExcelExportEntity(name, value);if (merge != null) {entity.setNeedMerge(merge);}if (width != null) {entity.setWidth(width);}if (orderNum != null) {entity.setOrderNum(orderNum);}if (format != null) {entity.setFormat(format);}if (CollectionUtil.isNotEmpty(list)) {entity.setList(list);}return entity;}public static List<ExcelExportEntity> classToEntityList(Class<?> pojoClass) {List<ExcelExportEntity> rst = new ArrayList<>();try {Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);ExcelTarget etarget = (ExcelTarget) pojoClass.getAnnotation(ExcelTarget.class);String targetId = etarget == null ? null : etarget.value();new ExcelExportService().getAllExcelField(null, targetId, fileds, rst, pojoClass, (List) null, (ExcelEntity) null);} catch (Exception e) {}return rst;}
}

这个工具类一般的格式都能使用的，具体复杂的Excel表格样式，还是使用Excel表格模板去做会更方便点，工具类的方发也有说明，大家自行查看哦。
本篇分享就到这里了，如果有使用问题，可以评论留言，有不对的地方，请批评指正，谢谢！