Use Visual Studio 2008 C++/CLI to Automate Excel

Introduction and Disclaimers

A few years ago I wrote an article for this site titled “How to use Managed C++ to Automate Excel” using Microsoft’s Visual Studio (VS) 2003.

How to use Managed C++ to Automate Excel

Since that time Microsoft has come out with VS 2005 and VS 2008. There were some significant differences between VS 2003 and VS2005/VS2008. For one thing, Microsoft now refers to what was “Managed” code in VS2003 to “C++/CLI” code in VS2005 and VS2008. In addition, there have been some significant (to me at least) syntax changes between Managed code and C++/CLI code. One of the biggies is that the asterisk symbol (*) is no longer used in C++/CLI code. Instead the caret (^) or “hat” is used to denote a “tracking handle” which, in a lot of ways, kind of behaves like a pointer. I’m not going to try and go into the details of how they work (that’s not the purpose of this article and besides, I’m not smart enough to make it clear to you). Other syntax changes include implicit boxing and the ability to directly index. Both of these changes eliminate a lot of clunky code.

VS 2008 comes with Visual Studio Tools for Office. I haven’t looked closely at this feature yet but it seems to be directed toward .NET Framework 3.5. All the machines that I support use .NET Framework 2.0 so I’m sticking to the way that I migrated from earlier versions of VS. There is a lot of information about automating Excel at: msdn - Excel Tasks

I suggest you research that site. You won’t find many (if any at all) C++ examples there but if you are vaguely familiar with C# or VB, you may be able to interpret the C# or VB code to C++.

I will try and follow the same structure as I used in my previous article to demonstrate how to automate Excel. The main difference is that the code used herein will be C++/CLI compliant and will run in VS2008 (probably VS2005 as well). In my current job, I am responsible for developing and maintaining 17 Windows Forms applications and, with a very few exceptions, all are written in pure C++/CLI. My point is that I am able to do most everything in C++/CLI that I can do in native code. The exceptions are that I occasionally use a couple of native library functions for which I haven’t discovered an exact C++/CLI equivalent.

As a final disclaimer, I’ll repeat what I said in my previous article about my code. It works. It’s probably not the most efficient, or elegant, or even the most logical. I didn’t take the time to do a lot of digging and research when I converted my project from VS2003 to VS2005 and then to VS2008. But the code I came up with works with VS 2008 C++/CLI. I apologize in advance to all the “purists” out there who may be offended by what they might view as my ugly inefficient code and I’ll be happy to accept any suggestions for improvement, as long as you have tested them and they work!

Project Overview

The purpose of this article is to show you how I got Excel to work using MC++ in a Windows Forms Application so I’m not going to try and make this example very elaborate. I’ll be using made-up data in an imaginary circumstance. We will create a .NET Windows Forms Application and put a button on the form that will cause Excel to run and display a Workbook with three Worksheets. I’ll show you a way to delete and add Worksheets to the Workbook and a way to create bar charts and line charts and place them and their supporting data onto a Worksheet. This is what the finished product will look like if you are using Office 2003:

If you are using Office 2007, it will look like this (I’ll be using Office2007 in all my remaining examples):

Project Setup

1. I’m using Visual Studio 2008 and targeting .NET Framework 2.0. Start a new project in Visual Studio. I selected as my Project Type Visual C++ CLR and Windows Forms Application as the Template.

   

2. You will need to add an Office Primary Interop Assembly (PIA) for Excel to your References. You can find it by bringing up your project’s properties window and clicking the “Add New Reference” button. On the .Net tab, scroll down to “Microsoft.Office.Interop.Excel”. There are two assemblies on my machine, 11.0.0.0 and 12.0.0.0. I use 11.0.0.0 and it works with Office 2003 and Office 2007. I haven’t tried 12.0.0.0 but 11.0.0.0 works for me and that’s good enough for me. So select the one you want to try and click “OK”.

   

3. While you have the properties window open, select “Configuration Properties” in the left window then select ”Safe MSIL Common Language Runtime Support (/clr:safe)” as the “Common Language Runtime support” option.

   

4. Add the following line to your header file (Form1.h):
   C++

   using namespace Microsoft::Office::Interop::Excel;
   

