java导出execl每隔N行第M列合并

java导出execl每隔N行第M列合并

创建单元格方法

/**
     * 用户存在需要合并数据的方法
     *
     * @param sheet          表格窗口
     * @param dataList       数据
     * @param dataRowStart   开始合并的行下标 第一行的下标为 0
     * @param mergeNumberRow 合并的行数
     * @param mergeIndex     第几个数据需要进行合并
     */
    public void setData(Sheet sheet, List<String[]> dataList, int dataRowStart, int mergeNumberRow, int mergeIndex) {
        if (CollectionUtils.isEmpty(dataList)) {
            return;
        }
        Cell cell;
        int rowIndex = dataRowStart;
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(rowIndex);
            for (int j = 0; j < dataList.get(i).length; j++) {
                if ((i + mergeNumberRow) % mergeNumberRow != 0 && j == mergeIndex) {
                    continue;
                }
                cell = row.createCell(j, CellType.STRING);
                cell.setCellValue(dataList.get(i)[j]);
            }
            if ((i + mergeNumberRow) % mergeNumberRow == 0) {
                sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + mergeNumberRow - 1, mergeIndex, mergeIndex));
            }
            rowIndex += 1;
        }
    }

测试方法

@Test
    public void multipleRowMerge() {
        FileOutputStream fos = null;
        // 创建一个空文件
        String saveFilePath = "C:\\Users\\用户名\\Desktop\\测试文件.xlsx";
        try {
            File file = new File(saveFilePath);
            if (!file.exists()) {
                file.createNewFile();
            }
            fos = new FileOutputStream(saveFilePath);
            Workbook workbook = new SXSSFWorkbook();
            Sheet sheet = workbook.createSheet();

            List<String[]> dataList = new ArrayList<>();
            String[] stringOne = new String[2];
            stringOne[0] = "第一次合并";
            stringOne[1] = "第一行";
            String[] stringTwo = new String[2];
            stringTwo[0] = "";
            stringTwo[1] = "第二行";
            String[] stringThree = new String[2];
            stringThree[0] = "第二次合并";
            stringThree[1] = "第三行";
            dataList.add(stringOne);
            dataList.add(stringTwo);
            dataList.add(stringThree);
            //设置data 并且设置每两行的第一列进行合并  从第一行开始
            setData(sheet, dataList, 0, 2, 0);
            workbook.write(fos);
        } catch (IOException exp) {
            exp.printStackTrace();
        } finally {
            if (fos != null) {
                try {
                    fos.flush();
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

结果截图

总结：其中发挥主要的方法是addMergedRegion（）设置合并的开始行和开始列和结束行和结束列

/*
     * Sets the CellStyle that applies to the given
     *  (0 based) column.
     */
//    public CellStyle setColumnStyle(int column, CellStyle style);

    /**
     * Adds a merged region of cells (hence those cells form one)
     *
     * @param region (rowfrom/colfrom-rowto/colto) to merge
     * @return index of this region
     */
    int addMergedRegion(CellRangeAddress region);