1. 工具类
package com.perye.dokit.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayOutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;public class OfficeUtil {/*** @param dataList    数据* @param headNameMap 标题* @param type        类型 1 xls 2 xlsx* @param mergeIndex  需要合并的列 从1开始  0是序号* @param benchmarkColumn 基准列（就是以那一列为标准来决定合不合并 以下面的例子为说明 我输入1 就是以订单号为准 订单号合并才决定合并）* @return* @throws Exception*/public static byte[] toExcelMergeCell(List<?> dataList, Map<String, String> headNameMap, int type, int[] mergeIndex, Integer benchmarkColumn) throws Exception {Workbook workbook;if (type == 1) {workbook = new XSSFWorkbook();} else if (type == 2) {workbook = new SXSSFWorkbook();} else {workbook = new HSSFWorkbook();}List<Method> methodList = null;Sheet sheet = workbook.createSheet("数据列表");sheet.setColumnWidth(1, 6000);sheet.setColumnWidth(4, 8000);sheet.setColumnWidth(5, 10000);sheet.setColumnWidth(7, 5000);sheet.setColumnWidth(10, 4000);sheet.setColumnWidth(12, 4000);sheet.setColumnWidth(13, 6000);sheet.setColumnWidth(15, 6000);sheet.setColumnWidth(17, 6000);sheet.setColumnWidth(18, 6000);sheet.setColumnWidth(19, 6000);sheet.setColumnWidth(20, 6000);sheet.setColumnWidth(21, 6000);int index = sheet.getPhysicalNumberOfRows();for (int i = 0; i < dataList.size(); i++) {Object object = dataList.get(i);if (methodList == null) {Method[] methods = object.getClass().getMethods();methodList = new ArrayList<>();Row rowHead = sheet.createRow(index);rowHead.createCell(0).setCellValue("序号");Iterator<Entry<String, String>> iterator = headNameMap.entrySet().iterator();int c = 1;while (iterator.hasNext()) {Entry<String, String> entry = iterator.next();for (int m = 0; m < methods.length; m++) {if (methods[m].getName().toLowerCase().equals(("get" + entry.getKey()).toLowerCase())) {methodList.add(methods[m]);Cell cell = rowHead.createCell(c);setCellValue(cell, entry.getValue());c++;}if (methods[m].getName().toLowerCase().equals(("getlist"))) {Object invoke = methods[m].invoke(object);}}}}Row row = sheet.createRow(index + 1);row.createCell(0).setCellValue(i + 1);for (int m = 0; m < methodList.size(); m++) {Object value = methodList.get(m).invoke(object);Cell cell = row.createCell(m + 1);Object textValue = getValue(value);setCellValue(cell, textValue);}index++;}String str = null;int strBeginIndex;int strEndIndex;int j;int start;int end = 0;Map<Integer, Integer> benchmarkMap = new LinkedHashMap<>();for (int i = 0; i < mergeIndex.length; i++) {j = 0;start = 0;strBeginIndex = 0;strEndIndex = 0;if (mergeIndex[i] >= 110 && mergeIndex[i] <= 120) {for (Integer endIndex : benchmarkMap.keySet()) {CellRangeAddress region = new CellRangeAddress(benchmarkMap.get(endIndex), endIndex, mergeIndex[i], mergeIndex[i]);sheet.addMergedRegion(region);}}for (Row row : sheet) {if (j == 0) {j++;continue;}if (Objects.isNull(str)) {if (Objects.nonNull(row.getCell(mergeIndex[i]))) {str = row.getCell(mergeIndex[i]).getStringCellValue();}else {continue;}if (str.equals(sheet.getRow(2).getCell(1).getStringCellValue())) {strBeginIndex = row.getRowNum();}} else if (str.equals(row.getCell(mergeIndex[i]).getStringCellValue())) {if (strBeginIndex == 0) {strBeginIndex = sheet.getRow(j - 1).getRowNum();}strEndIndex = sheet.getLastRowNum();end = strEndIndex;if (sheet.getLastRowNum() == j) {//末尾合并if (mergeIndex[i] == benchmarkColumn) {CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);sheet.addMergedRegion(region);} else if (!benchmarkMap.isEmpty() && null != benchmarkColumn && mergeIndex[i] > benchmarkColumn) {consolidatedColumn(benchmarkMap, strBeginIndex, strEndIndex, mergeIndex[i], end, sheet);}if (mergeIndex[i] == 1) {benchmarkMap.put(strEndIndex, strBeginIndex);}}} else if (!str.equals(row.getCell(mergeIndex[i]).getStringCellValue())) {strEndIndex = row.getRowNum();if (start == 0 && strBeginIndex > 0 && strEndIndex > 0) {//首行合并strEndIndex = strEndIndex - 1;end = strEndIndex;if (mergeIndex[i] == benchmarkColumn) {CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);sheet.addMergedRegion(region);} else if (!benchmarkMap.isEmpty() && null != benchmarkColumn && mergeIndex[i] > benchmarkColumn) {consolidatedColumn(benchmarkMap, strBeginIndex, strEndIndex, mergeIndex[i], end, sheet);}if (mergeIndex[i] == 1) {benchmarkMap.put(strEndIndex, strBeginIndex);}strBeginIndex = 0;start = 1;} else if (strBeginIndex > 0 && strEndIndex > 0) {//中间行合并strEndIndex = strEndIndex - 1;end = strEndIndex;if (mergeIndex[i] == benchmarkColumn) {CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex, mergeIndex[i], mergeIndex[i]);sheet.addMergedRegion(region);} else if (!benchmarkMap.isEmpty() && null != benchmarkColumn && mergeIndex[i] > benchmarkColumn) {consolidatedColumn(benchmarkMap, strBeginIndex, strEndIndex, mergeIndex[i], end, sheet);}if (mergeIndex[i] == 1) {benchmarkMap.put(strEndIndex, strBeginIndex);}strBeginIndex = 0;}str = row.getCell(mergeIndex[i]).getStringCellValue();}j++;}}ByteArrayOutputStream baos = new ByteArrayOutputStream();workbook.write(baos);workbook.close();return baos.toByteArray();}private static void consolidatedColumn(Map<Integer,Integer> benchmarkMap,Integer strBeginIndex,Integer strEndIndex,Integer mergeIndex,Integer end,Sheet sheet){for (Integer endIndex : benchmarkMap.keySet()) {if (strBeginIndex>=benchmarkMap.get(endIndex)&&strEndIndex>=endIndex&&strBeginIndex<endIndex){strEndIndex=endIndex;if (strBeginIndex<strEndIndex){CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex,  mergeIndex,  mergeIndex);sheet.addMergedRegion(region);}strBeginIndex=strEndIndex+1;strEndIndex=end;}else if (strBeginIndex>=benchmarkMap.get(endIndex)&&strEndIndex<=endIndex&&strBeginIndex<endIndex){if (strBeginIndex<strEndIndex){CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex,  mergeIndex,  mergeIndex);sheet.addMergedRegion(region);}strBeginIndex=strEndIndex+1;strEndIndex=end;}else if (strBeginIndex<benchmarkMap.get(endIndex)&&strEndIndex>=endIndex&&strBeginIndex<endIndex){strBeginIndex=benchmarkMap.get(endIndex);strEndIndex=endIndex;if (strBeginIndex<strEndIndex){CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex,  mergeIndex,  mergeIndex);sheet.addMergedRegion(region);}strBeginIndex=strEndIndex+1;strEndIndex=end;}else if (strBeginIndex<=benchmarkMap.get(endIndex)&&strEndIndex<=endIndex&&strBeginIndex<endIndex){if (!isSection(benchmarkMap,strBeginIndex)){strBeginIndex=benchmarkMap.get(endIndex);if (strBeginIndex<strEndIndex){CellRangeAddress region = new CellRangeAddress(strBeginIndex, strEndIndex,  mergeIndex,  mergeIndex);sheet.addMergedRegion(region);}strBeginIndex=strEndIndex+1;strEndIndex=end;}}}}private static Object getValue(Object value) {Object textValue = "";if (value != null) {if (value instanceof Boolean) {textValue = (Boolean) value ? "是" : "否";} else if (value instanceof Date) {
//                textValue = DateUtil.format((Date) value, "yyyy-MM-dd HH:mm:ss");java.text.SimpleDateFormat formatter = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");textValue = formatter.format(new Date());//格式化数据} else if (value instanceof String) {String val = (String) value;textValue = Objects.isNull(val) || "null".equalsIgnoreCase(val) ? "" : val;} else {textValue = value;}}return textValue;}private static boolean isSection(Map<Integer,Integer> benchmarkMap,Integer value){for (Integer integer : benchmarkMap.keySet()) {if (value>=benchmarkMap.get(integer)&&value<=integer){return true;}}return false;}private static void setCellValue(Cell cell, Object value) {if (value != null) {if (value instanceof Integer) {cell.setCellValue((Integer) value);} else if (value instanceof Boolean) {Boolean booleanValue = (Boolean) value;cell.setCellValue(booleanValue);} else if (value instanceof Date) {Date dateValue = (Date) value;cell.setCellValue(dateValue);} else if (value instanceof Float) {Float floatValue = (Float) value;cell.setCellValue(floatValue);} else if (value instanceof Double) {Double doubleValue = (Double) value;cell.setCellValue(doubleValue);} else if (value instanceof Long) {Long longValue = (Long) value;cell.setCellValue(longValue);}else {cell.setCellValue(value.toString());}}}}
2.调用工具类byte[] excelBytes =OfficeUtil.toExcelMergeCell(pdlist,headNameMap,2,new int[]{1,2,3,4,5,6,7,8,9,10,11,17,18,19,20,21},1);response.setContentType("application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(IdUtil.fastSimpleUUID()+".xls", "utf-8"));response.getOutputStream().write(excelBytes);response.getOutputStream().flush();response.getOutputStream().close();
3. 调用说明pdlist 集合中一定要放入实体类，导不出数据。列大小在工具类中自行调整