java 使用 poi 导出Excel 根据相同内容动态合并单元格可指定列合并

import com.maidanli.common.utils.DateUtil;
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.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(2, 25000);
        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] >= 11 && mergeIndex[i] <= 12) {
                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();
                    }eles{
                        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");
            } 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());
            }
        }
    }

    //接口调用示例

    @ApiOperation("物流列表导出")
    @PostMapping("/on/line/export")
    public AccessResult<String> exportOrderOnLineListDTO(HttpServletResponse response, @RequestBody OrderOnLineExcelQueryDTO queryDTO) throws Exception {
        List<OrderOnLineExcelListDTO> list = orderManageService.getOrderOnLineListDTO(queryDTO);
        if (list.isEmpty()) {
            return AccessResult.initFailure("99999", "暂无数据");
        } else {
            Map<String, String> headNameMap = new LinkedHashMap<>();
            //key对象属性字段  valuel excel列标题
            headNameMap.put("orderNo", "订单编号");
            headNameMap.put("addTime", "下单时间");
            headNameMap.put("goodsProducerName", "厂家");
            headNameMap.put("goodsName", "商品名称");
            headNameMap.put("specItemName", "商品属性");
            headNameMap.put("quantitys", "商品数量");
            headNameMap.put("name", "收件人姓名");
            headNameMap.put("phone", "收件人手机");
            headNameMap.put("address", "收件人地址");
            headNameMap.put("courierSn", "快递单号");
            headNameMap.put("courierName", "快递公司名称");
            byte[] excelBytes = OfficeUtil.toExcelMergeCell(list, headNameMap, 1,new int[]{1,2,3,8,9,10,11,12},1);

            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("物流列表.xls", "utf-8"));
            response.getOutputStream().write(excelBytes);
            response.getOutputStream().flush();
            response.getOutputStream().close();
            return null;
        }
    }
}

运行结果：

public class Model {
    private String orderNo;
    private String changjia;
    private String goods;

    public Model(String orderNo, String changjia, String goods) {
        this.orderNo = orderNo;
        this.changjia = changjia;
        this.goods = goods;
    }

    public String getOrderNo() {        return orderNo;
    }

    public void setOrderNo(String orderNo) {
        this.orderNo = orderNo;
    }

    public String getChangjia() {
        return changjia;
    }

    public void setChangjia(String changjia) {
        this.changjia = changjia;
    }

    public String getGoods() {
        return goods;
    }

    public void setGoods(String goods) {
        this.goods = goods;
    }
}

最后

以上就是野性荷花为你收集整理的java 使用 poi 导出Excel 根据相同内容动态合并单元格可指定列合并的全部内容，希望文章能够帮你解决java 使用 poi 导出Excel 根据相同内容动态合并单元格可指定列合并所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。