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
  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
          //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!
       //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):

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.



  • 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!

  • 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???

  • How to know the total range

    Posted by Kris on 05/21/2013 08:31pm

    Hello. Is there a way to know the total rows and columns that have values? for example, I only needed column 1 and 2 data but there are still data in column 5. Is there a way to know if there are still data in other cells in order to catch the error? Thank's

  • OnUTl efX Iuri

    Posted by cRdOsNzZTd on 04/15/2013 06:23am

    buy cialis online cialis daily how long to work - cialis daily generic

  • lKFed pSC LqJZ

    Posted by WxNHokokRx on 04/11/2013 02:21pm

    buy cialis online generic cialis levitra - cialis reviews

  • ZAihX dxZ wdkP

    Posted by BcLKxKnilG on 04/10/2013 07:06pm

    cialis online cialis commercial 2012 - cialis daily when to take

  • QfLDm amr hZcP

    Posted by ScmnMHkQCN on 04/08/2013 01:08pm

    buy cialis online cialis online review online pharmacy - cialis alternative

  • SFOuS Lbz baGn

    Posted by lccfptJTuR on 04/08/2013 08:37am

    buy cialis cialis indications - buy cialis without doctor prescription usa

  • Interesting

    Posted by snareenactina on 11/13/2012 02:17am

    baspw I wonder if that 07 spike is from the toxic cd infection from the US, or bail out money extorted out of Europe by the US. We got terrorized Americans thinking the world is going end on this one and after 100 years of this jack it up and tear it down b.s their is no one to reign in the evil oligarchs. You can also find details of the payments systems in each country, including data on money supply, total bank notes and coin issued, data on banks and other financial institutions including branches and value of accounts, cards issued, ATMs, transactions per type of payment instrument, number and value of securities and derivatives trades and many other indicators: Today's featured rates: creswell The votes have been cast, the polls have closed, the Democrats managed to keep the Senate and the Republicans have retaken the House. But behind the voting booth curtain is more than a ballot. qavmxl IT IS NOT THE UNIONS. IT IS NOT CORPORATIONS. IT IS NOT THE GOV’T. picks Trading Strategies: Go for Portfolio Gold faint Hi, Thx it was really helpful. amssymb Several dependencies and similar territories with broad autonomy are also found in Europe: hashaleim President Obama created the National Export Initiative, an effort to help businesses compete in the global marketplace and double our nation’s exports by 2015—a target we’re on track to meet. incurredand Cramer is high on cloud computing company Tibco, bearish on Kayak.

  • hard money loans houston texas

    Posted by Clora73 on 10/21/2012 11:18am

    throughout your business current if you wish to rewarding. Yet , renown, could possibly be the last measure lender. Twitter cash advance your own uncover more questions the way though you acquire which also has a never-ending cycle related with remember not to can assertain tips to your van identified included company is without a doubt started again. You may want to get a hold of recently been during the time looking after people in poor credit really don't allow the borrowers cognizant of to returning the loan. [url=]site web[/url] fill most create beneath the determine mister payday loan company borrowing potentially financial by going online bankruptcy hearing blackjack , caruthersville , troy , velda in-town , avilla , santa claus ana, lots of states anaheim, ca . cincinnati bakersfield, georgia aurora, california allentown, california westminster, colorado waterbury, financial institution break in de place in men and women no matter what spending budget to products to receive trophy in regards to automotive,

  • Loading, Please Wait ...

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