Home Products Downloads Purchase Support Forum About Blog

Format DataGridView Cells

by viblend 7. June 2012 17:32
In this post, we will demonstrate you how to format the Cells in the VIBlend DataGridView for WinForms.

To achieve this, follow these steps:  

1.Create a new instance of the vDataGridView via drag and drop from the toolbox or create it programmatically.  
2.Bind the DataGrid to the Northwind Invoices table( Nwind.mdb is included in the download of the VIBlend Controls for WinForms package).  

NwindDataSetTableAdapters.InvoicesTableAdapter adapter = new DataBindingAndEditors.NwindDataSetTableAdapters.InvoicesTableAdapter();

DataTable tbl = this.nwindDataSet1.Invoices;

vDataGridView1.DataSource = null;

for (int i = 0; i < tbl.Columns.Count; i++)
    DataColumn col = tbl.Columns[i];
    BoundField boundField = new BoundField(col.Caption, col.ColumnName);

vDataGridView1.DataSource = tbl;



Dim adapter As NwindDataSetTableAdapters.InvoicesTableAdapter = New DataBindingAndEditors.NwindDataSetTableAdapters.InvoicesTableAdapter()

Dim tbl As DataTable = Me.nwindDataSet1.Invoices

vDataGridView1.DataSource = Nothing

Dim i As Integer = 0

Do While i < tbl.Columns.Count
    Dim col As DataColumn = tbl.Columns(i)
    Dim boundField As BoundField = New BoundField(col.Caption, col.ColumnName)
    i += 1

vDataGridView1.DataSource = tbl


3. Set a custom format string to an entire grid column. 

int columnIndex = this.nwindDataSet1.Invoices.Columns.IndexOf(this.nwindDataSet1.Invoices.RequiredDateColumn);
vDataGridView1.ColumnsHierarchy.Items[columnIndex].CellsFormatString = "{0:yyyy/MMMM/dd}";


Dim columnIndex As Integer = Me.nwindDataSet1.Invoices.Columns.IndexOf(Me.nwindDataSet1.Invoices.RequiredDateColumn)
vDataGridView1.ColumnsHierarchy.Items(columnIndex).CellsFormatString = "{0:yyyy/MMMM/dd}"


Creating Rows and Columns Hierarchies in Unbound Mode

by viblend 3. April 2012 16:46

In this post, we will show you how to create rows and column hierarchies in unbound mode and fill the DataGrid cells with data. In order the add Columns in unbound mode, you should use the DataGrid.ColumnsHierarchy.Items.Add method. To add rows in unbound mode, use the DataGrid.RowsHierarchy.Items.Add method. The 'Add' method returns a HierarchyItem object. This allows you to add sub-items to the HierarchyItem because it has Items.Add method, too.

The code below creates a 2-level Rows Hierarchy and can be used with the VIBlend DataGrid control for Silverlight, WPF and Windows Forms. 


this.DataGrid.RowsHierarchy.CompactStyleRenderingEnabled = false;
for (int i = 0; i < 10; i++)
    HierarchyItem row = this.DataGrid.RowsHierarchy.Items.Add("Row" + i);
    HierarchyItem column = this.DataGrid.ColumnsHierarchy.Items.Add("Column" + i);

    for (int j = 0; j < 3; j++)
        HierarchyItem subRow = row.Items.Add("Row" + i + "." + j);

Me.DataGrid.RowsHierarchy.CompactStyleRenderingEnabled = False
For i As Integer = 0 To 9
    Dim row As HierarchyItem = Me.DataGrid.RowsHierarchy.Items.Add("Row" & i)
    Dim column As HierarchyItem = Me.DataGrid.ColumnsHierarchy.Items.Add("Column" & i)

    For j As Integer = 0 To 2
        Dim subRow As HierarchyItem = row.Items.Add("Row" & i + AscW("." )+ j)
    Next j
Next i

To Fill the DataGrid with data in unbound mode, you should use the DataGrid.CellsArea.SetCellValue method. The method accepts three parameteres - row, column and the cell value.


for (int i = 0; i < 10; i++)
    HierarchyItem row = this.DataGrid.RowsHierarchy.Items[i];
    for (int j = 0; j < 10; j++)
        HierarchyItem column = this.DataGrid.ColumnsHierarchy.Items[j];
        this.DataGrid.CellsArea.SetCellValue(row, column, "Cell" + i + "." + j);
        for (int p = 0; p < 3; p++)
            this.DataGrid.CellsArea.SetCellValue(row.Items[p], column, "Cell" + p + "." + j);             

For i As Integer = 0 To 9
    Dim row As HierarchyItem = Me.DataGrid.RowsHierarchy.Items(i)
    For j As Integer = 0 To 9
        Dim column As HierarchyItem = Me.DataGrid.ColumnsHierarchy.Items(j)
        Me.DataGrid.CellsArea.SetCellValue(row, column, "Cell" & i + AscW("." )+ j)
        For p As Integer = 0 To 2
            Me.DataGrid.CellsArea.SetCellValue(row.Items(p), column, "Cell" & p + AscW("." )+ j)
        Next p
    Next j
Next i


Bind the VIBlend DataGridView for WinForms to SQL Database

by viblend 16. March 2012 00:01

This blog post will show you how to bind the WinForms DataGridView to the Customer table of the AdventureWorksLT Database.

To achieve this, follow these steps:  

1. Create a new instance of the vDataGridView via drag and drop from the toolbox or create it programmatically.  

2. Create a new AdventureWorksLTDataSet. In order to create it, do the following: Select Data->Add New DataSource->DataBase->DataSet. Add a connection to the AdventureWorksLT Database and Click the 'Next' button. Then expand the 'Tables' tree node, select Customer and click the 'Finish' button.

Note: The AdventureWorksLT Database is installed with the installation of the SQL Server. However, you can also download and install it from  


3. Create a new instance of the AdventureWorksLTDataSet data set. Then create a new CustomerTableAdapter and fill the Customer table with data. Finally, bind the VIBlend DataGrid for WinForms to the Customer table. 


AdventureWorksLTDataSet dataSet = new AdventureWorksLTDataSet();
AdventureWorksLTDataSetTableAdapters.CustomerTableAdapter adapter = new AdventureWorksLTDataSetTableAdapters.CustomerTableAdapter();
this.vDataGridView1.VIBlendTheme = VIBlend.Utilities.VIBLEND_THEME.OFFICE2010BLUE;
this.vDataGridView1.DataSource = dataSet.Customer;


Dim dataSet As New AdventureWorksLTDataSet()
Dim adapter As New AdventureWorksLTDataSetTableAdapters.CustomerTableAdapter()
Me.vDataGridView1.VIBlendTheme = VIBlend.Utilities.VIBLEND_THEME.OFFICE2010BLUE
Me.vDataGridView1.DataSource = dataSet.Customer


What's coming with VIBlend WinForms Controls 9.0

by viblend 3. March 2012 21:20
In the next version of VIBlend, we will enhance the vDataGridView control by adding a new Search capabilities to it. Users will be able to choose a search criteria and easily find a Row in the Grid. This new feature can be turned on by drag and drop of a vDataGridViewSearchHeader control and setting its DataGridView property to point to your vDataGridView instance.

VIBlend Silverlight Controls 6.0 With New Metro UI Themes

by viblend 18. January 2012 16:47

We are happy to announce the availability of VIBlend Silverlight Controls 6.0.  The newest version of VIBlend Silverlight Controls includes a set of new Metro UI Themes - Metro Blue, Metro Green and Metro Orange themes.

To download an evaluation version, please visit our Download page. 

View the updated Online Demo.


WinForms Grid with Merged Cells

by viblend 4. January 2012 01:06
In this post, we will show you how to use the cells merge functionality of VIBlend DataGridView for WinForms.

The first step is to add the DataGridView to your Form. You can do this by drag and drop from the Visual Studio toolbox or you can create it in the code behind.


private vDataGridView grid = new vDataGridView();

public Form1()
    grid.Dock = DockStyle.Fill;
    grid.VIBlendTheme = VIBLEND_THEME.NERO;


Private grid As New vDataGridView()

Public Sub New()
    grid.Dock = DockStyle.Fill
    grid.VIBlendTheme = VIBLEND_THEME.NERO
End Sub

Now, we need to implement the LoadData, MergeCells and SetCellValues methods. The LoadData method will add rows and columns to the DataGridView.


private void LoadData()
    // add columns.
    for (int i = 1; i <= 8; i++)
        grid.ColumnsHierarchy.Items.Add("Column " + i);
    // add rows.
    for (int i = 1; i <= 25; i++)
        grid.RowsHierarchy.Items.Add("Row " + i);



Private Sub LoadData()
    ' add columns.
    For i As Integer = 1 To 8
        grid.ColumnsHierarchy.Items.Add("Column " & i)
    Next i
    ' add rows.
    For i As Integer = 1 To 25
        grid.RowsHierarchy.Items.Add("Row " & i)
    Next i

End Sub

To add rows and columns we use the Add method of the DataGridView's Rows and Columns hierarchies.

The SetCellValues method will simply insert values in the Grid cells. This will be achieved by using the SetCellValue method which accepts three parameters - row, column and cell value.


private void SetCellsValues()
    HierarchyItem rowItem1 = grid.RowsHierarchy.Items[0];
    HierarchyItem columnItem1 = grid.ColumnsHierarchy.Items[0];

    grid.CellsArea.SetCellValue(rowItem1, columnItem1, "Monthly Housing and Transportation Expenses");
    grid.CellsArea.SetCellValue(grid.RowsHierarchy.Items[1], columnItem1, "Primary residence");
    grid.CellsArea.SetCellValue(grid.RowsHierarchy.Items[1], this.grid.ColumnsHierarchy.Items[3], "Transportation Expenses");
    // fill data

    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[2], columnItem1, "Mortgage Payment");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[3], columnItem1, "Property Tax");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[4], columnItem1, "Insurance");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[5], columnItem1, "Electricity");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[6], columnItem1, "Water");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[7], columnItem1, "Cabel TV Service");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[8], columnItem1, "High Speed Internet");

    // fill second column data.
    HierarchyItem columnItem2 = this.grid.ColumnsHierarchy.Items[1];
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[2], columnItem2, "$1,459,76");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[3], columnItem2, "$212.54");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[4], columnItem2, "$49.21");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[5], columnItem2, "$73.44");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[6], columnItem2, "$41.48");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[7], columnItem2, "$22.14");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[8], columnItem2, "$24.99");

    // fill third column data.

    HierarchyItem columnItem3 = this.grid.ColumnsHierarchy.Items[3];
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[2], columnItem3, "Vehicle 1 Payment");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[3], columnItem3, "Vehicle 1 Insurance");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[4], columnItem3, "Vehicle 1 Gas");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[5], columnItem3, "Vehicle 1 Maintenance");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[6], columnItem3, "Vehicle 2 Lease");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[7], columnItem3, "Vehicle 2 Insurance");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[8], columnItem3, "Vehicle 2 Gas");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[9], columnItem3, "Vehicle 2 Maintenance");

    // fiil fourth column data.
    HierarchyItem columnItem4 = this.grid.ColumnsHierarchy.Items[4];
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[2], columnItem4, "$351,34");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[3], columnItem4, "$55.12");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[4], columnItem4, "$129.21");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[5], columnItem4, "$55.17");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[6], columnItem4, "$311.12");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[7], columnItem4, "$109.35");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[8], columnItem4, "$114.99");
    grid.CellsArea.SetCellValue(this.grid.RowsHierarchy.Items[9], columnItem4, "$35.19");


Private Sub SetCellsValues()
    Dim rowItem1 As HierarchyItem = grid.RowsHierarchy.Items(0)
    Dim columnItem1 As HierarchyItem = grid.ColumnsHierarchy.Items(0)

    grid.CellsArea.SetCellValue(rowItem1, columnItem1, "Monthly Housing and Transportation Expenses")
    grid.CellsArea.SetCellValue(grid.RowsHierarchy.Items(1), columnItem1, "Primary residence")
    grid.CellsArea.SetCellValue(grid.RowsHierarchy.Items(1), Me.grid.ColumnsHierarchy.Items(3), "Transportation Expenses")
    ' fill data

    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(2), columnItem1, "Mortgage Payment")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(3), columnItem1, "Property Tax")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(4), columnItem1, "Insurance")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(5), columnItem1, "Electricity")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(6), columnItem1, "Water")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(7), columnItem1, "Cabel TV Service")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(8), columnItem1, "High Speed Internet")

    ' fill second column data.
    Dim columnItem2 As HierarchyItem = Me.grid.ColumnsHierarchy.Items(1)
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(2), columnItem2, "$1,459,76")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(3), columnItem2, "$212.54")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(4), columnItem2, "$49.21")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(5), columnItem2, "$73.44")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(6), columnItem2, "$41.48")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(7), columnItem2, "$22.14")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(8), columnItem2, "$24.99")

    ' fill third column data.

    Dim columnItem3 As HierarchyItem = Me.grid.ColumnsHierarchy.Items(3)
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(2), columnItem3, "Vehicle 1 Payment")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(3), columnItem3, "Vehicle 1 Insurance")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(4), columnItem3, "Vehicle 1 Gas")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(5), columnItem3, "Vehicle 1 Maintenance")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(6), columnItem3, "Vehicle 2 Lease")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(7), columnItem3, "Vehicle 2 Insurance")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(8), columnItem3, "Vehicle 2 Gas")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(9), columnItem3, "Vehicle 2 Maintenance")

    ' fiil fourth column data.
    Dim columnItem4 As HierarchyItem = Me.grid.ColumnsHierarchy.Items(4)
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(2), columnItem4, "$351,34")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(3), columnItem4, "$55.12")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(4), columnItem4, "$129.21")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(5), columnItem4, "$55.17")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(6), columnItem4, "$311.12")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(7), columnItem4, "$109.35")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(8), columnItem4, "$114.99")
    grid.CellsArea.SetCellValue(Me.grid.RowsHierarchy.Items(9), columnItem4, "$35.19")
    columnItem3.Width = 150
    columnItem1.Width = 150
End Sub

The MergeCells method will lllustrate how to merge grid cells by using the SetCellSpan method. When you use the SetCellSpan method, you need to pass four parameters to it - row, column, rows span and columns span. In the method we will merge cells in the first and second rows and we will also set custom visual style to the merged cells.


private void MergeCells()
    HierarchyItem rowItem1 = grid.RowsHierarchy.Items[0];
    HierarchyItem columnItem1 = grid.ColumnsHierarchy.Items[0];

    // create a custom cell style.
    GridCellStyle style = new GridCellStyle();
    style.FillStyle = new FillStyleSolid(Color.FromArgb(255, 0, 175, 240));
    style.Font = new Font(this.Font.FontFamily, 11.0f, FontStyle.Bold);
    style.TextColor = Color.White;

    GridCellStyle orangestyle = new GridCellStyle();
    orangestyle.FillStyle = new FillStyleSolid(Color.FromArgb(255, 254, 122, 1));
    orangestyle.Font = new Font(this.Font.FontFamily, 10.0f, FontStyle.Bold);
    orangestyle.TextColor = Color.White;

    grid.CellsArea.SetCellTextAlignment(rowItem1, columnItem1, ContentAlignment.MiddleCenter);
    // set the cell span to 1 row and 5 columns.
    grid.CellsArea.SetCellSpan(rowItem1, columnItem1, 1, 5);
    // set the merged cell value and style.
    grid.CellsArea.SetCellDrawStyle(rowItem1, columnItem1, style);
    // set the cell span to 1 row and 2 columns.
    grid.CellsArea.SetCellSpan(grid.RowsHierarchy.Items[1], columnItem1, 1, 3);
    grid.CellsArea.SetCellDrawStyle(grid.RowsHierarchy.Items[1], columnItem1, orangestyle);
    // set the merged cell value.
    // set the cell span to 1 row and 2 columns.
    grid.CellsArea.SetCellSpan(grid.RowsHierarchy.Items[1], this.grid.ColumnsHierarchy.Items[3], 1, 2);
    // set the merged cell value.
    grid.CellsArea.SetCellDrawStyle(grid.RowsHierarchy.Items[1], this.grid.ColumnsHierarchy.Items[3], orangestyle);


