Microsoft Excel Automation Class

Excel Automation: Class to Start Microsoft Excel and Access Basic Excel Functions from a C++ Application

This example implements OLE and IDispatch interfaces to access basic Excel functions from a C++ application. Two classes are provided: an Excel Automation class CXLAutomation and a wrapper class CXLEzAutomation.

The B CXLAutomation is a C++ implementation of the AutoXL project from “Microsoft Excel 97 Developer’s Kit” (Microsoft Press, 1997); see also “XLClient: Automation Client for Excel,” MSDN Article ID Q141759. Make changes in this class if you need to extend the Excel Automation functionality.

CXLEzAutomation is the wrapper class for CXLAutomation. CXLEzAutomation exposes a small set of methods to start Excel programmatically, exchange data between application and Excel, create Excel XY chart, inserte image to Excel, and save/open Excel files:

CXLConnection methods

Adding Excel Automation Capability to a C++ Project

1. Add CXLAutomation and CXLEzAutomation classes to your project by copying files XLAutomation.h, XLAutomation.cpp, XLEzAutomation.h, and XLEzAutomation.cpp from the XLAutomationTester project. Replace #include “XLAutomationTester.h” with #include “YourProjectName.h” in the XLAutomation.cpp and XLEzAutomation.cpp files.
2. To start Excel and export data to Excel’s worksheet by pressing Button1, add the following code to your project:

YourProjectName::OnButton1()
{
   CXLEzAutomation XL;
   CString szDataCollection;

   //Do something to add tab-delimited text to szDataCollection
   //Use TAB and NewLine to format X and Y data, for instance:
   //szDataLine.Format("%f \t %f\n", x, y);
      // create (x,y) point
   //szDataCollection = szDataCollection + szDataLine;
      //Add (x,y) point

   XL.ExportCString(szDataCollection);
}

3. To export data and create an XYChart, add this code:

{
   CXLEzAutomation XL;
   CString szDataCollection;

   //Do something to place tab-delimited text to szDataCollection
   //Use TAB and NewLine to format X and Y data, for instance:
   //szDataLine.Format("%f \t %f\n", x, y) create (x,y) poiunt
   //szDataCollection = szDataCollection + szDataLine;
      //Add (x,y) point

   XL.ExportCString(szDataCollection);
   XL.CreateXYChart(2);B
      //Y-values are in column B (in other words, column 2)
}

4. To read data from the existing Excel file, add this code:

{
   CXLEzAutomation XL;
   //Use CXLEzAutomation XL(FALSE); if you do not want to open
   //an Excel interface.
   //CString szFileName = _T("ExcelFileName");
      // Get valid Excel name here!
   XL.OpenExcelFile(szFileName);

   //Iterate through all cells from which you need to read data
   //CString szValue;
   //szValue = XL.GetCellValue(Column, Row);
   //If cell has a numerical value, for instance a float
   //value - do conversion:
   //float fValue = atof(szValue);

   //Close Excel when done (or leave it open by deleting the
   //next line):
   XL.ReleaseExcel();
}

Test Project

The XLAutomationTester provides an example of using CXLAutomation and CXLEzAutomation. The demo application demonstrates:

1. The use of the Clipboard function to export data to Excel and to create an XY plot (static mode);
2. The use of direct access to worksheet cells by exporting data “in-real time” from a continuous data stream. This demo mode updates XYChart as new (x,y) data points arrive (dynamic mode);
3. The use of Excel functions to save data in an Excel file format. This demo mode runs Excel in the background (in other words, by starting Excel with bVisible = FALSE). Excel is exited and OLE resources are released after the data have been saved.
4. The use of Excel functions to open an existing Excel file and prepare to read cells. The cell value is read as a CString. If required, the returned CString value can be converted to a numerical value by atoi(), atol(), and atof().

Microsoft Excel has to be installed on your computer for this demo to function properly!

Press the “Start” button in the Static or Dynamic mode to start Excel programmatically, move XY plot data to Excel, and create a chart as shown below.

In Dynamic mode, the chart is automatically updated (the plot is shifted left) as new XY data are supplied by the application.