EASYEXCEL导出表格（有标题、单元格合并）
xlsx格式报表的导出，导出的数据存在父子关系，即相当于树形数据，有单元格合并和标题形式的要求，查阅了一些资料，总算是弄出来了，这里另写一个小样简单分享一下关于easyExcel导出具有合并单元格和标题的小结 代码，也算记录一下自己的工作学习。
public class BizMergeStrategy extends AbstractMergeStrategy {
private Map<String, List<RowRangeDto>> strategyMap;
private Sheet sheet;
public BizMergeStrategy(Map<String, List<RowRangeDto>> strategyMap) {
this.strategyMap = strategyMap;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
this.sheet = sheet;
//如果没有标题，只有表头的话，这里的 cell.getRowIndex() == 1
if (cell.getRowIndex() == 2 && cell.getColumnIndex() == 0) {
/**
* 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，
* 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3，
* 但此时A2,A3已经是合并的单元格了
*/
for (Map.Entry<String, List<RowRangeDto>> entry : strategyMap.entrySet()) {
Integer columnIndex = Integer.valueOf(entry.getKey());
entry.getValue().forEach(rowRange -> {
//添加一个合并请求
sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
rowRange.getEnd(), columnIndex, columnIndex));
});
}
}
}
public static Map<String, List<RowRangeDto>> addAnnualMerStrategy(List<ContrastIndicatorDeptExcel> projectDtoList) {
Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
ContrastIndicatorDeptExcel preUser = null;
for (int i = 0; i < projectDtoList.size(); i++) {
ContrastIndicatorDeptExcel curUser = projectDtoList.get(i);
//如果名字一样，将名字合并（真正开发中一般不会通过名字这样字段，而是通过一些关联的唯一值，比如父id）
if (preUser != null) {
if (curUser.getIndicatorName() == preUser.getIndicatorName()){
// 名字相同则合并第一列
// BizMergeStrategy.fillStrategyMap(strategyMap, "0", i+1);
//如果没有标题，只有表头的话，这里为 BizMergeStrategy.fillStrategyMap(strategyMap, "1", i);
BizMergeStrategy.fillStrategyMap(strategyMap, "1", i+1);
}
}
preUser = curUser;
}
return strategyMap;
}
/**
* @description: 新增或修改合并策略map
* @param strategyMap
* @param key
* @param index
* @return
*/
private static void fillStrategyMap(Map<String, List<RowRangeDto>> strategyMap, String key, int index){
List<RowRangeDto> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
boolean flag = false;
for (RowRangeDto dto : rowRangeDtoList) {
//分段list中是否有end索引是上一行索引的，如果有，则索引+1
if (dto.getEnd() == index) {
dto.setEnd(index + 1);
flag = true;
}
}
//如果没有，则新增分段
if (!flag) {
rowRangeDtoList.add(new RowRangeDto(index, index + 1));
}
strategyMap.put(key, rowRangeDtoList);
}
/**
* @description: 表格样式
* @return
*/
public static HorizontalCellStyleStrategy CellStyleStrategy(){
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置背景颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//设置头字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)13);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//设置头居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
}
import cn.exrick.xboot.jwaq.entity.contrast.ContrastIndicatorDeptExcel;
import cn.exrick.xboot.jwaq.tool.TitleSheetWriteHandler;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/easyExcelController/ContrastIndicatorDeptExcel")
public class EasyExcelController {
@GetMapping("/excel")
public void excel(HttpServletResponse response) throws IOException {
Map<String, List<RowRangeDto>> strategyMap = BizMergeStrategy.addAnnualMerStrategy(data());
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String filename = URLEncoder.encode("用户表测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");
EasyExcel.write(response.getOutputStream(), ContrastIndicatorDeptExcel.class)
.excelType(ExcelTypeEnum.XLSX).head(ContrastIndicatorDeptExcel.class)
.registerWriteHandler(new TitleSheetWriteHandler("我是一个小标题",13))
// 标题及样式，lastCol为标题第0列到底lastCol列的宽度
//设置默认样式及写入头信息开始的行数
.relativeHeadRowIndex(1)
.registerWriteHandler(new BizMergeStrategy(strategyMap))
// 注册合并策略
.registerWriteHandler(BizMergeStrategy.CellStyleStrategy())
// 设置样式
.sheet("测试")
.doWrite(data());
}catch (Exception e) {
e.printStackTrace();
response.reset();
response.setCharacterEncoding("utf-8");
response.setContentType("application/json");
response.getWriter().println("打印失败");
}
}
private List<ContrastIndicatorDeptExcel> data(){
List<ContrastIndicatorDeptExcel> list = new ArrayList<>();
ContrastIndicatorDeptExcel ontrastIndicatorDeptExcel = new ContrastIndicatorDeptExcel();
ontrastIndicatorDeptExcel.setUnit("1");
ontrastIndicatorDeptExcel.setIndicatorName("2");
ontrastIndicatorDeptExcel.setBigType("3");
ContrastIndicatorDeptExcel ontrastIndicatorDeptExcel1 = new ContrastIndicatorDeptExcel();
ontrastIndicatorDeptExcel1.setUnit("1");
ontrastIndicatorDeptExcel1.setIndicatorName("2");
ontrastIndicatorDeptExcel1.setBigType("3");
ContrastIndicatorDeptExcel ontrastIndicatorDeptExcel2 = new ContrastIndicatorDeptExcel();
ontrastIndicatorDeptExcel2.setUnit("1");
ontrastIndicatorDeptExcel2.setIndicatorName("2");
ontrastIndicatorDeptExcel2.setBigType("3");
ContrastIndicatorDeptExcel ontrastIndicatorDeptExcel3 = new ContrastIndicatorDeptExcel();
ontrastIndicatorDeptExcel3.setUnit("1");
ontrastIndicatorDeptExcel3.setIndicatorName("2");
ontrastIndicatorDeptExcel3.setBigType("3");
list.add(ontrastIndicatorDeptExcel);
list.add(ontrastIndicatorDeptExcel1);
list.add(ontrastIndicatorDeptExcel2);
list.add(ontrastIndicatorDeptExcel3);
return list;
}
}
public class RowRangeDto {
private int start;
private int end;
public RowRangeDto(int start,int end){
this.start = start;
this.end = end;
}
public int getStart() {
return start;
}
public void setStart(int start) {
this.start = start;
}
public int getEnd() {
return end;
}
public void setEnd(int end) {
this.end = end;
}
}
package cn.exrick.xboot.jwaq.tool;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class TitleSheetWriteHandler implements SheetWriteHandler {
private String title;
private int lastCol;
public TitleSheetWriteHandler(String title,int lastCol){
this.title = title;
this.lastCol = lastCol;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
//设置标题
Row row = sheet.createRow(0);
row.setHeight((short) 800);
Cell cell = row.createCell(0);
cell.setCellValue(title);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = workbook.createFont();
font.setBold(true);
font.setFontHeight((short) 400);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, lastCol));
}
}
