江西哪家网站建设公司好,山西推广型网站开发,php网站打开速度慢,互动创意网站零、希望Springboot-java导出excel文件#xff0c;包括动态表头与下边合并的列
使用 org.apache.poi 与自己封装工具类实现相关功能。代码如下 一、代码
1、依赖 implementation(group: org.apache.poi,name: poi-ooxml,version: 4.1.0)implementation(group: org.apache.po…零、希望Springboot-java导出excel文件包括动态表头与下边合并的列
使用 org.apache.poi 与自己封装工具类实现相关功能。代码如下 一、代码
1、依赖 implementation(group: org.apache.poi,name: poi-ooxml,version: 4.1.0)implementation(group: org.apache.poi,name: poi,version: 4.1.0)implementation(group: cn.hutool, name: hutool-all, version: 5.8.3)2、工具类 ExcelMergeUtil.java import cn.hutool.json.JSONUtil;
import com.longze.fengqx.HeaderNode;
import com.longze.fengqx.PoiModel;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.IntStream;/*** author Fengqx* version 1.0* description: excel文件合并* date 2023/8/20 13:13*/
public class ExcelMergeUtil {public static SXSSFSheet createExcelHead(SXSSFWorkbook book, String sheetName, String headJson){ListHeaderNode headerNodes JSONUtil.toList(headJson, HeaderNode.class);SXSSFSheet sxssfSheet book.createSheet(sheetName);CellStyle headStyle book.createCellStyle();defaultHeadStyle(headStyle);//表头层级int deep headerNodes.stream().map(HeaderNode::getRow).reduce(Integer::max).orElse(1);for (int i 0; i deep; i) {sxssfSheet.createRow(i);}//创建单元格for (HeaderNode headerNode : headerNodes) {int row headerNode.getRow();int col headerNode.getColumn();SXSSFCell sxssfCell sxssfSheet.getRow(row).createCell(col);sxssfSheet.setColumnWidth(col, headerNode.getWidth() * 256);sxssfCell.setCellStyle(headStyle);sxssfCell.setCellValue(headerNode.getHeaderName());CellRangeAddress region;//是否跨列if (headerNode.isOverNode()) {region new CellRangeAddress(row, deep, col, col);} else {region new CellRangeAddress(row, row, col, (col headerNode.getOverNodeCount() - 1));}if (region.getNumberOfCells() 1) {sxssfSheet.addMergedRegionUnsafe(region);//合并后设置下边框RegionUtil.setBorderTop(BorderStyle.THIN, region, sxssfSheet);RegionUtil.setBorderLeft(BorderStyle.THIN, region, sxssfSheet);RegionUtil.setBorderBottom(BorderStyle.THIN, region, sxssfSheet);RegionUtil.setBorderRight(BorderStyle.THIN, region, sxssfSheet);}}return sxssfSheet;}public static void mergeCellFunc(Sheet sheet, String[] title, String[] field, ListMapString, String list, Integer deep,ListInteger mergeIndex){MapString, ListMapString, String map Maps.newHashMap();map.put(测试合并数据, list);// 模拟大数据量情况下任务中心可分页查询接口分批返回数据ListListMapString, String listList excelPageByNum(list, 5);// 数据总数 表头int size listList.stream().mapToInt(List::size).sum() deep;ListPoiModel poiModels Lists.newArrayList();for (ListMapString, String listmid : listList) {for (MapString, String mapMid : listmid) {int index sheet.getLastRowNum()1;Row row sheet.createRow(index);for (int i 0; i title.length; i) {String titleField field[i];String old null;if (index deep1) {old poiModels.get(i) null ? null : poiModels.get(i).getContent();}for (int k : mergeIndex) {if (index deep1) {PoiModel poiModel new PoiModel(mapMid.get(titleField),mapMid.get(titleField),null,deep1,i);poiModels.add(poiModel);break;}PoiModel poiModel poiModels.get(i);String content mapMid.get(titleField);// 当前行的当前列与上一行的当前列的内容不一致时则把当前行以上的合并if (i 0 k i) {// 如果不需要考虑当前行与上一行内容相同但是它们的前一列内容不一样则不合并的情况把或条件删除if (!StringUtils.equalsIgnoreCase(content, poiModel.getContent())
// || (StringUtils.equalsIgnoreCase(content, poiModel.getContent()) !StringUtils.equalsIgnoreCase(poiModels.get(i - 1).getOldContent(),mapMid.get(field[i - 1])))) {get(poiModel, content, index, i, sheet);}}// 处理第一列的情况if (k i i 0 !StringUtils.equalsIgnoreCase(content,poiModel.getContent())) {get(poiModel, content, index, i, sheet);}// 最后一行没有后续的行与之比较所有当到最后一行时则直接合并对应列的相同内容if (k i index size poiModels.get(i).getRowIndex() ! index) {CellRangeAddress cra new CellRangeAddress(poiModels.get(i).getRowIndex(), index, poiModels.get(i).getCellIndex(), poiModels.get(i).getCellIndex());sheet.addMergedRegion(cra);}}Cell cell row.createCell(i);cell.setCellValue(mapMid.get(titleField));poiModels.get(i).setOldContent(old);}}}}/*** 表头样式** param headStyle*/private static void defaultHeadStyle(CellStyle headStyle) {headStyle.setBorderTop(BorderStyle.THIN);headStyle.setBorderLeft(BorderStyle.THIN);headStyle.setBorderBottom(BorderStyle.THIN);headStyle.setBorderRight(BorderStyle.THIN);headStyle.setAlignment(HorizontalAlignment.CENTER);headStyle.setVerticalAlignment(VerticalAlignment.CENTER);headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);headStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());}//合并单元格private static void get(PoiModel poiModel, String content, int index, int i, Sheet sheet) {if (poiModel.getRowIndex() ! index - 1) {CellRangeAddress cra new CellRangeAddress(poiModel.getRowIndex(), index - 1, poiModel.getCellIndex(), poiModel.getCellIndex());//在sheet里增加合并单元格sheet.addMergedRegion(cra);}/*重新记录该列的内容为当前内容行标记改为当前行标记列标记则为当前列*/poiModel.setContent(content);poiModel.setRowIndex(index);poiModel.setCellIndex(i);}public static T ListListT excelPageByNum(ListT list, int pageSize) {return IntStream.range(0, list.size()).boxed().filter(t - t % pageSize 0).map(t - list.stream().skip(t).limit(pageSize).collect(Collectors.toList())).collect(Collectors.toList());}
}3、实体对象
HeaderNode.java 和 PoiModel.java
public class PoiModel {private String content;private String oldContent;private String primaryKey;private int rowIndex;private int cellIndex;public PoiModel() {}public PoiModel(String content, String oldContent, String primaryKey, int rowIndex, int cellIndex) {this.content content;this.oldContent oldContent;this.primaryKey primaryKey;this.rowIndex rowIndex;this.cellIndex cellIndex;}public String getContent() {return content;}public void setContent(String content) {this.content content;}public String getOldContent() {return oldContent;}public void setOldContent(String oldContent) {this.oldContent oldContent;}public String getPrimaryKey() {return primaryKey;}public void setPrimaryKey(String primaryKey) {this.primaryKey primaryKey;}public int getRowIndex() {return rowIndex;}public void setRowIndex(int rowIndex) {this.rowIndex rowIndex;}public int getCellIndex() {return cellIndex;}public void setCellIndex(int cellIndex) {this.cellIndex cellIndex;}
}public class HeaderNode {/*** 标题头*/private String headerName;/*** 层级*/private int row;/*** 非叶子节点列跨度*/private int overNodeCount;/*** 当前列没有子节点*/private boolean overNode true;/*** 列*/private int column;/*** 宽度*/private int width 13;public String getHeaderName() {return headerName;}public void setHeaderName(String headerName) {this.headerName headerName;}public int getRow() {return row;}public void setRow(int row) {this.row row;}public int getOverNodeCount() {return overNodeCount;}public void setOverNodeCount(int overNodeCount) {this.overNodeCount overNodeCount;}public boolean isOverNode() {return overNode;}public void setOverNode(boolean overNode) {this.overNode overNode;}public int getColumn() {return column;}public void setColumn(int column) {this.column column;}public int getWidth() {return width;}public void setWidth(int width) {this.width width;}
}4、下载Controller GetMapping(value /downExcel)ResponseBodypublic void downExcel(HttpServletResponse response,RequestParam(required true) String type) throws Exception {try {tengxunService.downExcel(response, type);} catch (Exception ex) {throw ex;}}
5、下载service Overridepublic void downloadExcel(HttpServletResponse response, String type)throws Exception {response.setContentType(application/vnd.ms-excel);response.setCharacterEncoding(utf-8);// 这里URLEncoder.encode可以防止中文乱码String fileName URLEncoder.encode(腾讯充值文件, UTF-8);response.setHeader(Content-disposition, attachment;filename fileName .xlsx);OutputStream os response.getOutputStream();
//加工数据ListMapString, String list Lists.newArrayList();for(int i0;ichongzhiList.size();i){Chongzhi dtonew Chongzhi ();list.add(JSONObject.parseObject(JSON.toJSONString(dto),Map.class));}String weekStart 08.01;String weekEnd 08.07;String nextWeekStart 08.08;String nextWeekEnd 08.15;//合并单元格方法try {String customizeLabel [{\headerName\:\区域\,\column\:0,\row\:0}, {\headerName\:\用户姓名\,\column\:1,\row\:0}, {\headerName\:\本周weekStart-weekEnd充值记录\,\column\:2,\row\:0}, {\headerName\:\本周weekStart-weekEnd充值详情\,\column\:3,\row\:0,\overNodeCount\:4,\overNode\:false},{\headerName\:\充值时间\,\column\:3,\row\:1}, {\headerName\:\充值项目\,\column\:4,\row\:\1\}, {\headerName\:\充值方式\,\column\:5,\row\:1}, {\headerName\:\充值金额\,\column\:6,\row\:1}, {\headerName\:\下周nextWeekStart-nextWeekEnd充值计划\,\column\:7,\row\:0}];//1、生bookSXSSFWorkbook book new SXSSFWorkbook();//2、生成动态标题SXSSFSheet sxssfSheet ExcelMerge.createExcelHead(book,Sheet1, customizeLabel);//3、取数据对应字段值 汇总String[] title {区域, 用户姓名, 本周weekStart-weekEnd充值记录, 充值时间, 充值项目, 充值方式, 充值金额, 下周nextWeekStart-nextWeekEnd充值计划};//4、取数据对应属性的字段值 汇总String[] field {areaName, name, chongzjilu, chongzTime, chongzProject, chongzMethod, chongzMoney, nextChongz};//5、需要合并的列ListInteger mergeIndex Arrays.asList(0,1,7);//6、合并ExcelMerge.mergeCellFunc(sxssfSheet,title,field, list, sxssfSheet.getLastRowNum(),mergeIndex);//创建excel文件 下载book.write(os);} catch (IOException e){logger.error(文件导出失败,错误信息{},e);// 重置responseresponse.reset();response.setContentType(application/json);response.setCharacterEncoding(utf-8);MapString, String map new HashMap();map.put(statusCode, 500);map.put(message, 下载文件失败 e.getMessage());response.getWriter().println(JSON.toJSONString(map));}finally {try {os.close();} catch (IOException e) {e.printStackTrace();}}}
三、下载
完事通过controller调用下载接口直接可以下载出文件
可以任意改表头与选择是否要合并的字段当做参数传入将需要合并的列顺序传入即可完成合并一步到位十分方便 //5、需要合并的列
ListInteger mergeIndex Arrays.asList(0,1,7); ExcelMerge.mergeCellFunc(sxssfSheet,title,field, list, sxssfSheet.getLastRowNum(),mergeIndex); 截图如下 荆轲刺秦王
