实现
@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<>();
@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 > 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.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 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;
}
@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;
}
}
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;
}
@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) {
}
}
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;
}
参考：https://blog.csdn.net/a116385895/article/details/124734823
1205
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