5. I also added a line in the header file:
   C++

   #define Excel   Microsoft::Office::Interop::Excel
   

   This avoids having to type “”Microsoft::Office::Interop::Excel when I refer to an Excel method or property.

6. To avoid confusing the compiler between a System application and an Excel application, you will also have to change the lines in the main method in the Automate_Excel.cpp from:
   C++

   // Create the main window and run it
   Application::Run(gcnew Form1());
   return 0;

   To:

   C++

   System::Windows::Forms::Application::EnableVisualStyles();
   System::Windows::Forms::Application::SetCompatibleTextRenderingDefault(false);
   
   // Create the main window and run it
   System::Windows::Forms::Application::Run(gcnew Form1());
   
   return 0;

7. Put a button on Form1. I named mine butExcel and set the Text property to “Run Excel”.

8. Double-click the button to create an event handler in the Form1.h file:
   C++

   private: System::Void butExcel_Click(System::Object^ sender, System::EventArgs^ e) {
   }

If you compile and run, all you have at this point is:

In the next section we will create a method that will run Excel and call this method from the butExcel_Click event handler.

Code to make Excel Run

1. Add a method to your project that will create and run an Excel application.

void Form1::RunExcel()
{
      //1. Create a new Excel application with 3-sheet Workbook
      Excel::Application^ exApp = gcnew Excel::ApplicationClass();
      //2. Add a workbook (comes with three Worksheets)
      Workbook^   exWb  = exApp->Workbooks->Add(Type::Missing);
            .
            .
            .
      // Show the Workbook
      exApp->Visible = true;
}

Note: If you didn’t add the #define line I told you about in step 5 above to your header file, you will have to type “Microsoft::Office::Interop::Excel::Application” instead of “Excel::Application”.

2. Put a call to this method in the butExcel event handler:

private: System::Void butExcel_Click(System::Object^ sender, System::EventArgs^ e) {
    RunExcel();
}

3. Now compile and run. Clicking the Run Excel button should cause Excel to start and open a Workbook with three empty Worksheets in it.

Note: Working with Excel is the only place I’ve encountered extensive use of the Type::Missing argument. It causes the called method to use a default parameter value. You can probably find out more about them by using the Object Browser and/or going to Microsoft’s web site at the URL I mentioned in the beginning of this article.

Delete, Rename, Select, and Add a Worksheet

1. Let’s say you only want two worksheets in your Workbook. You can delete a Worksheet by referring to its order in the Sheets collection. Worksheet numbering starts at one (1) not zero (0). This line will delete the second (Sheet2) Worksheet:
   C++

   safe_cast<Worksheet^>(exApp->ActiveWorkbook->Sheets[2])->Delete();

2. Although I don’t do it in this example, you can add one or more Worksheets with the following line of code which adds two (2) Worksheets to the Workbook.
   C++

   exWb->Worksheets->Add(Type::Missing,Type::Missing,2,Type::Missing);

3. If you have several Worksheets and you want to work with a particular one, you need to make the one you want to work with your active Worksheet. You can do that with this line of code that makes the second Worksheet active:
   C++

   safe_cast<Worksheet^>(exApp->ActiveWorkbook->Sheets[2])->Select(Type::Missing);

4. You can create a variable reference to the active Worksheet’s tracking handle to be able to easily rename it and to pass it to the methods that will create charts. The active sheet is the first sheet after Workbook creation (I do this in the RunExcel() method shown below).
   C++

   Worksheet^  exWs = safe_cast<Worksheet^>(exApp->ActiveSheet);

