Find merged cell in Excel, split the cells and write those in new spreadsheet?

Question

I have been given a Assignment that I need to Split the data of a Spreadsheet and Write it into the new Spreadsheet. The Conditions are, Given Spreadsheet may have multiple numbers of Merged Cells and I need to find those Merged cells and write those Data in a New SpreadSheet. ie, the data or cells between one merged cell till to another Merged cell must be written in another Spreadsheet. My Code of Effort is given below, ``` import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class CopyTest { public static void main(String[] args) throws IOException { CopyTest excel = new CopyTest(); excel.process("D:\B3.xls"); } public void process(String fileName) throws IOException { Buffe…

Accepted Answer

According to the Javadocs for `HSSFSheet`, `getMergedCellRegionAt()` was deprecated in 2008 because the `Region` it returns was also deprecated, in favor of `CellRangeAddress`. It suggests that you should use `getMergedRegion(int)` instead, which returns a `CellRangeAddress`.

The merged region data is not stored directly with the cells themselves, but with the `Sheet` object. So you do not need to loop through rows and cells looking for whether they are part of a merged region; you just need to loop through the list of merged regions on the sheet, then add the merged region to your new sheet with `addMergedRegion(CellRangeAddress)`.

```
for (int i = 0; i < sheet.getNumMergedRegions(); i++)
{
    CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
    // Just add it to the sheet on the new workbook.
    mySheet.addMergedRegion(mergedRegion);
}
```

These methods on `HSSFSheet` are in the `Sheet` interface, so they will work with any Excel workbook that Apache POI supports, .xls (HSSF) or .xlsx (XSSF).

Find merged cell in Excel, split the cells and write those in new spreadsheet?

Solution

Problem