效果图: package com.ai.dsg.utils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.util.CellRangeAddress; import java.util.HashMap; import java.util.List; import java.util.Map; public class DynamicExport { /** * * @param sheetName sheet名称 * @param titles 表头数组 LinkedList(防止乱序) * @param keyList 表格数据填充时根据key获取数据 LinkedList(与表头对应) * @param list 表格填充需要的数据 * @param needMergeColIndexList 需要进行合并的列索引 * @return */ public static HSSFWorkbook getDynamicExport(String sheetName,List<String> titles,List<String> keyList, List<Map<String, Object>> list,List<Integer> needMergeColIndexList) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(sheetName); HSSFCellStyle titelStyle = createTitleCellStyle(workbook); HSSFCellStyle dataStyle = createDataCellStyle(workbook); //设置表头 HSSFRow row = sheet.createRow(0); for (int i = 0; i < titles.size(); i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(titles.get(i)); cell.setCellStyle(titelStyle); } Map<String,Object> preMap = null; Map<String,Integer> fisrtRowMap = new HashMap<>(titles.size()); Map<String,Integer> lastRowMap = new HashMap<>(titles.size()); for(int i=0;i<list.size();i++){ if(i==0){ preMap = list.get(0); } String preValue = null; int firstCol = 0; int lastCol = 0; Map<String,Object> map = list.get(i); HSSFRow dataRow = sheet.createRow(i + 1); for(int j=0;j<keyList.size();j++){ HSSFCell cell = dataRow.createCell(j); cell.setCellStyle(dataStyle); sheet.autoSizeColumn(j); Object valueObj = map.get(keyList.get(j)); dataRow.createCell(j).setCellValue(null==valueObj?"":String.valueOf(valueObj)); if(needMergeColIndexList.contains(j)){ if(null!=preValue&&preValue.equals(String.valueOf(valueObj))){ lastCol = j; if(j==list.get(i).entrySet().size()-1){ //最后一列 mergedRegion(sheet,i+1,i+1,firstCol,lastCol); } }else{ if(lastCol>firstCol){ mergedRegion(sheet,i+1,i+1,firstCol,lastCol); } preValue = String.valueOf(valueObj); firstCol = j; lastCol = j; } if(i==0){ fisrtRowMap.put(keyList.get(j),i+1); lastRowMap.put(keyList.get(j),i+1); }else{ if(null!=valueObj&&String.valueOf(preMap.get(keyList.get(j))).equals(String.valueOf(valueObj))){ lastRowMap.put(keyList.get(j),i+1); if(i==list.size()-1){ //最后一行 mergedRegion(sheet,fisrtRowMap.get(keyList.get(j)),lastRowMap.get(keyList.get(j)),j,j); } }else{ if(null!=lastRowMap.get(keyList.get(j))&&lastRowMap.get(keyList.get(j))>fisrtRowMap.get(keyList.get(j))){ mergedRegion(sheet,fisrtRowMap.get(keyList.get(j)),lastRowMap.get(keyList.get(j)),j,j); } fisrtRowMap.put(keyList.get(j),i+1); lastRowMap.put(keyList.get(j),i+1); preMap.put(keyList.get(j), String.valueOf(valueObj)); } } } } } return workbook; } /** * 设置合并单元格 * @param sheet * @param firstRow 开始行 * @param lastRow 结束行 * @param firstCol 开始列 * @param lastCol 结束列 */ private static void mergedRegion(HSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) { boolean isMergedRegion = true; List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions(); for(CellRangeAddress mergedRegion : cellRangeAddressList){ int fstRow = mergedRegion.getFirstRow(); int lstRow = mergedRegion.getLastRow(); int fstColumn = mergedRegion.getFirstColumn(); int lstColumn = mergedRegion.getLastColumn(); if(fstRow==firstRow&&fstColumn==firstCol){ //合并区域存在重叠 lstRow = lstRow>lastRow?lstRow:lastRow; lstColumn = lstColumn>lastCol?lstColumn:lastCol; mergedRegion.setLastRow(lstRow); mergedRegion.setLastColumn(lstColumn); isMergedRegion = false; break; } if(lstRow==lastRow&&lstColumn==lastCol){ //合并区域存在重叠 lstRow = lstRow>lastRow?lstRow:lastRow; lstColumn = lstColumn>lastCol?lstColumn:lastCol; mergedRegion.setLastRow(lstRow); mergedRegion.setLastColumn(lstColumn); isMergedRegion = false; break; } } if(isMergedRegion){ sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol)); } } private static HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook) { HSSFCellStyle cellStyle = workbook.createCellStyle(); //设置水平居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); //cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); //cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //HSSFFont font = workbook.createFont(); //font.setColor(HSSFColor.WHITE.index); //cellStyle.setFont(font); return cellStyle; } private static HSSFCellStyle createDataCellStyle(HSSFWorkbook workbook) { HSSFCellStyle cellStyle = workbook.createCellStyle(); //设置垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return cellStyle; } }