easypoi导出一对多,合并单元格,且根据内容自适应行高
EasyPoi一对多导出
导出需求:
部门,持仓数量,持仓市值及负面消息为动态多数据列,要求导出时同一日期同一证券代码证券名称下进行合并单元格
导出excel示例
一.pom引入依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.2</version>
</dependency>
二.导出实体类
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import lombok.Data;
import java.util.List;
import lombok.experimental.Accessors;
@Data
@Accessors(chain = true)
public class TestExportVo {
@Excel(name = "业务日期",width = 20,needMerge = true,orderNum = "1",format ="yyyy-MM-dd")
private Date bizDt;
@Excel(name = "证券代码",width = 20,needMerge = true,orderNum = "2")
private String fnclToolCd;
@Excel(name = "证券名称",width = 20,needMerge = true,orderNum = "3")
private String fnclToolNum;
@ExcelCollection(name = "",orderNum="4")
private List<TestExportObjVo> holdData;
@Excel(name = "初步分析",width = 50,needMerge = true,orderNum = "8")
private String initAnal;
public TestExportVo(Date bizDt,String fnclToolCd,String fnclToolNum,List<TestExportObjVo> holdData,String initAnal) {
this.bizDt = bizDt;
this.fnclToolCd = fnclToolCd;
this.fnclToolNum = fnclToolNum;
this.holdData = holdData;
this.initAnal = initAnal;
}
}
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;
import java.math.BigDecimal;
@Data
@ExcelTarget("TestExportVo")
public class TestExportObjVo {
/**
* mergeVertical = true 开启纵向合并相同内容单元格
* mergeRely = {0,1} 合并单元格的依赖关系,当前是指在业务日期和证券代码相同的情况下,部门字段内容相同时进行合并
*/
@Excel(name = "部门",width = 30,needMerge = true,orderNum = "4",mergeVertical = true, mergeRely = {0,1})
private String depts;
@Excel(name = "持仓数量",width = 20,needMerge = true,orderNum = "5",numFormat = "##,##0.00", mergeVertical = true, mergeRely = {0,1,3})
private BigDecimal hldQtys;
@Excel(name = "持仓市值",width = 20,needMerge = true,orderNum = "6",numFormat = "##,##0.00", mergeVertical = true, mergeRely = {0,1,3})
private BigDecimal hldMKtvals;
@Excel(name = "负面信息",width = 80,needMerge = true,orderNum = "7",mergeVertical = true, mergeRely = {0,1})
private String negInfo;
public TestExportObjVo(String depts,BigDecimal hldQtys,BigDecimal hldMKtvals,String negInfo){
this.depts = depts;
this.hldQtys = hldQtys;
this.hldMKtvals = hldMKtvals;
this.negInfo = negInfo;
}
}
excelPoi常用注解说明
@Excel注解
属性 | 类型 | 默认值 | 功能 |
---|---|---|---|
name | String | null | 对应Excel的列名 |
orderNum | String | “0” | 列的排序 |
format | String | ” “ | 时间格式化,相当于同时设置exportFormat和importFormat |
export | Format | String | " " |
importFormat | String | " " | 导入时的时间格式 |
type | int | 1 | 导出类型:1是文本(默认),2是图片,3是函数,10是数字 |
replace | String[] | {} | 值的替换,replace = {“男_1”, “女_2”}将值为1的替换为男 |
needMerge | boolean | false | 是否需要纵向合并单元格(用于list创建的多个row) |
numFormat | String | " " | 数字格式化,使用对象DecimalFormat |
suffix | String | " " | 文字后缀 |
width | double | 10 | 列宽 |
height | double | 10 | 行高,后期打算统一使用@ExcelTarget的height,这个会被废弃 |
savePath | String | “/upload/” | 文件保存路径,默认是”target/classes/upload/类名“ |
isStatistics | boolean | false | 自动统计数据,在行尾进行统计,会吞没异常 |
isImportField | boolean | false | 导入Excel时,对Excel中的字段进行校验,如果没有该字段,导入失败 |
isColumnHidden | boolean | false | 导出隐藏列 |
databaseFormat | String | “yyyyMMddHHmmss” | 导出时间设置,如果字段是data类型则不需要设置,数据库如果是String类型,这个需要设置这个数据库格式,用来转换时间格式输出 |
isWrap | boolean | true | 是否换行及支持\n |
mergeRely | int[] | {} | 合并单元格依赖关系,比如第二列合并是基于第一列 |
mergeVertical | boolean | fasle | 纵向合并内容相同的单元格 |
imageType | int | 1 | 导出类型1:从file读取,2:从数据库中读取,默认是文件,导入也是一样 |
@ExcelCollection 注解
@ExcelCollection 注解表示一个集合,主要针对一对多的导出
比如一个老师对应多个科目,科目就可以用集合表示,作用在一个类型是List的属性上面,属性如下:
属性 | 类型 | 默认值 | 功能 |
---|---|---|---|
name | String | null | 对应集合的列名 |
orderNum | String | “0” | 排序 |
type | Class | ArrayList.class | 导入时创建对象时使用 |
@ExcelTarget注解
作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理
@ExcelEntity注解
@ExcelEntity注解表示一个继续深入导出的实体,是作用一个类型为实体的属性上面
@ExcelIgnore注解
@ExcelIgnore 忽略这个属性,多使用需循环引用中
三.导出工具类
1.导出样式工具类
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 qianqian
* @date 2022年07月06日 14:15
*/
public class ExcelExportStylerUitl implements IExcelExportStyler {
private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
private static final short FONT_SIZE_TEN = 10;
private static final short FONT_SIZE_ELEVEN = 11;
private static final short FONT_SIZE_TWELVE = 12;
/**
* 大标题样式
*/
private CellStyle headerStyle;
/**
* 每列标题样式
*/
private CellStyle titleStyle;
/**
* 数据行样式
*/
private CellStyle styles;
public ExcelExportStylerUitl(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 color
* @return
*/
@Override
public CellStyle getHeaderStyle(short color) {
return headerStyle;
}
/**
* 每列标题样式
* @param color
* @return
*/
@Override
public CellStyle getTitleStyle(short color) {
return titleStyle;
}
/**
* 数据行样式
* @param parity 可以用来表示奇偶行
* @param entity 数据内容
* @return 样式
*/
@Override
public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
return styles;
}
/**
* 获取样式方法
* @param dataRow 数据行
* @param obj 对象
* @param data 数据
*/
@Override
public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
return getStyles(true, entity);
}
/**
* 模板使用的样式设置
*/
@Override
public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
return null;
}
/**
* 初始化--大标题样式
* @param workbook
* @return
*/
private CellStyle initHeaderStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, (short) 14, true));
return style;
}
/**
* 初始化--每列标题样式
* @param workbook
* @return
*/
private CellStyle initTitleStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
//背景色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
/**
* 初始化--数据行样式
* @param workbook
* @return
*/
private CellStyle initStyles(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
style.setDataFormat(STRING_FORMAT);
return style;
}
/**
* 基础样式
* @return
*/
private CellStyle getBaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(true);
return style;
}
/**
* 字体样式
* @param size 字体大小
* @param isBold 是否加粗
* @return
*/
private Font getFont(Workbook workbook, short size, boolean isBold) {
Font font = workbook.createFont();
//字体样式
font.setFontName("宋体");
//是否加粗
font.setBold(isBold);
//字体大小
font.setFontHeightInPoints(size);
return font;
}
}
2. 导出工具类
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
/**
* @author qianqian
* @date 2022年07月06日 14:09
*/
public class EasyPoiUtil {
/**
* @param list 导出数据集合
* @param title 表头
* @param sheetName 工作表名
* @param pojoClass 导出实体类Class
* @param fileName 导出Excel名称
* @param isCreateHeader
* @param response
* @throw Exception
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throw Exception{
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeader(isCreateHeader);
exportParams.setStyle(ExcelExportStylerUitl.class);
defaultExport(list, pojoClass, fileName, response, exportParams, false);
}
/**
* 原始导出
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setStyle(ExcelExportStylerUitl.class);
defaultExport(list, pojoClass, fileName, response, exportParams, false);
}
/**
* 一对多,自定义合并单元格行高
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response,boolean isOneToMany){
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setStyle(ExcelExportStylerUitl.class);
defaultExport(list, pojoClass, fileName, response, exportParams, isOneToMany);
}
/**
*Map导出
*/
public static void exportExcel(List<Map<String,Object>> list, String fileName, HttpServletResponse response) throw Exception{
defaultExport(list, fileName, response);
}
public static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams, boolean isOneToMany){
Workbook workbook = null;
try{
workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
}catch(Exception e){
e.printStackTrace();
}
if (workbook != null);
//判断是否设置行高
if (isOneToMany){
setRowHeight(row);
}
try{
downLoadExcel(fileName, response, workbook);
}catch(Exception e){
e.printStackTrace();
}
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws Exception{
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
throw new Exception(e.getMessage());
}
}
public static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null) {
downLoadExcel(fileName, response, workbook);
}
}
/**
* 一对多,设置行高
*/
private static void setRowHeight(Workbook workbook){
Sheet sheet = workbook.getSheetAt(0);
//设置第6列的列宽为60(下标从0开始),TestExportObjVo 不知道为什么设置了列宽但是不起作用,只能在这里单独设置
sheet.setColumnWidth(6,80*256);
for(int i = 0; i <= sheet.getLastRowNum(); i ++) {
Row row = sheet.getRow(i);
/* if (i==0){
//设置第一行的行高(表格标题)
row.setHeightInPoints(45);
} */
if (i == 1){
//设置第二行的行高(表格表头)
row.setHeightInPoints(35);
}else {
//设置其他行的行高根据内容自适应
setRowHeight(row);
}
}
}
private static void setRowHeight(Row row){
//根据内容长度设置行高
int enterCnt = 0;
for(int j = 0; j < row.getPhysicalNumberOfCells(); j ++) {
int rwsTemp = row.getCell(j).toString().length();
//这里取每一行中的每一列字符长度最大的那一列的字符
if (rwsTemp > enterCnt) {
enterCnt = rwsTemp;
}
}
//设置默认行高为35
row.setHeightInPoints(35);
//如果字符长度大于35,判断大了多少倍,根据倍数来设置相应的行高
if (enterCnt>35){
float d = enterCnt/35;
float f = 35*d;
/*if (d>2 && d<4){
f = 35*2;
}else if(d>=4 && d<6){
f = 35*3;
}else if (d>=6 && d<8){
f = 35*4;
}*/
row.setHeightInPoints(f);
}
}
}
四.应用
@GetMapping("/")
public void exportExcel(String param, HttpServletResponse response){
String fileName = "股票风控日报.xls";
Lsit<TestExportVo> exportData = HoldDailyService.queryList(param);
EasyPoiUtil.exportExcel(exportData,null,null,TestExportVo.class,fileName,response,true);
}
注意:
service层装配的数据层级为下图:
holdData每个对象中所需合并的字段值都必须存在,只有内容相同时,才会进行合并单元格