动态excel模板下载 可改造成任意excel

模板下载 可改造成任意excel"/>

动态excel模板下载 可改造成任意excel

实现

    @RequestMapping(value = "v1/test02.do",method = RequestMethod.GET,headers = "Accept=application/octet-stream")@ApiOperation(value = "测试excel导出动态数据")public void test02(HttpServletResponse response) {//自定义需要导出的excel列字段名称List<String> names = Arrays.asList("姓名(自定义)","工号（自定义）","年龄（自定义名称）","下拉列（自定义名称）");//列字段的英文名称,根据英文名称对应示例数据List<String> fieldEn = Arrays.asList("id","workCode","age","select");//设置第三行示例数据List<OaUfVO> userList = new ArrayList<>();OaUfVO user = new OaUfVO();user.setId("11");userList.add(user);//设置sheet页名称String sheetName = "测试sheet页";//设置文件名称String fileName = "测试导出动态excel数据";DynamicExcelUtils.excelExportData(response,names,fieldEn,userList,sheetName,fileName);//        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())
//                    .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(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();
//            }
//        }}

工具类

package com.lbx.ms.biz.xp.project.release.util.excelUtil;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 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<>();@Overrideprotected 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 > 1) {
//                    columnWidth = 1;
//                }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;}}}}}

package com.lbx.ms.biz.xp.project.release.util.excelUtil;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 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.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 static 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 static 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) ? "" : 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 static 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;}}

package com.lbx.ms.biz.xp.project.release.util.excelUtil;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;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic 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:$B2String 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;}
}

package com.lbx.ms.biz.xp.project.release.util.excelUtil;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;}@Overrideprotected void initCellStyle(Workbook workbook) {this.workbook = workbook;}@Overrideprotected 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);}}}}@Overrideprotected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {}
}

package com.lbx.ms.biz.xp.project.release.util.excelUtil;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;/*** @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;
}

参考：