备注:动态字段导出主要是用了反射的原理,跟excel需要导出的字段一一映射。话不多说,直接上代码;
1.生成的动态模板如图:
如上图,如果下拉框里不是选择的值,会给用户提示,下拉框用来限制用户导入只能选择下拉框中的值
先放controller层调用
@RestController @RequestMapping("/test") @Slf4j @RequiredArgsConstructor public class TestExcelController { private final DynamicExcelUtils excelUtils; @GetMapping("v1/test01.do") @CrossOrigin @ApiOperation(value = "测试动态模板生成下载") public void test01(HttpServletResponse response) { //标题头数据 String title = "说明:\n"+ "1、带*号的字段必填\n"+ "2、员工工号只能输入英文和数字,长度限制10。\n"+ "3、员工姓名长度限制20。\n"+ "4、下拉框选项只能选择模板提供的下拉选项。\n"+ "5、年龄只能输入数字。\n"+ "6、第三行数据为示例数据,可以删除输入正式数据。\n"+ "7、导入表格时本说明不要删除。"; //需要生成的excel列字段名称 List<String> names = Arrays.asList("姓名","工号","年龄","下拉列"); //设置第三行示例数据 List<User> userList = new ArrayList<>(); User user = new User("张三", "B112233", "18", "下拉值01"); userList.add(user); //列字段的英文名称,根据英文名称对应示例数据 List<String> fieldEn = Arrays.asList("name","workCode","age","select"); //设置第几列显示下拉框,用户只能选择下拉框中的值,非下拉框则不允许保存excel Map<Integer, List<String>> selectMap = new HashMap<>(8); //设置下拉框的值 List<String> selectList = Arrays.asList("下拉字段1", "下拉字段2", "下拉字段3"); //设置excel第几列为下拉列 selectMap.put(3,selectList); //设置sheet页名称 String sheetName = "测试sheet页"; //设置文件名称 String fileName = "测试生成动态模板"; //以上所有数据都是支持动态设置的 excelUtils.excelDownloadLink(response,title,names,fieldEn,userList,selectMap,sheetName,fileName); } @GetMapping("v1/test02.do") @CrossOrigin @ApiOperation(value = "测试excel导出动态数据") public void test02(HttpServletResponse response) { //自定义需要导出的excel列字段名称 List<String> names = Arrays.asList("姓名(自定义)","工号(自定义)","年龄(自定义名称)","下拉列(自定义名称)"); //列字段的英文名称,根据英文名称对应示例数据 List<String> fieldEn = Arrays.asList("name","workCode","age","select"); //要导出的数据 List<User> userList = new ArrayList<>(); User user1 = new User("张三01", "B11", "18", "下拉值01"); User user2 = new User("张三02", "B112", "19", "下拉值02"); User user3 = new User("张三03", "B11223", "20", "下拉值03"); userList.add(user1); userList.add(user2); userList.add(user3); //设置sheet页名称 String sheetName = "测试sheet页"; //设置文件名称 String fileName = "测试导出动态excel数据"; excelUtils.excelExportData(response,names,fieldEn,userList,sheetName,fileName); } @GetMapping("v1/test03.do") @CrossOrigin @ApiOperation(value = "测试excel导出普通通用实体类型数据") public void test03(HttpServletResponse response) { List<UserExcel> userExcels = new ArrayList<>(); userExcels.add(new UserExcel("张01","张测试1","B001","16","下拉01")); userExcels.add(new UserExcel("张02","张测试2","B002","17","下拉02")); userExcels.add(new UserExcel("张03","张测试3","B003","18","下拉03")); //设置sheet页名称 String sheetName = "测试sheet页"; //设置文件名称 String fileName = "测试导出excel数据"; excelUtils.excelExportOrdinaryData(response,sheetName,fileName,userExcels,UserExcel.class); } }
写的excel工具类,包含了动态生成模板,动态导出数据,和普通导出数据
import cn.hutool.core.collection.CollectionUtil; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.support.ExcelTypeEnum; import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy; import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy; import lombok.extern.slf4j.Slf4j; import net.sharing.backstage.oa.constant.Constants; import org.springframework.stereotype.Component; import javax.servlet.http.HttpServletResponse; import java.beans.IntrospectionException; import java.beans.PropertyDescriptor; import java.io.IOException; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.net.URLEncoder; import java.util.*; /** * @program: sharing-backstage * @Description: 动态excel工具类 * @Author: zwx * @Date: 2022/5/12 13:42 */ @Component @Slf4j public class DynamicExcelUtils { /** * 动态excel模板下载 * @param response 响应 * @param title 大标题说明,多行使用转义符\n换行 * @param names 列集合 * @param fieldEn 列英文名称 * @param selectMap 下拉框 * @param fileName 自定义文件名称 * @param sheetName 自定义sheet页名称 */ public void excelDownloadLink(HttpServletResponse response, String title,List<String> names, List<String> fieldEn ,List list ,Map<Integer, List<String>> selectMap,String sheetName, String fileName) { try { //设置表格第三行的示例数据的值 List<List<String>> datas = setData(list,fieldEn); response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx"); response.setCharacterEncoding("UTF-8"); EasyExcel.write(response.getOutputStream()) .excelType(ExcelTypeEnum.XLSX) .head(head(CollectionUtil.isNotEmpty(names) ? names.toArray(new String[0]) : new String[0], title)) //开启内存模式才能使用动态设置标题样式 .inMemory(true) .registerWriteHandler(new TitleStyleUtils(names)) .registerWriteHandler(new SelectSheetWriteHandler(selectMap)) .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)) .sheet(sheetName) .doWrite(CollectionUtil.isNotEmpty(datas) ? datas:new ArrayList()); } catch (IOException e) { e.printStackTrace(); response.reset(); response.setCharacterEncoding("utf-8"); response.setContentType("application/json"); try { response.getWriter().println("打印失败"); } catch (IOException ex) { ex.printStackTrace(); } } } /** * 动态excel数据导出 * @param response 响应 * @param fileName 自定义文件名称 * @param sheetName 自定义sheet页名称 * @param list 数据集合 */ public void excelExportData(HttpServletResponse response,List<String> names,List<String> fieldEn,List list ,String sheetName, String fileName){ try { //设置返回数据的值跟动态列一一对应 List<List<String>> datas = setData(list,fieldEn); response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx"); response.setCharacterEncoding("UTF-8"); EasyExcel.write(response.getOutputStream()) .excelType(ExcelTypeEnum.XLSX) .head(headData(CollectionUtil.isNotEmpty(names) ? names.toArray(new String[0]) : new String[0])) .registerWriteHandler(new AutoWidthHandler()) .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 25)) .sheet(sheetName) .doWrite(CollectionUtil.isNotEmpty(datas) ? datas:new ArrayList()); } catch (IOException e) { e.printStackTrace(); response.reset(); response.setCharacterEncoding("utf-8"); response.setContentType("application/json"); try { response.getWriter().println("打印失败"); } catch (IOException ex) { ex.printStackTrace(); } } } /** * 普通excel数据导出(实体类方式) * @param response 响应 * @param fileName 自定义文件名称 * @param sheetName 自定义sheet页名称 * @param list 数据集合 * @param clazz 实体对象字节码对象 */ public void excelExportOrdinaryData(HttpServletResponse response,String sheetName, String fileName, List list, Class clazz){ try { response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx"); response.setCharacterEncoding("UTF-8"); EasyExcel.write(response.getOutputStream(),clazz) .excelType(ExcelTypeEnum.XLSX) .head(clazz) //设置默认样式及写入头信息开始的行数 .relativeHeadRowIndex(0) .registerWriteHandler(new AutoWidthHandler()) .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 25)) .sheet(sheetName) .doWrite(list); } catch (IOException e) { e.printStackTrace(); response.reset(); response.setCharacterEncoding("utf-8"); response.setContentType("application/json"); try { response.getWriter().println("打印失败"); } catch (IOException ex) { ex.printStackTrace(); } } } /** * 根据反射构造动态数据 */ private List<List<String>> setData(List list,List<String> fieldEn){ List<List<String>> datas = new ArrayList<>(); //对象反射转map方法 List<Map<Object, Object>> maps = new ArrayList<>(); if (CollectionUtil.isNotEmpty(list)){ for (Object o : list) { Class<?> aClass = o.getClass(); Field[] fields = aClass.getDeclaredFields(); Map<Object, Object> map = new HashMap<>(40); for (Field field : fields) { map.put(field.getName(), getResult(field.getName(), o)); } maps.add(map); } for (Map<Object, Object> map : maps) { //用于接收返回数据行? List<String> data = new LinkedList<String>(); for (int i = 0; i < fieldEn.size(); i++) { Object o = map.get(fieldEn.get(i)); data.add(Objects.isNull(o) ? Constants.CHAR : o.toString()); } datas.add(data); } } return datas; } /** * 对象实体反射方法 */ private static Object getResult(Object fieldName, Object o) { try { Class<?> aClass = o.getClass(); Field declaredField = aClass.getDeclaredField(fieldName.toString()); declaredField.setAccessible(true); PropertyDescriptor pd = new PropertyDescriptor(declaredField.getName(), aClass); Method readMethod = pd.getReadMethod(); return readMethod.invoke(o); } catch (NoSuchFieldException e) { e.printStackTrace(); } catch (IntrospectionException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } return null; } /** 动态头传入 */ private List<List<String>> head(String[] header, String bigTitle) { List<String> head0 = null; List<List<String>> list = new LinkedList<List<String>>(); for (String h : header) { head0 = new LinkedList<>(); head0.add(bigTitle); head0.add(h); list.add(head0); } return list; } /** * 数据动态头传入 */ private List<List<String>> headData(String[] header) { List<String> head0 = null; List<List<String>> list = new LinkedList<List<String>>(); for (String h : header) { head0 = new LinkedList<>(); head0.add(h); list.add(head0); } return list; } }
其他样式设置类:
import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.style.AbstractCellStyleStrategy; import org.apache.poi.ss.usermodel.*; import java.util.List; /** * @program: sharing-backstage * @Description: 标题样式工具类 * @Author: zwx * @Date: 2022/2/21 16:34 */ public class TitleStyleUtils extends AbstractCellStyleStrategy { private List<String> names; private Workbook workbook; public TitleStyleUtils(){ } public TitleStyleUtils (List<String> names){ this.names = names; } @Override protected void initCellStyle(Workbook workbook) { this.workbook = workbook; } @Override protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) { //设置标题说明样式(excel第一行,多个换行符拼接的标题说明文字) if(cell.getColumnIndex()==0 && relativeRowIndex==0){ CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setWrapText(true); //设置 水平居中 cellStyle.setAlignment(HorizontalAlignment.LEFT); //设置 垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.TOP); Font font = workbook.createFont(); //颜色 font.setColor(IndexedColors.RED.getIndex()); //加粗 font.setBold(true); //字体 font.setFontName("宋体"); //大小 font.setFontHeightInPoints((short) 12); //高度 workbook.getSheetAt(0).getRow(0).setHeight((short) 3888); //宽度 workbook.getSheetAt(0).setColumnWidth(cell.getColumnIndex(), 10240); cellStyle.setFont(font); cell.setCellStyle(cellStyle); } //设置表头样式(excel第二行,动态列名称) if (relativeRowIndex==1){ for (int i = 0; i < names.size(); i++) { if (cell.getColumnIndex() == i){ CellStyle cellStyle = workbook.createCellStyle(); //设置 水平居中 cellStyle.setAlignment(HorizontalAlignment.LEFT); //设置 垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.TOP); Font font = workbook.createFont(); //颜色 font.setColor(IndexedColors.BLACK.getIndex()); //加粗 font.setBold(true); font.setFontName("宋体"); font.setFontHeightInPoints((short) 12); cellStyle.setFont(font); cell.setCellStyle(cellStyle); } } } } @Override protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) { } }
import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import org.apache.poi.hssf.usermodel.HSSFDataValidation; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddressList; import java.util.List; import java.util.Map; /** * @program: sharing-backstage * @Description: 处理下拉 * @Author: zwx * @Date: 2022/3/3 15:16 */ public class SelectSheetWriteHandler implements SheetWriteHandler { private Map<Integer, List<String>> selectMap; private char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'}; public SelectSheetWriteHandler(Map<Integer, List<String>> selectMap) { this.selectMap = selectMap; } @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { if (selectMap == null || selectMap.size() == 0) { return; } // 需要设置下拉框的sheet页 Sheet curSheet = writeSheetHolder.getSheet(); DataValidationHelper helper = curSheet.getDataValidationHelper(); String dictSheetName = "字典sheet"; Workbook workbook = writeWorkbookHolder.getWorkbook(); // 数据字典的sheet页 Sheet dictSheet = workbook.createSheet(dictSheetName); for (Map.Entry<Integer, List<String>> entry : selectMap.entrySet()) { // 设置下拉单元格的首行、末行、首列、末列 CellRangeAddressList rangeAddressList = new CellRangeAddressList(2, 65533, entry.getKey(), entry.getKey()); int rowLen = entry.getValue().size(); // 设置字典sheet页的值 每一列一个字典项 for (int i = 0; i < rowLen; i++) { Row row = dictSheet.getRow(i); if (row == null) { row = dictSheet.createRow(i); } row.createCell(entry.getKey()).setCellValue(entry.getValue().get(i)); } String excelColumn = getExcelColumn(entry.getKey()); // 下拉框数据来源 eg:字典sheet!$B1:$B2 String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen; // 创建可被其他单元格引用的名称 Name name = workbook.createName(); // 设置名称的名字 name.setNameName("dict" + entry.getKey()); // 设置公式 name.setRefersToFormula(refers); // 设置引用约束 DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + entry.getKey()); // 设置约束 DataValidation validation = helper.createValidation(constraint, rangeAddressList); if (validation instanceof HSSFDataValidation) { validation.setSuppressDropDownArrow(false); } else { validation.setSuppressDropDownArrow(true); validation.setShowErrorBox(true); } // 阻止输入非下拉框的值 validation.setErrorStyle(DataValidation.ErrorStyle.STOP); validation.createErrorBox("提示", "此值与单元格定义格式不一致!"); // 添加下拉框约束 writeSheetHolder.getSheet().addValidationData(validation); } } /** * 将数字列转化成为字母列 * * @param num * @return */ private String getExcelColumn(int num) { String column = ""; int len = alphabet.length - 1; int first = num / len; int second = num % len; if (num <= len) { column = alphabet[num] + ""; } else { column = alphabet[first - 1] + ""; if (second == 0) { column = column + alphabet[len] + ""; } else { column = column + alphabet[second - 1] + ""; } } return column; } }
import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy; import net.sharing.backstage.oa.constant.Constants; import org.apache.poi.ss.usermodel.Cell; import org.springframework.util.CollectionUtils; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @program: sharing-backstage * @Description: 动态表头设置自适应宽度 * @Author: zwx * @Date: 2022/1/25 15:26 */ public class AutoWidthHandler extends AbstractColumnWidthStyleStrategy { private Map<Integer, Map<Integer, Integer>> cache = new HashMap<>(); @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) { boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList); if (needSetWidth) { Map<Integer, Integer> maxColumnWidthMap = cache.get(writeSheetHolder.getSheetNo()); if (maxColumnWidthMap == null) { maxColumnWidthMap = new HashMap<>(50); cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap); } Integer columnWidth = this.dataLength(cellDataList, cell, isHead); if (columnWidth >= 0) { if (columnWidth > 20) { columnWidth = 20; } Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex()); if (maxColumnWidth == null || columnWidth > maxColumnWidth) { maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } } private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { CellData cellData = cellDataList.get(0); CellDataTypeEnum type = cellData.getType(); if (type == null) { return -1; } else { switch (type) { case STRING: return cellData.getStringValue().getBytes().length; case BOOLEAN: return cellData.getBooleanValue().toString().getBytes().length; case NUMBER: return cellData.getNumberValue().toString().getBytes().length; default: return -1; } } } } }
实体类:
/** * @program: sharing-backstage * @Description: 用户实体类 * @Author: zwx * @Date: 2022/5/12 14:21 */ @Data @AllArgsConstructor @NoArgsConstructor public class User { /** * 姓名 */ private String name; /** * 工号 */ private String workCode; /** * 年龄 */ private String age; /** * 下拉框 */ private String select; }
/** * @program: sharing-backstage * @Description: 用户EXCEL类 * @Author: zwx * @Date: 2022/5/12 15:58 */ @Data @NoArgsConstructor @AllArgsConstructor public class UserExcel { /** * 姓名 */ @ExcelProperty(value = {"姓名"}, index = 0) private String name; /** * 测试名称 */ @ExcelIgnore private String testName; /** * 工号 */ @ExcelProperty(value = {"工号"}, index = 1) private String workCode; /** * 年龄 */ @ExcelProperty(value = {"年龄"}, index = 2) private String age; /** * 下拉框 */ @ExcelProperty(value = {"下拉框"}, index = 3) private String select; }