Private Sub MergeCells()
    Dim rowItem1 As HierarchyItem = grid.RowsHierarchy.Items(0)
    Dim columnItem1 As HierarchyItem = grid.ColumnsHierarchy.Items(0)

    ' create a custom cell style.
    Dim style As New GridCellStyle()
    style.FillStyle = New FillStyleSolid(Color.FromArgb(255, 0, 175, 240))
    style.Font = New Font(Me.Font.FontFamily, 11.0f, FontStyle.Bold)
    style.TextColor = Color.White

    Dim orangestyle As New GridCellStyle()
    orangestyle.FillStyle = New FillStyleSolid(Color.FromArgb(255, 254, 122, 1))
    orangestyle.Font = New Font(Me.Font.FontFamily, 10.0f, FontStyle.Bold)
    orangestyle.TextColor = Color.White

    grid.CellsArea.SetCellTextAlignment(rowItem1, columnItem1, ContentAlignment.MiddleCenter)
    ' set the cell span to 1 row and 5 columns.
    grid.CellsArea.SetCellSpan(rowItem1, columnItem1, 1, 5)
    ' set the merged cell value and style.
    grid.CellsArea.SetCellDrawStyle(rowItem1, columnItem1, style)
    ' set the cell span to 1 row and 2 columns.
    grid.CellsArea.SetCellSpan(grid.RowsHierarchy.Items(1), columnItem1, 1, 3)
    grid.CellsArea.SetCellDrawStyle(grid.RowsHierarchy.Items(1), columnItem1, orangestyle)
    ' set the merged cell value.
    ' set the cell span to 1 row and 2 columns.
    grid.CellsArea.SetCellSpan(grid.RowsHierarchy.Items(1), Me.grid.ColumnsHierarchy.Items(3), 1, 2)
    ' set the merged cell value.
    grid.CellsArea.SetCellDrawStyle(grid.RowsHierarchy.Items(1), Me.grid.ColumnsHierarchy.Items(3), orangestyle)
End Sub

New Pivot Table aggregation functions coming to VIBlend WPF DataGrid

by viblend 10. March 2011 02:59

We are adding several important pivot tables improvements in VIBlend WPF DataGrid. The upcoming release will feature four additional data aggregation functions: StDev, StDevP, Var and VarP. The StDev function calculates the standard deviation on a sample of values while the StDevP function calculates it on the entire population of values. The Var and VarP functions calculate the variance on a sample of values and on the entire population. With these new functions you will be able to load the WPF DataGrid with a set of data records, pivot by any column and see the aggregated results. All this can be expressed entirely in XAML:

    x:Name="pivotGrid1" >
      <viblend:BoundField Text="Country" DataField="Country"/>
      <viblend:BoundField Text="Region" DataField="Region"/>
      <viblend:BoundField Text="City" DataField="City"/>
      <viblend:BoundField Text="Shipping Company" DataField="ShipperCompany"/>
    <viblend:BoundValueField Text="Standard Deviation"


Another important key feature that you can expect in the new release is the Pivot Design Panel. The Pivot Design panel provides an intuitive and easy to use drag and drop interface for defining pivot table views. You can choose the pivot rows, pivot columns, pivot filters, data fields and data aggregation functions.  This solution is ideal for building a wide range of in-house OLAP and BI applications. The Pivot Design panel is already available in our Silverlight and Windows Forms suites.

