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

    Reply
  • OnUTl efX Iuri

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

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

    Reply
  • lKFed pSC LqJZ

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

    buy cialis online generic cialis levitra - cialis reviews

    Reply
  • ZAihX dxZ wdkP

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

    cialis online cialis commercial 2012 - cialis daily when to take

    Reply
  • QfLDm amr hZcP

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

    buy cialis online cialis online review online pharmacy - cialis alternative

    Reply
  • SFOuS Lbz baGn

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

    buy cialis cialis indications - buy cialis without doctor prescription usa

    Reply
  • 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.

    Reply
  • 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=http://rushfastloans.com]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,

    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

  • With JRebel, developers get to see their code changes immediately, fine-tune their code with incremental changes, debug, explore and deploy their code with ease (both locally and remotely), and ultimately spend more time coding instead of waiting for the dreaded application redeploy to finish. Every time a developer tests a code change it takes minutes to build and deploy the application. JRebel keeps the app server running at all times, so testing is instantaneous and interactive.

  • You probably have several goals for your patient portal of choice. Is "community" one of them? With a bevy of vendors offering portal solutions, it can be challenging for a hospital to know where to start. Fortunately, YourCareCommunity helps ease the decision-making process. Read this white paper to learn more. "3 Ways Clinicians can Leverage a Patient Portal to Craft a Healthcare Community" is a published document owned by www.medhost.com

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds