easyExcel导出自定义表头以及自定义合并单元格
easyExcel导出自定义表头
1、自定义拦截器，写入头部样式
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, 30));
} else if (cell.getRowIndex() == 1) {
cell.setCellStyle(EasyExcelUtils.getColumnSecondLineStyle(workbook,10));
}
if(cell.getRowIndex() > 1){
cell.setCellStyle(EasyExcelUtils.getColumnStyle(workbook));
}
}
@Override
protected void setContentCellStyle(Cell cell, Head head, Integer integer) {
}
}
2、样式
public class EasyExcelUtils {
/**
* 首行单元格
* @param workbook
* @param fontSize
* @return
*/
public static CellStyle getColumnTopStyle(Workbook workbook, int fontSize) {
if (fontSize == 0) {
fontSize = 10;
}
// 设置字体
Font font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) fontSize);
//字体加粗
font.setBold(true);
//设置字体名字
font.setFontName("宋体");
//设置样式;
CellStyle style = workbook.createCellStyle();
//左右居中
style.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
return style;
}
/**
*
* @param workbook
* @param fontSize
* @return
*/
public static CellStyle getColumnSecondLineStyle(Workbook workbook, int fontSize) {
if (fontSize == 0) {
fontSize = 10;
}
Font font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) fontSize);
//设置字体名字
font.setFontName("Arial");
//设置样式;
CellStyle style = workbook.createCellStyle();
//左右居中
style.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
return style;
}
/*
* 字段样式
*/
public static CellStyle getColumnStyle(Workbook workbook) {
// 设置字体
Font font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 10);
//设置字体名字
font.setFontName("Arial");
//设置样式;
CellStyle style = workbook.createCellStyle();
//左右居中
style.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置边框
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(true);
return style;
}
}
3、合并单元格utils
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
private List<Integer> mergeColumnIndex;
private int mergeRowIndex;
public ExcelFillCellMergeStrategy() {
}
public List<Integer> getMergeColumnIndex() {
return mergeColumnIndex;
}
public void setMergeColumnIndex(List<Integer> mergeColumnIndex) {
this.mergeColumnIndex = mergeColumnIndex;
}
public int getMergeRowIndex() {
return mergeRowIndex;
}
public void setMergeRowIndex(int mergeRowIndex) {
this.mergeRowIndex = mergeRowIndex;
}
public ExcelFillCellMergeStrategy(int mergeRowIndex, List<Integer> 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 && mergeColumnIndex.contains(curColIndex) ) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
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);
}
}
}
}
4、使用
//获取数据源，数据源实体类不能包含List、Map等
List<FirProfessionalByDeptVo> list = (List<FirProfessionalByDeptVo>) stringListMap.get("resultList");
//需要合并的列
List<Integer> mergeColumnIndex = Arrays.asList(new Integer[] {0, 1, 2, 3});
//需要从第一行开始，列头第一行
int mergeRowIndex = 1;
Set<String> excludeColumnFiledNames = new HashSet<String>();
excludeColumnFiledNames.add("params");
excludeColumnFiledNames.add("ratingNum");
excludeColumnFiledNames.add("remark");
excludeColumnFiledNames.add("rating");
ExcelWriter excelWriter = null;
try{
WriteWorkbook writeWorkbook = new WriteWorkbook();
writeWorkbook.setOutputStream(outputStream);
writeWorkbook.setClazz(FirProfessionalByDeptVo.class);
writeWorkbook.setHead(head(ratingYear+"年"+material.getDeptName()+"评价总体情况分析表"));
//07的excel版本,节省内存
writeWorkbook.setExcelType(ExcelTypeEnum.XLS);
//忽略字段
writeWorkbook.setExcludeColumnFiledNames(excludeColumnFiledNames);
//是否自动关闭输入流
writeWorkbook.setAutoCloseStream(Boolean.TRUE);
//自定义拦截器，设置头部样式
List<WriteHandler> customWriteHandlerList = new ArrayList<>();
customWriteHandlerList.add(new CustomCellWriteHandler());
customWriteHandlerList.add(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumnIndex));
writeWorkbook.setCustomWriteHandlerList(customWriteHandlerList);
excelWriter = new ExcelWriter(writeWorkbook);
WriteSheet writeSheet = new WriteSheet();
writeSheet.setSheetName("XXXXXXX表");
excelWriter.write(list, writeSheet);
//一般到到这里就结束了
/**---------------------这里是我自己特殊处理，合并处理----------------------------- */
WriteContext writeContext = excelWriter.writeContext();
Sheet sheet = writeContext.getCurrentSheet();
Iterator<Row> rowIterator= sheet.rowIterator();
while(rowIterator.hasNext()){
Row row = rowIterator.next();
int rowNum = row.getRowNum();
if(rowNum > mergeRowIndex && list.size() > rowNum - mergeRowIndex -1){
FirProfessionalByDeptVo vo = list.get(rowNum - mergeRowIndex - 1 );
Cell cell = row.getCell(4);
cell.setCellValue(vo.getNowYearDeduction());
cell = row.getCell(5);
cell.setCellValue(vo.getLastYearDeduction());
cell = row.getCell(6);
cell.setCellValue(vo.getNowYearRemark());
cell = row.getCell(7);
cell.setCellValue(vo.getLastYearRemark());
/**----------------------------------------------------- */
}
}
}catch (Exception e){
logger.error("生成评分卡文件失败",e);
}finally {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
/**
* 自定义头
* @param headTitle 统一头
*
* @return 返回整个头list。 头部相同连续的单元格会自动合并。
*/
private static List<List<String>> head(String headTitle){
List<List<String>> list = new ArrayList<List<String>>();
List<String> head1 = new ArrayList<String>();
head1.add(headTitle);
head1.add("模块");
List<String> head2 = new ArrayList<String>();
head2.add(headTitle);
head2.add("一级指标");
List<String> head3 = new ArrayList<String>();
head3.add(headTitle);
head3.add("二级指标");
List<String> head4 = new ArrayList<String>();
head4.add(headTitle);
head4.add("评分内容");
List<String> head5 = new ArrayList<String>();
head5.add(headTitle);
head5.add("今年评分");
List<String> head6 = new ArrayList<String>();
head6.add(headTitle);
head6.add("去年评分");
List<String> head7 = new ArrayList<String>();
head7.add(headTitle);
head7.add("今年备注");
List<String> head8 = new ArrayList<String>();
head8.add(headTitle);
head8.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);
return list;
}
效果展示