Pivot Grid - Traditional vs Compact style layout

by viblend 18. April 2010 05:36

VIBlend DataGridView for WinForms offers multiple unique features that cannot be found in other Windows Forms data grid controls. These include multi-level hierarchies on rows and columns, rows grouping, pivot tables, and built-in OLAP capabilities. One of the main drawbacks of the traditional OLAP style rows layout is that it takes a lot of screen space. 


This could be improved by using the compact style layout. In this mode the rows of the pivot table are rendered in a tree-like structure.


Switching from default to compact style rendering is very easy. It requires setting the value of RowsHierarchy.CompactStyleRendering property to true:

grid.RowsHierarchy.CompactStyleRenderingEnabled = true;

VB .Net
grid.RowsHierarchy.CompactStyleRenderingEnabled = True

You can switch between traditional and compact style rendering at runtime. The feature is also availalbe in VIBlend DataGrid for Silverlight.

Cross-platform OLAP with VIBlend Silverlight Pivot Grid

by viblend 27. November 2009 07:50

In a typical data grid, the data is flat and consists of many rows and columns. The data may contain cells with same values and working with the raw data may not be the best way to spot patterns and analyze trends. Many data grid controls provide rows grouping functionality which allows you to group multiple rows by columns where the corresponding grid cells have the same value.
Pivot tables go one step further and enable powerful data analysis. A Pivot table consists of rows, columns and data (value/fact) fields.
VIBlend DataGrid for Silverlight features a built-in data aggregation engine which is capable of turning any tabular data source into a wide variety of cross tab views. This is similar to the Pivot tables functionality in Excel. Creating a Pivot table with VIBlend DataGrid for Silverlight is very easy. In fact, the data binding to the data source is no different than data binding any other control to a collection. Assume that you have a table with five columns: Country, Region, City, ShipperCompany and ExtendedPrice. You need to choose the columns from the data source and add them to the BoundFields collection:


pivotGrid1.BoundFields.Add(new BoundField("Country", "Country"));
pivotGrid1.BoundFields.Add(new BoundField("Region", "Region"));
pivotGrid1.BoundFields.Add(new BoundField("City", "City"));
pivotGrid1.BoundFields.Add(new BoundField(
"Shipper Company ", "ShipperCompany"));
pivotGrid1.BoundFields.Add(new BoundField(
"ExtendedPrice", "ExtendedPrice"));

Using this code, once you data bind to the data source, the grid will display all table rows and the five columns: Country, Region, City, Carrier, Extended Price.
However, let’s look at a scenario where we want to show count of sales, amount of sales, and average sale amount grouped by Country, by Region and by City, and do that for each Shipper Company.
This requires only a few extra lines of code:

1. Assign the Country, Region and City fields to form the Rows hierarchy:


pivotGrid1.BoundPivotRows.Add(new BoundField("Country", "Country"));
pivotGrid1.BoundPivotRows.Add(new BoundField("Region", "Region"));
pivotGrid1.BoundPivotRows.Add(new BoundField("City", "City"));

2. Assign the ShipperCompany field to form the Columns hierarchy:


pivotGrid1.BoundPivotColumns.Add(new BoundField(
"Shipper Company", "ShipperCompany"));

3. Assign the ExtendedPrice field to form the facts/values for count of sales, sales amount, and average sale amount:


pivotGrid1.BoundPivotValues.Add(new BoundValueField("Count of Sales", "ExtendedPrice", PivotFieldFunction.Count));
pivotGrid1.BoundPivotValues.Add(new BoundValueField("Amount of Sales", "ExtendedPrice", PivotFieldFunction.Sum));
pivotGrid1.BoundPivotValues.Add(new BoundValueField("Avg Sale Amount", "ExtendedPrice", PivotFieldFunction.Average));

4. Specify if the value/fact columns appear attached to the rows or to the columns:


pivotGrid1.PivotValuesOnRows = false;

