How to get merged regions using apache POI's event API?

Question

How can I get merged regions (merged cells) of an excel sheet using the event API provided by Apache POI?

Using the "traditional" DOM-like parsing style there are methods called Sheet.getNumMergedRegions() and Sheet.getMergedRegion(int). Unfortunately I need to handle huge Excel files where I get out of memory errors even with the highest Xmx-value I am allowed to use (in this project). So I'd like to use the event API, but wasn't able to find out how to get information about merged regions, which I need to know to "understand" the content correctly...

Using the example given here: http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api I get events for each single cell of a merged region (only the first of them contains any textual content though). So maybe, if there isn't a more direct way, it would help to know how those merged cells could be (safely) distinguished from other (empty) cells...

Answer 1

I don't know for sure where merged cell info gets stored, but I'm fairly sure it won't be with the cell data itself, as that's not the Excel way.

What I'd suggest you do is create a simple file without merged cells. Then, take a copy, and add a single merged cell. Unzip both of these (.xlsx is a zip of xml files), and diff them. That'll show you quite quickly what gets set to mark cells as merged. (My hunch is that it'll be somewhere in the sheet settings, near the start but not near the cell values, BICBW)

Once you know where the merged cell details live, you can take a look at the XSSF UserModel code for working with merged cells to get an idea of how they work, how they're manipulated, what the options are etc. With that in mind, you can look at the file format docs for the full details, but those can be a bit heavy and detailed to go to first. Finally, you can add in your code to use the merged info details, once you know where to get it from!