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.



About the Author

Val Golovlev

Physicist, now is working in life science research. Fan of C++ for six years; have used C++ in various projects for interfacing instruments, data processing; image acquisition and analysis, internet applications, databases, etc.

Downloads

Comments

  • How to "Save Change" Excel File through OLE

    Posted by Lê Việt Thắng on 07/22/2016 01:32am

    Your project is a great, I learned a lot while reading this project. I also learn about OLE to manage Word and Excel. About Word, I manage it very simple. However, when I do that to excel, I have the trouble with Excel. Detail: 1. When I open the Excel file and edit few places. 2. I want to close that excel file, but not close (because excel file repair unsaved) == I think it must be saved before you can close it. So my question "how to Save Change that excel file". I will be happy if you show how to do this. Thank you!

    Reply
  • Moving through Sheets

    Posted by Rajnarayanan on 11/06/2014 01:48am

    Awesome work!!!! How can i move through the worksheets???? Is there any function to do that???

    Reply
  • Thnx

    Posted by Ranjith on 07/25/2012 07:03am

    Thnx for Ur code. very helpful for me for reading xlsx files.

    Reply
  • Excel Automation Code

    Posted by Joseph on 05/24/2012 07:28pm

    Thanks your Sample Code!

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • As all sorts of data becomes available for storage, analysis and retrieval - so called 'Big Data' - there are potentially huge benefits, but equally huge challenges...
  • The agile organization needs knowledge to act on, quickly and effectively. Though many organizations are clamouring for "Big Data", not nearly as many know what to do with it...
  • Cloud-based integration solutions can be confusing. Adding to the confusion are the multiple ways IT departments can deliver such integration...

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date