Click here to Skip to main content
15,867,835 members
Articles / Programming Languages / C++

Automating Excel 2007 and creating charts using C++ MFC application in Visual Studio 2008

Rate me:
Please Sign up or sign in to vote.
4.89/5 (16 votes)
14 Oct 2012CPOL3 min read 129.8K   3.7K   47   40
This article describes how to automate Excel 2007 and create charts using a C++ MFC application.

Introduction

This article describes how to automate Excel 2007 using a C++ MFC application. The solution also works for Excel 2010. I have also highlighted some of the issues faced while accomplishing this task. In this article I describe how to open up the Excel Application, how to create a worksheet and enter data into the worksheet and to create charts using the data in the worksheet. I used Visual studio 2008 for this project but I believe that it should work with VS 2010 as well.

Using the code

Assuming you have Microsoft Excel 2007 installed, open visual studio and create a new MFC application named as AutomateExcel.

Sample Image

Chose the application type as Dialog based and click on Finish.

Sample Image

Click on Add Class from the Project menu and select MFC Class From TypeLib.

Sample Image

In the Add Class From Typelib Wizard select the Registry option and select Microsoft Excel 12.0 Object Library<1.6> from the dropdown list.

Select the following interfaces:

  1. _Application
  2. _Chart
  3. _Workbook
  4. _Worksheet
  5. Charts
  6. Font
  7. Range
  8. Workbooks
  9. Worksheets

Sample Image

On clicking Finish, all the related header files will be created. Open AutomateExcelDlg.cpp and include all these header files.

C++
#include "CApplication.h"
#include "CFont0.h"
#include "CRange.h"
#include "CWorkbook.h"
#include "CWorkbooks.h"
#include "CWorksheet.h"
#include "CWorksheets.h"
#include "CChart.h"
#include "CCharts.h"

Try building your project at this point. You will get a large number of errors in excel.tlh file. In order to get rid of these errors you will need to comment out all the #import statements in all the header files that were created by Visual Studio. You can do a find all for "#import" and quickly go to each of the header files and comment those lines.

C++
//#import "C:\\Program Files\\Microsoft Office\\Office12\\EXCEL.EXE" no_namespace
...

Once you have commented out all the #import statements and saved all those files, try building your project again. You will get some syntax error in the file crange.h on the line "VARIANT DialogBox()". The key to resolving this error is to put an underscore in front of DialogBox().

C++
VARIANT _DialogBox()
...

Build again to make sure that your build succeeds. Now we are ready to write some code Smile | :)

Open the AutomateExcel.cpp file and inside the InitInstance function add the following code:

C++
if(!AfxOleInit())
{
  AfxMessageBox(_T("Cannot initialize COM dll"));
  return FALSE;
}
...
AfxEnableControlContainer();

From your solution explorer expand Resource Files and double click on AutomateExcel.rc. In the Resource View expand the Dialog folder and double click on IDD_AUTOMATEEXCEL_DIALOG to open the dialog page of your application. Delete the initial label and also delete the Cancel button. Change the caption of the OK button to Run and rename the ID as IDRUN. Double click the Run button to create the OnBnClickedRun() event handler. Add the following code into this function:

C++
void CAutomateExcelDlg::OnBnClickedRun()
{
	// Commonly used OLE variants.
	COleVariant
      covTrue((short)TRUE),
      covFalse((short)FALSE),
      covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
	
	CApplication app;

   // Start Excel and get an Application object.
   if(!app.CreateDispatch(TEXT("Excel.Application")))
   {
      AfxMessageBox(TEXT("Couldn't start Excel and get Application object."));
      return;
   }

   app.put_Visible(TRUE);
   app.put_UserControl(TRUE);
}

Build the solution and run it. Click on the Run button and notice the Excel application open up. Congratulations! you have successfully completed your first automation task - Opening up the Excel application. Now lets add a workbook in it and add some information in the first sheet of the workbook.

C++
if(!app.CreateDispatch(TEXT("Excel.Application")))
{
  AfxMessageBox(TEXT("Couldn't start Excel and get Application object."));
  return;
}
.
.
.
CWorkbooks books;
CWorkbook book;
CWorksheets sheets;
CWorksheet sheet;
CRange range;
CFont0 font;

