Negative numeric values for GetTableView function - Table Views

[joakimo]

Hi,

In the GetTableView function of a Spreadsheet BR, I extract data from a database table (XFW_TLP_Register). For a numeric value (e.g. NCode1) negative numbers are by default extracted to Excel as varchar (with “,” as decimal separator) and positive numbers as numeric values (with “.” as decimal separator). Hence, the value cannot be submitted back since it’s trying to submit a text value into a numeric database column. If I cast the column (in my SQL query in the GetTableView function) to numeric it works but then it will not display digits in the Excel. Any idea how to solve this?

 

[MarcusH]

We process the result data table and change the values to invariant culture in a table view. And then return the table view rather than the data table.

' Create the table view object
Dim tv As New TableView()

' Create the header row object
Dim tvhr As New TableViewRow()
tvhr.IsHeader = True

' Create the columns
' dt is the datatable containing the data
For Each dc As DataColumn In dt.Columns
    'Create the column and set them as headers
    Dim tvc = Me.CreateTableViewColumn(si, dc.ColumnName, dc, True, dc.ColumnName)
    tv.Columns.Add(tvc)
    'Populate the header row
    tvhr.items.Add(tvc.Name, tvc)
Next

'Add the header row to the table view
tv.Rows.Add(tvhr)

Return tv

.....

And this is the function to create the table view:

' Function to create a new column for table views
Private Function CreateTableViewColumn(ByVal si As SessionInfo, ByVal columnName As String, ByVal dc As DataColumn, ByVal isHeader As Boolean, ByVal value As String) As TableViewColumn
    Dim tvc As New TableViewColumn()
    tvc.Name = columnName
    
    tvc.IsHeader = isHeader
    tvc.Value = value

    If dc.DataType = GetType(String)
        tvc.DataType = XFDataType.Text
    Else If dc.DataType = GetType(Decimal)
        If Not isHeader And Not String.IsNullOrEmpty(value.ToString) Then
            'Converts decimal numbers back to standard / invariant culture (i.e: using '.')
            'so it works regardless Of User Culture
            tvc.Value = Decimal.Parse(value).XFToStringForFormula
        End If
        tvc.DataType = XFDataType.Decimal				
    Else If dc.DataType = GetType(Integer)
        tvc.DataType = XFDataType.Int16
    Else If dc.DataType = GetType(Boolean)
        tvc.DataType = XFDataType.Boolean
    Else If dc.DataType = GetType(Date) Or dc.DataType = GetType(DateTime)
        tvc.DataType = XFDataType.DateTime
    End If
    
    Return tvc					

End Function 

The table view then has properties such as 

tv.HeaderFormat and 

tv.Columns.Item(2).ColumnFormat.ColumnWidth = 20

[joakimo]

Hi Marcus,

Thanks for your reply. I am using the same function but still get the issue.