Creating a Chart from the Excel Data

Excel Chart Project: For C# and VB NET Students

 

In the previous lesson, you got which member of staff was selected from the dropdown list. In this lesson, we'll write code to grab data from our data array using that selected staff member. We'll then create a chart from it.

Double click your button to create a code stub. The first thing we can do is to do some error checking. Add this in C#

if (cmbStaff.Text == "Sales Staff")
{

MessageBox.Show("Select a value from the dropdown list");
return;

}

And this in VB:

If cmbStaff.Text = "Sales Staff" Then

MessageBox.Show("Select a value from the dropdown list")
Exit Sub

End If

The default text for our combo box is "Sales Staff". If it's still "Sales Staff" when the button is clicked then it means you haven't selected anything from the list. In which case, display a message and exit to button code.

What our button is going to do is to grab a single line from our arrayData array. This holds all the rows from the spreadsheet, remember. The line we'll grab depends on which staff member was selected in the dropdown. If it's the first staff member then a value of 0 will be stored in our getRowNumber variable. If the second item in the combo box is selected, getRowNumber will be 1, the third is 2, and so on. We can use that number from the combo box to grab a row from the arrayData array.

Add this line of code, just under your button's if statement:

C#

double[] arraySingleRow = new double[12];

VB:

Dim arraySingleRow(11) As Double

This sets up a new array which will hold just a single row from the bigger data array. You'll see why we're creating this array shortly.

Now grab a line from the big array and store into the new array with this loop in C#:

for (int i = 0; i < 12; i++)
{

arraySingleRow[i] = arrayData[getRowNumber, i];

}

And this in Visual Basic .Net:

For i = 0 To 11

arraySingleRow(i) = arrayData(getRowNumber, i)

Next i

When the loop is done, a line from arrayData will be grabbed and stored in arraySingleRow. In between the brackets of arrayData, we first have that getRowNumber variable. After the comma, we have our i loop variable. This will ensure that each item in the row will be accessed.

You've probably noticed the hard-coded 12 in C# and 11 in VB Net. That's because we know that we have 12 columns of data in the spreadsheet. The hard-coded values are not good coding practice, however. Instead, we could have created a new Class level variable and stored the number of columns like this:

arrayCols = dataRange.Columns.Count;

The arrayCols would be an integer variable set up near the top of the code. We could then do this for the loop (C#):

for (int i = 0; i <= arrayCols; i++)

But it's easier to understand with the hard-coded numbers. It's not good practise, though!

Anyway, after the loop, we can add the data to the chart. This is done with Series Points DataBinding. Add this line to your code in C#:

chartSales.Series["Series1"].Points.DataBindY(arraySingleRow);

And this in VB Net:

chartSales.Series("Series1").Points.DataBindY(arraySingleRow)

The "Series1" text between the round brackets of Series is the default name for a series (you can have more than one series). Then you refer to the Points property. After a dot, we have this:

DataBindY(arraySingleRow)

DataBindY is used to bind chart points to the Y Axis of a chart. In between round brackets, you type what your points are, or the name of an array where your points are stored, in our case.

Your code should look like this in C#:

C# code that uses Data Binding for a chart

And this in VB Net:

Visual Basic .Net code that uses Data Binding for a chart

You can try it out now. Run your program. Select one of the Sales Staff from the dropdown list. Now click your button. You should see this for Bob, the first employee:

A chart displaying data points grabbed from an Excel spreadsheet

Select a different name from the dropdown list, Mona Lisa, for example. The chart will change to this:

A second chart chart displaying data points

And all that data is coming from an Excel spreadsheet!

You can see from our chart, though, that it just has numbers on the bottom, the X Axis. What we'll do instead is to put the months of the year at the bottom, instead of 0 to 12. We'll do that in the next lesson.

Adding months for the X Axis >>

Back to the C# NET Contents Page

Back to the VB NET Contents Page