Part Eight - Reading a Range of Cells from Excel

Using the data file of sales information from Part Seven, you will write a C# application that will determine which item sold the most units and which item was responsible for the most revenue. The spreadsheet only has data for each sale, so we will have to write some loops to read through the cells and summarize information.

Step One - Interface Design

Be sure to name the buttons and the two labels that change before you write any code.

You should be able to copy and paste code from Part Seven to make the Browse button work. And you should be able to copy and paste code to make sure the file does exist.

Step Two - Connect to Excel

Also add the code to declare MyApp, MyBook, and MySheet.

The MySheet object contains a lot of information. As we saw in Part Seven, MySheet contains the contents of each cell. The property "MySheet.UsedRange" contains the range of cells that are actually used for something. We can use this information to determine how many lines and columns need to be processed. Here is some debugging code from my version of this program:

            // declare variables to hold the size of this spreadsheet
            Excel.Range  MyRange     =  MySheet.UsedRange;
            int          column_cnt  =  MyRange.Columns.Count;
            int          row_cnt     =  MyRange.Rows.Count;

            // debugging message
            MessageBox.Show("columns = " + column_cnt.ToString() + "\nrows = " + row_cnt.ToString());

Step Three - Read a Range

            int next_units;   // temporary value;
            int sum = 0;      // total number of items
            for (int r = 2; r <= row_cnt; r++)
            {
                next_units = (int)(MySheet.Cells[r, 5] as Excel.Range).Value;
                sum += next_units;
            }
            MessageBox.Show ("Total Units = " + sum.ToString());

That code shows how to loop through the Excel file, but it does not answer any question our application is supposed to answer. Our program is supposed to tell the user which item had the highest number of units sold. So, we need to modify this loop so that pencils are all added together, and pen sets are added together, etc.

It would be nice if C# had associative arrays. Associative arrays use names as indexes instead of numbers. Suppose "Units" is a normal array. Units[0] can hold a value. If Units was an associate array, then Units["pens"] could hold a value.

Associative arrays would make our code easier to write. We could just read the type of item sold from column 4 and the number of units from column 5 and do something like this:

        next_item  = (String)(MySheet.Cells[r, 4] as Excel.Range).Value;
        next_units = (int)(MySheet.Cells[r, 5] as Excel.Range).Value;
        Units[next_item] += next_units;

C# has something close to an associative array. The data type is called a Dictionary.

Step Four - Add a Dictionary

The code to declare this dictionary is

            Dictionary<string, int> units = new Dictionary<string, int>();

The method "ContainsKey(name)" will return true or false to tell us if that item name is already in the dictionary.

The method "Add (name, value)" adds a new item with that integer value.

If something is already in the dictionary, we can retrieve its value simply by accessing it like an array. For example
      int number_of_pencils = units["pencil"];

So, now we can cycle through each row. Each time through the loop we retrieve that row's data out of columns 4, 5 and 7.  If an item is not yet in the dictionary then we should add it to the dictionary. If the item is already in the dictionary then we just add this row's item count to the value that is already in the dictionary. Here is some of that code:

            string next_item;   // column D of the sheet
            int next_units;     // column E
            float next_total;   // column G

            // use data in each row to build the big list
            for (int r = 2; r <= row_cnt; r++)
            {
                // retrieve next row's data from the Excel file
                next_item = (String)(MySheet.Cells[r, 4] as Excel.Range).Value;
                next_units =   (int)(MySheet.Cells[r, 5] as Excel.Range).Value;
                next_total = (float)(MySheet.Cells[r, 7] as Excel.Range).Value;

                // is that a new item?
                if (!units.ContainsKey(next_item))
                {
                    units.Add(next_item, next_units);
                }
                // if item already in list, then add the new values
                else
                {
                    figure this out on your own
                }
            }

Step Five - Loop Through the Dictionary

Here is some debugging code from my version. I wanted to make sure my totals were correct. So after the loop above had summed up all the pencils, desks, etc., I used this loop to tell me each total. It pops up several message boxes. Each message box tells me the total for each type of item. I used Excel to double check my math.

            foreach (KeyValuePair<string, int> pair in units)
            {
                string msg = pair.Key + pair.Value.ToString();
                MessageBox.Show (msg);
            }

Step Six - Finish the Code

Now you finish the coding.