pom.xml
<!-- easypoi excel --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.4.0</version></dependency>
一、多级表头和多个sheet
🧐 效果
因为这里是用注解实现的，只有到三级表头，如果表头超过了三级，比如有四级、五级的话，就不建议用注解，还是用模板比较方便。
🍔 代码
多级表头一个是可以用注解实现：@ExcelCollection 和 @Excel 组合使用；还有就是可以用模板实现；还有一种就是用原生poi写，这个就更麻烦了。模板实现就更灵活，很复杂的表格都可以实现，不过就是填充数据比较麻烦。这里先写用注解实现的方式。
1、实体bean
import cn.afterturn.easypoi.excel.annotation.Excel;import cn.afterturn.easypoi.excel.annotation.ExcelCollection;import lombok.Data;import java.util.List;@Datapublic class ExcelExportVo {// 这两个就是一级表头，最后一级表头对应的是具体的某个属性，它们都是被包裹在一级表头下的@ExcelCollection(name = "用户信息")private List<UserInfo> userInfoList;@ExcelCollection(name = "用户角色和权限")private List<RoleInfo> roleInfoList;/*** 用户信息*/@Datapublic static class UserInfo{// 二级表头可以用 groupName 实现@Excel(name = "用户账号",width = 20,groupName = "基本信息")private String userName;@Excel(name = "用户姓名",width = 20,groupName = "基本信息")private String realName;@Excel(name = "手机号码",width = 20,groupName = "基本信息")private String phone;@Excel(name = "所在公司",width = 20,groupName = "单位部门")private String com;@Excel(name = "所在部门",width = 20,groupName = "单位部门")private String dept;public UserInfo(String userName, String realName, String phone, String com, String dept) {this.userName = userName;this.realName = realName;this.phone = phone; = com;this.dept = dept;}}/*** 用户角色权限*/@Datapublic static class RoleInfo{@Excel(name = "所属角色名称",width = 20,groupName = "角色")private String roleName;@Excel(name = "所属角色代码",width = 20,groupName = "角色")private String roleCode;@Excel(name = "菜单权限",width = 40,groupName = "权限")private String menu;@Excel(name = "数据权限",width = 40,groupName = "权限")private String data;public RoleInfo(String roleName, String roleCode, String menu, String data) {this.roleName = roleName;this.roleCode = roleCode;this.menu = menu;this.data = data;}}}
2、ExcelUtil、ExcelStyleUtil
import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.ExcelImportUtil;import cn.afterturn.easypoi.excel.annotation.Excel;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.imports.ExcelImportService;import mons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.multipart.MultipartFile;import sun.misc.BASE64Decoder;import javax.imageio.ImageIO;import javax.servlet.http.HttpServletResponse;import java.awt.image.BufferedImage;import java.io.*;import java.lang.reflect.Field;import .URLEncoder;import java.util.*;/*** excel操作工具类*/public class ExcelUtil {/*** 偶数行设置背景色*/private static void setRowBackground(Workbook workbook){Sheet sheet = workbook.getSheetAt(0);CellStyle styles = ExcelStyleUtil.getStyles(workbook,false,(short) 12);for(int i = 0; i <= sheet.getLastRowNum(); i ++) {Row row = sheet.getRow(i);if (i%2==0){for(int j = 0; j < row.getPhysicalNumberOfCells(); j ++) {Cell cell = row.getCell(j);cell.setCellStyle(styles);}}}}/*** 导出设置隔行背景色* @param params 导出参数* @param list 数据* @param pojoClass pojo类型* @param fileName 文件名称* @param isSetRowBackground 是否设置隔行背景色*/public static void exportExcel(ExportParams params, List<?> list, Class<?> pojoClass, String fileName,boolean isSetRowBackground, HttpServletResponse response){Workbook workbook = ExcelExportUtil.exportExcel(params,pojoClass,list);if (workbook != null);if (isSetRowBackground) setRowBackground(workbook);downLoadExcel(fileName, response, workbook);}/*** excel 导出* @param list 数据* @param title标题* @param sheetNamesheet名称* @param pojoClasspojo类型* @param fileName 文件名称*/public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response){ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);exportParams.setStyle(ExcelStyleUtil.class);defaultExport(list, pojoClass, fileName, response, exportParams);}/*** excel 导出* @param list 数据* @param pojoClasspojo类型* @param fileName 文件名称*/public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response){ExportParams exportParams = new ExportParams();exportParams.setStyle(ExcelStyleUtil.class);defaultExport(list, pojoClass, fileName, response, exportParams);}/*** list map 导出* @param list数据* @param fileName 文件名称*/public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){ExportParams exportParams = new ExportParams();exportParams.setStyle(ExcelStyleUtil.class);defaultExport(list, fileName, response);}/*** 默认的 excel 导出* @param list 数据* @param pojoClass pojo类型* @param fileName文件名称* @param exportParams 导出参数*/private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams){Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);downLoadExcel(fileName, response, workbook);}/*** 默认的 excel 导出* @param list数据* @param fileName 文件名称*/private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response){Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);downLoadExcel(fileName, response, workbook);}/*** 下载* @param fileName 文件名称* @param response* @param workbook excel数据*/public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook){try {response.setCharacterEncoding("UTF-8");response.setHeader("content-Type", "application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLS.getValue(), "UTF-8"));workbook.write(response.getOutputStream());} catch (IOException e) {e.printStackTrace();}}/*** excel 导入* @param filePath excel文件路径* @param titleRows 标题行* @param headerRows 表头行* @param pojoClass pojo类型*/public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {if (StringUtils.isBlank(filePath)) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);params.setNeedSave(true);params.setSaveUrl("/excel/");try {return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);} catch (NoSuchElementException e) {throw new IOException("模板不能为空");} catch (Exception e) {throw new IOException(e.getMessage());}}/*** excel 导入* @param file 上传的文件* @param titleRows 标题行* @param headerRows 表头行* @param needVerify 是否检验excel内容* @param pojoClass pojo类型*/public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass) throws IOException {if (file == null) {return null;}try {return importExcel(file.getInputStream(), titleRows, headerRows, needVerify, pojoClass);} catch (Exception e) {throw new IOException(e.getMessage());}}/*** excel 导入* @param inputStream 文件输入流* @param titleRows 标题行* @param headerRows 表头行* @param needVerify 是否检验excel内容* @param pojoClass pojo类型*/public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass) throws IOException {if (inputStream == null) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);params.setSaveUrl("upload/excel/");params.setNeedSave(true);params.setNeedVerify(needVerify);try {return ExcelImportUtil.importExcel(inputStream, pojoClass, params);} catch (NoSuchElementException e) {throw new IOException("excel文件不能为空");} catch (Exception e) {throw new IOException(e.getMessage());}}/*** Excel 类型枚举*/enum ExcelTypeEnum {XLS("xls"), XLSX("xlsx");private String value;ExcelTypeEnum(String value) {this.value = value;}public String getValue() {return value;}public void setValue(String value) {this.value = value;}}/*** 上传文件，返回一个workbook* @param file*/public static Workbook importExcel(MultipartFile file) throws IOException {File toFile = new File(file.getOriginalFilename());Workbook workbook = null;if(toFile.getPath().endsWith("xls")){workbook = new HSSFWorkbook(file.getInputStream());}else if(toFile.getPath().endsWith("xlsx")){workbook = new XSSFWorkbook(file.getInputStream());}else {throw new RuntimeException("请确认你上传的文件类型");}return workbook;}/*** 读取指定sheet的数据* @param file 上传的文件* @param sheetName 要读取的sheetName* @param titleRows 表头行数* @param headRows 标题行数* @param startRows 表头之前有多少行不要的数据，从1开始，忽略空行* @param readRows 要读取多少行数据，从0开始，比如读取十行，值就是9; 不指定时默认为0* @param pojoClass 实体*/public static <T> List<T> importExcel(MultipartFile file,String sheetName,Integer titleRows,Integer headRows, Integer startRows,Integer readRows,Class<T> pojoClass) throws Exception {Workbook workbook = importExcel(file);int numberOfSheets = workbook.getNumberOfSheets();List<T> list = null;for (int i = 0; i < numberOfSheets; i++) {String name = workbook.getSheetName(i).trim();if (name.equals(sheetName) || name.endsWith(sheetName)){ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headRows);params.setStartRows(startRows);params.setReadRows(readRows);//第几个sheet页params.setStartSheetIndex(i);final ExcelImportService excelImportService = new ExcelImportService();ExcelImportResult<T> result = excelImportService.importExcelByIs(file.getInputStream(), pojoClass, params, false);list = result.getList();break;}}return list;}/*** 以map的形式导出表格* @param list 数据*/public static <T> List<Map<String, Object>> objectToMap(List<T> list){List<Map<String, Object>> result = new ArrayList<>();Map<String, Object> map = null;try {for (T item : list) {map = new HashMap<>();Class<?> clazz = item.getClass();for (Field field : clazz.getDeclaredFields()) {field.setAccessible(true);String fieldName = field.getName();Object value = field.get(item);map.put(fieldName, value);}result.add(map);}return result;}catch (IllegalAccessException e){e.printStackTrace();}return null;}/*** 设置注解参数* @param annotation 注解* @param entity*/private static void annotationParams(Excel annotation,ExcelExportEntity entity){if (annotation.addressList()){entity.setAddressList(annotation.addressList());entity.setReplace(annotation.replace());}entity.setReplace(annotation.replace());entity.setOrderNum(Integer.parseInt(annotation.orderNum()));entity.setGroupName(annotation.groupName());entity.setNeedMerge(annotation.needMerge());entity.setMergeVertical(annotation.mergeVertical());}/*** 动态导出列，根据Excel注解获取列的字段注释（表头名）、宽度* @param clazz* @param fields 选择要导出的列* @param changeHead 要更改表头的列，格式是{"字段1":"更改的表头1","字段2":"更改的表头2"}*/public static List<ExcelExportEntity> dynamicExport(Class<?> clazz,String fields, JSONObject changeHead) {List<ExcelExportEntity> beanList = new ArrayList<>();String[] split = fields.split(",");int length = split.length;try {for (int i = 0; i < length; i++) {Field f = clazz.getDeclaredField(split[i]);Excel annotation = f.getAnnotation((Excel.class));String comment = annotation.name();if (changeHead != null && Objects.nonNull(changeHead.get(f.getName()))){comment = changeHead.get(f.getName()).toString();}Double width = annotation.width();ExcelExportEntity entity = new ExcelExportEntity(comment, f.getName(), width.intValue());annotationParams(annotation,entity);beanList.add(entity);}}catch (NoSuchFieldException e){e.printStackTrace();}return beanList;}/*** 动态导出列（选择要忽略的列），根据Excel注解获取列的字段注释（表头名）、宽度* @param clazz* @param fields 选择要忽略的列* @param changeHead 要更改表头的列，格式是{"字段名1":"更改的表头1","字段名2":"更改的表头2"}*/public static List<ExcelExportEntity> dynamicIgnoreExport(Class<?> clazz, String fields, JSONObject changeHead) {List<ExcelExportEntity> beanList = new ArrayList<>();String[] split = fields.split(",");int length = split.length;Field[] declaredFields = clazz.getDeclaredFields();for (Field f : declaredFields) {Excel annotation = f.getAnnotation((Excel.class));if (annotation != null){boolean flag = false;for (int i = 0; i < length; i++) {if (f.getName().equals(split[i])){flag = true;break;}}if (flag) continue;String comment = annotation.name();if (changeHead != null && Objects.nonNull(changeHead.get(f.getName()))){comment = changeHead.get(f.getName()).toString();}Double width = annotation.width();ExcelExportEntity entity = new ExcelExportEntity(comment, f.getName(), width.intValue());annotationParams(annotation,entity);beanList.add(entity);}}return beanList;}/*** 导出Excel，并在最后追加图片* @param sheetName sheet名称* @param wb HSSFWorkbook对象*/public static Workbook getWorkbook(String sheetName,Workbook wb, String imgUrl) throws IOException {// 第一步，创建一个HSSFWorkbook，对应一个Excel文件if (wb == null) {wb = new HSSFWorkbook();}// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheetSheet sheet = wb.getSheet(sheetName);/*生成图表*/if(!StringUtils.isEmpty(imgUrl)) {String[] imgUrlArr = imgUrl.split("base64,");//拆分base64编码后部分byte[] buffer = new BASE64Decoder().decodeBuffer(imgUrlArr[1]);String picPath = System.getProperty("user.dir")+"\\upload\\image\\pic.png";File file = new File(picPath);//图片文件try {//生成图片OutputStream out = new FileOutputStream(file);//图片输出流out.write(buffer);out.flush();//清空流out.close();//关闭流ByteArrayOutputStream outStream = new ByteArrayOutputStream(); // 将图片写入流中BufferedImage bufferImg = ImageIO.read(new File(picPath));ImageIO.write(bufferImg, "PNG", outStream);// 利用HSSFPatriarch将图片写入EXCELDrawing<?> drawing = sheet.createDrawingPatriarch();//位置：第1个单元格中x轴的偏移量、第1个单元格中y轴的偏移量、 第2个单元格中x轴的偏移量、 第2个单元格中y轴的偏移量、第1个单元格的列号、第1个单元格的行号、 第2个单元格的列号、第2个单元格的行号//HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 8, (short) 10, 40);ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 6, 9, 40);drawing.createPicture(anchor, wb.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));} catch (Exception ex) {ex.printStackTrace();}if (file.exists()) {file.delete();//删除图片}}return wb;}}
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;import org.apache.poi.ss.usermodel.*;/*** excel导出样式设置工具类*/public class ExcelStyleUtil implements IExcelExportStyler {private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");private static final short FONT_SIZE_TEN = 12;private static final short FONT_SIZE_ELEVEN = 14;private static final short FONT_SIZE_TWELVE = 18;/*** 大标题样式*/private CellStyle headerStyle;/*** 每列标题样式*/private CellStyle titleStyle;/*** 数据行样式*/private CellStyle styles;public ExcelStyleUtil(Workbook workbook) {this.init(workbook);}/*** 初始化样式* @param workbook*/private void init(Workbook workbook) {this.headerStyle = initHeaderStyle(workbook);this.titleStyle = initTitleStyle(workbook,true,FONT_SIZE_ELEVEN);this.styles = initStyles(workbook);}@Overridepublic CellStyle getHeaderStyle(short i) {return headerStyle;}@Overridepublic CellStyle getTitleStyle(short i) {return titleStyle;}@Overridepublic CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) {return null;}@Overridepublic CellStyle getStyles(boolean b, ExcelExportEntity excelExportEntity) {return styles;}@Overridepublic CellStyle getStyles(Cell cell, int i, ExcelExportEntity entity, Object o, Object o1) {return getStyles(true, entity);}/*** 设置隔行背景色*/public static CellStyle getStyles(Workbook workbook,boolean isBold,short size) {CellStyle style = initTitleStyle(workbook,isBold,size);style.setDataFormat(STRING_FORMAT);return style;}/*** 初始化--大标题样式*/private static CellStyle initHeaderStyle(Workbook workbook) {CellStyle style = getBaseCellStyle(workbook);style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));return style;}/*** 初始化--每列标题样式*/private static CellStyle initTitleStyle(Workbook workbook,boolean isBold,short size) {CellStyle style = getBaseCellStyle(workbook);style.setFont(getFont(workbook, size, isBold));//背景色style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);return style;}/*** 初始化--数据行样式*/private static CellStyle initStyles(Workbook workbook) {CellStyle style = getBaseCellStyle(workbook);style.setFont(getFont(workbook, FONT_SIZE_TEN, false));style.setDataFormat(STRING_FORMAT);return style;}/*** 基础样式*/private static CellStyle getBaseCellStyle(Workbook workbook) {CellStyle style = workbook.createCellStyle();//下边框style.setBorderBottom(BorderStyle.THIN);//左边框style.setBorderLeft(BorderStyle.THIN);//上边框style.setBorderTop(BorderStyle.THIN);//右边框style.setBorderRight(BorderStyle.THIN);//水平居中style.setAlignment(HorizontalAlignment.CENTER);//上下居中style.setVerticalAlignment(VerticalAlignment.CENTER);//设置自动换行style.setWrapText(true);return style;}/*** 字体样式* @param size 字体大小* @param isBold 是否加粗*/private static Font getFont(Workbook workbook, short size, boolean isBold) {Font font = workbook.createFont();//字体样式font.setFontName("宋体");//是否加粗font.setBold(isBold);//字体大小font.setFontHeightInPoints(size);return font;}}
3、导出
import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.entity.ExportParams;import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;import mon.util.ExcelStyleUtil;import mon.util.ExcelUtil;import com.entity.sys.query.ExcelExportVo;import org.apache.poi.ss.usermodel.Workbook;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;@RestController@RequestMapping("/api/test")public class TestController {/*** 多级表头、多个sheet导出*/@RequestMapping("/export")public void export(HttpServletResponse response){// 模拟数据List<ExcelExportVo> dataList = new ArrayList<>();List<ExcelExportVo.UserInfo> userInfoList = new ArrayList<>();List<ExcelExportVo.RoleInfo> roleInfoList = new ArrayList<>();userInfoList.add(new ExcelExportVo.UserInfo("chenyi","陈仪","12345678910","战神联盟","科研部"));userInfoList.add(new ExcelExportVo.UserInfo("honger","洪洱","13241220000","前途有限公司","市场部"));userInfoList.add(new ExcelExportVo.UserInfo("zhangsan","张三","12544445555","前途有限公司","研发部"));userInfoList.add(new ExcelExportVo.UserInfo("lisi","李四","13125223561","战神联盟","科研部"));userInfoList.add(new ExcelExportVo.UserInfo("wangwu","王五","15423226355","战神联盟","销售部"));roleInfoList.add(new ExcelExportVo.RoleInfo("经理","JL","sys,dept,role,menu","view,add,update,delete"));roleInfoList.add(new ExcelExportVo.RoleInfo("经理","JL","sys,dept,role,menu","view,add,update,delete"));roleInfoList.add(new ExcelExportVo.RoleInfo("组长","ZZ","role,menu","view,add,update"));roleInfoList.add(new ExcelExportVo.RoleInfo("普通","PT","menu","view,add"));roleInfoList.add(new ExcelExportVo.RoleInfo("普通","PT","menu","view,add"));ExcelExportVo vo = new ExcelExportVo();vo.setUserInfoList(userInfoList);vo.setRoleInfoList(roleInfoList);dataList.add(vo);// 第一个sheetExportParams params1 = new ExportParams();params1.setStyle(ExcelStyleUtil.class);params1.setSheetName("用户信息1");params1.setTitle("用户信息");Map<String, Object> params1Map = new HashMap<>();params1Map.put("title", params1);params1Map.put("entity", ExcelExportVo.class);params1Map.put("data", dataList);// 第二个sheetExportParams params2 = new ExportParams();params2.setStyle(ExcelStyleUtil.class);params2.setSheetName("用户信息2");Map<String, Object> params2Map = new HashMap<>();params2Map.put("title", params2);params2Map.put("entity", ExcelExportVo.class);params2Map.put("data", new ArrayList<>());// 第三个sheetExportParams params3 = new ExportParams();params3.setStyle(ExcelStyleUtil.class);params3.setSheetName("用户信息3");Map<String, Object> params3Map = new HashMap<>();params3Map.put("title", params3);params3Map.put("entity", ExcelExportVo.class);params3Map.put("data", new ArrayList<>());// 将3个sheet封装List<Map<String, Object>> sheetsList = new ArrayList<>();sheetsList.add(params1Map);sheetsList.add(params2Map);sheetsList.add(params3Map);Workbook workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);ExcelUtil.downLoadExcel("用户信息",response,workBook);}}
二、模板导出
关于模板导出，其他倒没什么，主要是填充数据比较麻烦，此外还有一个要注意的点，就是关于数据横向遍历，也就是列遍历，就是新建列而不是新建行。
横向遍历，模板的语法是用#fe，然后还需要在代码中手动开启列遍历。（之前我看文档上写的这个语法是横向遍历的，但是我用了一直不起作用，后面找啊找，终于在网上找到原因了）
{{#fe:headList t.name }}
String excelPath = System.getProperty("user.dir")+"\\src\\main\\resources\\template\\1.xls";TemplateExportParams params = new TemplateExportParams(excelPath);// 就是这一句，必须要设置为 trueparams.setColForEach(true);
三、动态导出不同列、动态更改表头
就是有的时候有某列或者某几列不符合条件，不要导出。
举个🌰：
1、就比如有两个页面，它们的数据来源、字段其实都是一个表的，但是因为有一些条件，页面1只展示符合条件xx的数据，页面2只展示符合条件xxx的数据，而且展示的字段也不尽相同；
2、还有一个假如根据天数来导出数据，每一天作为列，但是每个月的天数都不一样，要怎么根据月份的天数去导出正确的列数呢？
我们可以用ExcelExportEntity和@Excel来实现。
思路：案例1因为两个页面数据都是同一个数据库表的数据，所以实体bean也是同一个。我们可以把两个页面要导出的字段都注解上@Excel，然后点击导出，调用接口的时候，控制哪个页面要导出哪几个字段（或者忽略哪几个字段不用导出）。
// 这里简单说一下，比如实体bean有这几个字段：姓名、性别、年龄、手机、邮箱。// 页面1 要导出 姓名、性别、年龄 这几个字段；页面2 要导出 姓名、手机、邮箱 这几个字段。// 那么在controller中，直接把这几个字段名用逗号隔开拼接就行，调用 ExcelUtil.dynamicExport 方法，会创建动态列的表头。// 如果要导出的列太多，拼接的字符串太长，可以选择忽略列，哪几列是不用导出的，用逗号隔开拼接，调用 ExcelUtil.dynamicIgnoreExport 方法。/*** 还有就是，有的时候可能同一个字段在不同页面，显示的表头不一样。* 比如姓名这列，在页面1表头是姓名，在@Excel注解中设置的name也是姓名，但是它在页面2的表头是用户姓名。* 这个时候我们就需要在页面2的导出中，改变姓名这列的表头了。在 dynamicExport、dynamicIgnoreExport 这两个方法中，设置* changeHead 的值为 {"name":"用户姓名"} ，其中 name 表示字段名。*/@Excel(name = "姓名",width = 15)private String name;
下面这里详细讲一下案例2。
🍟 根据天数动态导出列
@Datapublic class TemplateVo {// 这里只导出25号以后的天数（不然的话字段太多了，这里只是举个栗子，具体字段看实际情况）@Excel(name = "25日",width = 12)private String day1;@Excel(name = "26日",width = 12)private String day2;@Excel(name = "27日",width = 12)private String day3;@Excel(name = "28日",width = 12)private String day4;@Excel(name = "29日",width = 12)private String day5;@Excel(name = "30日",width = 12)private String day6;@Excel(name = "31日",width = 12)private String day7;public TemplateVo(String day1, String day2, String day3, String day4, String day5, String day6, String day7) {this.day1 = day1;this.day2 = day2;this.day3 = day3;this.day4 = day4;this.day5 = day5;this.day6 = day6;this.day7 = day7;}}
/*** 根据年月获取需要导出的列*/private String getExportColumn(int year,int month){String column = "";String date = year+"-"+month+"-25";LocalDate parse = LocalDateTimeUtil.parseDate(date,"yyyy-M-dd");// 获取这个月份的长度，也就是天数int length = parse.lengthOfMonth();// 因为是导出25号（包括25号）以后的天数，所以需要 总天数-25+1int day = length-25+1;// 25号到31号总共7天，遍历7次，拼接要导出的列名for (int i = 1; i <= 7; i++) {if (i<day) column += "day"+i+",";else if (i==day) column += "day"+i;}return column;}/*** 导出动态列*/@RequestMapping("/dynamicColumnExport")public void dynamicColumnExport(int year,int month,HttpServletResponse response){List<TemplateVo> list = new ArrayList<>();for (int i = 0; i < 5; i++) {list.add(new TemplateVo("25号"+i,"26号"+i,"27号"+i,"28号"+i,"29号"+i,"30号"+i,"31号"+i));}// 获取要导出的列名String column = getExportColumn(year, month);// 这里是创建动态列的表头，dynamicExport 这个方法在上面那个 ExcelUtil 有List<ExcelExportEntity> beanList = ExcelUtil.dynamicExport(TemplateVo.class, column);ExportParams params = new ExportParams();params.setStyle(ExcelStyleUtil.class);Workbook workbook = ExcelExportUtil.exportExcel(params, beanList, list);ExcelUtil.downLoadExcel("下载",response,workbook);}
🌭 效果
🥞 动态更改表头
@Datapublic class SysUser{@Excel(name = "姓名",width = 20)private String name;@Excel(name = "性别",width = 15)private String sex;@Excel(name = "年龄",width = 15)private Integer age;@Excel(name = "部门",width = 20)private String dept;@Excel(name = "职位",width = 20)private String post;@Excel(name = "电话",width = 20)private String phone;@Excel(name = "邮箱",width = 20)private String email;}/*** 页面1：导出 姓名、性别、年龄、手机、邮箱 这几个字段，忽略部门、职位。*/@GetMapping("/export")public void export(HttpServletResponse response) {List<SysUser> list = sysUserService.getList();String fileName = "用户列表";List<ExcelExportEntity> exportEntityList = ExcelUtil.dynamicIgnoreExport(SysUser.class, "dept,post", null);ExportParams exportParams = new ExportParams();exportParams.setTitleHeight((short) 15);exportParams.setStyle(ExcelStyleUtil.class);Workbook workbook = ExcelExportUtil.exportExcel(exportParams, exportEntityList, result);ExcelUtil.downLoadExcel(fileName,response,workbook);}/*** 页面2：导出 姓名、手机、邮箱 这几个字段，其中姓名的表头改为用户姓名，邮箱的表头改为电子邮箱。*/@GetMapping("/export")public void export(HttpServletResponse response) {List<SysUser> list = sysUserService.getList();String fileName = "用户列表";JSONObject obj = new JSONObject();obj.putOpt("name","用户姓名");obj.putOpt("email","电子邮箱");List<ExcelExportEntity> exportEntityList = ExcelUtil.dynamicExport(SysUser.class, "name,phone,email", obj);ExportParams exportParams = new ExportParams();exportParams.setTitleHeight((short) 15);exportParams.setStyle(ExcelStyleUtil.class);Workbook workbook = ExcelExportUtil.exportExcel(exportParams, exportEntityList, result);ExcelUtil.downLoadExcel(fileName,response,workbook);}
暂时就这些了，其实做项目的时候，easypoi碰到的问题不止这些，现在项目做完了，有空了，想总结下把解决办法都写出来，发现项目中遇到的那些问题好像都不适合用来做案例来讲。看一下以后有没有什么补充的吧。（最后，如果觉得文章对你有帮助的话，点个赞再走吧~👀）
