How to remove Merged region using POI?

Question

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

1. Can we use sheet.removeMergedRegion(int)
2. If yes then tell me what should be the argument
3. What will happen to the data present in the Merged cell.

Answer 1

Quick answer

1. Yes
2. The id of the merged region in the sheet
3. The data is kept in the first cell of the region, other cells will be empty

Explanation

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

/* ... 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".

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);
    }
}

Answer 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.

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);
                    }
                }
            }

        }