Dear Wisej Support Team,
I am working on a Visual Basic .NET application and trying to connect a Wisej.NET KendoUI PivotGrid to a DataTable. My requirement is very simple: I want all columns of my DataTable to appear in the filter section of the PivotGrid, without any additional features like calculations, rows, or columns in other sections. Despite my efforts, including converting the sample code from the following link and testing it with manual data, I have not succeeded.
Link to Sample Code: https://github.com/iceteagroup/wisej-demobrowser/blob/main/Wisej.DemoBrowser.Premium/Wisej.Web.Ext.Kendo.Demo/Component/kendoPivotGrid.cs
I would greatly appreciate it if you could provide a simple sample program or guide that demonstrates how to achieve this. The solution should be straightforward, using Visual Basic .NET, and allow me to quickly understand and implement it.
Thank you for your assistance!
Best regards,
reza kaboli
Below is a basic example to connect a Wisej.NET KendoUI PivotGrid to a DataTable with all columns in the filter section. This code assumes you have Wisej.NET and KendoUI extensions installed in your project.
Imports Wisej.Web
Imports Wisej.Web.Ext.Kendo
Imports System.Data
Public Class Form1
Inherits Form
Private WithEvents kendoPivotGrid1 As KendoPivotGrid
Public Sub New()
InitializeComponent()
End Sub
Private Sub InitializeComponent()
‘ Create PivotGrid
kendoPivotGrid1 = New KendoPivotGrid()
kendoPivotGrid1.Location = New Point(10, 10)
kendoPivotGrid1.Size = New Size(800, 600)
Me.Controls.Add(kendoPivotGrid1)
‘ Create sample DataTable
Dim dt As New DataTable(“SampleData”)
dt.Columns.Add(“ID”, GetType(Integer))
dt.Columns.Add(“Name”, GetType(String))
dt.Columns.Add(“Price”, GetType(Double))
dt.Rows.Add(1, “Product A”, 10.5)
dt.Rows.Add(2, “Product B”, 20.0)
dt.Rows.Add(3, “Product C”, 15.75)
‘ Configure PivotGrid DataSource
Dim dataSourceOptions As Object = New With {
.type = “local”,
.data = ConvertDataTableToDynamicArray(dt),
.schema = New With {
.model = New With {
.fields = CreateFields(dt)
}
},
.filters = CreateFilters(dt) ‘ All columns in filters
}
Dim options As Object = New With {
.dataSource = dataSourceOptions,
.autoBind = True,
.height = 600,
.showFieldList = True
}
kendoPivotGrid1.Options = options
kendoPivotGrid1.Update()
End Sub
Private Function ConvertDataTableToDynamicArray(dt As DataTable) As Object()
Dim dataArray As New List(Of Object)
For Each row As DataRow In dt.Rows
Dim expando As New System.Dynamic.ExpandoObject()
Dim expandoDict As IDictionary(Of String, Object) = expando
For Each col As DataColumn In dt.Columns
Dim value As Object = If(IsDBNull(row(col)), Nothing, row(col))
If col.DataType Is GetType(Integer) OrElse col.DataType Is GetType(Double) Then
expandoDict(col.ColumnName) = Convert.ToDouble(value)
Else
expandoDict(col.ColumnName) = If(value IsNot Nothing, value.ToString(), “”)
End If
Next
dataArray.Add(expando)
Next
Return dataArray.ToArray()
End Function
Private Function CreateFields(dt As DataTable) As Object
Dim fields As New Object()
fields = New With {
.ID = New With {.type = “number”},
.Name = New With {.type = “string”},
.Price = New With {.type = “number”}
}
Return fields
End Function
Private Function CreateFilters(dt As DataTable) As Object()
Dim filtersList As New List(Of Object)
For Each col As DataColumn In dt.Columns
filtersList.Add(New With {.name = col.ColumnName})
Next
Return filtersList.ToArray()
End Function
Public Shared Sub Main()
Application.Run(New Form1())
End Sub
End Class