It is not clear how to auto fit a column. I see there is a ShrinkToFit property but that does not change anything is set to True or not. Basically, for now, I define all the columns with a specified width. But, sometimes, there are a lot of columns which are not needed that width depending on the data which is in it. Is there support for that?
Hi,
Have you tried:
xls.AutofitCol(column, false, 1.1); ?
or if you want to autofit a range of columns:
xls.AutofitCol(first_column, last_column, false, 1.1); ?
this is of course using the API (XlsFile). If using FlexCelReport, look at the demo: 85.Autofit in the reports section. (just fire MainDemo.exe, and search for "autofit" in the search box)
No matter if you are using reports or the api, make sure to read the section: "Autofitting Rows and Columns" in UsingFlexCelAPI.pdf (documentation folder in the FlexCel installation). Autofit is a more complex thing than what it would like, as explained in that section.
Thanks, this worked.
A few suggestions:
1. Documentation might state to turn off wrap when autofit is used
2. Documentation might state to not set the width if autofit is used
3. Documentation might state to only set autofit after all the cell initialization is completed
1) isn't completely true:Cells will be autofitted even if wrap text is true. So if for example you write "supercalifragilisticexpialidocious" in a cell with wrap enabled and autofit the column, the column will be made big enough to hold the full word. But if you have "hello world" and wrap enabled, the column will be made only big enough to fit the biggest of "hello" and "world", since the text can wrap, and making the column big enough to hold the biggest word is enough. Cell with wrap enabled are autofitted, but only to make fit the biggest word, since that is all that is needed. But they are autofitted.
Also of course, when autofitting rows instead of columns, the cells that will be autofitted are those that have "wrap", since the others will always take a single row.
About 2) and 3), I am not sure if they are worth explaining, I think your confusion might be about something else, which might be better to make clear in the docs. The thing is, in Excel there isn't a property so columns autofit, "Autofit" is a command, not a property of the column. In Excel, the command is at the ribbon, home tab, "Format", "Autofit column width".
Once you understand it is a command, which what it does is to look at all the cells in the column and make the column as big as the biggest one, 2) and 3) become evident.It all depends in the order in which you do the commands.
For example, if you do:
xls.SetColWidth(1, 100);
xls.SetColWidth(1, 500);
You would expect that the column 1 will be 500, even if you set it to 100 first. In the same way if you do:
xls.AutofitCol(1);
xls.SetColWidth(1, 100);
you would expect that the column has a width of 100, even if you autofitted it first. And if you do:
xls.SetColWidth(1, 100);
xls.AutofitCol(1);
Then the column will be autofitted, and the 100 ignored. It all depends in the order you make hte commands, as in every other command.
Same for 3). If you do:
xls.SetCellValue(1,1,"hello");
xls.AutofitCol(1);
xls.SetCellValue(1,1,"this it a test");
Then the autofit will autofit the text "hello", since that was what was at the cell A1 when you run the autofit command. Of course, autofit can't know that you are later going to write "this is a test" in the same cell. So you do need to autofit after the cells have their final values, but this is self-evident.
As said, I thing the problem here is more in understanding that this is a command that looks at the cell values and sets the column widht to the biggest cell on them, and not a property of the Excel file which might be evaluated after everything is done. There isn't a property to automatically autofit columns in Excel.Do you think the explanation would be clearer if I focus in explaining that autofit is a command, not a property?
Regards,
Adrian.
Adrian Gallero2014-10-25 18:42:18