EASYEXCEL导出表格（有标题、单元格合并）

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;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {this.sheet = sheet;//如果没有标题，只有表头的话，这里的 cell.getRowIndex() == 1if (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索引是上一行索引的，如果有，则索引+1if (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;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic 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));}
}