books = app.get_Workbooks();
book = books.Add (covOptional);


//Get the first sheet.
sheets =book.get_Sheets();
sheet = sheets.get_Item(COleVariant((short)1));

range = sheet.get_Range(COleVariant(TEXT("A1")),COleVariant(TEXT("A1")));
range.put_Value2(COleVariant(TEXT("Average precipation (mm)")));
range = sheet.get_Range(COleVariant(TEXT("A1")),COleVariant(TEXT("C1")));
range.Merge(covOptional);
range = sheet.get_Range(COleVariant(TEXT("B2")),COleVariant(TEXT("B2")));
range.put_Value2(COleVariant(TEXT("Acapulco")));
range = sheet.get_Range(COleVariant(TEXT("C2")),COleVariant(TEXT("C2")));
range.put_Value2(COleVariant(TEXT("Amsterdam")));


//Fill A3:A6 with an array of values (Months).
COleSafeArray saRet;
DWORD numElements[]={4,1};   //4x1 element array
saRet.Create(VT_BSTR, 2, numElements);
 
FillSafeArray(L"January", 0, 0, &saRet);
FillSafeArray(L"April", 1, 0, &saRet);
FillSafeArray(L"July", 2, 0, &saRet);
FillSafeArray(L"October", 3, 0, &saRet);
  
range = sheet.get_Range(COleVariant(TEXT("A3")), COleVariant(TEXT("A6")));
range.put_Value2(COleVariant(saRet));
saRet.Detach();

//Fill B3:C6 with values
range = sheet.get_Range(COleVariant(TEXT("B3")),COleVariant(TEXT("B3")));
range.put_Value2(COleVariant(short(10)));
range = sheet.get_Range(COleVariant(TEXT("B4")),COleVariant(TEXT("B4")));
range.put_Value2(COleVariant(short(69)));
range = sheet.get_Range(COleVariant(TEXT("B5")),COleVariant(TEXT("B5")));
range.put_Value2(COleVariant(short(5)));
range = sheet.get_Range(COleVariant(TEXT("B6")),COleVariant(TEXT("B6")));
range.put_Value2(COleVariant(short(53)));
range = sheet.get_Range(COleVariant(TEXT("C3")),COleVariant(TEXT("C3")));
range.put_Value2(COleVariant(short(208)));
range = sheet.get_Range(COleVariant(TEXT("C4")),COleVariant(TEXT("C4")));
range.put_Value2(COleVariant(short(76)));
range = sheet.get_Range(COleVariant(TEXT("C5")),COleVariant(TEXT("C5")));
range.put_Value2(COleVariant(short(145)));
range = sheet.get_Range(COleVariant(TEXT("C6")),COleVariant(TEXT("C6")));
range.put_Value2(COleVariant(short(74)));
  
//Format A1:C1 as bold, vertical alignment = center.
range = sheet.get_Range(COleVariant(TEXT("A1")), COleVariant(TEXT("C1")));
font = range.get_Font();
font.put_Bold(covTrue);
range.put_VerticalAlignment(COleVariant((short)-4108));   //xlVAlignCenter = -4108

//AutoFit columns A:D.
range = sheet.get_Range(COleVariant(TEXT("A1")), COleVariant(TEXT("D1")));
CRange cols;
cols = range.get_EntireColumn();
cols.AutoFit();
.
.
.
app.put_Visible(TRUE);
app.put_UserControl(TRUE);

Here we grab the first sheet of the workbook and enter data into it by using ranges. The FillSafeArray function is below:

C++
void FillSafeArray(OLECHAR FAR* sz, int iRow, int iCol,
                   COleSafeArray* sa)
{

   VARIANT v;
   long index[2];

   index[0] = iRow;
   index[1] = iCol;

   VariantInit(&v);
   v.vt = VT_BSTR;
   v.bstrVal = SysAllocString(sz);
   sa->PutElement(index, v.bstrVal);
   SysFreeString(v.bstrVal);
   VariantClear(&v);

}

Let's now add a chart real quick.

C++
CCharts charts;
CChart chart;
charts = book.get_Charts();
chart = charts.Add(covOptional, covOptional, covOptional);

Build and run the project. Click on the Run Button. Here's how it looks for Excel 2007.

