Cannot Fit Spreadsheet Columns to Content

Posted by: vijay on 20 February 2023, 9:01 am EST

    • Post Options:
    • Link

    Posted 20 February 2023, 9:01 am EST


    I have a simple sheet, displaying data in columns. I am trying to have the sheet automatically size each column to fit its content using the GetPreferredWidth method, but it is not using the column header text to determine the ideal width, even though the documentation says that it should. Here is my code for sizing the columns:

    Public Sub Post_Setup_Spread_Formatting(Sprd As FarPoint.Win.Spread.FpSpread)

        With Sprd.ActiveSheet
            ' Size columns
            For c As Integer = 0 To .Columns.Count - 1
                Dim sz As Single = .Columns(c).GetPreferredWidth
                .Columns(c).Width = sz
        End With
    End Sub

    I have disabled WordWrap for all of the column headers as follows (also in documentation):

        Dim ch As New FarPoint.Win.Spread.CellType.EnhancedColumnHeaderRenderer
        ch.WordWrap = False
        fpFeeds.ActiveSheet.ColumnHeader.DefaultStyle.Renderer = ch

    I have also tried using the GetPreferredColumnWidth method of the sheet, setting the ignoreheaders parameter to False, but that doesn’t do anything. In fact, for any given column the values returned by:

    fpFeeds.Sheets(0).Columns(x).GetPreferredWidth, fpFeeds.Sheets(0).GetPreferredColumnWidth(x, True), and fpFeeds.Sheets(0).GetPreferredColumnWidth(x, False)

    are all the same.

    One thing I noticed is that setting the Font of the spread did not change the fonts of the columns or of the column headers, so I manually changed those before trying to size the columns, but that did not fix things.

    So I am at my wits end with this. What can I do? Thank you.


  • Posted 20 February 2023, 10:57 pm EST

    Hello Vijay,

    Apologies for the inconvenience caused.

    You can use the following overload of the GetPreferredColumnWidth method of the SheetView class to get the column width based on data and header part both. You need to set the excludeWordWrap parameter to false to get your desired value.

    Please find the following code implementing the same:

    For i As Integer = 0 To fpSpread1.ActiveSheet.Columns.Count - 1
        Dim sz As Single = fpSpread1.ActiveSheet.GetPreferredColumnWidth(i, False, False, False, False)
        fpSpread1.ActiveSheet.Columns(i).Width = sz

    I hope it resolved the issue at your end. If you need any other help, please feel free to ask.


    Prabhat Sharma.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels