I am trying to do auto fit of column A in file “ap-Scheme_Inputsample15.xlsx”. The sample code is attached. I want to get an output file as attached file “AutoFitRowsandColumns_out2.xlsx” , in which the column A clearly displays all the data. I m using aspose cells for JAVA. I have attached the java code i have tried. Its not able to produce the desired output file.
PLease help on this.
Thanks…
Hi Aspose team,
please let me know if any update. Its urgent.
@shashimn,
I have tested your code and below is the console output and output excel file. Could you please share screenshots if it is not your expected output.
max row :18.
intermediate row value width, ht:54; 520
intermediate row value width, ht:59; 560
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:59; 560
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
intermediate row value width, ht:54; 400
AutoFitRowsandColumns_output.zip (8.7 KB)
Let us know your feedback.
That is not the output expected. This attached snapshot is the expected one.
The first column A width should be automatically expanded to see column A of all rows clearly without having to resize the column. Also the height of each row should be expanded so that all the text in column A is clearly visible, and there should be no need to manually expand row height. ExcelOutput.jpg (142.7 KB)
Please note, you should first extend the column’s width as per your desired output so the wrapping text option will work accordingly (after you call auto-fit row operation). This is same as MS Excel where you have to extend the first column’s width and then wrap the text to display the data as per your needs. See the updated sample code that works fine as I tested:
e.g.
Sample code:
String folderPath = "f:\\files\\" ;
String filePath = folderPath + "ap-Scheme_Inputsample15.xlsx";
Workbook workbook = null;
try {
workbook = new Workbook(filePath);
//for (int sheetindex =0 ; sheetindex < workbook.getWorksheets().getCount(); sheetindex++) {
Worksheet worksheet = workbook.getWorksheets().get("sheet1");
int maxRowIndex = worksheet.getCells().getMaxDataRow();
System.out.println("max row :" + maxRowIndex +".");
worksheet.getCells().setColumnWidthPixel(0,377);
for (int rowIndex =1 ; rowIndex <= maxRowIndex; rowIndex++) {
Style style1 = worksheet.getCells().get(rowIndex, 0).getStyle();
style1.setTextWrapped(true);
worksheet.getCells().get(rowIndex, 0).setStyle(style1);
worksheet.autoFitRow(rowIndex);
System.out.println("intermediate row value width, ht:" + worksheet.getCells().get(rowIndex, 0)
.getWidthOfValue() +"; "+ worksheet.getCells().get(rowIndex, 0).getHeightOfValue());
}
worksheet.autoFitColumn(0);
workbook.save(folderPath + "AutoFitRowsandColumns_out2" + ".xlsx", SaveFormat.XLSX);
} catch (Exception e) {
e.printStackTrace();
}
Hope, this helps a bit.
HI Amjad
I see that you have written one statement as :
worksheet.getCells().setColumnWidthPixel(0,377);
how do we get the value 377 ? I can have different lengths of text in each cell. The max lenght of text in a cell is not fixed, it can be dynamic. So how do I handle that situation?
I am afraid, there is no automatic way/option but to do manual extension of the column’s width. Even in MS Excel, you got to extend the column width (manually) as per your desired display (of data) and then set wrapping text attribute of the cell(s) and apply auto-fit operations (if required). Please note, if your data has manual line breaks (e.g. via using “\n” char with data) then you do not need to extend the column’s width as when wrapping the text and applying auto-fit row, it will display accordingly.
In short, you have to do extend column’s width accordingly before wrapping text. If you still think and find any built-in option of MS Excel that can accomplish the task automatically, do let us know with details (with steps involved) and sample file, we will check it on how to do it via Aspose.Cells APIs.