5. To rename the active Worksheet do this:
   C++

   exWs->Name = "Charts";

   The Controlling Method

   I use the RunExcel() method to set up and control what happens in Excel. Here is the method in its entirety:

   C++

   void  Form1::RunExcel()
   {
         //1. Create a new Excel application
         Excel::Application^ exApp = gcnew Excel::ApplicationClass();
    
         //2. Add a workbook (comes with three Worksheets)
         Workbook^   exWb  = exApp->Workbooks->Add(Type::Missing);
    
         //3. Delete the last two worksheets
         safe_cast<Worksheet^>(exApp->ActiveWorkbook->Sheets[3])->Delete();
         safe_cast<Worksheet^>(exApp->ActiveWorkbook->Sheets[2])->Delete();
    
         //4. Create a variable for the active Worksheet's tracking handle
   //  (first Worksheet is the default active one)
         Worksheet^  exWs  = safe_cast<Worksheet^>(exApp->ActiveSheet);
    
         //5. Rename the active worksheet
         exWs->Name = "Charts";
    
         //6. Load the data
         LoadData();
    
         //7. Make the bar chart
         MakeBarChart(exWs, 2, 1);
    
         //8. Make a line chart
         MakeLineChart(exWs, 2, 8);
    
         // Show the Workbook
         exApp->Visible = true;
   }

   Here is what’s happening in the above steps:

   1. This step creates the application. If you didn’t use a define statement for Excel you will have to use the full path (Microsoft::Office::Interop::Excel) to reference Excel:
   2. The application created in step 1 is empty; there is no Workbook in it. This step adds a Workbook that contains three Worksheets (three get automatically added).
   3. Since I only plan to use one sheet, I delete the last two. Delete the last one first, works better that way.
   4. I will be passing a reference (tracking handle) for the Worksheet to the methods that build my charts so here I create a reference to the active Worksheet. When you add the Workbook to the application, the first Worksheet, Sheet1, is the default active Worksheet. You can make another Worksheet active with:
   C++

   safe_cast<Worksheet^>(exApp->ActiveWorkbook->Sheets->Item[3])->Select(Type::Missing);

   Where the number 3 is the third Worksheet in the collection.

6. This line renames the active Worksheet.
7. Call the method to load data. You need to get data from somewhere. In my real world application I read it from a file and store it in a global SortedList until I process it and am ready to put it onto my Worksheet. You can store data in a SortedList, Hashtable, Array, or some other data structure until you need it or you can put it on the Worksheet as you read it into your application, whichever works best for you. You can also obtain data from a database server using SQL commands. For this example, I use LoadData() to put some fake data into three global SortedLists.
8. This is a call to the method that will build a bar chart. I pass it a reference to the Worksheet where I want the chart and its data placed and I pass the row and column numbers where I want to start placing the data.
9. This calls the method to build the line chart. Same information gets passed as in step 7.
10. After all the work is done, you have to cause the application to be visible.

Load Data

Since this article deals with Excel, I’m going to phony-up data in a method called LoadData(). I’m putting names of ports and tons of materials received in a SortedList that I’ll use for generating a bar chart. I’m also building two SortedLists for use with the line charts, one for tons projected and one for tons actual. For what it’s worth, here’s the code:

void  Form1::LoadData()
{
      slTonsRcvd              = gcnew SortedList();   //Global, declared in Form1.h
      slByDayNYProjected      = gcnew SortedList();   //Global, declared in Form1.h
      slByDayNYActual         = gcnew SortedList();   //Global, declared in Form1.h
 
      slTonsRcvd->Add("New York",   46.826);
      slTonsRcvd->Add("New Jersey", 21.865);
      slTonsRcvd->Add("Boston",     4.8);
      slTonsRcvd->Add("Los Angles", 30.87);
      slTonsRcvd->Add("Portland",   16.4876);
 
      slByDayNYProjected->Add(1, 2.0);
      slByDayNYProjected->Add(2, 11.5);
      slByDayNYProjected->Add(3, 7.5);
      slByDayNYProjected->Add(4, 5);
      slByDayNYProjected->Add(5, 10);
      slByDayNYProjected->Add(6, 6.5);
      slByDayNYProjected->Add(7, .5);
 
      slByDayNYActual->Add(1, 2.3);
      slByDayNYActual->Add(2, 12.345);
      slByDayNYActual->Add(3, 8.331);
      slByDayNYActual->Add(4, 5.702);
      slByDayNYActual->Add(5, 10.45);
      slByDayNYActual->Add(6, 6.718);
      slByDayNYActual->Add(7, .98);
}

Make a Bar Chart

Here is the bar chart I want to produce, appropriately sized, the data I produce it from, and the position on the Worksheet where I want it to appear. The chart shows a fictitious amount of cargo in tons delivered to various ports:

I want the data to be in the first two columns of the Worksheet and I want the chart to be next to the data. I want the Tons column to be formatted as a decimal to two places but show as an integer on the chart. I want a chart title and titles on both the X and Y axes.

Here is the method I used to produce that chart, explanations follow the code:

void  Form1::MakeBarChart(Worksheet ^ws, int row, int col)
{
      int         xPos = (col+2)*48;      //Col width 48 points. Chart starts in 3rd col
      int         yPos = row*9;           //Row height = 9, Chart starts in 2nd row
      double      tons = 0;
      String^     port;
 
      //1. Format a Worksheet column to 2 decimal places for chart data
      ws->Range["B1", Type::Missing]->EntireColumn->NumberFormat = "#,##0.00";
 
      //2. Set all Worksheet column widths to 12 to fit column titles and data
      safe_cast<Range^>(ws->Columns)->ColumnWidth = 12;
 
      //3. Extract Tons Received data from the SortedList and place on the chart
      IDictionaryEnumerator^ ide = slTonsRcvd->GetEnumerator();
      while (ide->MoveNext()) {
            port = ide->Key->ToString();
            tons = Convert::ToDouble(ide->Value);
            ws->Cells[row, col]     =  port;
            ws->Cells[row, col+1]   = tons;
            row++;
      }
 
      //4. Create a ChartObjects Collection for the Worksheet
      ChartObjects^ chObjs = safe_cast<ChartObjects^>(ws->ChartObjects(Type::Missing));
 
      //5. Add a ChartObject to the collection at(x, y, width, height) in points
      ChartObject^ chObj = chObjs->Add(xPos, yPos, 300, 300);
 
      //6. Create a chart from the ChartObject
      Chart^ ch = chObj->Chart;
 
      //7. Create a Range object & set the data range.
      Range^ rn = ws->Range["A2:B6", Type::Missing];
 
      //8. Do the chart using ChartWizard
      ch->ChartWizard(rn->CurrentRegion,                    //Source
                        Constants::xlColumn,                //Gallery
                        Type::Missing,                      //Format
                        XlRowCol::xlColumns,                //Plot by
                        1,                                  //Category Labels
                        Type::Missing,                      //Series Labels
                        false,                              //Has Legend
                        "Weekly Tons Received by Port",     //Title
                        "Port",                             //Category Title (X)
                        "Tons",                             //Value Title (Y)
                        Type::Missing);                     //Extra Title
 
      //9. Format the x-axis of the Cargo graph
      safe_cast<Axis^>(ch->Axes(XlAxisType::xlValue, XlAxisGroup::xlPrimary))-> \
                                                      TickLabels->NumberFormat = "#,##0";
}

I use some variables simply to make the code easier (for me) to deal with. So let’s go through step-by-step.

1. I want to display my Tons data to two decimal places. So in the first step, I do a numeric format on the entire column. If you don’t want the entire column formatted, you can specify a range. For example to format only rows 1 through 10 you would substitute ”B1:B10” for ”B1”. If you did not want to display any decimal places you could use "#,##0" as your format string as I did in step 9.
2. I set the column width of the entire Worksheet to a width of 12. If you want to adjust the width of a single column, you can do it with:
   C++

   safe_cast<Range^>(ws->Columns["B1",
   Type::Missing])->EntireColumn->ColumnWidth = 12;

3. In step 3, I just enumerate through a SortedList containing the port name as the key and the tons as the value. I plunk each key/value pair into the appropriate cells in the Worksheet.
4. This step creates a Chart object Collection for the Worksheet but doesn’t do anything with it. That gets done next.
5. Here we add a Chart object to the Chart Object Collection and specify its position and size in points. The arguments are integer: X position, Y position, Width and Height. You have to play around with these numbers to get the chart positioned and sized exactly where and how you want it.
6. I create a Chart reference variable that I can use with the Chart Wizard method.
7. I found that using a Range reference variable makes it easier to deal with in the Chart Wizard method. The Range should cover only the cells that your data resides in. In the next chart example I’ll include series titles in that range.
8. This is the Chart Wizard method. You can find the Chart Wizard Method described on Microsoft’s site at here:

• The first argument, Source, is where the data for the chart is located in the Worksheet.
• Gallery is an integer that specifies the chart type that you want to draw. Logically, it should be an enum of XlChartType but my object browser does not show a chart type for a plain old bar chart. So, I dug around and found that the integer value for a bar chart is 3 and that there is an Excel constant xlColumn that has a value of 3, so I use that as a value just to remind me that this is a column bar chart. You will see in the Line Chart example that there really is a chart type of xlLine.
• I’m not sure exactly how Format argument works and Microsoft’s explanation isn’t very clear to me. Since I’m able to get what I want on the chart with its default, I use that.
• My understanding of PlotBy is that it tells Excel how your data is arranged on the Worksheet, by columns as mine is or by rows. The XlRowCol is an enum that can be either xlRow or xlColumn.
• Categorylabels tells Excel where in your specified range to look for X-axis labels. Here, I’m telling it to look in the first column of the range that I specify in the source argument.
• Series labels deals with chart legend labels and I show an example of that in the line chart example.
• Has Legend tells Excel if you want to show a legend.
• The next three arguments tell Excel what you want for titles. Title is the chart title, Category is the X-axis title, and Value is the Y-axis title.
• I don’t have a need for the last “extra title” argument and I haven’t tried using it.

9. I did not want decimal places to show on the chart’s Y-axis values. So after a lot of hacking, I came up with a line of code that would format those values to integers.

If you run the application at this point and click the “Run Excel” button here’s what you should get (using Office 2007, Office 2003 should give similar results):

Make a Line Chart

The line chart is intended to compare the amount of tons projected to arrive at a port against the amount that actually arrived over a seven day period. The data columns have titles and there is a legend at the bottom that identifies which line is which. I will modify the line color and thickness and reposition the legend.

Here is the code that will produce the chart:

void  Form1::MakeLineChart(Worksheet ^ws, int row, int col)
{
      int         xPos = (col+5)*48;      //Col width 48 points. Chart starts in 3rd col
      int         yPos = row*9;           //Row height = 9, Chart starts in 2nd row
      double      tonsA = 0;              //Actual tons
      double      tonsP = 0;              //Projected tons
      String^     day;                    //Day being plotted
      String^     title = "Tons Received at NY port by day";
 
      //1. Format two Worksheet columns to two decimal places for chart data
      ws->Range["I1:J1", Type::Missing]->EntireColumn->NumberFormat = "#,##0.00";
 
      //2. Reset the three Worksheet data column widths to better fit data
      ws->Range["H1",    Type::Missing]->EntireColumn->ColumnWidth = 5;
      ws->Range["I1:J1", Type::Missing]->EntireColumn->ColumnWidth = 9;
 
      //3. Put Column titles on the chart – two are Legend titles
      ws->Cells[row, col]       = "Day";
      ws->Cells[row, col+1] = "Projected";
      ws->Cells[row, col+2] = "Actual";
 
      //4. Extract the data from two SortedLists and put it on the chart
      IDictionaryEnumerator^ ide = slByDayNYProjected->GetEnumerator();
      while (ide->MoveNext()) {
            //Day and projected tons form one SortedList
            day = ide->Key->ToString();
            tonsP = Convert::ToDouble(ide->Value);
            ws->Cells[row+1, col] = day;
            ws->Cells[row+1, col+1] = tonsP;
            //Use key to get actual tons form the other SortedList
            tonsA = Convert::ToDouble(slByDayNYActual[ide->Key]);
            ws->Cells[row+1, col+2] = tonsA;
            row++;
      }
 
      //5. Create a ChartObject Collection for the Worksheet
      ChartObjects^ chObjs = safe_cast<ChartObjects^>(ws->ChartObjects(Type::Missing));
 
      //6. Add the ChartObject to the collection at(x, y, width, height) in points
      //   Width = 350 to prevent title from wrapping
      ChartObject^  chObj = chObjs->Add(xPos, yPos, 350, 300);
 
      //7. Create a chart from the ChartObject
      Chart^ ch = chObj->Chart;
 
      //8. Create a Range object & set the data range.
      Range^ rn = ws->Range["I2:J9", Type::Missing];
 
      //9. Do the chart
      ch->ChartWizard(rn->CurrentRegion,        //Source
                        XlChartType::xlLine,    //Gallery
                        Type::Missing,          //Format
                        XlRowCol::xlColumns,    //Plot by
                        1,                      //Category Labels
                        1,                      //Series Labels
                        true,                   //Has Legend
                        title,                  //Title
                        "Day",                  //Category Title
                        "Tons",                 //Value Title
                        Type::Missing);         //Extra Title
 
      //10. Tell it the chart type again - initially comes up as a "lineMarked" type
      ch->ChartType = safe_cast<XlChartType>(XlChartType::xlLine);
 
      //11. Position the Chart Legend from the side to the bottom
      ch->Legend->Position = XlLegendPosition::xlLegendPositionBottom;
 
      //12. Format the Y-axis numbers to integers
      safe_cast<Axis^>(ch->Axes(XlAxisType::xlValue, \
                         XlAxisGroup::xlPrimary))->TickLabels->NumberFormat = "#,##0";
 
      //13. Make the lines thick
      safe_cast<Series^>(ch->SeriesCollection(1))->Border->Weight = \
XlBorderWeight::xlThick;
      safe_cast<Series^>(ch->SeriesCollection(2))->Border->Weight = \
XlBorderWeight::xlThick;
 
      //14. Change the line colors
      safe_cast<Series^>(ch->SeriesCollection(1))->Border->ColorIndex = 3;
      safe_cast<Series^>(ch->SeriesCollection(2))->Border->ColorIndex = 32;
}

