You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Srikanth Vattikonda <su...@gmail.com> on 2014/07/11 13:13:40 UTC
RE: XSSF data hidden in cell/column when cells merged
Hi,
I am new to using POI and trying to generate a excel sheet with two cells
as shown below (only adding required code here)
XSSFCellStyle cellStyle= workbook.createCellStyle();
cellStyle.setFillForegroundColor(new XSSFColor(new Color(255, 255, 255)));
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setWrapText(true);
cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
cellStyle.setFont(fontReference);
for (int i=0; i<10; i++) {
XSSFRow row = workSheet.createRow(i);
XSSFCell cell = row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue("some data goes here");
cell = row.createCell(1);
cell.setCellStyle(cellStyle);
cell.setCellValue("");
workSheet.addMergedRegion(new CellRangeAddress(i, i, 0, 1));
}
workSheet.setColumnWidth(0, 4000);
workSheet.setColumnWidth(1, 4000);
But as I am merging the two cells the data in the merged cell is hidden(I
mean the cell height is not as expected), Whereas if I create only one cell
the cell height automatically fits based on the data and the complete text
is visible. What should I do to make the complete text visible when cells
are merged(Merged cells having height so that complete text is visible).
Please help me out in proceeding further.
Thanks,
Srikanth
RE: XSSF data hidden in cell/column when cells merged
Posted by sunny8875 <su...@gmail.com>.
Can anyone please help me out with this.
Thanks,
Srikanth
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/RE-XSSF-data-hidden-in-cell-column-when-cells-merged-tp5715961p5715973.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: XSSF data hidden in cell/column when cells merged
Posted by sunny8875 <su...@gmail.com>.
I tried but it even didn't work
--
Srikanth
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/RE-XSSF-data-hidden-in-cell-column-when-cells-merged-tp5715961p5715967.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: XSSF data hidden in cell/column when cells merged
Posted by "Kadiyala, Srinivasa (US - Newton)" <sr...@deloitte.com>.
worksheet.autoSizeColumn(<column num>);
--
Naga
(Srinivasa N Kadiyala)
+1 617 678 9164
This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and any disclosure, copying, or distribution of this message, or the taking of any action based on it, by you is strictly prohibited.
v.E.1
-----Original Message-----
From: sunny8875 <su...@gmail.com>
Reply-To: POI Users List <us...@poi.apache.org>
Date: Friday, July 11, 2014 at 7:43 AM
To: "user@poi.apache.org" <us...@poi.apache.org>
Subject: Re: XSSF data hidden in cell/column when cells merged
>Yes whereas in the below snippet I am setting the cell style individually
>for
>each cell
>
> XSSFCell cell = row.createCell(0);
> // Set cell style
> cell.setCellStyle(cellStyle);
> cell.setCellValue("some data goes here");
> cell = row.createCell(1);
> // Set cell style
> cell.setCellStyle(cellStyle);
> cell.setCellValue("");
>
>
>--
>Srikanth
>
>
>
>--
>View this message in context:
>http://apache-poi.1045710.n5.nabble.com/RE-XSSF-data-hidden-in-cell-column
>-when-cells-merged-tp5715961p5715965.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: XSSF data hidden in cell/column when cells merged
Posted by sunny8875 <su...@gmail.com>.
Yes whereas in the below snippet I am setting the cell style individually for
each cell
XSSFCell cell = row.createCell(0);
// Set cell style
cell.setCellStyle(cellStyle);
cell.setCellValue("some data goes here");
cell = row.createCell(1);
// Set cell style
cell.setCellStyle(cellStyle);
cell.setCellValue("");
--
Srikanth
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/RE-XSSF-data-hidden-in-cell-column-when-cells-merged-tp5715961p5715965.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: XSSF data hidden in cell/column when cells merged
Posted by "Kadiyala, Srinivasa (US - Newton)" <sr...@deloitte.com>.
POI do not copy style automatically. You also need to copy row by row, and
cell by cell. It is detailed in POI examples documentation.
You need to do programatically. The following snippet is in the sample
code I had shared.
if (newCellStyle != null && oldCell != null){
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
worksheet.autoSizeColumn(i);
}
--
Naga
(Srinivasa N Kadiyala)
+1 617 678 9164
This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and any disclosure, copying, or distribution of this message, or the taking of any action based on it, by you is strictly prohibited.
v.E.1
-----Original Message-----
From: sunny8875 <su...@gmail.com>
Reply-To: POI Users List <us...@poi.apache.org>
Date: Friday, July 11, 2014 at 7:27 AM
To: "user@poi.apache.org" <us...@poi.apache.org>
Subject: Re: XSSF data hidden in cell/column when cells merged
>Hi Srinivasa,
>
>Thank you very much for you reply. Sorry I didn't get what you mean.
>
>What I am trying to do is programatically create a new excel document, And
>when I merge two cells the height is automatically fit so that the
>complete
>text isn't visible but where as I need the complete text to be displayed
>(Cell having the height so that complete text in the cell visible) even
>after merge. Please help me out here.
>
>Thanks,
>Srikanth
>
>
>
>--
>View this message in context:
>http://apache-poi.1045710.n5.nabble.com/RE-XSSF-data-hidden-in-cell-column
>-when-cells-merged-tp5715961p5715963.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: XSSF data hidden in cell/column when cells merged
Posted by sunny8875 <su...@gmail.com>.
Hi Srinivasa,
Thank you very much for you reply. Sorry I didn't get what you mean.
What I am trying to do is programatically create a new excel document, And
when I merge two cells the height is automatically fit so that the complete
text isn't visible but where as I need the complete text to be displayed
(Cell having the height so that complete text in the cell visible) even
after merge. Please help me out here.
Thanks,
Srikanth
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/RE-XSSF-data-hidden-in-cell-column-when-cells-merged-tp5715961p5715963.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: XSSF data hidden in cell/column when cells merged
Posted by "Kadiyala, Srinivasa (US - Newton)" <sr...@deloitte.com>.
Try the following ….
// Loop through source columns to add to new row
for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
// Grab a copy of the old/new cell
HSSFCell oldCell = sourceRow.getCell(i);
HSSFCell newCell = newRow.createCell(i);
// Copy style from old cell and apply to new cell
//HSSFCellStyle newCellStyle = workbook.createCellStyle();
if (newCellStyle != null && oldCell != null){
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
worksheet.autoSizeColumn(i);
}
newCell.setCellStyle(newCellStyle);
// If there is a cell comment, copy
if (newCell.getCellComment() != null) {
newCell.setCellComment(oldCell.getCellComment());
}
// If there is a cell hyperlink, copy
if (oldCell != null && oldCell.getHyperlink() != null) {
newCell.setHyperlink(oldCell.getHyperlink());
}
// Set the cell data type
if (oldCell != null) {
newCell.setCellType(oldCell.getCellType());
// Set the cell data value
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
}
}
}
--
Naga
(Srinivasa N Kadiyala)
This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and any disclosure, copying, or distribution of this message, or the taking of any action based on it, by you is strictly prohibited.
v.E.1
-----Original Message-----
From: Srikanth Vattikonda <su...@gmail.com>
Reply-To: POI Users List <us...@poi.apache.org>
Date: Friday, July 11, 2014 at 7:13 AM
To: POI Users List <us...@poi.apache.org>
Subject: RE: XSSF data hidden in cell/column when cells merged
>Hi,
>
>I am new to using POI and trying to generate a excel sheet with two cells
>as shown below (only adding required code here)
>
>XSSFCellStyle cellStyle= workbook.createCellStyle();
>cellStyle.setFillForegroundColor(new XSSFColor(new Color(255, 255, 255)));
>cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
>cellStyle.setWrapText(true);
>cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
>cellStyle.setBorderBottom(BorderStyle.THIN);
>cellStyle.setBorderRight(BorderStyle.THIN);
>cellStyle.setBorderTop(BorderStyle.THIN);
>cellStyle.setBorderLeft(BorderStyle.THIN);
>cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
>cellStyle.setFont(fontReference);
>
>for (int i=0; i<10; i++) {
> XSSFRow row = workSheet.createRow(i);
> XSSFCell cell = row.createCell(0);
> cell.setCellStyle(cellStyle);
> cell.setCellValue("some data goes here");
> cell = row.createCell(1);
> cell.setCellStyle(cellStyle);
> cell.setCellValue("");
> workSheet.addMergedRegion(new CellRangeAddress(i, i, 0, 1));
>}
>
>workSheet.setColumnWidth(0, 4000);
>workSheet.setColumnWidth(1, 4000);
>
>But as I am merging the two cells the data in the merged cell is hidden(I
>mean the cell height is not as expected), Whereas if I create only one
>cell
>the cell height automatically fits based on the data and the complete text
>is visible. What should I do to make the complete text visible when cells
>are merged(Merged cells having height so that complete text is visible).
>Please help me out in proceeding further.
>
>Thanks,
>Srikanth