OK. I cannot test this code as I no longer have access to Excel - we use
OpenOffice exclusivly and there are a few differences between the two - so
you will have to do that.
1. Open the workbook.
2. Get a sheet from the workbook.
3. On the sheet object, call the getNumberMergedRegions() method to discover
how many merged regions
there are on the sheet.
4. Enter a for loop conditioned on the number of merged regions and recover
a CellRangeAddress object
for each merged region something like this
List<CellRangeAddress> regionsList = new ArrayList<CellRangeAddress>();
for(int i = 0; i < sheet.getNumberMergedRegions(); i++) {
regionsList.add(sheet.getMergedRegion(i));
}
5. Now, when you get a cell from the sheet check to see if it is in a merged
region. If it is, from what I
understood about your original request, you want to return the contents
of the merged area
every time you encounter a cell that is contained within it. So, all you
need to do is the following.
for(CellRangeAddress region : regionsList) {
// If the region does contain the cell you have just read from the row
if(region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
// Now, you need to get the cell from the top left hand corner of this
int rowNum = region.getFirstRow();
int colIndex = region.getFirstColumn();
cell = sheet.getRow(rowNum).getCell(colIndex);
System.out.println("Cel is in merged region. The value stored in that
region is " +
cell.getStringCellValue());
}
}
Obviously, each time you get a new sheet from the workbook, you will need to
rebuild the list of CellRangeAddress objects describing any merged areas.
You can optimise the code so that the checks are never performed if there
are no merged areas, you can exit the for loop above once the cell is found
to be in a merged area, and so on.
That ought to work. I have not tested it and have just written what I think
should meet your criteria but the overall flow seems correct. Also, some of
the method names may be slightly off - written from memory - so do not rely
on just copying and pasting the code; check with the javadoc and make sure
the sisnatures for the methods are correct please.
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Problem-in-reading-data-from-merged-cells-in-excel-using-XSSF-tp5512364p5516388.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