If you compile and run, you will get the following Excel Charts:

I have changed and added some variables to accommodate the data and to handle the long string used for a title. Here is the step-by-step explanation:

1. Here I format the two columns of numbers to show two decimal places.
2. I shrink the column width for the three data columns to reduce the distance between charts (just because I want to). J
3. This chart has column titles and a legend. The column two titles over the tons data will be the legend titles. See steps 8 and 9.
4. Here I enumerate through a SortedList that has the day as its key and projected tons as its value. I use the key from this SortedList to get the actual tons value from a second SortedList that also has the day as its key. I put the data into the appropriate Worksheet cells. Again, there’s probably a more efficient or clever way to do this, but this way works for me.
5. Same as bar chart.
6. Same as bar chart except the X position has been changed to move the chart over to the right and the chart width is made wider to keep the chart title from wrapping.
7. Same as bar Chart.
8. Note that the range includes the column titles. This is because I want to use them for the legend.
9. The Chart Wizard:
   • Source argument is data source to include the title row.
   • This time there is an XlChartType enum that can be used for the Gallery argument. Unfortunately, it doesn’t produce the type of line that it is supposed to. It produces a line with markers instead of a plain line. So, in step 10 the ChartType is set again, this time it works. Seems to me that Microsoft needs to do some work in this area!
   • Plot By and Category Labels are the same as in bar charts
   • Series Labels is where we tell Excel to use the first row of our data columns as the title for the legend that goes with the data in that particular column.
   • Since we want a legend, we set Has Legend value to true.
   • I use a variable for the chart title. The rest of the arguments are just like those in the bar chart.
10. Here is where I again set the Chart Type. I don’t have a clue as to why this has to be done or why it works. Anyone know?
11. The default position of the legend is at the right side of the chart. I want mine at the bottom and this is how it gets moved. The XlLegendPosition enum can be found using the Object Browser.
12. Again, I want the Y-axis numbers to show as integers.
13. This step adjusts the line thickness. The XlBorderWeight enum can also be found in the Object Browser.
14. And finally, I want the colors of the lines to be a true red and blue so I set the colors. To get the value to use I opened Excel and built a chart with legend (Object Browser was no help with this one). Then started the macro recorder and double clicked the legend to bring up the Format Legend dialog box. I selected the color I wanted from the legend’s color chart, closed the dialog box and stopped the recording. I then opened the macro in the macro editor and used the VB .ColorIndex value shown therein. I tried using RGB values but got a run-time exception.

Conclusion

A word of caution! When I was using Office 2003, I found that when I closed Excel, sometimes the EXCEL.EXE remained alive in memory. The next time I opened or started Excel I had two EXCEL.EXE instances running and Excel displayed Book2 (or Book3 or however as many processes were running) in its title instead of Book1. This continued until I killed all the EXCEL.EXE processes that were running using Windows Task Manager by selecting each EXCEL.EXE shown in the Processes tab’s window and clicking the End Process button. Thus far, I have not seen this behavior with Office 2007.

The transition from Managed code in VS2003 to C++/CLI in VS 2005 and VS2008 was a bit painful due to syntax changes. But, to me, the resulting code is cleaner, easier to read, understand, and maintain. Now if Microsoft would just provide C++/CLI examples everywhere that they provide C# and VB examples, life would be easier!