Bug 46832 - XSSF - Cannot create Merged Regions when column is bigger than 255 or row bigger than 65536
Summary: XSSF - Cannot create Merged Regions when column is bigger than 255 or row big...
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.5-dev
Hardware: PC Windows Vista
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2009-03-10 19:55 UTC by jurgen.prokein
Modified: 2009-04-06 08:14 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description jurgen.prokein 2009-03-10 19:55:11 UTC
When creating XSSFSheets, it is not possible to ctreate merged regions beyond the 255 column or 65536 row (HSSF) limits. 

Calling sheet.addMergedRegion() requires a parameter of org.apache.poi.ss.util.CellRangeAddress. This class (CellRangeAddress) calls super() (CellRangeAddressBase), which throws an IllegalArgumentException "invalid cell range" if the column is bigger than 255 or the row is bigger than 65536.

CellRangeAddressBase is an abstract base class and should not impose this restriction on actual implementation classes, e.g. there should be a HSSFCellRangeAddress (with its specific limits) and a XSSFCellRangeAddress (with its specific limits), but those classes do not exist (yet?) in poi-3.5-beta5.

To reproduce run the following code:
final int NUM_COLUMNS = 300;
final String output = "MergedRegions.xlsx";
final int columnSpan = 5;

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("sheet1");

// Create two rows.
XSSFRow firstRow = sheet.createRow((short)0);
XSSFRow secondRow = sheet.createRow((short)1);

XSSFCellStyle centeredStyle = wb.createCellStyle();

// Create NUM_COLUMNS columns.
for (int column = 0; column < NUM_COLUMNS; column++) {
	// Create a new cell in secondRow.
	if (column % columnSpan == 0) {
		// Create a new cell in firstRow spanning over columnSpan cells.
		short colFrom = (short)column;
                short colTo = (short)(colFrom + columnSpan - 1);
		Cell cell = firstRow.createCell(column);
		cell.setCellValue("Heading for columns "+colFrom+" to "+colTo);
		sheet.addMergedRegion(new CellRangeAddress(0,0,colFrom,colTo));

FileOutputStream fileOut = new FileOutputStream(output);
Comment 1 Yegor Kozlov 2009-04-06 08:14:34 UTC
Fixed in r762372

The CellRangeAddress constructor now allows columns greater than 255 and rows greater than 65535. No changes in user code are required. I removed validation from the constructor of CellRangeAddress and moved it to the consumer method sheet.addMergedRegion. This way the grid limits can be gracefully handled without subclassing.