Hi
Situation:
I want to be able to autofit rows and if incase the row height is less than 30 want to set it to 30.
What I have done is autofit all the rows and columns and after constructing the worksheet. I parse all columns and set max width of 50. After which, I parse all rows and set its wrap to true and apply the style.
Now when I get the height of row, it does not show the actual height of the row but rather before applying any style.
When the Excel is saved I can see the styles being applied successfully…
Issue:
Trying to get height of a row after applying styles does not return the new height.
Update:
Even after I save and try to get the height it is same and does not get updated height.
@wishy.em,
Thanks for providing us details.
Please provide us sample code (runnable) and template file to show the issue, we will check it soon.
Hi Amjad
Please find below the sample code. If you see the printed value, it is always 12.75 where as when you open the excel and see the row height it is 117.
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
try {
worksheet.autoFitColumns(0,1);
worksheet.autoFitRow(0);
RowCollection rows = worksheet.getCells().getRows();
ColumnCollection cols = worksheet.getCells().getColumns();
Row row = rows.get(0);
for (int j = 0; j < 4; j++) {
Column column = cols.get(j);
Style colStyle = column.getStyle();
StyleFlag flag = new StyleFlag();
colStyle.setTextWrapped(true);
flag.setWrapText(true);
column.applyStyle(colStyle, flag);
String cellText = StringUtils.repeat("very very", (int)Math.pow(2, j));
row.get(j).putValue(cellText);
}
//normalize col width
for (int j = 0; j < 4; j++) {
Column column = cols.get(j);
if (column.getWidth() > 30) {
column.setWidth(30);
}
}
//get row height
double rowHeight = rows.get(0).getHeight();
System.out.println("RowHeight: " + rowHeight);
workbook.save("TestRowHeight.xlsx", FileFormatType.XLSX);
} catch (Exception e) {
System.out.println("Exception: " + e.getMessage());
}
@wishy.em,
We also need the template Excel file you are noticing the problem with. We will reproduce the problem and provide our feedback after analysis.
@wishy.em,
I have evaluated your code segment and found there is an issue with your code segment. You have called auto-fit row operation at the start even before inserting the values, so you have to move the line of code after inserting the values to the cells and before retrieving the height of the row. See the updated code segment for your reference, I have tested it works fine:
e.g
Sample code:
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
try {
worksheet.autoFitColumns(0,1);
RowCollection rows = worksheet.getCells().getRows();
ColumnCollection cols = worksheet.getCells().getColumns();
Row row = rows.get(0);
for (int j = 0; j < 4; j++) {
Column column = cols.get(j);
Style colStyle = column.getStyle();
StyleFlag flag = new StyleFlag();
colStyle.setTextWrapped(true);
flag.setWrapText(true);
column.applyStyle(colStyle, flag);
String cellText = StringUtils.repeat("very very", (int)Math.pow(2, j));
row.get(j).putValue(cellText);
}
//normalize col width
for (int j = 0; j < 4; j++) {
Column column = cols.get(j);
if (column.getWidth() > 30) {
column.setWidth(30);
}
}
worksheet.autoFitRow(0);
//get row height
double rowHeight = rows.get(0).getHeight();
System.out.println("RowHeight: " + rowHeight);
workbook.save("f:\\files\\TestRowHeight.xlsx", FileFormatType.XLSX);
} catch (Exception e) {
System.out.println("Exception: " + e.getMessage());
}
Sounds good. Thank you.
I am just curious how come autoFitColumns can be set before setting data but autoFitRows has to be set after setting data?
@wishy.em,
Well, both auto-fit rows and auto-fit cols APIs work the same way (both APIs should be called after inserting data). I noticed your initial line of code i.e., worksheet.autoFitColumns(0,1); does not do anything as there is no data inserted at that time, so the column’s width is retained to default “8.43”. In short, you may remove this line as it is not needed.
In my application I call both autoFitRows(start, end) and autoFitColumns(start, end) after setting data. Column works and row does not.
However if I do autoFitRow just before calling height, it seems to work fine.
@wishy.em,
Aspose.Cells works the same way as MS Excel does for auto-fitting rows and columns features. I think you may exercise auto-fitting columns and rows operations in Ms Excel manually for better understanding. If there is some data in a cell and you apply (for example) auto-fit column operation first, you will see that column width would be expanded (based on the whole long data in the cell). So, if you then apply auto-fit rows operation, it won’t work any longer (it won’t wrap text). The reason is the data would have been already expanded width wise, so there would be no data for wrapping text in multiple lines in the cells. Same case is true when you apply auto-fit rows operation first.
Hope, this helps.
Yeah I am experimenting on excel manually and then implementing the same in Aspose. I will try what you said. Thanks