【Java】POI导出Excel 动态合并单元格

参考博客——博主：My_码农

有一个需求是动态合并列，参考文章做了下调整，代码如下。

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class Test {

		/**
	     * @param sheet           页码
	     * @param dataList        数据
	     * @param headNameMap     标题
	     * @param benchmarkColumn 基准列（就是以那一列为标准来决定合不合并 以下面的例子为说明 我输入1 就是以订单号为准 订单号合并才决定合并）
	     * @param mergeIndexArray 需要合并的列 从1开始  0是序号
	     * @return
	     * @throws Exception
	     */
	    public static void toExcelMergeCell(Sheet sheet
	            , List<?> dataList
	            , Map<String, String> headNameMap
	            , Integer benchmarkColumn
	            , int[] mergeIndexArray) throws Exception {
	        List<Method> methodList = null;
	        int rowIndex = sheet.getPhysicalNumberOfRows();
	        for (int dataIndex = 0; dataIndex < dataList.size(); dataIndex++) {
	            Object object = dataList.get(dataIndex);
	            if (methodList == null) {
	                methodList = new ArrayList<>();
	
	                Row rowHead = sheet.createRow(rowIndex);
	                rowHead.createCell(0).setCellValue("序号");
	
	                int cellIndex = 1;
	                Method[] methods = object.getClass().getMethods();
	                for (Map.Entry<String, String> entry : headNameMap.entrySet()) {
	                    for (Method method : methods) {
	                        if (method.getName().toLowerCase().equals(("get" + entry.getKey()).toLowerCase())) {
	                            methodList.add(method);
	                            Cell cell = rowHead.createCell(cellIndex);
	                            createCellSetValue(rowHead, cellIndex, entry.getValue());
	                            cellIndex++;
	                        }
	                    }
	                }
	            }
	
	            Row row = sheet.createRow(rowIndex + 1);
	            row.createCell(0).setCellValue(dataIndex + 1);
	            for (int methodIndex = 0; methodIndex < methodList.size(); methodIndex++) {
	                Object value = methodList.get(methodIndex).invoke(object);
	                Cell cell = row.createCell(methodIndex + 1);
	                createCellSetValue(row,methodIndex+1,value);
	            }
	            rowIndex++;
	        }
	
	        Map<Integer, Integer> benchmarkMap = new LinkedHashMap<>();
	        for (int columnIndex : mergeIndexArray) {
	            rowIndex = 0;
	            int mergeStart = 0;
	            int mergeEnd = 0;
	            String value = null;
	            for (Row row : sheet) {
	                if (rowIndex == 0) {
	                    rowIndex++;
	                    continue;
	                }
	                for (int cellIndex = 0; cellIndex < 20; ++cellIndex) {
	                    Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
	                    cell.setCellType(CellType.STRING);
	                }
	
	                if (StringUtils.isEmpty(value)) {
	                    mergeStart = row.getRowNum();
	                    if (!StringUtils.isEmpty(row.getCell(columnIndex).getStringCellValue())) {
	                        value = row.getCell(columnIndex).getStringCellValue();
	                    }
	                } else if (value.equals(row.getCell(columnIndex).getStringCellValue())) {
	                    mergeEnd = row.getRowNum();
	                    if (rowIndex == sheet.getLastRowNum()) {
	                        if (columnIndex == benchmarkColumn) {
	                            benchmarkMap.put(mergeEnd, mergeStart);
	                            CellRangeAddress region = new CellRangeAddress(mergeStart, mergeEnd, columnIndex, columnIndex);
	                            sheet.addMergedRegion(region);
	                        } else if (!benchmarkMap.isEmpty() && columnIndex > benchmarkColumn) {
	                            mergeColumn(benchmarkMap, mergeStart, mergeEnd, columnIndex, sheet);
	                        }
	                    }
	                } else if (!value.equals(row.getCell(columnIndex).getStringCellValue())) {
	                    mergeEnd = row.getRowNum() - 1;
	                    if (columnIndex == benchmarkColumn) {
	                        benchmarkMap.put(mergeEnd, mergeStart);
	                        CellRangeAddress region = new CellRangeAddress(mergeStart, mergeEnd, columnIndex, columnIndex);
	                        sheet.addMergedRegion(region);
	                    } else if (!benchmarkMap.isEmpty() && columnIndex > benchmarkColumn) {
	                        mergeColumn(benchmarkMap, mergeStart, mergeEnd, columnIndex, sheet);
	                    }
	                    mergeStart = row.getRowNum();
	                    value = row.getCell(columnIndex).getStringCellValue();
	                }
	                rowIndex++;
	            }
	        }
	    }
	
		/**
	     * 合并列
	     */
	    private static void mergeColumn(Map<Integer, Integer> benchmarkMap
	            , Integer mergeBegin
	            , Integer mergeEnd
	            , Integer columnIndex
	            , Sheet sheet) {
	
	        if (mergeBegin >= mergeEnd) {
	            return;
	        }
	        for (Integer endIndex : benchmarkMap.keySet()) {
	            Integer startIndex = benchmarkMap.get(endIndex);
	            if (mergeBegin <= startIndex && (mergeEnd >= startIndex && mergeEnd <= endIndex)) {
	                mergeBegin = startIndex;
	                CellRangeAddress region = new CellRangeAddress(mergeBegin, mergeEnd, columnIndex, columnIndex);
	                sheet.addMergedRegion(region);
	                break;
	            }
	            if (mergeBegin >= startIndex && mergeEnd <= endIndex) {
	                CellRangeAddress region = new CellRangeAddress(mergeBegin, mergeEnd, columnIndex, columnIndex);
	                sheet.addMergedRegion(region);
	                break;
	            }
	            if (mergeBegin >= startIndex && mergeBegin <= endIndex) {
	                mergeEnd = endIndex;
	                CellRangeAddress region = new CellRangeAddress(mergeBegin, mergeEnd, columnIndex, columnIndex);
	                sheet.addMergedRegion(region);
	                break;
	            }
	            if (mergeBegin < startIndex && mergeEnd > endIndex) {
	                mergeBegin = startIndex;
	                mergeEnd = endIndex;
	                CellRangeAddress region = new CellRangeAddress(mergeBegin, mergeEnd, columnIndex, columnIndex);
	                sheet.addMergedRegion(region);
	                break;
	            }
	        }
	    }
	
	public static void createCellSetValue(Row row, int column, Object value) {
	        if (value == null) {
	            value = "";
	        }
	        Cell cell = row.createCell(column);
	        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());
	        }
	    }
	}