public enum CellDataTypeEnum {
STRING,
DIRECT_STRING,
NUMBER,
BOOLEAN,
EMPTY,
ERROR,
IMAGE;
......
}
EasyExcel默认支持的为我们常见的类型，但在实际项目中，可能会遇到一对一，一对多的关系，这时候EasyExcel就会报错
方法一：
因为一对一或者一对多就相当于一个list里面又包含另一个list集合，这时候我们可以将list集合进行解析，换个思路：
association、collection进行关联，这时候，我问了公司大佬，发现Mybatis可以继承，也就是extends进行关联现在具体来看代码实现：
我现在是涉及到是三个类的连表查询：
新建一个实体类来接收返回的数
public class FirRatingcardTemplateVo extends FirRatingCard{
private static final long serialVersionUID = 1L;
//------------------------第一个类------------------------------//
/** 评分模板ID */
@ExcelIgnore
private Long ratingTemplateId;
/** 模板ID */
@ExcelIgnore
private Long templateId;
......
//------------------------第二个类------------------------------//
/** 评分资料ID */
@ExcelIgnore
private Long rmId;
/** 任务ID */
@ExcelIgnore
private String taskId;
......
}
继承FirRatingCard是第三个类
XXXmapper.xml的配置，也是继承
<resultMap type="FirRatingcardTemplateVo" id="FirRatingMaterialResultVo" extends="FirRatingcardTemplateResultW1">
......
</resultMap>
<resultMap type="FirRatingcardTemplateVo" id="FirRatingcardTemplateResultW1" extends="FirRatingCardResult">
......
</resultMap>
<resultMap type="FirRatingCard" id="FirRatingCardResult">
......
</resultMap>
业务成实现类Service
public List<FirRatingcardTemplateVo> selectFirRatingMaterialByRmId(Long rmId);
用我们新建的实体类来接收
接口进行测试
@Controller
public class ExcelController {
@Autowired
private IFirRatingMaterialService iFirRatingMaterialService;
@RequestMapping(value = "/getExcel", method = RequestMethod.GET)
public void getEXcel( HttpServletResponse response) throws IOException {
//获取数据源
List<FirRatingcardTemplateVo> list = iFirRatingMaterialService.selectFirRatingMaterialByRmId(1L);
//设置输入流，设置响应域
response.setContentType("application/ms-excel");
response.setCharacterEncoding("utf-8");
//生成文件名
String fileName = URLEncoder.encode("xxx.xlsx","utf-8");
response.setHeader("Content-disposition","attachment;filename="+fileName);
//需要合并的列
int[] mergeColumeIndex = {0, 1, 2};
//需要从第一行开始，列头第一行
int mergeRowIndex = 1;
EasyExcel//将数据映射到DownloadDTO实体类并响应到浏览器
.write(new BufferedOutputStream(response.getOutputStream()), FirRatingcardTemplateVo.class)
//07的excel版本,节省内存
.excelType(ExcelTypeEnum.XLS)
//是否自动关闭输入流
.autoCloseStream(Boolean.TRUE)
// // 自定义列宽度，有数字会
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//设置excel保护密码
// .password("123456")
.sheet().doWrite(list);
}
方法二：
把嵌套的集合或者对象的属性提取出来，也就是把对象的属性和集合对象的属性放在一个实体类中进行接收，然后再进行返回
效果如下:
工具类ExcelFillCellMergeStrategy：可以直接使用，这是自定义的拦截器，使用在后面会提到
@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public ExcelFillCellMergeStrategy() {
}
public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的第一列的数据和上一行的第一列数据，通过第一行数据是否相同进行合并
// Cell preCell_now = cell.getSheet().getRow(curRowIndex ).getCell(curColIndex);
// Object curData = preCell_now.getCellTypeEnum() == CellType.STRING ? preCell_now.getStringCellValue() : preCell_now.getNumericCellValue();
// Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex - 1);
// Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
//
if (curData.equals(preData)) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
public class EasyExcelUtils {
/**
* 首行单元格
* @param workbook
* @param fontSize
* @return
*/
public static CellStyle getColumnTopStyle(Workbook workbook, int fontSize) {
if (fontSize == 0) {
fontSize = 12;
}
// 设置字体
Font font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) fontSize);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("宋体");
//设置样式;
CellStyle style = workbook.createCellStyle();
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
return style;
}
/**
*
* @param workbook
* @param indexedColors
* @return
*/
public static CellStyle getColumnTopStyle(Workbook workbook, IndexedColors indexedColors) {
// 设置字体
Font font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 14);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("宋体");
//设置样式;
CellStyle style = workbook.createCellStyle();
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
if(indexedColors != null){
style.setFillForegroundColor(indexedColors.getIndex());// 设置背景色
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
return style;
}
/*
* 字段样式
*/
public static CellStyle getColumnStyle(Workbook workbook) {
// 设置字体
Font font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 10);
//设置字体名字
font.setFontName("宋体");
//设置样式;
CellStyle style = workbook.createCellStyle();
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(true);
return style;
}
}
/**
* @Author: Xiaobear
* @Time: 2020/9/19 14:09
* @Description: 自定义拦截，写入头部样式
*/
public class CustomCellWriteHandler extends AbstractCellStyleStrategy implements CellWriteHandler {
Workbook workbook;
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
super.beforeCellCreate(writeSheetHolder, writeTableHolder, row, head, columnIndex, relativeRowIndex, isHead);
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
super.afterCellDispose(writeSheetHolder, writeTableHolder, cellDataList, cell, head, relativeRowIndex, isHead);
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
this.initCellStyle(writeSheetHolder.getSheet().getWorkbook());
this.setHeadCellStyle(cell,head,relativeRowIndex);
}
@Override
protected void initCellStyle(Workbook workbook) {
this.workbook = workbook;
}
@Override
protected void setHeadCellStyle(Cell cell, Head head, Integer integer) {
//第一行样式
if (cell.getRowIndex() == 0) {
cell.setCellStyle(EasyExcelUtils.getColumnTopStyle(workbook, 16));
} else if (cell.getRowIndex() == 1) { //第二行样式
cell.setCellStyle(EasyExcelUtils.getColumnTopStyle(workbook, 14));
}else if (cell.getRowIndex() == 2){ //第三行样式
cell.setCellStyle(EasyExcelUtils.getColumnTopStyle(workbook,14));
}
//大于第二行的
if(cell.getRowIndex() > 2){
cell.setCellStyle(EasyExcelUtils.getColumnStyle(workbook));
}
}
@Override
protected void setContentCellStyle(Cell cell, Head head, Integer integer) {
}
}
/**
* 自定义头
* @param headTitle 统一头
*
* @return 返回整个头list。 头部相同连续的单元格会自动合并。
*/
private static List<List<String>> head(String headTitle, String deptName,String deptType,String ratingYear,String ratingPhase,String location){
List<List<String>> list = new ArrayList<List<String>>();
List<String> head1 = new ArrayList<String>();
head1.add(headTitle);
head1.add("金融机构名称："+deptName);
head1.add("模块");
List<String> head2 = new ArrayList<String>();
head2.add(headTitle);
head2.add("");
head2.add("一级指标");
List<String> head3 = new ArrayList<String>();
head3.add(headTitle);
head3.add("机构类型："+deptType);
head3.add("二级指标");
List<String> head4 = new ArrayList<String>();
head4.add(headTitle);
head4.add("");
head4.add("对应资料调阅清单编号");
List<String> head5 = new ArrayList<String>();
head5.add(headTitle);
head5.add("评级年份："+ratingYear);
head5.add("评分内容ID");
List<String> head6 = new ArrayList<String>();
head6.add(headTitle);
head6.add("");
head6.add("评分内容");
List<String> head7 = new ArrayList<String>();
head7.add(headTitle);
head7.add("评级期数："+ratingPhase);
head7.add("评分方式");
List<String> head8 = new ArrayList<String>();
head8.add(headTitle);
head8.add("");
head8.add("问询");
List<String> head9 = new ArrayList<String>();
head9.add(headTitle);
head9.add("所在地："+location);
head9.add("现场核查");
List<String> head10 = new ArrayList<String>();
head10.add(headTitle);
head10.add("");
head10.add("打分");
List<String> head11 = new ArrayList<String>();
head11.add(headTitle);
head11.add("");
head11.add("打分指引依据");
List<String> head12 = new ArrayList<String>();
head12.add(headTitle);
head12.add("");
head12.add("资料调阅依据");
list.add(head1);
list.add(head2);
list.add(head3);
list.add(head4);
list.add(head5);
list.add(head6);
list.add(head7);
list.add(head8);
list.add(head9);
list.add(head10);
list.add(head11);
list.add(head12);
return list;
}
@Controller
public class ExcelController {
@Autowired
private IFirRatingMaterialService iFirRatingMaterialService;
@RequestMapping(value = "/getExcel", method = RequestMethod.GET)
public void getEXcel( HttpServletResponse response) throws IOException {
//获取数据源
List<FirRatingcardTemplateVo> list = iFirRatingMaterialService.selectFirRatingMaterialByRmId(1L);
for (FirRatingcardTemplateVo template : list) {
System.out.println(template);
}
//设置输入流，设置响应域
response.setContentType("application/ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(list.get(0).getRatingYear()+"年第"+list.get(0).getRatingPhase()+"期"+list.get(0).getDeptName()+".xlsx","utf-8");
response.setHeader("Content-disposition","attachment;filename="+fileName);
//需要合并的列
int[] mergeColumeIndex = {0, 1, 2, 3, };
//需要从第一行开始，列头第一行
int mergeRowIndex = 1;
EasyExcel//将数据映射到DownloadDTO实体类并响应到浏览器
.write(new BufferedOutputStream(response.getOutputStream()), FirRatingcardTemplateVo.class)
//07的excel版本,节省内存
.excelType(ExcelTypeEnum.XLS)
//头部信息
.head(head("评级打分",list.get(0).getDeptName(),list.get(0).getDeptType(),list.get(0).getRatingYear(),list.get(0).getRatingPhase(),list.get(0).getLocation()))
//是否自动关闭输入流
.autoCloseStream(Boolean.TRUE)
//自定义的表头拦截
.registerWriteHandler(new CustomCellWriteHandler())
//合并单元格拦截器
.registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
// // 自定义列宽度，有数字会
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
//设置excel保护密码
// .password("123456")
.sheet().doWrite(list);
}
具体实现+代码：关于解决EasyExcel不支持解析List以及实体类对象问题(具体实现+代码)
不懂得私聊：2861184805@qq.com