You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Sudheer Jonna <js...@gmail.com> on 2013/03/16 19:03:56 UTC
How to find whether excel Cell is in merged region/having Blank value
Hello,
I would like to export the table(which is having muItiple rowspan
and colspans) in excel using POI 3.9 library .I'am filling/writing the
excel cells according to the table design.To achieve rowspan and colspan in
excel I'am going to use addMergedRegion() method.But if the current cell is
already filled with previous rowspan or colspan(here achieving with merged
region) then it is causing the excel in unreadable format.
If we able to find out whether current cell is already in merged
region/filled cell then we can skip that cell and fill the next blank
cell.Or Is there any better approach to add rowSpan and Colspan.
int i=0;
Row xlRow = sheet.createRow(sheetRowIndex);
for (UIComponent rowComponent : row.getChildren()) { //JSF code:Get all
the values in each row
if (rowSpan > 1) {
int cellIndex = xlRow.getLastCellNum();
cell = xlRow.createCell((short) cellIndex);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(
sheetRowIndex, //first row (0-based)
sheetRowIndex + rowSpan - 1, //last
row (0-based)
i, //first column (0-based)
i //last column (0-based)
));
}
if (colSpan > 1) {
int cellIndex = xlRow.getLastCellNum();
cell = xlRow.createCell((short) cellIndex);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(
sheetRowIndex, //first row (0-based)
sheetRowIndex, //last row (0-based)
i, //first column (0-based)
i + colSpan - 1 //last column
(0-based)
));
}
} else {
int cellIndex = xlRow.getLastCellNum();
cell = xlRow.createCell((short) cellIndex);
cell.setCellValue(value);
cell.setCellStyle(facetStyle);
}
i++;
}
Here we need to find out whether excel is not blank(or which is not filled
with merged region) then we can write the cell value.
Re: How to find whether excel Cell is in merged region/having Blank value
Posted by Sudheer Jonna <js...@gmail.com>.
Thank you much Mark :)
On Sun, Mar 17, 2013 at 1:28 PM, Mark Beardsley <ma...@tiscali.co.uk>wrote:
> Well, the CellRangeAddress class inherits a method from the
> CellRangeAddressBase class with the signature isInRange(int, int). This
> allows you to pass the column and row indices of the cell and find out
> whether the cell is already part of a merged region - assuming you used a
> specific CellRangeAddress to create a merged region. So, all you need to do
> is keep track of the CellRabgeAddress objects you create whilst building
> the
> sheets and iterate through them to see whether a cell is in a merged range.
>
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/How-to-find-whether-excel-Cell-is-in-merged-region-having-Blank-value-tp5712357p5712358.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: How to find whether excel Cell is in merged region/having Blank
value
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Well, the CellRangeAddress class inherits a method from the
CellRangeAddressBase class with the signature isInRange(int, int). This
allows you to pass the column and row indices of the cell and find out
whether the cell is already part of a merged region - assuming you used a
specific CellRangeAddress to create a merged region. So, all you need to do
is keep track of the CellRabgeAddress objects you create whilst building the
sheets and iterate through them to see whether a cell is in a merged range.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/How-to-find-whether-excel-Cell-is-in-merged-region-having-Blank-value-tp5712357p5712358.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org