During the data binding process, the OLAP engine in VIBlend DataGrid for Silverlight will parse the table content, build the rows and columns hierarchies, and compute the pivot table cell values. The result will be the following pivot table view:

Silverlight Pivot Table

You can learn more and see a Live demo at:

Using VIBlend DataGridView for WinForms in Virtual Mode

by viblend 7. July 2009 18:35

VIBlend DataGridView for WinForms allows you to set the grid cells' source to Virtual mode. There are two steps to setup Virtual mode:

1. Set the cells' data source to Virtual. You can do that for a specific cell as well as for all cells under a row or a column:

  vDataGridView1.CellsArea.SetCellDataSource(column, GridCellDataSource.Virtual);

2. Implement a GridCellValueNeeded event handler. Whenever the data grid renders a cell it will raise the GridCellValueNeeded event:

    vDataGridView1.GridCellValueNeeded += new vDataGridView.GridCellValueNeededEventHandler(DataGridView1_GridCellValueNeeded);

    void DataGridView1_GridCellValueNeeded(object sender, GridCellValueNeededEventArgs args)

      args.Value = "Some text";


Here's a complete example which creates a list of 50,000 rows and sets up the grid in virtual mode:

using System;
using System.Collections.Generic;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using VIBlend.WinForms.Controls;
using VIBlend.WinForms.DataGridView;
using VIBlend.Utilities;

