GrapeCity Forums

Hello,

Spread is a great product, but does have a few gotchas. One of them is getting Column.Width set properly. You would think that the default would be no WordWrap but that is not the case. To work around this issue, I create a SetupSheetView method in every application with code in it like below:

    Private Sub SetupFarPointSheetView(ByVal sheet As SheetView)
        Try
            With sheet
                .SelectionUnit = Model.SelectionUnit.Cell
                .DataAutoSizeColumns = False

                ' Set row headers to no wrap.
                Dim rowRendererNoWrapText As New CellType.RowHeaderRenderer
                rowRendererNoWrapText.WordWrap = False
                .RowHeader.DefaultStyle.Renderer = rowRendererNoWrapText

                ' Set column headers to no wrap.
                Dim columnRendererNoWrapText As New CellType.ColumnHeaderRenderer
                columnRendererNoWrapText.WordWrap = False
                .ColumnHeader.DefaultStyle.Renderer = columnRendererNoWrapText
            End With
        Catch ex As Exception
            Throw
        End Try
    End Sub 'SetupFarPointSheetView

Once you have done the above, then you can use the SheetView GetPreferredColumnWidth method which has some overloads pertaining to column headers, spans, etc to get your column widths to behave the way you would expect them to from reading about GetPreferredWidth in the docs.

    With Me.FpSpread1.ActiveSheet
         For Each column As Column In .Columns
            column.Width = .GetPreferredColumnWidth(column.Index, IgnoreHeaders:=False)
        Next
    End With

Hello,

Thank you for posting this code.

Here are the C# equivalents

 private void SetupFarPointSheetView(SheetView sheet)
{
    try {
        { 
            sheet.DataAutoSizeColumns = false;
           
            // Set row headers to no wrap.
            CellType.RowHeaderRenderer rowRendererNoWrapText = new CellType.RowHeaderRenderer();
            rowRendererNoWrapText.WordWrap = false;
            sheet.RowHeader.DefaultStyle.Renderer = rowRendererNoWrapText;
           
            // Set column headers to no wrap.
            CellType.ColumnHeaderRenderer columnRendererNoWrapText = new CellType.ColumnHeaderRenderer();
            columnRendererNoWrapText.WordWrap = false;
            sheet.ColumnHeader.DefaultStyle.Renderer = columnRendererNoWrapText;
        }
    }
    catch Exception {
        throw;
    }
}

Iterate the columns to set the width

{ 
    foreach (Column column in this.FpSpread1.ActiveSheet.Columns) { 
        column.Width = this.FpSpread1.ActiveSheet.GetPreferredColumnWidth(column.Index, IgnoreHeaders = false); 
    } 
} 
 

The GetPreferredColumnWidth() method is VERY slow compared to the old COM version.
(Apparently it has to take account of a lot of things that the old COM version did not have to worry about.)

In my own app I found that I was able to fetch (from a network database) and load the sheet almost 10 times faster than I could set the column widths! This meant that no matter how well I optimized my own code it was still a very slow process overall.
I was able to optimize the SetWidth() function considerably by doing things like simply spinning through the rows (for numeric and date type columns) to locating the cell with the greatest Text.Length. Then use MeasureString() on that one value to find the optimum width.

Similarly if you are working with columns that may be wider than the display window you probably want to set the width at slightly less than the client width rather than at the max width. In this case as soon as you find a width greater than your client width you can stop looping and simply set that value.

Even when the above special cases do not apply I found that spinning through the rows and using MeasureString() on the Cell.Text was faster than using the built in method. (Note that if your rows may be Grouped you need to add code to handle that situation also. (Ignore the Group header rows)

Mike

Hello Mike,

This is the intended behavior GetPreferredColumnWidth( ) method will try to re size the Columns based on longest string in a Column and in order to do that it has to go through every cell.You may call SuspendLayout( ) before calling GetPreferredColumnWidth( ) and then call ResumeLayout( ) method.

Thanks,