Is there a way to use best fit for the columns in the excel document when exporting? Currently, the columns are all crammed up and require the user to clean up the column widths.
Thanks,
Chris.
Export to XLSX - How do you make the columns of the document use best-fit?
Answers approved by DevExpress Support
created 15 years ago
(modified 12 years ago)
Chris,
Thank you for the feedback.
To accomplish this task, please set the GridView.OptionsPrint.AutoWidth and GridView.OptionsView.ColumnAutoWidth to false, and call the GridView.BestFitColumns() method.
Please try this solution, and let us know the results.
Thanks,
Andrew
Show previous comments
(2)
Hi Brandon,
Yes this solution should work correctly. Please check the GridView.OptionsPrint.AutoWidth and GridView.OptionsView.ColumnAutoWidth properties values and call the GridView.BestFitColumns() method before performing export. If it does not help, please provide us with a small sample (create a separate thread please) illustrating the issue. We will examine it and do our best to help you.
EA
12 years ago
Andrew's solution worked for me. The only exception was a Currency column.
For my app, I have a hidden GridControl, with the .DataSource set to List(of MyClass)
The column in question ("Cost") is populated by an Integer field, with the .DisplayFormat propert is set to Numeric "c0"
When the exported excel file is opened, the column values are displayed as "#####" because the column width is not wide enough. The workaround I use is to multiply the Cost's column width by 1.2, adding 20% to the column width.
Visual BasicExportGridView.BestFitColumns() ' re-size columns
colCost.Width *= 1.2 ' currency columns need 20% more width
ExportGridControl.ExportToXlsx(FullFileName) ' export to Excel 2007
Hello Eric,
Thank you for your feedback. I am glad to hear that the issue has been resolved. Please contact us if you experience any further difficulties.
Chris,
Unfortunately, it's not possible.
However, you can adjust the width of corresponding columns before generating a report. You can use the TextRenderer.MeasureText() method to calculate the required width.
Please feel free to contact us if you need any additional assistance. We will be happy to help you.
Thanks,
Andrew
Just to be clear. I am using a XtraGrid control and when I click ExportToXlsx(), I get an xlsx file with a bunch of squished up columns. They don't reflect the width of the columns they represent on the grid. I don't see how adjusting the width will help. I'm attaching an image to illustrate.
My first question was a bit vague. I know the answer may not change, but I just want to make sure your answer applies to my situation.
Thanks,
Chris.