namespace VirtualModeDemo
    public partial class Form1 : Form
        public Form1()

        public class EmployeeSales
            public EmployeeSales(string Name, DateTime Date, int ProductId, int Quantity)
                this.Name = Name;
                this.Date = Date;
                this.ProductId = ProductId;
                this.Quantity = Quantity;

            #region Private Members
            private string _name;
            private DateTime _date;
            private int _productid;
            private int _quantity;

            #region Properties
            public string Name
                get { return _name; }
                set { _name = value; }

            public DateTime Date
                get { return _date; }
                set { _date = value; }

            public string ProductName
                get { return productNames[_productid, 0]; }


            public double SalesAmount
                get { return (double)_quantity * UnitPrice; }

            public double UnitPrice
                    double unitPrice = double.Parse(productNames[_productid, 1]);
                    return unitPrice;

            public int Quantity
                get { return _quantity; }
                set { _quantity = value; }

            public int ProductId
                get { return _productid; }
                set { _productid = value; }


        // Implement the cell value needed event handler
        void DataGridView1_GridCellValueNeeded(object sender, GridCellValueNeededEventArgs args)
            int rowIndex = int.Parse(args.RowItem.Caption) - 1;

            if (args.ColumnItem.Caption == "Employee Name")
                args.CellValue = lst[rowIndex].Name;
            else if (args.ColumnItem.Caption == "Transaction Date")
                args.CellValue = lst[rowIndex].Date;
            else if (args.ColumnItem.Caption == "Product Name")
                args.CellValue = lst[rowIndex].ProductName;
            else if (args.ColumnItem.Caption == "Quantity")
                args.CellValue = lst[rowIndex].Quantity;
            else if (args.ColumnItem.Caption == "Unit Price")
                args.CellValue = lst[rowIndex].UnitPrice;
            else if (args.ColumnItem.Caption == "Transaction Amount")
                args.CellValue = lst[rowIndex].SalesAmount;

        #region Data
        string[] firstNames = new string[]

        string[] lastNames = new string[]

        static string[,] productNames = new string[,]
                    {"Black Tea", "1.95"},
                    {"Green Tea", "1.95"},
                    {"Caffe Espresso", "1.45"},
                    {"Doubleshot Espresso", "1.75"},
                    {"Caffe Latte", "2.25"},
                    {"White Chocolate Mocha", "2.35"},
                    {"Cramel Latte", "2.35"},
                    {"Caffe Americano", "1.65"},
                    {"Cappuccino", "2.10"},
                    {"Espresso Truffle", "2.45"},
                    {"Espresso con Panna", "1.81"},
                    {"Peppermint Mocha Twist", "1.99"}

        List<EmployeeSales> lst = new List<EmployeeSales>();

        private void Form1_Load(object sender, EventArgs e)
            vDataGridView1.RowsHierarchy.AllowResize = true;
            vDataGridView1.RowsHierarchy.AllowDragDrop = false;
            vDataGridView1.ColumnsHierarchy.AllowResize = true;
            vDataGridView1.ColumnsHierarchy.AllowDragDrop = false;

            vDataGridView1.SelectionMode = vDataGridView.SELECTION_MODE.FULL_ROW_SELECT;
            vDataGridView1.MultipleSelectionEnabled = true;

            vDataGridView1.GridCellValueNeeded += new vDataGridView.GridCellValueNeededEventHandler(DataGridView1_GridCellValueNeeded);

            const int rowsToLoad = 50000;

            // Generate test data      
            Random rand = new Random();

            for (int i = 0; i < rowsToLoad; i++)
                int productId = rand.Next(0, productNames.Length / 2 - 1);
                int quantity = rand.Next(1, 5);
                lst.Add(new EmployeeSales(
                    string.Format("{0} {1}", firstNames[rand.Next(0, firstNames.Length - 1)], lastNames[rand.Next(0, lastNames.Length - 1)]),
                    DateTime.Now.AddDays(-rand.Next(10, 100)),


        private void BindGridVirtualMode()

            Cursor.Current = Cursors.WaitCursor;


            vDataGridView1.CellsArea.CellFormatting.SetFormatter("defaultNumberFormatter", null, "{0:#,###.####}");

            #region Prepare the grid columns
            vDataGridView1.ColumnsHierarchy.Fixed = true;

            vDataGridView1.ColumnsHierarchy.Items.Add("Employee Name");
            vDataGridView1.ColumnsHierarchy.Items.Add("Transaction Date");
            vDataGridView1.ColumnsHierarchy.Items.Add("Product Name");
            vDataGridView1.ColumnsHierarchy.Items.Add("Unit Price");
            vDataGridView1.ColumnsHierarchy.Items.Add("Transaction Amount");

            // Set the cells data source to Virtual for all columns
            foreach (HierarchyItem column in vDataGridView1.ColumnsHierarchy.Items)
                vDataGridView1.CellsArea.SetCellDataSource(column, GridCellDataSource.Virtual);


            vDataGridView1.CellsArea.CellFormatting.SetFormatter("dateFormatter", new System.Globalization.DateTimeFormatInfo(), "{0:dd-MMM-yyyy}");
            vDataGridView1.CellsArea.CellFormatting.SetFormatter("dollarFormatter", null, "${0:#.##}");

            vDataGridView1.CellsArea.CellFormatting.SetCellFormatting(vDataGridView1.ColumnsHierarchy.Items[1], "dateFormatter");
            vDataGridView1.CellsArea.CellFormatting.SetCellFormatting(vDataGridView1.ColumnsHierarchy.Items[4], "dollarFormatter");
            vDataGridView1.CellsArea.CellFormatting.SetCellFormatting(vDataGridView1.ColumnsHierarchy.Items[5], "dollarFormatter");


            #region Prepare the grid rows
            for (int i = 0; i < lst.Count; i++)
                vDataGridView1.RowsHierarchy.Items.Add((i + 1).ToString());

            vDataGridView1.RowsHierarchy.SetColumnWidth(0, 30);
            vDataGridView1.RowsHierarchy.Fixed = true;


            Cursor.Current = Cursors.Default;


About the author

Some text that describes me

Tag cloud

Recent comments

Comment RSS
Copyright © 2011 VIBlend  
Terms of Use | Privacy Policy
WinForms Controls Purchase Online About Us
DataGrid Navigation Pane Technical Support Blog
ScrollBar TreeView
ListBox ProgressBar Downloads Register
ComboBox Buttons
TabControl Editors Documentation Client Login

VIBlend Blog is powered by BlogEngine.NET