Sample Image

Sample Image

The charts are created using the data from the currently active sheet by Excel 2007 and 2010. This sheet is the one we just created. There is a lot more that can be done with charts specially when you want to create your own charts choosing your series and axes. But for this article, I am going to keep it simple and end here. I will try to cover more things in a future article.

History

Version 1.0

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionWorking Without Excel and SaveAs Pin
Jeff.Bk29-Sep-16 13:44
Jeff.Bk29-Sep-16 13:44 
AnswerRe: Working Without Excel and SaveAs Pin
abhinavsly12-Oct-17 11:44
abhinavsly12-Oct-17 11:44 
Questionsave Pin
Raducu Mihai10-Aug-15 2:55
Raducu Mihai10-Aug-15 2:55 
AnswerRe: save Pin
Raducu Mihai11-Aug-15 3:01
Raducu Mihai11-Aug-15 3:01 
GeneralMy vote of 5 Pin
Member 1155799126-Mar-15 16:08
Member 1155799126-Mar-15 16:08 
Questionhow to auto increment column Pin
Member 112888245-Dec-14 2:40
Member 112888245-Dec-14 2:40 
QuestionCan't we use the function app.CreateDispatch() in MFC regular DLL?? Pin
RajnarayananR6-Nov-14 19:46
RajnarayananR6-Nov-14 19:46 
QuestionAcessing the Undo Command of excel from VC++ program. Pin
Member 1102154118-Aug-14 23:42
Member 1102154118-Aug-14 23:42 
QuestionAdding data from excel file insted of hard coding the cell values Pin
peoria1232-Jul-14 3:49
peoria1232-Jul-14 3:49 
QuestionHow to make excel loose focus Pin
wangwei1989090117-Mar-14 1:14
wangwei1989090117-Mar-14 1:14 
QuestionHow to make the programe fill the excel by itself Pin
忘世麒麟17-Feb-14 19:00
professional忘世麒麟17-Feb-14 19:00 
AnswerRe: How to make the programe fill the excel by itself Pin
忘世麒麟18-Feb-14 21:31
professional忘世麒麟18-Feb-14 21:31 
SuggestionRe: How to make the programe fill the excel by itself Pin
aslucky31-Jul-14 21:41
aslucky31-Jul-14 21:41 
GeneralRe: How to make the programe fill the excel by itself Pin
忘世麒麟11-Feb-15 15:12
professional忘世麒麟11-Feb-15 15:12 
QuestionI need country code and language for excel application Pin
Member 1054300422-Jan-14 18:41
Member 1054300422-Jan-14 18:41 
AnswerRe: I need country code and language for excel application Pin
Member 1054300410-Feb-14 23:42
Member 1054300410-Feb-14 23:42 
GeneralMy vote of 5 Pin
Qu Dong1-Sep-13 21:09
Qu Dong1-Sep-13 21:09 
QuestionHow to Open an exist Excel File, and then set the cell format for Text? Pin
Soldier198117-Aug-13 5:51
Soldier198117-Aug-13 5:51 
AnswerRe: How to Open an exist Excel File, and then set the cell format for Text? Pin
Soldier198117-Aug-13 17:34
Soldier198117-Aug-13 17:34 
GeneralRe: How to Open an exist Excel File, and then set the cell format for Text? Pin
Jeff.Bk29-Sep-16 15:45
Jeff.Bk29-Sep-16 15:45 
Questionput_Visible and put_UserControl Pin
Priyanka Sabharwal815-Aug-13 23:33
Priyanka Sabharwal815-Aug-13 23:33 
AnswerRe: put_Visible and put_UserControl Pin
abhinavsly6-Aug-13 5:19
abhinavsly6-Aug-13 5:19 
GeneralRe: put_Visible and put_UserControl Pin
Priyanka Sabharwal816-Aug-13 5:44
Priyanka Sabharwal816-Aug-13 5:44 
GeneralRe: put_Visible and put_UserControl Pin
abhinavsly6-Aug-13 9:12
abhinavsly6-Aug-13 9:12 
GeneralRe: put_Visible and put_UserControl Pin
Mạnh Lê7-Feb-17 17:52
Mạnh Lê7-Feb-17 17:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.