Forum Discussion

Cosimo's avatar
Cosimo
Contributor II
3 years ago
Solved

TableView - Setting data type to TableView columns

Hey all, I'm struggling to figure out how to define a TableViewColumn to be of type Decimal instead of the default type being Text. I'm in the section where I create a TableViewRowHeader and addi...
Spreadsheet
  • JackLacava's avatar
    JackLacava
    3 years ago

    I figured it out.

    The problem happens if you try to add visible headers. The minute you call tableView.Rows.Add, the system will ignore the isHeader property and just assume that any cell in the row must conform to the datatype you've declared for the column (you cannot override datatype per row, it's either the entire column or nothing; that also means you can save yourself the trouble to assign it to every cell).

    So really there are only two options at this point: strings everywhere, or no headers in your TableView.

    Here's a working implementation that renounces headers:

    	Dim dt As New DataTable("FakeData")
	dt.Columns.Add("SomeDescription", GetType(String))
	dt.Columns.Add("DecimalNumber", GetType(Decimal))
	dt.Rows.Add("Row one", 10.5)
	dt.Rows.Add("Row Two", 11.0)

	Dim tv As TableView = New TableView()
	tv.CanModifyData = False
	
	' column definitions
	Dim tvHeader As New TableViewRow()
	For Each dtCol As DataColumn In dt.Columns
		Dim tvCol1 As New TableViewColumn()
		tvCol1.Name = dtCol.ColumnName
		tvCol1.Value = dtCol.ColumnName
		If tvCol1.Name.XFEqualsIgnoreCase("DecimalNumber") Then
			tvCol1.DataType = XFDataType.Decimal
		End If
		tvCol1.IsHeader = True
		tv.Columns.Add(tvCol1)
		' uncomment this to break
		' tvHeader.Items.Add(tvCol1.Name, tvCol1)
	Next
	' uncomment this to break
	' tv.Rows.Add(tvHeader)
		
	' data
	For Each row In dt.Rows
		Dim tvDataRow1 As New TableViewRow()
		For Each tvCol As TableViewColumn In tv.Columns
			Dim col As New TableViewColumn()
			col.Name = tvCol.Name
			col.Value = row.Item(tvCol.Name)
			col.IsHeader = False
			tvDataRow1.Items.Add(col.Name, col)
		Next
		tv.Rows.Add(tvDataRow1)
	Next
	Return tv

     I would probably consider this a bug, or at the very least something that should be documented. Feel free to submit it and claim the glory 😉

JackLacava's avatar
JackLacava
Community Manager
3 years ago

#worksforme

I'm on v7.0 and the following code works as expected:

 

Dim tv As TableView = New TableView()
tv.CanModifyData = False
Dim tvHeader As New TableViewRow()
Dim tvCol1 As New TableViewColumn()
tvCol1.Name = "Test"
tvCol1.Value = "Test"
tvCol1.DataType = XFDataType.Decimal
tvCol1.IsHeader = True
tv.Columns.Add(tvCol1)
tvHeader.Items.Add(tvCol1.Name, tvCol1)

Dim tvDataRow1 As New TableViewRow()
For Each tvCol As TableViewColumn In tv.Columns
	Dim col As New TableViewColumn()
	col.Name = tvCol.Name
	col.Value = Decimal.Parse("10.5")
	col.DataType = XFDataType.Decimal
	col.IsHeader = False
	tvDataRow1.Items.Add(col.Name, col)
Next
tv.Rows.Add(tvDataRow1)
Return tv

 

I suspect the trick is to mark THE HEADER COLUMN, as well as the data column, with the correct datatype.

Edit: for the record, CreateTableViewColumn is not in the library, it's a simple utility function that most Spreadsheet example rules define at the bottom. I will paste it here, but I would recommend you build your own version if you need to manipulate the datatype, because (as you can see) this doesn't do it.

Private Function CreateTableViewColumn(ByVal columnName As String, ByVal columnValue As String, ByVal isHeader As Boolean) As TableViewColumn
	Dim tableViewColumn As New TableViewColumn()	
	tableViewColumn.Name = columnName
	tableViewColumn.Value = columnValue
	tableViewColumn.IsHeader = isHeader
	Return tableViewColumn
