How to use Managed C++ to Automate Excel

Introduction

I was given the task of adding a button to an existing Graphical User Interface (GUI) that, when clicked, would read data from a file, do some calculations, then cause MS Excel to run, open a Workbook, and use the data to display several charts as well as show some summary data in a Worksheet. Excel was to run as a separate application and not die when the GUI was closed.

I code in C++, specifically, Managed C++ (MC++). Over the years, I have had to code in various languages but now I have settled in on Visual Studio .NET 2003�s version of MC++ and, being a stubborn old goat, refuse to code in another language.

So, when I started looking for examples of how to use MC++ to automate Excel, I was disappointed to say the least. I found a lot of VB examples, a few C# examples, one example using MFC, but no MC++ examples. So I spent a frustrating week hacking around with MC++ code, trying various modifications of C# and VB examples as well as numerous MC++ syntax variations to arrive at working MC++ code.

The code I arrived at works. It�s probably not the most efficient, or elegant, or even the most logical. I didn�t have the time to do a lot of digging and research. But the code I came up with works with .NET 2003 MC++! I apologize in advance to all the �purists� out there who may be offended by what they view as my ugly code and I�ll be happy to accept any suggestions for improvement, as long as you have tested them and they work! As for MC++, this is just another example showing how Microsoft has forgotten about us C++ people in their zeal to promote the VB and C# languages. Microsoft needs to regroup and treat us old C++ types with more respect! Enough grousing and on to the project.

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:

Project Setup

1. I�m using Visual Studio 2003 and .NET 1.1. Start a new project in Visual Studio. I selected as my Project Type Visual C++ .NET, and Windows Forms Application (.NET) as the Template.

   

2. You will need to add the latest Excel assembly to your References. Chances are the one currently on your machine is not current and will not work right. I spent the better part of a day trying to get the original library to work. Forget it, go to one of the sites below to download the latest Office Primary Interop Assemblies (PIA).
   • Office 2003
   • Office XP

   I can�t say what to do if you are using Office 2000. Perhaps the original reference library will work with it, perhaps not. I just don�t know.

3. Extract it to C:\temp directory or wherever else you want to place it.
4. Then go to that directory and run the O2003PIA.MSI file to install the interop. This will update the Microsoft Excel Object Library.
5. In the project, add a reference to the interop (Main Menu - Project->Add Reference). Select the COM tab and scroll down to Microsoft Excel 11.0 Type Library. Click Select, then OK.

   

   The following references get added to your project and a temp directory gets created in your folder with the reference files placed in it:

   • Microsoft.Vbe.Interop
   • Office
   • stdole
   • Microsoft.Office.Interop.Excel

   You can delete the project references to Microsoft.Vbe.Interop, Office, and stdole.

6. Add the following line to your header file (Form1.h):

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

   You will probably have to type in Interop::Excel. IntelliSense doesn�t work well with this assembly, hardly works at all! If anyone knows how to get IntelliSense working correctly for this assembly, I�d sure like to know.

7. To avoid confusing the compiler between a System application and an Excel application, you will also have to change the second line in the APIENTRY _tWinMain method in the Form1.cpp from:

   Application::Run(new Form1());

   to

   System::Windows::Forms::Application::Run(new Form1());

8. Put a button on Form1. I named mine butExcel and set the Text property to �Run Excel�.
9. Create an event handler for butExcel:

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

   This is all you have at this point:

   

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 the Excel application
   
       Microsoft::Office::Interop::Excel::Application* xlApp = \
           new Microsoft::Office::Interop::Excel::ApplicationClass();
   
   //2. Add a Workbook
   
       Workbook* wb = xlApp->Workbooks->Add(Type::Missing);
   
       xlApp->Visible = true;
   }

   Note: If you want to avoid having to type in Microsoft::Office::Interop::Excel, you can add the following to your header file:

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

   Then you can write:

   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 Worksheets in it.

Note: I�ve been working with MC++ for a bit over a year now and this is the first time I have encountered using �Type::Missing� as a method argument. It seems to be used a lot when working with Excel. I guess I�ve led a sheltered life!

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 (sheet 2) Worksheet:

   static_cast<Worksheet*>(exApp->ActiveWorkbook->Sheets->Item[
                                          __box(2)])->Delete();

