Cannot Fit Spreadsheet Columns to Content

Cannot Fit Spreadsheet Columns to Content

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

• 

  vijay

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 20 February 2023, 9:01 am EST

  Hello,

  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
          Next
      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.

  Vijay

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  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
  Next

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

  Regards,
  
  Prabhat Sharma.