吴中网站开发建设多少钱,南京网站seo,梁山做网站,自助分红网站建设EASYEXCEL导出表格#xff08;有标题、单元格合并#xff09;
xlsx格式报表的导出#xff0c;导出的数据存在父子关系#xff0c;即相当于树形数据#xff0c;有单元格合并和标题形式的要求#xff0c;查阅了一些资料#xff0c;总算是弄出来了#xff0c;这里另写一个…EASYEXCEL导出表格有标题、单元格合并
xlsx格式报表的导出导出的数据存在父子关系即相当于树形数据有单元格合并和标题形式的要求查阅了一些资料总算是弄出来了这里另写一个小样简单分享一下关于easyExcel导出具有合并单元格和标题的小结 代码也算记录一下自己的工作学习。
public class BizMergeStrategy extends AbstractMergeStrategy {private MapString, ListRowRangeDto strategyMap;private Sheet sheet;public BizMergeStrategy(MapString, ListRowRangeDto 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.EntryString, ListRowRangeDto 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 MapString, ListRowRangeDto addAnnualMerStrategy(ListContrastIndicatorDeptExcel projectDtoList) {MapString, ListRowRangeDto strategyMap new HashMap();ContrastIndicatorDeptExcel preUser null;for (int i 0; i projectDtoList.size(); i) {ContrastIndicatorDeptExcel curUser projectDtoList.get(i);//如果名字一样将名字合并真正开发中一般不会通过名字这样字段而是通过一些关联的唯一值比如父idif (preUser ! null) {if (curUser.getIndicatorName() preUser.getIndicatorName()){ // 名字相同则合并第一列
// BizMergeStrategy.fillStrategyMap(strategyMap, 0, i1);//如果没有标题只有表头的话这里为 BizMergeStrategy.fillStrategyMap(strategyMap, 1, i);BizMergeStrategy.fillStrategyMap(strategyMap, 1, i1);}}preUser curUser;}return strategyMap;}/*** description: 新增或修改合并策略map* param strategyMap* param key* param index* return*/private static void fillStrategyMap(MapString, ListRowRangeDto strategyMap, String key, int index){ListRowRangeDto 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 {MapString, ListRowRangeDto 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 ListContrastIndicatorDeptExcel data(){ListContrastIndicatorDeptExcel 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));}
}
