Column width problem with empty table

Posted by: j2associates on 8 September 2017, 12:56 pm EST

  • Posted 8 September 2017, 12:56 pm EST

    Hello all,


    I am using a multi row header for a bound spread control. Initially, the underlying datatable contains zero rows. After the initialization code shown below runs, the columns are too narrow and are all "squashed down". If I manually double click on the column dividers they autosize just fine. Once there is at least one row of data in the table they also autosize correctly.


    The attached jpeg shows before and after screen shots of the spread. What am I missing here that they are not displaying correctly? Thanks in advance for any ideas and/or suggestions! 

    <PRE class=coloredcode><SPAN class=kwd>With Me</SPAN>.FpSpread1.ActiveSheet
    .OperationMode = OperationMode.<SPAN class=kwd>ReadOnly</SPAN>
    .SelectionPolicy = Model.SelectionPolicy.<SPAN class=kwd>Single</SPAN>
    .AutoGenerateColumns = <SPAN class=kwd>False</SPAN>
    .DataAutoSizeColumns = <SPAN class=kwd>False</SPAN>

    .ColumnHeader.RowCount = 3

    .DataSource = m_DataSet.Tables(0)

    .RowCount = m_DataSet.Tables(0).Rows.Count
    .ColumnCount = m_DataSet.Tables(0).Columns.Count

    <SPAN class=cmt>' Set column headers to no wrap - this doesn't seem to do anything
    </SPAN>
    <SPAN class=kwd>Dim</SPAN> rendererNoWrapText <SPAN class=kwd>As New</SPAN> Spread.CellType.ColumnHeaderRenderer
    <SPAN class=kwd>For Each</SPAN> columnHeader <SPAN class=kwd>As</SPAN> Spread.Column <SPAN class=kwd>In</SPAN> .ColumnHeader.Columns
    columnHeader.Renderer = rendererNoWrapText
    <SPAN class=kwd>Next

    For Each</SPAN> column <SPAN class=kwd>As</SPAN> Spread.Column <SPAN class=kwd>In</SPAN> .Columns
    column.Width = column.GetPreferredWidth()
    <SPAN class=cmt>'column.Width = .GetPreferredColumnWidth(column.Index, False, True)
    </SPAN>

    <SPAN class=kwd>Dim</SPAN> columnName <SPAN class=kwd>As String</SPAN> = m_DataSet.Tables(0).Columns(column.Index).ColumnName
    column.DataField = columnName

    <SPAN class=kwd>If</SPAN> m_VisibleColumns.ContainsKey(columnName) <SPAN class=kwd>Then</SPAN>
    column.Visible = <SPAN class=kwd>True

    If</SPAN> m_AlternateHeaders.ContainsKey(columnName) <SPAN class=kwd>Then
    Dim</SPAN> headers() <SPAN class=kwd>As String</SPAN> = m_AlternateHeaders.Item(columnName).Trim.Split(<SPAN class=st>" "</SPAN>c)
    <SPAN class=kwd>Select Case</SPAN> headers.Length
    <SPAN class=kwd>Case</SPAN> 0, 1
    .ColumnHeader.Cells(0, column.Index).Value = <SPAN class=kwd>Nothing</SPAN>
    .ColumnHeader.Cells(1, column.Index).Value = <SPAN class=kwd>Nothing</SPAN>
    .ColumnHeader.Cells(2, column.Index).Value = column.DataField
    <SPAN class=kwd>Case</SPAN> 2
    .ColumnHeader.Cells(0, column.Index).Value = <SPAN class=kwd>Nothing</SPAN>
    .ColumnHeader.Cells(1, column.Index).Value = headers(0)
    .ColumnHeader.Cells(2, column.Index).Value = headers(1)
    <SPAN class=kwd>Case</SPAN> 3
    .ColumnHeader.Cells(0, column.Index).Value = headers(0)
    .ColumnHeader.Cells(1, column.Index).Value = headers(1)
    .ColumnHeader.Cells(2, column.Index).Value = headers(2)
    <SPAN class=kwd>Case Else
    Throw New</SPAN> ArgumentException(<SPAN class=st>"Invalid Alternate Header"</SPAN> & vbNewLine & m_AlternateHeaders.Item(column.DataField).Trim)
    <SPAN class=kwd>End Select
    End If
    Else</SPAN>
    column.Visible = <SPAN class=kwd>False
    End If
    Next
    End With</SPAN>
    <FONT face="Times New Roman"></FONT></PRE>
    2007/12/GetPreferredWidth issue.JPG
  • Replied 8 September 2017, 12:56 pm EST

    Hey Gina,


    Yes, I am using 3.0.2005.2005. In your example above, your columns are string types which will default to a wider column. Add the following code which has a long column name for an integer column and see what you get:

    <PRE class=coloredcode>div.Columns.Add(<SPAN class=st>"SocialSecurityNumber"</SPAN>, <SPAN class=kwd>GetType</SPAN>(<SPAN class=kwd>Integer</SPAN>))
    .
    .
    FpSpread1.Sheets(0).Columns(1).Width = _
    FpSpread1.ActiveSheet.GetPreferredColumnWidth(0, <SPAN class=kwd>False</SPAN>)
    FpSpread1.Sheets(0).Columns(2).Width = _
    FpSpread1.ActiveSheet.GetPreferredColumnWidth(0, <SPAN class=kwd>False</SPAN>)
    </PRE> 
  • Replied 8 September 2017, 12:56 pm EST

    I tried this code.  I changed the .columns(1).width = ...(0,false) to (1,false).  The width of the column did match the width of the header text.  If you try basic code like this, do you still have the problem with the header text going to the next line?

    pSpread1.Sheets(0).RowCount = 0
            FpSpread1.Sheets(0).ColumnHeader.RowCount = 3
            Dim ds As New DataSet()
            Dim emp As New DataTable("Employees")
            Dim div As New DataTable("Division")
            emp.Columns.Add("LastName")
            emp.Columns.Add("FirstName")
            div.Columns.Add("SocialSecurityNumber", GetType(Integer))
            div.Columns.Add("Test", GetType(Integer))
            ds.Tables.AddRange(New DataTable() {emp, div})
            FpSpread1.DataSource = ds
            FpSpread1.DataMember = "Division"
            FpSpread1.Sheets(0).Columns(0).Width = FpSpread1.ActiveSheet.GetPreferredColumnWidth(0, False)
            FpSpread1.Sheets(0).Columns(1).Width = FpSpread1.ActiveSheet.GetPreferredColumnWidth(1, False)
  • Replied 8 September 2017, 12:56 pm EST

    Are you using 3.0.2005.2005?  I tried the following and it looked okay:  I used the getpreferredcolumnwidth method that had the ignore headers option.

     FpSpread1.Sheets(0).RowCount = 0
            FpSpread1.Sheets(0).ColumnHeader.RowCount = 3
            Dim ds As New DataSet()
            Dim emp As New DataTable("Employees")
            Dim div As New DataTable("Division")
            emp.Columns.Add("LastName")
            emp.Columns.Add("FirstName")
            div.Columns.Add("Section")
            div.Columns.Add("Specialty")
            ds.Tables.AddRange(New DataTable() {emp, div})
            FpSpread1.DataSource = ds
            FpSpread1.DataMember = "Division"
            FpSpread1.Sheets(0).Columns(0).Width = FpSpread1.ActiveSheet.GetPreferredColumnWidth(0, False)
          
  • Replied 8 September 2017, 12:56 pm EST

    Hello,


    Attached is a stripped down project demonstrating the behavior I am describing. When I run it, I see all of the columns word wrapped like the first part of the jpeg I attached to my original post. As you can see, there are 150+ columns in the underlying data table. These are from a 3rd party application that I cannot control. We use the config file to only make a few of the columns visible. Thanks again for the help!


    2007/12/GetPreferredWidthIssue.zip
  • Replied 8 September 2017, 12:56 pm EST

    Hello,


    The problem is being caused by you trying to get the preferred width before the text is put in the header. You need to move the following line of code to the bottom of your if column visible code.

    <FONT size=2>

    column.Width = .GetPreferredColumnWidth(column.Index, </FONT><FONT color=#0000ff size=2>False</FONT><FONT size=2>)

    </FONT>
  • Replied 8 September 2017, 12:56 pm EST

    Thanks Scott. I couldn't see the forest for the trees.
Need extra support?

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

Learn More

Forum Channels