You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by TimShiu <ti...@ssc-ltd.com> on 2009/06/21 11:43:41 UTC
Autosize row for HSSF library
In excel, the row height will be auto resized if any cell in the row is set
as wrap-text.
Unfortunately, this auto resizing behavior will be malfunctioned when the
cell is a merged cell.
So, I would like to ask if there is any method in HSSF library that can
autosize the row????
--
View this message in context: http://www.nabble.com/Autosize-row-for-HSSF-library-tp24132911p24132911.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: Autosize row for HSSF library
Posted by sujikin <su...@gmail.com>.
Hi,
I tried the below code
public void testMultiMergeRegions() throws IOException {
int currentStartRow = 5;
int currentStartColumn = 0;
Row row = null;
Cell sideQuestionCell = null;
HSSFFont redFont = null;
HSSFFont font = null;
HSSFCellStyle xResponseWithTopBorder = null;
HSSFCellStyle xResponseWithoutTopBorder = null;
String xAxisQuestion = "We Are Trying to replicate the problem here";
Response response = new Response();
response.setId("1");
response
.setValue("Always have breakthroughs on appearance and functions");
List<Response> xAxisResponseList = new ArrayList<Response>();
for (int i = 0; i < 20; i++) {
xAxisResponseList.add(response);
}
// initialising style here
font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
redFont = workbook.createFont();
redFont.setColor(Font.COLOR_RED);
xResponseWithTopBorder = workbook.createCellStyle();
xResponseWithTopBorder.setWrapText(true);
xResponseWithTopBorder.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
xResponseWithTopBorder.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
xResponseWithTopBorder.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
xResponseWithTopBorder.setAlignment(HSSFCellStyle.ALIGN_LEFT);
xResponseWithTopBorder.setFillForegroundColor(HSSFColor.WHITE.index);
xResponseWithTopBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
xResponseWithTopBorder.setLocked(false);
xResponseWithTopBorder.setFont(font);
xResponseWithoutTopBorder = workbook.createCellStyle();
xResponseWithoutTopBorder.setWrapText(true);
xResponseWithoutTopBorder.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
xResponseWithoutTopBorder
.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
xResponseWithoutTopBorder.setAlignment(HSSFCellStyle.ALIGN_LEFT);
xResponseWithoutTopBorder.setFillForegroundColor(HSSFColor.WHITE.index);
xResponseWithoutTopBorder
.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
xResponseWithoutTopBorder.setLocked(false);
xResponseWithoutTopBorder.setFont(font);
sideQuestionCell = sheet.createRow(currentStartRow).createCell(
currentStartColumn + 4);
//
sheet.getRow(currentStartRow).setHeightInPoints(sheet.getDefaultRowHeightInPoints());
CellRangeAddress cellXRange = new CellRangeAddress(currentStartRow,
currentStartRow, currentStartColumn + 4,
((currentStartColumn + 4) + xAxisResponseList.size()));
sheet.addMergedRegion(cellXRange);
setStyleMergedRegion(cellXRange, false, false, false, false, true);
row = sheet.createRow(currentStartRow + 1);
sheet.getRow(currentStartRow).setHeightInPoints(
sheet.getDefaultRowHeightInPoints());
sheet.getRow(currentStartRow + 1).setHeightInPoints(
sheet.getDefaultRowHeightInPoints());
row = sheet.createRow(currentStartRow + 1);
sheet.setColumnWidth(currentStartRow + 1, xAxisResponseList.size() + 1);
sideQuestionCell.setCellValue(xAxisQuestion);
sideQuestionCell.setCellStyle(xResponseWithoutTopBorder);
for (int indx = 0; indx < xAxisResponseList.size(); indx++) {
String xAxisRepsonse = xAxisResponseList.get(indx).getValue();
Cell sideQuestionResponseCell = row.createCell(currentStartColumn
+ 4 + indx);
sideQuestionResponseCell.setCellValue(xAxisRepsonse);
sideQuestionResponseCell.setCellStyle(xResponseWithTopBorder);
}
FileOutputStream out = new FileOutputStream("MergedRegion.xls");
workbook.write(out);
out.close();
}
but the problem is not solved.
Interestingly when the number of columns is less(upto 17 in the above case)
this behavior is not visible.
Any help will be appreciated.
dgv123 wrote:
>
> or try
>
> sheet.getRow(int
> rownum).setHeightInPoints(sheet.getDefaultRowHeightInPoints());
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Autosize-row-for-HSSF-library-tp24132911p24209674.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: Autosize row for HSSF library
Posted by Derryl Varghese <de...@gmail.com>.
or try
sheet.getRow(int rownum).setHeightInPoints(sheet.getDefaultRowHeightInPoints());
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Autosize row for HSSF library
Posted by Derryl Varghese <de...@gmail.com>.
I can only think of the setDefaultRowHeight method in HSSFSheet. Not sure if
that will help.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Autosize row for HSSF library
Posted by MSB <ma...@tiscali.co.uk>.
Glad you posted that as I was just logging on to say that 'my' solution will
not work and indeed I made a mistake in the Excel behaviour I reported.
It seems that as soon as cells are merged then the auto fit behaviour simply
disappears
(http://excel.tips.net/Pages/T003207_Automatic_Row_Height_For_Merged_Cells_with_Text_Wrap.html).
There are tricks you can use but they all do entail calculating and setting
the row height(s) manually. Sorry to mislead anyone.
Yours
Mark B
TimShiu wrote:
>
> Thanks for everyone's help on this topic.
>
> I finally got another solution and would like to share it here.
> My new solution is to calculate how many lines the text will be needed by
> using LineBreakMeasurer and set the height of the row accordingly.
>
> // =======================
> // Program segment [START]
> // =======================
>
> // Create Font object with Font attribute (e.g. Font family, Font size,
> etc) for calculation
> java.awt.Font currFont = new java.awt.Font(fontName, 0, fontSize);
> AttributedString attrStr = new AttributedString(cellValue);
> attrStr.addAttribute(TextAttribute.FONT, currFont);
>
> // Use LineBreakMeasurer to count number of lines needed for the text
> FontRenderContext frc = new FontRenderContext(null, true, true);
> LineBreakMeasurer measurer = new LineBreakMeasurer(attrStr.getIterator(),
> frc);
> int nextPos = 0;
> int lineCnt = 0;
> while (measurer.getPosition() < cellValue.length())
> {
> nextPos = measurer.nextOffset(mergedCellWidth); // mergedCellWidth is
> the max width of each line
> lineCnt++;
> measurer.setPosition(nextPos);
> }
>
> Row currRow = currSht.getRow(rowNum);
> currRow.setHeight((short)(currRow.getHeight() * lineCnt));
>
> // =====================
> // Program segment [END]
> // =====================
>
> The above solution didn't handle the newline character, i.e. "\n", and
> only tested under horizontal merged cells only.
>
>
--
View this message in context: http://www.nabble.com/Autosize-row-for-HSSF-library-tp24132911p24218279.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: Autosize row for HSSF library
Posted by TimShiu <ti...@ssc-ltd.com>.
Thanks for everyone's help on this topic.
I finally got another solution and would like to share it here.
My new solution is to calculate how many lines the text will be needed by
using LineBreakMeasurer and set the height of the row accordingly.
// =======================
// Program segment [START]
// =======================
// Create Font object with Font attribute (e.g. Font family, Font size, etc)
for calculation
java.awt.Font currFont = new java.awt.Font(fontName, 0, fontSize);
AttributedString attrStr = new AttributedString(cellValue);
attrStr.addAttribute(TextAttribute.FONT, currFont);
// Use LineBreakMeasurer to count number of lines needed for the text
FontRenderContext frc = new FontRenderContext(null, true, true);
LineBreakMeasurer measurer = new LineBreakMeasurer(attrStr.getIterator(),
frc);
int nextPos = 0;
int lineCnt = 0;
while (measurer.getPosition() < cellValue.length())
{
nextPos = measurer.nextOffset(mergedCellWidth); // mergedCellWidth is
the max width of each line
lineCnt++;
measurer.setPosition(nextPos);
}
Row currRow = currSht.getRow(rowNum);
currRow.setHeight((short)(currRow.getHeight() * lineCnt));
// =====================
// Program segment [END]
// =====================
The above solution didn't handle the newline character, i.e. "\n", and only
tested under horizontal merged cells only.
MSB wrote:
>
> I know that this will not help anyone but the behviour you describe is
> just what Excel itself does.
>
> This morning, using Excel, I opened a new worksheet, increased the width
> of column A, clicked on cell A1 and set wrap text to true. When I typed a
> long string ot text into that cell, it wrapped around and the row did
> resize itself automatically.
>
> Next, I clicked on cell A4, set Wrap Text to true and then merged cells A4
> and A5. Typing a lone string into that merged cell I saw it wrap but the
> row(s) did not autore-size.
>
> However, I did find one way to make Excel do what you are after. The trick
> was to set the wrap text attribute of both cells in the merged region to
> true. I will not have the time to test this in POI for a few hours so to
> explain what I did in Excel;
>
> Click on cell A6 and set the wrap text attribute true. Click on cell A7
> and set the wrap text attribute to true. Merge cells A6 and A7. Type a
> very long string of text into cell A6. To re-iterate, it seems that the
> key is to set the wrap text attribute on the cells in the merged region; I
> do not know yet how multi column merged regions will work, that is another
> test.
>
> Hop that helps and I willpost some POI code later if I have the chance.
>
> Yours
>
> Mark B
>
>
> TimShiu wrote:
>>
>> In excel, the row height will be auto resized if any cell in the row is
>> set as wrap-text.
>> Unfortunately, this auto resizing behavior will be malfunctioned when the
>> cell is a merged cell.
>>
>> So, I would like to ask if there is any method in HSSF library that can
>> autosize the row????
>>
>
>
--
View this message in context: http://www.nabble.com/Autosize-row-for-HSSF-library-tp24132911p24216153.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: Autosize row for HSSF library
Posted by MSB <ma...@tiscali.co.uk>.
I know that this will not help anyone but the behviour you describe is just
what Excel itself does.
This morning, using Excel, I opened a new worksheet, increased the width of
column A, clicked on cell A1 and set wrap text to true. When I typed a long
string ot text into that cell, it wrapped around and the row did resize
itself automatically.
Next, I clicked on cell A4, set Wrap Text to true and then merged cells A4
and A5. Typing a lone string into that merged cell I saw it wrap but the
row(s) did not autore-size.
However, I did find one way to make Excel do what you are after. The trick
was to set the wrap text attribute of both cells in the merged region to
true. I will not have the time to test this in POI for a few hours so to
explain what I did in Excel;
Click on cell A6 and set the wrap text attribute true. Click on cell A7 and
set the wrap text attribute to true. Merge cells A6 and A7. Type a very long
string of text into cell A6. To re-iterate, it seems that the key is to set
the wrap text attribute on the cells in the merged region; I do not know yet
how multi column merged regions will work, that is another test.
Hop that helps and I willpost some POI code later if I have the chance.
Yours
Mark B
TimShiu wrote:
>
> In excel, the row height will be auto resized if any cell in the row is
> set as wrap-text.
> Unfortunately, this auto resizing behavior will be malfunctioned when the
> cell is a merged cell.
>
> So, I would like to ask if there is any method in HSSF library that can
> autosize the row????
>
--
View this message in context: http://www.nabble.com/Autosize-row-for-HSSF-library-tp24132911p24215772.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