BuyLog In
Ticket T526559
Visible to All Users

Export to Excel column auto fit

created 8 years ago

I am exporting a data grid to Excel using ExportToXlsx.  Am having issue with column widths in Excel not being auto fit.

The code I use is:
GridView1.BestFitColumns()
GridView1.OptionsPrint.AutoWidth = False
GridView1.OptionsView.ColumnAutoWidth = False
GridView1.ExportToXlsx(Excel location and name)

The grid font is Tahoma 8.  The column widths are correct in the grid.

When I open the Excel file the font is Calibri 11 (the default for Excel).  From what I understand I can change the Excel default font/size for new worksheets only, am unable to have Excel open with a specific font/size of my choosing.  Because the Excel font size is larger than the grid font size column content can be larger than column size.

If I open the exported Excel file, change the font/size, and save, then if I reopen the file it retains the font/size I supplied.  However, when I export the file again and reopen the font goes back to Calibri 11.

I realize I could change the grid font but not something I prefer to do.

Is there any solution so the exported file will be Tahoma 8?

Sign in to comment on this post

Answers approved by DevExpress Support

created 8 years ago (modified 8 years ago)

Hello,

You can use the WYSIWYG export mode by setting the static ExportSettings.DefaultExportType property to ExportType.WYSIWYG.
As an alternative solution, set the required font in the XlsExportOptionsEx.CustomizeCell event handler as follows:

Visual Basic
Private Sub simpleButton1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles simpleButton1.Click  
    Dim options = New XlsxExportOptionsEx()  
    AddHandler options.CustomizeCell, AddressOf Options_CustomizeCell  
    gridView1.ExportToXlsx("test.xlsx", options)  
End Sub  
 Private Sub Options_CustomizeCell(ByVal e As CustomizeCellEventArgs)  
    e.Handled = True  
    e.Formatting.Font.Name = "Tahoma"  
    e.Formatting.Font.Size = 8.25  
End Sub

Let me know if you have additional questions.

    Comments (2)
    JH JH
    James Hinson 8 years ago

      Thanks, but I cannot use WYSIWYG.

      Second solution worked fine.  Thanks for assistance.

      DevExpress Support Team 8 years ago

        You're welcome!

        Sign in to comment on this post

        Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

        Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.