如何使用POI删除合并区域？

I know we can merge cells using sheet.addMergedRegion(range). I want to know how to remove merge.

我知道我们可以使用sheet.addMergedRegion（range）合并单元格。我想知道如何删除合并。

1. Can we use sheet.removeMergedRegion(int)
2. 我们可以使用sheet.removeMergedRegion（int）
3. If yes then tell me what should be the argument
4. 如果是，那么告诉我该论点应该是什么
5. What will happen to the data present in the Merged cell.
6. 合并单元格中存在的数据会发生什么。

2 个解决方案

#1

Quick answer

1. Yes
2. 是
3. The id of the merged region in the sheet
4. 工作表中合并区域的ID
5. The data is kept in the first cell of the region, other cells will be empty
6. 数据保存在该区域的第一个单元格中，其他单元格将为空

Explanation

You can get the merged regions with sheet.getMergedRegion(i), where i is its identifier in the sheet.

您可以使用sheet.getMergedRegion（i）获取合并的区域，其中i是工作表中的标识符。

/* ... initialize your workbook and sheet here ... */

// Loop through every merged region in the sheet
for(int i=0; i < sheet.getNumMergedRegions(); ++i)
{
    // Delete the region
    sheet.removeMergedRegion(i);
}

When you do such a thing, the content of the merged region will be kept in the first cell of this region (the top-left one). Other cells will have an empty content.

当你做这样的事情时，合并区域的内容将保留在该区域的第一个单元格中（左上角）。其他单元格将具有空白内容。

Example: If you have (A1 and A2) merged with the content "merged region", the result of the split will be (A2) empty and (A1) with the content "merged region".

示例：如果（A1和A2）与内容“合并区域”合并，则拆分的结果将为（A2）为空，（A1）为内容“合并区域”。

Note that this is how the data is stored internally, even when you try to get the value of a cell contained in a merged region. Keeping the same example, you will have:

请注意，这是数据在内部存储的方式，即使您尝试获取合并区域中包含的单元格的值也是如此。保持相同的例子，您将拥有：

CellReference ref = new CellReference("A1");
Row row = sheet.getRow( ref.getRow() );
Cell cell = row.getCell( ref.getCol() );
System.out.println( new DataFormatter().formatCellValue(cell) ); // Will print "merged region"

ref = new CellReference("A2");
row = sheet.getRow( ref.getRow() );
cell = row.getCell( ref.getCol() );
System.out.println( new DataFormatter().formatCellValue(cell) ); // Will print empty string

This will be the same behaviour before and after removing the merged region.

在删除合并区域之前和之后，这将是相同的行为。

More information

If you want to get a specific merged region to split it, you will have to add a condition in your loop to identify it:

如果要获取特定的合并区域以将其拆分，则必须在循环中添加条件以识别它：

// If we want to split the merged cell starting at D3
CellReference ref = new CellReference("D3");

for(int i=0; i < sheet.getNumMergedRegions(); ++i)
{
    CellRangeAddress range = sheet.getMergedRegion(i);

    if ( range.getFirstRow() == ref.getRow() && range.getLastRow() == ref.getCol() )
    {
        sheet.removeMergedRegion(i);
    }
}

#2

I using poi-3.12 and the counter from 0 to numberOfRegions don't work for me but in the other direction from sheet.getNumMergedRegions() to 0 it works.

我使用poi-3.12并且从0到numberOfRegions的计数器对我不起作用，但是从sheet.getNumMergedRegions（）到0的另一个方向它起作用。

To remove regions and copy values this works for me:

要删除区域和复制值，这对我有用：

for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            Row firstRow = sheet.getRow(region.getFirstRow());
            Cell firstCellOfFirstRow = firstRow.getCell(region.getFirstColumn());

            if (firstCellOfFirstRow.getCellType() == Cell.CELL_TYPE_STRING) {
                value = firstCellOfFirstRow.getStringCellValue();
            }

            sheet.removeMergedRegion(i);

            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(value);
                    }
                }
            }

        }