Programming Excel COM Objects in C++

Listing 1: The program that generated the spreadsheet in Figure 1

//(c) 2000 - Grum Ketema and Philippe Lacoude - Any
//  portion of this code can be freely distributed and freely
//  licensed provided that it bears this mention as well as
//  the names of the two authors.

1  #include "stdafx.h"

2  #pragma warning (disable:4049)
3  #pragma warning (disable:4146)
4  #pragma warning (disable:4192)
5  #pragma warning (disable:4101)

6  #import <C:\Program Files\Microsoft Office\Office\mso97.dll>
      no_namespace rename("DocumentProperties", 
         "DocumentPropertiesXL")   
7  #import <C:\Program Files\Common Files\Microsoft Shared\VBA
      \vbeext1.olb> no_namespace   
8  #import <C:\Program Files\Microsoft Office\Office\excel8.olb>
      rename("DialogBox", "DialogBoxXL") rename("RGB", "RBGXL") 
      rename("DocumentProperties", "DocumentPropertiesXL")
      no_dual_interfaces

9  #include <stdio.h>
10 #include <tchar.h>

11 const Col = 4;
12 const Lin = 4;

13 void dump_com_error(_com_error &e)
14 {
15    _tprintf(_T("Oops - hit an error!\n"));
16    _tprintf(_T("\a\tCode = %08lx\n"), e.Error());
17    _tprintf(_T("\a\tCode meaning = %s\n"), e.ErrorMessage());
18    _bstr_t bstrSource(e.Source());
19    _bstr_t bstrDescription(e.Description());
20    _tprintf(_T("\a\tSource = %s\n"), (LPCTSTR) bstrSource);
21    _tprintf(_T("\a\tDescription = %s\n"), 
         (LPCTSTR) bstrDescription);
22 }

23 struct StartOle {
24    StartOle() { CoInitialize(NULL); }
25    ~StartOle() { CoUninitialize(); }
26 } instStartOle;

27 char *Cells(unsigned int j, short unsigned int i)
28 {
29    int k = 0;
30    static char Address[10];
31    if (i>26) {
32       Address[k++] = 64 + ((i-1) / 26);
33       Address[k++] = 65 + ((i-1) % 26);
34    }
35    else Address[k++] = 64 + i;
36    sprintf(Address+k,"%d",j);
37    return Address;
38 }

39 int main(int argc, char* argv[])
40 {
41    printf("Starting Excel...\n");
42    using namespace Excel;
43    int i, j;
44    char ColName[] = "Col  ";
45    char RowName[] = "Row  ";
46    char Formula[100];
47    char ChartRange[100];
48    try {
49        _ApplicationPtr pXL("Excel.Application.8");
50        pXL->Visible = true;
51        _WorkbookPtr pBook  = 
             pXL->Workbooks->Add((long) xlWorksheet);
52        _WorksheetPtr pSheet = pXL->ActiveSheet;
53        // Assign a title to the sheet
54        pSheet->Name = "Daily Report";
55        // Set the data in the table and format it
56        for (i=1; i<=Col; i++)
57        {
58           // Set the layout
59           pSheet->Range[Cells(1,i)]->Borders->
                Item[xlEdgeTop]->LineStyle = (long) xlContinuous;
60           pSheet->Range[Cells(1,i)]->Borders->
                Item[xlEdgeBottom]->LineStyle = (long) xlDouble;
61           // Set the titles
62           ColName[4] = 'A'+(i-1);
63           pSheet->Range[Cells(1,i)]->Value = ColName;
64           // Set the values in the table
65           for (j=1; j<=Lin; j++)
66           {
67              if (1 == i)
68              {
69                 // Set the row titles
70                 RowName[4] = '0'+j;
71                 pSheet->Range[Cells(j,1)]->Value = RowName;
72              }
73              else
74              {
75                 // Set numbers in the table
76                 pSheet->Range[Cells(j+1,i)]->Value = 
                      (double) (3 * (j - 1) + (i-1));
77              }
78           }
79           // Set sums in the bottom row
80           sprintf(Formula, "=SUM(%s", Cells(2,i)); 
             sprintf(Formula, "%s:%s)", Formula, Cells(Lin,i));
81           pSheet->Range[Cells(Lin+1, i)]->Formula = Formula;
82           pSheet->Range[Cells(Lin+1, i)]->Borders->
                Item[xlEdgeTop]->LineStyle = (long) xlDouble;
83           pSheet->Range[Cells(Lin+1, i)]->Borders->
                Item[xlEdgeBottom]->LineStyle = 
                   (long) xlContinuous;
84        }   
85        pSheet->Range[Cells(Lin+1,1)]->Value = "Total";
86        // Generate a chart
87        RangePtr pRange = pSheet->Range["A1"][Cells(Lin,Col)];
88        _ChartPtr pChart = pBook->Charts->Add();
89        pChart->ChartWizard((IDispatch*)pRange, 
             (long)xl3DColumn, 7L, (long)xlRows, 1L, 1L, 2L, 
             "Daily Report");
90        // Save the spreadsheet
91        pBook->SaveAs("Report", vtMissing, vtMissing, vtMissing, 
             vtMissing, vtMissing, xlExclusive, vtMissing, vtMissing, 
             vtMissing, vtMissing);
92        // Exit the Excel Com object
93        pXL->Quit();
94    }
95    catch(_com_error &e)
96    {
97       dump_com_error(e);
98    }
99    return(0);
100 }