excel文件合并单元格

最新推荐文章于 2023-12-19 19:45:30 发布

周八营业的代码人

最新推荐文章于 2023-12-19 19:45:30 发布

阅读量209

点赞数

分类专栏： WorkNotes 文章标签： java 后端

本文链接：https://blog.csdn.net/weixin_44320429/article/details/127325115

版权

WorkNotes 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

/**
     * 导出合并单元格的excel文件
     * @param dataList              数据列表
     * @param tableName             文件名称
     * @param sheetName             sheet名称
     * @param mergeColumnsIndex     需要合并单元格的列号
     * @param mergeRowIndex         开始合并单元格的行号
     */
    public static <T> void downloadMergeCellExcel(HttpServletResponse response, List<LandAllTableRow> dataList, Class rowClass,
                                                  String tableName, String sheetName, Collection<String> includeColumnFiledNames,
                                                  List<Integer> mergeColumnsIndex, int mergeRowIndex) throws IOException {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(tableName, "UTF-8");

        Map<Integer, List<LandAllTableRow>> collectByLandId = dataList.stream().collect(Collectors.groupingBy(LandAllTableRow::getId));
        //每个landId对应几条企业信息
        Map<Integer, Integer> landRows = new HashMap<>();
        Map<String, Integer> companyRows = new HashMap<>();

        for (Integer landId : collectByLandId.keySet()) {
            List<LandAllTableRow> companys = collectByLandId.get(landId);
            Map<String, List<LandAllTableRow>> collectByOrgCode = companys.stream().collect(Collectors.groupingBy(LandAllTableRow::getOrganizationCode));
            int companySize = 0;
            for (String orgCode : collectByOrgCode.keySet()) {
                companyRows.put(orgCode, collectByOrgCode.get(orgCode).size());
                companySize += collectByOrgCode.get(orgCode).size();
            }
            landRows.put(landId, companySize);
        }
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            EasyExcel.write(response.getOutputStream(), rowClass)
                    .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeColumnsIndex, mergeRowIndex, landRows, companyRows))
                    .sheet(sheetName)
                    .includeColumnFiledNames(includeColumnFiledNames)
                    .doWrite(dataList);

        }  catch (IOException e) {
            e.printStackTrace();
        }
    }

package com.popsmart.data.common;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
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.util.CellRangeAddress;

import java.util.*;

public class ExcelFillCellMergeStrategy implements CellWriteHandler {

    private List<Integer> mergeColumnIndex;
    private int mergeRowIndex;

    private Map<Integer, Integer> landRows;
    private Map<String, Integer> companyRows;

    public ExcelFillCellMergeStrategy(List<Integer> mergeColumnIndex, int mergeRowIndex, Map<Integer, Integer> landRows, Map<String, Integer> companyRows) {
        this.mergeColumnIndex = mergeColumnIndex;
        this.mergeRowIndex = mergeRowIndex;
        this.landRows = landRows;
        this.companyRows = companyRows;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {

        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();
        Row row = cell.getSheet().getRow(curRowIndex);

        int landIdIndex = 0;
        int organizationCodeIndex = 6;

        if (curRowIndex > mergeRowIndex) {

            //当前列不需要合并
            if (!mergeColumnIndex.contains(curColIndex)) {
                return;
            }

            Integer landIdValue = (int) row.getCell(landIdIndex).getNumericCellValue();
            int landSize = landRows.get(landIdValue);
            //当前行不需要合并
            if (landSize < 2) {
                return;
            }

            Integer lastLandIdValue = null;
            String lastOrgCode = null;
            if (curRowIndex > 1) {
                Row preRow = cell.getSheet().getRow(curRowIndex - 1);
                lastLandIdValue = (int) preRow.getCell(landIdIndex).getNumericCellValue();
                if (curColIndex >= organizationCodeIndex) {
                    lastOrgCode = preRow.getCell(organizationCodeIndex).getStringCellValue();
                }
            }

            if (curColIndex < organizationCodeIndex) {
                if (landIdValue.equals(lastLandIdValue)) {
                    return;
                }
                mergeCell(writeSheetHolder, curRowIndex, curRowIndex + landSize - 1, curColIndex);
            } else {
                String organizationCodeValue = row.getCell(organizationCodeIndex).getStringCellValue();
                if (organizationCodeValue.equals(lastOrgCode)) {
                    return;
                }

                int companySize = companyRows.get(organizationCodeValue);
                if (companySize > 2) {
                    mergeCell(writeSheetHolder, curRowIndex, curRowIndex + companySize - 1, curColIndex);
                }
            }
        }
    }


    /**
     * @param curRowIndex 当前行
     * @param lastRow     合并到第几行
     * @param curColIndex 当前列
     */
    private void mergeCell(WriteSheetHolder writeSheetHolder,
                           int curRowIndex,
                           int lastRow,
                           int curColIndex) {

        Sheet sheet = writeSheetHolder.getSheet();
        CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, lastRow, curColIndex, curColIndex);
        sheet.addMergedRegion(cellRangeAddress);
    }
}