问题原因
在开发中一定会遇见不一样的导出功能，比如说导出报表，导出列表数据，下载模板等。
公司最近在重构一些功能，此处涉及每一个列表的导出功能，且列有扩展字段。
pom依赖及版本
springmvc：5.3.9
poi：4.1.1
hutool：5.5.4
代码
package com.hulunbuir.admin.controller;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.style.StyleUtil;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.cglib.beans.BeanMap;
import org.springframework.web.servlet.view.document.AbstractXlsxView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;
/**
* <p>
* 导出excel视图
* </p>
*
* @author wangjunming
*/
@Slf4j
@Getter
public class ExcelViewImproveLine extends AbstractXlsxView {
/**
* 导出的文件名
*/
private String filename;
/**
* 是否需要直接转换为文件
*/
private Boolean convertFileFLag = Boolean.FALSE;
/**
* 设置是否需要下载
*
* @param convertFileFLag 默认-false-需要下载。true-不需要下载。
*/
public void setConvertFileFLag(Boolean convertFileFLag) {
this.convertFileFLag = convertFileFLag;
}
/**
* 导出的文件指定文件路径，默认只有在 convertFileFLag 为true的情况下使用
*/
private String fileAbsolutePath;
public void setFileAbsolutePath(String fileAbsolutePath) {
this.fileAbsolutePath = fileAbsolutePath;
}
/**
* 多个sheet页
*/
private final List<ExcelSheet> sheets;
@Getter
@Setter
public static class ExcelSheet {
{
timePattern = "yyyy-MM-dd HH:mm";
datePattern = "yyyy-MM-dd";
scale = 2;
}
/**
* sheet名称
*/
private String sheetName;
/**
* 标题头字段编码 + 标题头翻译
*/
private List<ExportField> fields;
/**
* 数据集合
*/
private Collection<?> data;
/**
* 导出的对象
*/
private Class<?> model;
/**
* 日期类型默认转换的格式，针对于 Date 、 LocalDateTime
*/
private String timePattern;
/**
* 日期类型默认转换的格式 针对于 LocalDate
*/
private String datePattern;
/**
* 四舍五入的位数
*/
private int scale;
private ExcelSheet() {
}
private ExcelSheet(String sheetName, Class<?> model, Collection<?> data) {
this.sheetName = sheetName;
this.data = data;
this.model = model;
fillAnnotationTitles(model, this);
}
private ExcelSheet(String sheetName, List<ExportField> fieldList, Collection<?> data) {
this.sheetName = sheetName;
this.data = data;
fillCustomizeTitles(this, fieldList);
}
}
/**
* 创建自定义列的excel
*
* @param sheetName sheet页
* @param fieldList 字段集合
* @param data 数据集
* @return ExcelSheet
*/
public static ExcelSheet createSheet(String sheetName, List<ExportField> fieldList, Collection<?> data) {
return new ExcelSheet(sheetName, fieldList, data);
}
/**
* 从注解中获取导出字段名
* // TODO 在此处将换掉注解，或者使用自定注解此处可以自定义改进，或者支持多个注解，即第一个注解没找到时，找第二个注解进行构建所需参数
*/
private static void fillAnnotationTitles(Class<?> model, ExcelViewImproveLine.ExcelSheet sheet) {
//将通过对model中的 Excel注解进行解析，然后设置标题头。
List<ExcelViewImproveLine.ExportField> easyPoiExcelFieldList = Arrays.stream(model.getDeclaredFields())
.filter(f -> f.isAnnotationPresent(Excel.class)).map(f -> {
String fieldCode = f.getName();
ExcelViewImproveLine.ExportField exportField = new ExcelViewImproveLine.ExportField();
exportField.setFieldCode(fieldCode);
Excel excelAnnotation = f.getAnnotation(Excel.class);
String name = excelAnnotation.name();
exportField.setFieldName(name);
int index = excelAnnotation.fixedIndex();
exportField.setOrder(index);
return exportField;
}).sorted().collect(Collectors.toList());
if (CollUtil.isNotEmpty(easyPoiExcelFieldList)) {
log.info("此处使用easy-poi内部注解设置动态列");
sheet.setFields(easyPoiExcelFieldList);
return;
}
}
/**
* 自定义标题头
*/
private static void fillCustomizeTitles(ExcelSheet sheet, List<ExportField> fields) {
fields = fields.stream().sorted().collect(Collectors.toList());
sheet.setFields(fields);
}
/**
* 多个sheet页进行导出
*
* @param filename 文件名
* @param sheets sheet页数组，可使用静态构建方法： ExcelViewImprove.createSheet()
*/
public ExcelViewImproveLine(String filename, ExcelSheet... sheets) {
this.filename = filename;
this.sheets = new ArrayList<>();
this.sheets.addAll(Arrays.asList(sheets));
}
/**
* 动态列作为参数进行导出
*
* @param filename 文件名
* @param sheetName sheet页名称
* @param fieldList 列字段编码，列字段名称，列排序
* @param data 数据集合
*/
public ExcelViewImproveLine(String filename, String sheetName, List<ExportField> fieldList, Collection<?> data) {
this.filename = filename;
ExcelSheet sheet = createSheet(sheetName, fieldList, data);
sheets = Collections.singletonList(sheet);
}
/**
* 真正执行创建excel的方法
*
* @param map
* @param workbook
* @param request
* @param response
* @throws Exception
*/
@Override
protected void buildExcelDocument(Map<String, Object> map, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
for (ExcelSheet sheet : sheets) {
buildExcel(workbook, sheet);
}
//此处判断是否需要将 workbook 直接转换为文件，如果需要则在当前文件夹下，或指定文件夹下生成excel文件。
if (convertFileFLag) {
downloadExcel(workbook);
return;
}
//设置响应为文件下载
response.setContentType("application/force-download");
if (StrUtil.isBlank(filename)) {
filename = "Excel导出.xlsx";
}
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
}
/**
* 创建excel表格
*
* @param workbook 工作簿
* @param param 所需参数
*/
private void buildExcel(Workbook workbook, ExcelSheet param) {
Sheet sheet;
if (StrUtil.isNotBlank(param.getSheetName())) {
sheet = workbook.createSheet(param.getSheetName());
} else {
sheet = workbook.createSheet();
}
List<ExportField> fields = param.getFields();
log.info("标题行的信息是：{}", JSONUtil.toJsonStr(fields));
int rowNumber = 0;
if (CollUtil.isNotEmpty(fields)) {
// 创建标题行
createHeader(sheet, workbook, rowNumber, fields);
++rowNumber;
}
Collection<?> dataList = param.getData();
if (CollUtil.isEmpty(dataList)) {
return;
}
//创建数据行信息
createDataLine(workbook, sheet, rowNumber, fields, param, dataList);
}
/**
* 创建标题行
*/
private void createHeader(Sheet sheet, Workbook workbook, int rowNumber, List<ExportField> fields) {
//进行构建表头
Row header = sheet.createRow(rowNumber);
//设置行高
header.setHeight((short) 420);
for (int column = 0; column < fields.size(); column++) {
ExportField exportField = fields.get(column);
String fieldName = exportField.getFieldName();
Cell cell = header.createCell(column);
//设置当前单元格的内容
cell.setCellValue(fieldName);
//创建样式
createHeaderCellStyleFont(cell, workbook, exportField);
//创建批注
createHeaderComment(cell, sheet, exportField);
//创建下拉框选项值
creatHeadSelectCellStyle(workbook, sheet, rowNumber, column, exportField);
//设置单元格宽度
sheet.setColumnWidth(column, getColumnWidth(cell));
}
}
/**
* 获取默认的宽度
*
* @param cell 单元格信息
* @return int
*/
public int getColumnWidth(Cell cell) {
return 7000;
}
/**
* 创建下拉框的值
*
* @param workbook 工作簿
* @param sheet 标签页
* @param rowNumber 第几行
* @param column 第几列
* @param exportField 字段信息
*/
private void creatHeadSelectCellStyle(Workbook workbook, Sheet sheet, int rowNumber, int column, ExportField exportField) {
List<String> fieldCodeSelect = exportField.getFieldCodeSelect();
if (CollUtil.isEmpty(fieldCodeSelect)) {
return;
}
String[] fieldSelectArray = fieldCodeSelect.toArray(new String[0]);
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
creatDropDownList(sheet, dataValidationHelper, fieldSelectArray, rowNumber, getLastRowNum(sheet), column, column);
}
/**
* 设置下拉框的最大行是 十万
*
* @param sheet 当前sheet对象
* @return 100000
*/
private Integer getLastRowNum(Sheet sheet) {
return 100000;
}
/**
* 设置标题样式和颜色
*
* @param cell 单元格
* @param workbook 工作簿
* @param exportField 是否必填，如果是必填项则字体颜色是红色
*/
private void createHeaderCellStyleFont(Cell cell, Workbook workbook, ExportField exportField) {
CellStyle headCellStyle = StyleUtil.createHeadCellStyle(workbook);
Boolean required = exportField.getRequired();
if (!required) {
headCellStyle.setFont(blackFont(workbook));
cell.setCellStyle(headCellStyle);
return;
}
headCellStyle.setFont(redFont(workbook));
cell.setCellStyle(headCellStyle);
}
/**
* 创建批注
*
* @param cell 当前单元格
* @param sheet 标签页
* @param exportField 批注信息
*/
private void createHeaderComment(Cell cell, Sheet sheet, ExportField exportField) {
String fieldNotes = exportField.getFieldNotes();
if (StrUtil.isBlank(fieldNotes)) {
return;
}
// 创建绘图对象
Drawing<?> patriarch = sheet.createDrawingPatriarch();
// 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
Comment comment = patriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 12, 12, (short) 16, 16));
// 输入批注信息
comment.setString(new XSSFRichTextString(fieldNotes));
// 添加作者,选中B5单元格,看状态栏
comment.setAuthor("系统");
cell.setCellComment(comment);
}
/**
* 红色字
*/
private Font redFont(Workbook workbook) {
Font font = StyleUtil.createFont(workbook, HSSFColor.HSSFColorPredefined.RED.getIndex(), (short) 14, "宋体");
font.setBold(true);
return font;
}
/**
* 黑色字
*/
private Font blackFont(Workbook workbook) {
Font font = StyleUtil.createFont(workbook, HSSFColor.HSSFColorPredefined.BLACK.getIndex(), (short) 14, "宋体");
font.setBold(true);
return font;
}
/**
* 创建下拉框
* <p>
* 参考：https://www.csdn.net/tags/OtDagg3sODUxMTQtYmxvZwO0O0OO0O0O.html
*
* @param taskInfoSheet 当前标签页
* @param helper 用于创建下拉框的工具
* @param list 下拉框的数据
* @param firstRow 开始第一行
* @param lastRow 最后一行
* @param firstCol 开始第一列
* @param lastCol 最后一列
*/
private static void creatDropDownList(Sheet taskInfoSheet, DataValidationHelper helper, String[] list,
Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {
//获取需要生成下拉框的格
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
//设置下拉框数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
//处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
taskInfoSheet.addValidationData(dataValidation);
}
/**
* 创建数据行信息
*
* @param workbook 工作簿
* @param sheet 标签页
* @param rowNumber 需要创建第几行
* @param fields 字段名信息
* @param param excel导出所需要的相关参数
* @param dataList 数据集合
*/
private void createDataLine(Workbook workbook, Sheet sheet, int rowNumber,
List<ExportField> fields, ExcelSheet param, Collection<?> dataList) {
//构建数据行
for (Object item : dataList) {
BeanMap beanMap = BeanMap.create(item);
Row row = sheet.createRow(rowNumber);
for (int column = 0; column < fields.size(); column++) {
ExportField exportField = fields.get(column);
String fieldCode = exportField.getFieldCode();
Cell cell = row.createCell(column);
String filedValue;
try {
filedValue = getValue(item, beanMap, fieldCode, param);
} catch (Exception e) {
log.error("导出数据行异常，字段名：{}，此数据对象信息：{}，赋值异常", JSONUtil.toJsonStr(exportField), JSONUtil.toJsonStr(beanMap), e);
filedValue = "";
}
cell.setCellStyle(StyleUtil.createDefaultCellStyle(workbook));
cell.setCellValue(filedValue);
sheet.setColumnWidth(column, getColumnWidth(cell));
}
++rowNumber;
}
}
/**
* 获取当前集合对象对应的字段值
*
* @param beanMap 当前对象的字段与值的map
* @param fieldCode 字段名
* @param param 设置参数
* @return String
*/
private String getValue(Object item, BeanMap beanMap, String fieldCode, ExcelSheet param) {
String value = "";
Object obj = beanMap.get(fieldCode);
if (obj == null) {
return value;
}
Class<?> propertyType = beanMap.getPropertyType(fieldCode);
if (BigDecimal.class.isAssignableFrom(propertyType)) {
value = ((BigDecimal) obj).setScale(param.getScale(), RoundingMode.HALF_UP).toString();
} else if (Date.class.isAssignableFrom(propertyType)) {
value = new SimpleDateFormat(param.getTimePattern()).format(obj);
} else if (LocalDate.class.isAssignableFrom(propertyType)) {
value = ((LocalDate) obj).format(DateTimeFormatter.ofPattern(param.getDatePattern()));
} else if (LocalDateTime.class.isAssignableFrom(propertyType)) {
value = ((LocalDateTime) obj).format(DateTimeFormatter.ofPattern(param.getTimePattern()));
} else {
value = (String) obj;
}
return value;
}
/**
* 直接生成excel文件
*
* @param workbook 工作簿
*/
private void downloadExcel(Workbook workbook) {
byte[] excelBytes = writeOuts(workbook);
if (StrUtil.isBlank(fileAbsolutePath)) {
fileAbsolutePath = "/gen_excel";
}
if (StrUtil.isBlank(filename)) {
filename = "excel_by_view.xlsx";
}
//创建文件件
FileUtil.mkdir(FileUtil.file(fileAbsolutePath));
fileAbsolutePath = fileAbsolutePath + "\\" + filename;
fileAbsolutePath = fileAbsolutePath.replaceAll("\\\\", "/");
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(fileAbsolutePath);
IoUtil.write(fileOutputStream, Boolean.TRUE, excelBytes);
} catch (Exception e) {
log.error("生成文件失败，请检查文件路径是否正确。", e);
} finally {
IoUtil.close(fileOutputStream);
}
log.info("生成的文件名...- file:///{}", fileAbsolutePath);
}
/**
* 将 workbook 转换为 字节数组
*
* @param workbook 工作簿
* @return byte[]
*/
public static byte[] writeOuts(Workbook workbook) {
if (workbook == null) {
return new byte[]{};
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
try {
workbook.write(outputStream);
} catch (IOException e) {
log.error("写入流异常", e);
} finally {
try {
workbook.close();
} catch (IOException e) {
log.error("关闭workbook异常", e);
}
}
return outputStream.toByteArray();
}
/**
* excel的标题行信息
*/
@Getter
@Setter
@ToString
@Accessors(chain = true)
public static class ExportField implements Comparable<ExportField> {
/**
* 字段名
*/
private String fieldCode;
/**
* 字段的中文说明
*/
private String fieldName;
/**
* 字段是否必填
*/
private Boolean required = Boolean.FALSE;
/**
* 字段的批注
*/
private String fieldNotes;
/**
* 字段名所属的下拉框的值
*/
private List<String> fieldCodeSelect;
/**
* 排序
*/
private int order;
@Override
public int hashCode() {
int result = fieldCode.hashCode();
result = 31 * result + fieldName.hashCode();
return result;
}
@Override
public int compareTo(ExportField exportField) {
return exportField.order;
}
}
}
使用
main方法：
public static void main(String[] args) throws Exception {
List<ExcelViewImproveLine.ExportField> exportTitleList = CollUtil.newArrayList();
//初始化标题
exportTitleList.add(new ExcelViewImproveLine.ExportField().setFieldCode("orderName").setFieldNotes("订单名称不能为空。").setFieldName("订单名称").setOrder(1));
exportTitleList.add(new ExcelViewImproveLine.ExportField().setFieldCode("orderDesc").setRequired(Boolean.TRUE).setFieldName("订单描述").setOrder(1));
exportTitleList.add(new ExcelViewImproveLine.ExportField().setFieldCode("orderStatus").setFieldName("订单状态")
.setFieldCodeSelect(CollUtil.newArrayList("待付款", "已取消", "待发货", "已收货"))
.setOrder(1));
ExcelViewImproveLine excelViewImproveLine = new ExcelViewImproveLine("订单列表.xlsx", "订单", exportTitleList, CollUtil.newArrayList());
excelViewImproveLine.setConvertFileFLag(Boolean.TRUE);
excelViewImproveLine.setFileAbsolutePath("");
excelViewImproveLine.buildExcelDocument(CollUtil.newHashMap(), new XSSFWorkbook(), null, null);
}
controller接口使用：
@ApiOperation("下载导入模板")
@GetMapping("/dynamic-improve-line-export")
public ExcelViewImproveLine improveLineExportList() {
List<ExcelViewImproveLine.ExportField> exportTitleList = CollUtil.newArrayList();
//初始化标题
exportTitleList.add(new ExcelViewImproveLine.ExportField().setFieldCode("orderName").setFieldNotes("订单名称不能为空。").setFieldName("订单名称").setOrder(1));
exportTitleList.add(new ExcelViewImproveLine.ExportField().setFieldCode("orderDesc").setRequired(Boolean.TRUE).setFieldName("订单描述").setOrder(1));
exportTitleList.add(new ExcelViewImproveLine.ExportField().setFieldCode("orderStatus").setFieldName("订单状态")
.setFieldCodeSelect(CollUtil.newArrayList("待付款", "已取消", "待发货", "已收货"))
.setOrder(1));
return new ExcelViewImproveLine("订单列表.xlsx", "订单", exportTitleList, CollUtil.newArrayList());;
}
GITHUB：
扫一扫
3018
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