End Function

 

  • db_pdx's avatar
    db_pdx
    Valued Contributor
    3 years ago

    Here's a slightly more realistic example that does not work.  It reproduces the error Cosimo mentions.  Can you see anything wrong with this?

    	'A more realistic way of bringing in data
	Dim dt As New DataTable("FakeData")
	dt.Columns.Add("SomeDescription", GetType(String))
	dt.Columns.Add("DecimalNumber", GetType(Decimal))
	dt.Rows.Add("Row one", 10.5)
	
	'Create the TableView Object
		Dim tableView As New TableView()
		'Create the columns in the Table View using the Data Table columns
		'Header row
		Dim tableViewRowHeader As New TableViewRow()
		For Each dc As DataColumn In dt.Columns
			Dim tc As New TableViewColumn()
			tc.Name = dc.ColumnName
			tc.Value = dc.ColumnName
			If tc.Name = "DecimalNumber" Then
				tc.DataType = XFDataType.Decimal
			End If
			tc.IsHeader = True
			tableView.Columns.Add(tc)
			tableViewRowHeader.Items.Add(tc.Name, tc)
		Next dc
		tableView.Rows.Add(tableViewRowHeader)

		For Each dr As DataRow In dt.Rows
		Dim tableViewRow As New TableViewRow()
			For Each tableViewColumn As TableViewColumn In tableView.Columns
				If tableViewColumn.Name = "DecimalNumber" Then
					Dim tcValue As Decimal
					Dim tc As New TableViewColumn()
					tc.Name = tableViewColumn.Name
					tc.DataType = XFDataType.Decimal
					tcValue = dr.Item(tableViewColumn.Name)
					tc.Value = tcValue
					tc.IsHeader = False
					tableViewRow.Items.Add(tableViewColumn.Name, tc)
				Else
					Dim tcValue As String = ""
					Dim tc As New TableViewColumn()
					tc.Name = tableViewColumn.Name
					tcValue = dr.Item(tableViewColumn.Name)
					tc.Value = tcValue
					tc.IsHeader = False
					tableViewRow.Items.Add(tableViewColumn.Name, tc)
				End If
			Next tableViewColumn
			tableView.Rows.Add(tableViewRow)
		Next dr
		Return tableView
    • JackLacava's avatar
      JackLacava
      Community Manager
      3 years ago

      I figured it out.

      The problem happens if you try to add visible headers. The minute you call tableView.Rows.Add, the system will ignore the isHeader property and just assume that any cell in the row must conform to the datatype you've declared for the column (you cannot override datatype per row, it's either the entire column or nothing; that also means you can save yourself the trouble to assign it to every cell).

      So really there are only two options at this point: strings everywhere, or no headers in your TableView.

      Here's a working implementation that renounces headers:

      	Dim dt As New DataTable("FakeData")
	dt.Columns.Add("SomeDescription", GetType(String))
	dt.Columns.Add("DecimalNumber", GetType(Decimal))
	dt.Rows.Add("Row one", 10.5)
	dt.Rows.Add("Row Two", 11.0)

	Dim tv As TableView = New TableView()
	tv.CanModifyData = False
	
	' column definitions
	Dim tvHeader As New TableViewRow()
	For Each dtCol As DataColumn In dt.Columns
		Dim tvCol1 As New TableViewColumn()
		tvCol1.Name = dtCol.ColumnName
		tvCol1.Value = dtCol.ColumnName
		If tvCol1.Name.XFEqualsIgnoreCase("DecimalNumber") Then
			tvCol1.DataType = XFDataType.Decimal
		End If
		tvCol1.IsHeader = True
		tv.Columns.Add(tvCol1)
		' uncomment this to break
		' tvHeader.Items.Add(tvCol1.Name, tvCol1)
	Next
	' uncomment this to break
	' tv.Rows.Add(tvHeader)
		
	' data
	For Each row In dt.Rows
		Dim tvDataRow1 As New TableViewRow()
		For Each tvCol As TableViewColumn In tv.Columns
			Dim col As New TableViewColumn()
			col.Name = tvCol.Name
			col.Value = row.Item(tvCol.Name)
			col.IsHeader = False
			tvDataRow1.Items.Add(col.Name, col)
		Next
		tv.Rows.Add(tvDataRow1)
	Next
	Return tv

       I would probably consider this a bug, or at the very least something that should be documented. Feel free to submit it and claim the glory 😉

      • db_pdx's avatar
        db_pdx
        Valued Contributor
        3 years ago

        Thanks for the follow-up Jack, much appreciated!  As a moderator, do you have an inside track for bug reporting and documentation updates?  If so, I'll let you submit it.

    • JackLacava's avatar
      JackLacava
      Community Manager
      3 years ago

      I suspect the issue is a weird situation due to how the data in the DataTable is retrieved. I tried with accessing the data via row index and it worked, so maybe something happens in the For Each loop - maybe looping by row index would work better? Sadly I'm going on holiday right now and cannot check, but I'll have a go next week.

  • Cosimo's avatar
    Cosimo
    Contributor II
    3 years ago

    Thanks Jack. I'll run some tests to confirm that both the column in the TableView and the TableViewRowHeader needs to be assigned the datatype separately.  Still a weird object model but when it works.. it works. 

    Also thanks for clarifying the function CreateTableViewColumn. It's not documented in the TableView user guide so someone will need to update that.