Developing a DataSource for your DataGridView
Imagine that you want to develop a DataSource that is capable to represent the data displayed in the following excel sheet
For each region the values are given. The YEAR value is defined as the sum of Q1, Q2, Q3 and Q4, eg: for EMEA this is SUM(B2:E2). The GLOBAL values are calculated as the sum of the regions for the quarter, eg: for Q1 this is SUM(B2:B5). We’ll start with the easiest task: Add columns to the DGV and define the headers
If we look at the DGV it becomes clear that each row exists out of a Label, values for Q1 to Q4 and the sum of those values. If we translate this to code we end up with something like: SalesRow
I’ve implemented INotifyPropertyChanged because i want the DGV to request and display the updated Year value when one of the quarterly values changes. Configure the Columns to use these properties as DataPropertyName and add a couple of SalesRow instances to a BindingList that we use as DataSource for the DGV.
public Form1()
{
InitializeComponent();
this.dataGridView1.AutoGenerateColumns = false;
this.ColumnRegion.DataPropertyName = "Label";
this.ColumnQ1.DataPropertyName = "Q1";
this.ColumnQ2.DataPropertyName = "Q2";
this.ColumnQ3.DataPropertyName = "Q3";
this.ColumnQ4.DataPropertyName = "Q4";
this.ColumnYear.DataPropertyName = "Year";
BindingList<salesRow> bindingList = new BindingList<salesRow>();
bindingList.Add(new SalesRow("EMEA", new int[] { 100, 100, 100, 100 }));
bindingList.Add(new SalesRow("LATAM", new int[] { 150, 150, 150, 150 }));
bindingList.Add(new SalesRow("APAC", new int[] { 100, 100, 100, 100 }));
bindingList.Add(new SalesRow("NORAM", new int[] { 150, 150, 150, 150 }));
this.dataGridView1.DataSource = bindingList;
}
Notice that the Year value is nicely updated when the user changes one of the quarterly values
In order to calculate the GLOBAL values we inherit from the SalesRow class and override the implementation for the Q1, Q2, Q3, Q4 and Year properties: GlobalSalesRow.
Finally we can wrap everyting in a SalesRows class to represent our Data. Hooking this code up is as easy as
public Form1()
{
InitializeComponent();
this.dataGridView1.AutoGenerateColumns = false;
this.ColumnRegion.DataPropertyName = "Label";
this.ColumnQ1.DataPropertyName = "Q1";
this.ColumnQ2.DataPropertyName = "Q2";
this.ColumnQ3.DataPropertyName = "Q3";
this.ColumnQ4.DataPropertyName = "Q4";
this.ColumnYear.DataPropertyName = "Year";
BindingList<salesRow> bindingList = new BindingList<salesRow>();
bindingList.Add(new SalesRow("EMEA", new int[] { 100, 100, 100, 100 }));
bindingList.Add(new SalesRow("LATAM", new int[] { 150, 150, 150, 150 }));
bindingList.Add(new SalesRow("APAC", new int[] { 100, 100, 100, 100 }));
bindingList.Add(new SalesRow("NORAM", new int[] { 150, 150, 150, 150 }));
SalesRows salesRows = new SalesRows(bindingList);
this.dataGridView1.DataMember = "Rows";
this.dataGridView1.DataSource = salesRows;
}
And here is a screenshot of the result
Feel free to download the complete project: DataGridViewDataSource.zip.