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

Method Group Function
Class constructor Open/Exit app Start Excel and create empty worksheet
ReleaseExcel() Open/Exit app Close Excel and release OLE resources
SetCellValue(..) Data exchange Set cell value of the active worksheet (get data out)
GetCellValue(..) Data exchange Get cell value from the active worksheet (get data in)
ExportCString(..) Data exchange Use Clipboard to export a large amount of data (get data out)
CreateXYChart(..) Chart methods Create XY chart
UpdateXYChart(..) Chart methods Update XY chart with a new set of XY data
InsertPictureFromClipboard(..) Picture methods Insert DIB picture from a buffer or clipboard
InsertPictureFromFile(..) Picture methods Insert picture from a file using Excel methods
SaveFileAs(..) File access Save worksheet as Excel file
OpenExcelFile(..) File access Open existing Excel file for read
DeleteRow(..) Worksheet housekeeping Delete row from the current worksheet

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:
  3. 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);
    }
    
  4. To export data and create an XYChart, add this code:
  5. {
       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)
    }
    
  6. To read data from the existing Excel file, add this code:
  7. {
       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.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read