2. I create a variable reference to the active Worksheet 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.

   Worksheet* ws  = static_cast<Worksheet*>(xlApp->ActiveSheet);

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 (it�s all one line of code):

       static_cast<Worksheet*>(exApp->ActiveWorkbook->Sheets->Item[__box(2)])
   ->Select(Type::Missing);

4. To rename the active Worksheet, do this:

       ws->Name = S"Charts";

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 Worksheets to the Workbook. The Add arguments are: Before, After, Count, Type.

xlApp->ActiveWorkbook->Worksheets->Add(Type::Missing, Type::Missing, \ 
__box(2), \Type::Missing);

However, I could not get the Before or After argument to work. Anytime I substituted a number or Worksheet name for one of them, a run-time exception was thrown. So the Worksheets that get added are put in front of existing Worksheets. I didn�t play with the Type argument, it defaults to a Worksheet type.

To get a look at methods used in working with the Excel object (VB and C# examples only, and not many of the latter), go to MSDN.

The Controlling Method

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

void Form1::RunExcel()
{
    //1. Create the Excel application

    Excel::Application* xlApp = new Excel::ApplicationClass();

    //2. Add a Workbook with worksheets

    Workbook* wb = xlApp->Workbooks->Add(Type::Missing);

    //3. The Workbook comes with 3 Worksheets - Delete the last two

    static_cast<Worksheet*>(xlApp->ActiveWorkbook->Sheets->Item[
                                              __box(3)])->Delete();
    static_cast<Worksheet*>(xlApp->ActiveWorkbook->Sheets->Item[
                                              __box(2)])->Delete();

    //4. Assign the Active Worksheet to a variable

    Worksheet* ws  = static_cast<Worksheet*>(xlApp->ActiveSheet);

    //5. Rename the Worksheet

    ws->Name = S"Charts";

    //6. Load the data

    LoadData();

    //7. Make the bar chart

    MakeBarChart(ws, 2, 1);

    //8. Make a line chart

    MakeLineChart(ws, 2, 8);

    //9. Make the application visible

    xlApp->Visible = true;
}

Here is what�s happening in the 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 to reference Excel: Microsoft::Office::Interop::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.
4. I will be passing a reference to 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 is the default active Worksheet. You can make another Worksheet active with:

       static_cast<Worksheet*>(exApp->ActiveWorkbook->Sheets->Item[__box(3)])
   ->Select(Type::Missing);

   where the number 3 is the third Worksheet in the collection. Note that the above code should be all in one line.

5. This line renames the active Worksheet.
6. 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 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 SortedLists.
7. 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.
8. This calls the method to build the line chart. Same information gets passed as in step 7.
9. 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->Add(S"New York",   __box(46.826));
    slTonsRcvd->Add(S"New Jersey", __box(21.865));
    slTonsRcvd->Add(S"Boston",     __box(4.8));
    slTonsRcvd->Add(S"Los Angles", __box(30.87));
    slTonsRcvd->Add(S"Portland",   __box(16.4876));

    slByDayNYProjected->Add(__box(1), __box(2.0));
    slByDayNYProjected->Add(__box(2), __box(11.5));
    slByDayNYProjected->Add(__box(3), __box(7.5));
    slByDayNYProjected->Add(__box(4), __box(5));
    slByDayNYProjected->Add(__box(5), __box(10));
    slByDayNYProjected->Add(__box(6), __box(6.5));
    slByDayNYProjected->Add(__box(7), __box(.5));

    slByDayNYActual->Add(__box(1), __box(2.3));
    slByDayNYActual->Add(__box(2), __box(12.345));
    slByDayNYActual->Add(__box(3), __box(8.331));
    slByDayNYActual->Add(__box(4), __box(5.702));
    slByDayNYActual->Add(__box(5), __box(10.45));
    slByDayNYActual->Add(__box(6), __box(6.718));
    slByDayNYActual->Add(__box(7), __box(.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 in 3rd col

    int      yPos = row*9;      //Row height = 9

    double   tons = 0;
    String*  port;

    //1. Format a Worksheet column to 2 decimal places for chart data 

    ws->get_Range(S"B1", 
      Type::Missing)->EntireColumn->NumberFormat = S"#,##0.00";

    //2. Set the all Worksheet column widths 

    //to 12 to fit column titles and data

    static_cast<Range*>(ws->Columns)->ColumnWidth = __box(12);

    //3. Extract the data from its SortedList and place it on the chart

    IDictionaryEnumerator* ide = slTonsRcvd->GetEnumerator();
    while (ide->MoveNext()) {
        port = ide->Key->ToString();
        tons = Convert::ToDouble(ide->Value);
        ws->Cells->set_Item(__box(row),__box(col),  port);
        ws->Cells->set_Item(__box(row),__box(col+1),__box(tons));
        row++;
    }

    //4. Create a ChartObject Collection for the Worksheet

    ChartObjects* chObjs = 
      static_cast<ChartObjects*>(ws->ChartObjects(Type::Missing));

    //5. Add the 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->get_Range(S"A2:B6", Type::Missing);

    //8. Do the chart using ChartWizard

    ch->ChartWizard(rn->CurrentRegion,        //Source

                __box(Constants::xlColumn),   //Gallery

                Type::Missing,                //Format

                __box(XlRowCol::xlColumns),   //Plot by

                __box(1),                     //Category Labels

                Type::Missing,                //Series Labels

                false,                        //Has Legend

                S"Weekly Tons Received by Port",    //Title

                S"Port",                    //Category Title (X)

                S"Tons",                    //Value Title (Y)

                Type::Missing);             //Extra Title


    //9. Format the x-axis of the Cargo graph

    static_cast<Axis*>(ch->Axes(__box(XlAxisType::xlValue), \
 XlAxisGroup::xlPrimary))->TickLabels->NumberFormat = \
 S"#,##0";
}

I use some variables simply to make the code easier (for me) to deal with. So let�s go through the code 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 S�B1:B10� for S�B1�. If you did not want to display any decimal places, you could use S"#,##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:

   ws->get_Range(S"B1", Type::Missing)->EntireColumn->ColumnWidth = __box(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 ChartObject for the Worksheet but doesn�t do anything with it. That gets done next.
5. Here we add the ChartObject to the ChartObjects collection and specify its position and size in points. The arguments are integers: 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. I started by opening Excel and seeing what numbers I got for column width and row height when I selected Format >Column->Width and Format->Row->Height. These numbers turned out to be very close to what I needed.
6. I create a Chart reference variable that I can use with the Chart Wizard.
7. I found that using a Range reference variable makes it easier to deal with in the Chart Wizard. 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. You can find the ChartWizard method described 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 the 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 what exactly the Format argument does. Since I�m able to get what I want on the chart, I don�t use it.
   • 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.
   • Category labels tell 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 deal with chart legend labels and I show an example of that in the line chart example.
   • HasLegend 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.

Note that in steps 1 and 7, I use a String object to identify cells for a range. I have not been able to figure out how I can just use the row number and column number to do this. Everything I�ve tried results in either a compiler error or a run-time exception being thrown. Microsoft provides the following C# example that I have not been able to convert to working MC++ syntax:

rng = (Excel.Range)ws.Cells[1,1];

I could cook up an algorithm that would convert a column number to the correct alpha character, then combine it with the row number in a String object. But there ought to be a better way! If anyone knows how to make this work in MC++, I�d sure like to know how. Any Microsoft gurus listening?

Make a Line Chart

This is what the Worksheet looks like with a line chart added.

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 have modified the line color and thickness and have repositioned the legend.

Here is the code that produced the chart:

void Form1::MakeLineChart(Worksheet* ws, int row, int col)
{
    int        xPos = (col+5)*48;   //Col width 48 points. Chart in 3rd col

    int        yPos = row*9;        //Row height = 9

    double    tonsA = 0;            //Actual tons

    double    tonsP = 0;            //Projected tons

    String*    day;                 //Day being plotted

    String* title = S"Tons Received at NY port by day";

    //1. Format two Worksheet columns to two decimal places for chart data 

    ws->get_Range(S"I1:J1", 
      Type::Missing)->EntireColumn->NumberFormat = S"#,##0.00";

    //2. Reset the three Worksheet data column widths to better fit data

    ws->get_Range(S"H1", Type::Missing)->EntireColumn->ColumnWidth = __box(5);
    ws->get_Range(S"I1:J1", Type::Missing)->EntireColumn->ColumnWidth = __box(9);

    //3. Put Column titles on the chart � two are Legend titles

    ws->Cells->set_Item(__box(row),__box(col),   S"Day");
    ws->Cells->set_Item(__box(row),__box(col+1), S"Projected");
    ws->Cells->set_Item(__box(row),__box(col+2), S"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->set_Item(__box(row+1),__box(col),  day);
        ws->Cells->set_Item(__box(row+1),__box(col+1),__box(tonsP));
//Use key to get actual tons form the other SortedList

        tonsA = Convert::ToDouble(slByDayNYActual->Item[ide->Key]);
        ws->Cells->set_Item(__box(row+1),__box(col+2),__box(tonsA));
        row++;
    }

    //5. Create a ChartObject Collection for the Worksheet

    ChartObjects* chObjs = 
      static_cast<ChartObjects*>(ws->ChartObjects(Type::Missing));

    //6. Add the ChartObject to the collection 

    //at (x, y, width, height) in points

    ChartObject*  chObj = chObjs->Add(xPos, yPos, 300, 300);

    //7. Create a chart from the ChartObject

    Chart* ch = chObj->Chart;

    //8. Create a Range object & set the data range.

    Range* rn  = ws->get_Range(S"I2:J9", Type::Missing);

    //9. Do the chart

    ch->ChartWizard(rn->CurrentRegion,         //Source

                __box(XlChartType::xlLine),    //Gallery

                 Type::Missing,                //Format

                __box(XlRowCol::xlColumns),    //Plot by

                __box(1),                //Category Labels

                __box(1),                //Series Labels 

                __box(true),             //Has Legend

                title,                   //Title

                S"Day",                  //Category Title

                S"Tons",                 //Value Title

                Type::Missing);          //Extra Title


    //10. Tell it the chart type again - initially 

    //comes up as a "lineMarked" type

    ch->ChartType = static_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

    static_cast<Axis*>(ch->Axes(__box(XlAxisType::xlValue), \
                XlAxisGroup::xlPrimary))->TickLabels->NumberFormat = S"#,##0";

    //13. Make the lines thick

    static_cast<Series*>(ch->SeriesCollection(__box(1)))->Border->Weight \
= __box(XlBorderWeight::xlThick);
    static_cast<Series*>(ch->SeriesCollection(__box(2)))->Border->Weight \
= __box(XlBorderWeight::xlThick);

    //14. Change the line colors

    static_cast<Series*>(ch->SeriesCollection(__box(1)))->Border->ColorIndex \
= __box(3);
    static_cast<Series*>(ch->SeriesCollection(__box(2)))->Border->ColorIndex \
= __box(32);
}

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).
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 way to do this and I�m open for suggestions (try it before you send it please).
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.
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 as well!
   • 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 HasLegend value to true but the compiler makes us box it. I suspect that it�s not a true Boolean type, just an integer that is 0 or 1.
   • 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 ChartType. I don�t have a clue as to why this has to be done or why it works. Anyone knows?
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 you close Excel, sometimes the EXCEL.EXE remains alive in memory. The next time you open or start Excel you will have two EXCEL.EXE instances running and Excel will display Book2 or Book3 (or however as many processes are running) in its title instead of Book1. This will continue until you kill all the EXCEL.EXE processes that are running. You can do this with Windows Task Manager by selecting each EXCEL.EXE shown in the Processes tab�s window and clicking the End Process button.

It seems to me that Microsoft never intended for MC++ developers to automate Excel the way that VB and C# developers can. If they did, then those responsible for the MC++ implementation were just plain negligent in this area (IMHO). There are a lot of things that appear to work fine in VB and C# and that should also work in MC++ but do not. Often the compiler will accept the syntax but a run-time exception will be thrown during execution. Despite that, skill, cunningness, and daringness can overcome fear and superstition (most of the time)!