java easyPOI导出一对多数据，设置边框，字体，字体大小
需求总是千奇百怪，解决的方式也可以是多种多样。
今天碰到导出excel是一对多结构的，以往导出的数据都是一条一条的，所以采用的是比较方便简单的方法easyExcel，今天猛然碰到一对多导出虽然用easyExcel也可以，但是相对比较麻烦，没有easyPOI快捷，之前有自己写过导出excel一个表格一个表格画，但是太麻烦。今天正好需求不急，就慢慢研究用easypoi导出，写出通用方法，以后遇到类似的就好办多了，直接调方法。废话不多说，先上效果图：
首先是实体中设置：
public class YjwzStockInVO extends TYjwzStockIn implements Serializable {
//注意：needMerge = true 必加，不然合并的单元格，边框不会合并，需要导出的实体加@Excel注解，不需要导出的字段加@ExcelIgnore注解排除
@Excel(needMerge = true,name = "状态名称")
@ApiModelProperty(value = "状态名称")
private String ztmc;
@Excel(needMerge = true,name = "一级品类")
@ApiModelProperty(value = "一级物资名称")
private String wzOnemc;
@Excel(needMerge = true,name = "二级品类")
@ApiModelProperty(value = "二级物资名称")
private String wzTwomc;
@Excel(needMerge = true,name = "三级品类")
@ApiModelProperty(value = "三级物资名称")
private String wzThreemc;
@Excel(needMerge = true,name = "四级品类")
@ApiModelProperty(value = "四级物资名称")
private String wzRourmc;
@Excel(needMerge = true,name = "数据来源")
@ApiModelProperty(value = "数据来源：1：市应急平台，2：人工新增")
@DataBindDict(sourceField = "#sjly", sourceFieldCombination = "sjly")
private String sjlymc;
@ExcelCollection(name = "应急物资明细")
@ApiModelProperty(value = "应急物资详情")
private List<YjwzStockInDtlVO> dtlList;
}
子类：
public class YjwzStockInDtlVO extends TYjwzStockInDtl implements Serializable {
@Excel(name = "单据类型")
@ApiModelProperty(value = "单据类型：1：采购入库，2：调拨入库")
@DataBindDict(sourceField = "#djlx", sourceFieldCombination = "lx")
private String djlxmc;
@Excel(name = "入库质检")
@ApiModelProperty(value = "入库质检：1：已检验入库，2：未检验入库")
@DataBindDict(sourceField = "#rkzj", sourceFieldCombination = "zjlx")
private String rkzjmc;
@Excel(name = "质检结果")
@ApiModelProperty(value = "质检结果：1：合格，2：不合格")
@DataBindDict(sourceField = "#zjjg", sourceFieldCombination = "zjjg")
private String zjjgmc;
@Excel(name = "存储期单位")
@ApiModelProperty(value = "存储期单位：1：年，2：月")
@DataBindDict(sourceField = "#ccqdw", sourceFieldCombination = "ccqdw")
private String ccqdwmc;
}
然后是工具类准备：
1）首先是设置字体样式的工具类：
package com.sydata.zt.common.excel;
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.*;
/**
* @Author xx
* @Date 2023/12/5 17:37
* @Description: poi导出excel样式设置工具
* @Version 1.0
*/
public class ExcelStyleUtil implements IExcelExportStyler {
private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
private static final short FONT_SIZE_TEN = 9;
private static final short FONT_SIZE_ELEVEN = 10;
private static final short FONT_SIZE_TWELVE = 10;
/**
* 大标题样式
*/
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);
this.styles = initStyles(workbook);
}
/**
* 初始化大标题样式
*
* @param workbook
* @return
*/
private CellStyle initHeaderStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook,FONT_SIZE_ELEVEN,Boolean.TRUE));
return style;
}
/**
* 初始化小标题样式
* @param workbook
* @return
*/
private CellStyle initTitleStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook,FONT_SIZE_ELEVEN,Boolean.TRUE));
return style;
}
/**
* 数据行样式
* @param workbook
* @return
*/
private CellStyle initStyles(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setDataFormat(STRING_FORMAT);
return style;
}
/**
* 大标题样式
* @param color
* @return
*/
@Override
public CellStyle getHeaderStyle(short color) {
return headerStyle;
}
/**
* 每列标题样式
* @param color
* @return
*/
@Override
public CellStyle getTitleStyle(short color) {
return titleStyle;
}
/**
* 数据行样式
* @param b
* @param excelExportEntity
* @return
*/
@Override
public CellStyle getStyles(boolean b, ExcelExportEntity excelExportEntity) {
return styles;
}
/**
* 获取行样式方法
* @param cell
* @param i
* @param entity
* @param o
* @param o1
* @return
*/
@Override
public CellStyle getStyles(Cell cell, int i, ExcelExportEntity entity, Object o, Object o1) {
return getStyles(true,entity);
}
@Override
public CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) {
return null;
}
/**
* 基础样式
* @param workbook
* @return
*/
private CellStyle getBaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(Boolean.TRUE);
return style;
}
/**
* 字体样式
* @param workbook
* @param size
* @param isBold
* @return
*/
private Font getFont(Workbook workbook,short size,boolean isBold){
Font font = workbook.createFont();
//字体大小
font.setFontHeightInPoints(size);
//字体是否加粗
font.setBold(isBold);
//设置字体
// font.setFontName("");
return font;
}
}
2）然后导出excel工具类
package com.sydata.zt.common.excel;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.CollectionUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;
/**
* @author hm
* @date 2023/3/14 17:33
*/
public class EasyExcelGeneralUtil {
/**
* 设置response编码
*/
public static void setResponseContentType(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8") + ".xlsx");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
}
public static void exportExcelByEasyPoi(HttpServletResponse response, String fileName, List<Object> vos, Class<?> classType) throws IOException {
ExportParams exportParams = new ExportParams();
// 设置sheet得名称
exportParams.setSheetName(fileName);
//设置边框，字体，字体大小
exportParams.setStyle(ExcelStyleUtil.class);
Map<String, Object> map = new HashMap<>();
// title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
map.put("title", exportParams);
// 模版导出对应得实体类型的class文件
map.put("entity", classType);
// sheet中要填充得数据
map.put("data", vos);
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(map);
//创建excel文件的方法
Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
//通过response输出流直接输入给客户端
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
}
准备工作已完成，接下来就可以愉快的导出了：
@SneakyThrows
@PostMapping("/export")
@ApiOperation(value = "导出")
public void export(HttpServletRequest request, HttpServletResponse response,@RequestBody YjwzStockInDTO yjwzStockInDTO){
request.getSession();
String fileName = "文件名称";
EasyExcelGeneralUtil.setResponseContentType(response,fileName);
//设置的分页最多能导出10000条数据
yjwzStockInDTO.setPageNum(1);
yjwzStockInDTO.setPageSize(10000);
//查库得到需要导出的数据
List<Object> vos = (List<Object>) stockInService.page(yjwzStockInDTO).getRows();
EasyExcelGeneralUtil.exportExcelByEasyPoi(response,fileName,vos,YjwzStockInVO.class);
}
好了，完整的过程就是这样了，直接掉接口导出就可以了。如有不足之处还请多多指导！
