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


package com.szboanda.wrfz.export;

import com.google.common.collect.Maps;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tools.ant.util.DateUtils;

import java.io.ByteArrayOutputStream;
import java.util.*;
import java.util.Map.Entry;

public class OfficeExcelMargeUtil{

/**
 * @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<Map<String, Object>> dataList, Map<String, Object> 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();
    }
    Sheet sheet = workbook.createSheet("数据列表");
    //sheet.setColumnWidth(2, 25000);
    int index = sheet.getPhysicalNumberOfRows();
    dataList = getList(dataList, headNameMap);
    Row rowHead = sheet.createRow(index);
    rowHead.createCell(0).setCellValue("序号");
    headNameMap.entrySet();
    Iterator<Entry<String, Object>> iterator = headNameMap.entrySet().iterator();
    int c = 1;
    while(iterator.hasNext()){
        Entry<String, Object> entry = iterator.next();
        Cell cell = rowHead.createCell(c);
        setCellValue(cell, entry.getValue());
        c++;
    }
    for(int i = 0; i < dataList.size(); i++){
        Map<String, Object> map = dataList.get(i);
        Row row = sheet.createRow(index + 1);
        row.createCell(0).setCellValue(i + 1);
        int m = 0;
        for(Entry<String, Object> entry : map.entrySet()){
            Cell cell = row.createCell(m + 1);
            Object textValue = getValue(entry.getValue());
            setCellValue(cell, textValue);
            m++;
        }
        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();
                }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);
    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 = DateUtils.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());
        }
    }
}

//取list里有用到的数据字段,没用到的需要删除出去
private static List<Map<String, Object>> getList(List<Map<String, Object>> mapList, Map<String, Object> columns){
    List<Map<String, Object>> newmapList = new ArrayList<>();
    for(Map<String, Object> map : mapList){
        map = getUnionSetByGuava(map, columns);
        newmapList.add(map);
    }
    return newmapList;
}
/**
 * 取Map集合的并集
 *
 * @param map1 大集合
 * @param map2 小集合
 * @return 两个集合的并集
 */
public static Map<String, Object> getUnionSetByGuava(Map<String, Object> map1, Map<String, Object> map2){
    // Set<String> bigMapKey = map1.keySet();
    Set<String> smallMapKey = map2.keySet();
    // Set<String> differenceSet = Sets.intersection(bigMapKey, smallMapKey);
    Map<String, Object> result = Maps.newLinkedHashMap();
    for(String key : smallMapKey){
        result.put(key, map1.get(key));
    }
    return result;
}

}

示例结果

声明:超博客|版权所有,违者必究|如未注明,均为原创|本网站采用BY-NC-SA协议进行授权

转载:转载请注明原文链接 - [JAVA]java 使用 poi 导出Excel 根据相同内容动态合并单元格可指定列合并


Java知识分享