How to unmerge cells in a spreadsheet using apache POI java library

Question

We can use sheet.addMergedRegion(rowFrom,rowTo,colFrom,colTo); for adding merged cells in the sheet But I already have a sheet with merged cells in it and I want to unmerge these cells.

Is there any way to unmerge cells of a sheet without creating new sheet using Apache POI ?

Answer 1

Promoting a comment to an answer...

The method you want is Sheet.removeMergedRegion(int)

That takes the index of the region, which you got when you added it. Otherwise, you can use getNumMergedRegions() and getMergedRegion(int) to iterate over the regions to find the index of the one you want to remove

Answer 2

I just figured out that internally the number of existing regions is checked after every region method. E.g. after removing Region 1 everything is new calculated like size is previousSize-1 and sheet.getNumMergedRegions() is also one less. So for me I added all regions to a list and deleted them from the sheet. I took always id 0 as the id is recalculated everytime.

for(int i = 0; i<numOfRegion;i++)
        {
            regions.add(sheet.getMergedRegion(0));
            sheet.removeMergedRegion(0);
        }