How to use MFC to automate Excel and create and format a new workbook

Steps to Create the Project

1. Follow steps 1 through 12 in the following Microsoft Knowledge Base article to create a sample project that uses the IDispatch interfaces and member functions defined in the Excel8.olb type library:

   178749How To Create Automation Project Using MFC and a Type Library

2. At the top of the AutoProjectDlg.cpp file, add the following line:

         #include "excel8.h"
   

3. Add the following code to CAutoProjectDlg::OnRun() in the AutoProjectDLG.cpp file:

   Sample Code

         // Commonly used OLE variants.
         COleVariant
                    covTrue((short)TRUE),
                    covFalse((short)FALSE),
                    covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
   
   
         _Application app;
         Workbooks books;
         _Workbook book;
   
         Worksheets sheets;
         _Worksheet sheet;
         Range range;
         Font font;
         Range cols;
   
         // Start Excel and get Application object.
   
         if(!app.CreateDispatch("Excel.Application"))
         {
          AfxMessageBox("Couldn't start Excel and get Application object.");
          return;
         }
   
   
         //Get a new workbook.
         books = app.GetWorkbooks();
         book = books.Add (covOptional);
   
         //Get the first sheet.
         sheets =book.GetSheets();
         sheet = sheets.GetItem(COleVariant((short)1));
   
         //Fill cells A1, B1, C1, and D1 one cell at a time with "headers".
         range = sheet.GetRange(COleVariant("A1"),COleVariant("A1"));
         range.SetValue(COleVariant("First Name"));
         range = sheet.GetRange(COleVariant("B1"),COleVariant("B1"));
         range.SetValue(COleVariant("Last Name"));
         range = sheet.GetRange(COleVariant("C1"),COleVariant("C1"));
         range.SetValue(COleVariant("Full Name"));
         range = sheet.GetRange(COleVariant("D1"),COleVariant("D1"));
         range.SetValue(COleVariant("Salary"));
   
   
         //Format A1:D1 as bold, vertical alignment = center.
         range = sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
         font = range.GetFont();
         font.SetBold(covTrue);
         range.SetVerticalAlignment(
                  COleVariant((short)-4108));   //xlVAlignCenter = -4108
   
         //Fill A2:B6 with an array of values (First & Last Names).
         {
            COleSafeArray saRet;
            DWORD numElements[]={5,2};   //5x2 element array
            saRet.Create(VT_BSTR, 2, numElements);
   
   
            //Fill the 5x2 SafeArray with the following data:
            //   John      Smith
            //   Tom       Brown
            //   Sue       Thomas
            //   Jane      Jones
            //   Adam      Johnson
   
            FillSafeArray(L"John", 0, 0, &saRet);
            FillSafeArray(L"Smith", 0, 1, &saRet);
            FillSafeArray(L"Tom", 1, 0, &saRet);
            FillSafeArray(L"Brown", 1, 1, &saRet);
            FillSafeArray(L"Sue", 2, 0, &saRet);
            FillSafeArray(L"Thomas", 2, 1, &saRet);
   
            FillSafeArray(L"Jane", 3, 0, &saRet);
            FillSafeArray(L"Jones", 3, 1, &saRet);
            FillSafeArray(L"Adam", 4, 0, &saRet);
            FillSafeArray(L"Johnson", 4, 1, &saRet);
   
            range = sheet.GetRange(COleVariant("A2"), COleVariant("B6"));
            range.SetValue(COleVariant(saRet));
   
            saRet.Detach();
         }
   
         //Fill C2:C6 with a relative formula (=A2 & " " & B2).
         range = sheet.GetRange(COleVariant("C2"), COleVariant("C6"));
         range.SetFormula(COleVariant("=A2 & \" \" & B2"));
   
         //Fill D2:D6 with a formula(=RAND()*100000) and apply a number
         //format.
         range = sheet.GetRange(COleVariant("D2"), COleVariant("D6"));
         range.SetFormula(COleVariant("=RAND()*100000"));
         range.SetNumberFormat(COleVariant("$0.00"));
   
         //AutoFit columns A:D.
         range = sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
         cols = range.GetEntireColumn();
         cols.AutoFit();
   
         //Manipulate a variable number of columns for Quarterly Sales Data.
         {
            short NumQtrs;
            CString msg;
            Range resizedrange;
            Interior interior;
            Borders borders;
   
            //Determine how many quarters to display data for.
            for(NumQtrs=1;NumQtrs<=3;NumQtrs++)
            {
               msg.Format("Enter sales data for %d quarter(s)?", NumQtrs);
               if(AfxMessageBox(msg,MB_YESNO)==IDYES)
               {
                  break;
               }
            }
            msg.Format("Displaying data for %d quarters.", NumQtrs);
            AfxMessageBox(msg);
   
            //Starting at E1, fill headers for the number of columns selected.
            range = sheet.GetRange(COleVariant("E1"), COleVariant("E1"));
            resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
            resizedrange.SetFormula(
                   COleVariant("=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\""));
            //Change the Orientation and WrapText properties for the headers.
            resizedrange.SetOrientation(COleVariant((short)38));
            resizedrange.SetWrapText(covTrue);
            //Fill the interior color of the headers.
            interior = resizedrange.GetInterior();
            interior.SetColorIndex(COleVariant((short)36));
   
            //Fill the columns with a formula and apply a number format.
            range = sheet.GetRange(COleVariant("E2"), COleVariant("E6"));
            resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
            resizedrange.SetFormula(COleVariant("=RAND()*100"));
            resizedrange.SetNumberFormat(COleVariant("$0.00"));
   
            //Apply borders to the Sales data and headers.
            range = sheet.GetRange(COleVariant("E1"), COleVariant("E6"));
            resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
            borders = resizedrange.GetBorders();
            borders.SetWeight(COleVariant((short)2));   //xlThin = 2
   
            //Add a Totals formula for the Quarterly sales data and apply a
            //border.
            range = sheet.GetRange(COleVariant("E8"), COleVariant("E8"));
            resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
            resizedrange.SetFormula(COleVariant("=SUM(E2:E6)"));
            borders = resizedrange.GetBorders();
            {
               Border bottomborder;
               bottomborder = borders.GetItem((long)9);
               bottomborder.SetLineStyle(
                              COleVariant((short)-4119));   //xlDouble = -4119
               bottomborder.SetWeight(
                              COleVariant((short)4));       //xlThick = 4
   
            }
         }
   
         //Make the application visible and give the user control of
         //Microsoft Excel.
         app.SetVisible(TRUE);
         app.SetUserControl(TRUE);
   

4. Add the following function to AutoProjectDLG.cpp, locating it somewhere before CAutoProjectDlg::OnRun():

   Sample Code

         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);
   
         }
   

5. In Developer Studio, run the application. When the dialog box appears (the example creates a dialog-based application), click the Run button.