HOWTO: Use MFC to Automate Excel and Fill a Range with an Array

• Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0

SUMMARY

This article demonstrates how to automate Microsoft Excel 97 and fill a multi-cell range with an array of values.

MORE INFORMATION

To fill a multi-cell range without populating the cells one-by-one, you must create a two-dimensional variant SAFEARRAY which you pass to Excel by calling the SetValue function for the Range object. The following steps illustrate this process.

Steps to Create Project

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




Q178749 HOWTO: Create an Automation Project Using MFC and a Type Library

2. To the dialog box created in steps 4 and 5 of the parent article Q178749, add the following controls with properties as specified. Also add the corresponding member variables:




                                  Member                Member
      Control   Name              Variable Type         Variable Name
      -----------------------------------------------------------------
      Edit      IDC_STARTINGCELL  m_sStartingCell       CString
      Edit      IDC_NUMROWS       m_iNumRows            short
      Edit      IDC_NUMCOLS       m_iNumCols            short
      CheckBox  IDC_STRING        m_bFillWithStrings    BOOL 

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




      #include "excel8.h" 

4. Add the following code to CAutoProjectDlg::OnRun() in the AutoProjectDlg.cpp file.




Sample Code

      // OLE Variant for Optional.
      COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

      _Application objApp;
      _Workbook objBook;
      Workbooks objBooks;
      Worksheets objSheets;
      _Worksheet objSheet;
      Range range;

      if(!UpdateData(TRUE))
      {
         return;
      }

      // Instantiate Excel and start a new workbook.
      objApp.CreateDispatch("Excel.Application");
      objBooks = objApp.GetWorkbooks();
      objBook = objBooks.Add(VOptional);
      objSheets = objBook.GetWorksheets();
      objSheet = objSheets.GetItem(COleVariant((short)1));

      //Get the range where the starting cell has the address
      //m_sStartingCell and it's dimensions are m_iNumRows x m_iNumCols.
      range = objSheet.GetRange(COleVariant(m_sStartingCell),
                                COleVariant(m_sStartingCell));
      range = range.GetResize(COleVariant(m_iNumRows),
                              COleVariant(m_iNumCols));

      //*** Fill the range with an array of values.

      //Create the SAFEARRAY.
      COleSafeArray saRet;
      DWORD numElements[2];
      numElements[0]= m_iNumRows;   //Number of rows in the range.
      numElements[1]= m_iNumCols;   //Number of columns in the range.

      if(m_bFillWithStrings)
      {
         saRet.Create(VT_BSTR, 2, numElements);
      }
      else
      {
         saRet.Create(VT_R8, 2, numElements);
      }

      //Fill the SAFEARRAY.
      long index[2];
      long iRow;
      long iCol;

      for(iRow=0;iRow<=m_iNumRows-1;iRow++)
      {
         for(iCol=0;iCol<=m_iNumCols-1;iCol++)
         {
            index[0] = iRow;
            index[1] = iCol;
            if(m_bFillWithStrings)      //Fill with Strings.
            {
               VARIANT v;
               CString s;
               VariantInit(&v);
               v.vt = VT_BSTR;
               s.Format("r%dc%d", iRow, iCol);
               v.bstrVal = s.AllocSysString();
               saRet.PutElement(index, v.bstrVal);
               SysFreeString(v.bstrVal);
               VariantClear(&v);
            }
            else                     //Fill with Numbers.
            {
               double d;
               d = (iRow*1000) + iCol;
               saRet.PutElement(index, &d);
            }
         }
      }

      //Set the range value to the SAFEARRAY.
      range.SetValue(COleVariant(saRet));
      saRet.Detach();

      //Return control of Excel to the user.
      objApp.SetVisible(TRUE);
      objApp.SetUserControl(TRUE); 

5. Compile and Run the project.



6. Specify the following values for the controls on the dialog box:

   
         Control               Contents
         ------------------------------
         IDC_STARTINGCELL      A1
         IDC_NUMROWS           10
         IDC_NUMCOLS           5
         IDC_STRING            True 

   Click OK.
   
   Results: A new workbook is generated and cells A1:E10 of the first worksheet are populated with string values.



7. Specify the following values for the controls on the dialog box:

   
         Control               Contents
         ------------------------------
         IDC_STARTINGCELL      C3
         IDC_NUMROWS           2
         IDC_NUMCOLS           9
         IDC_STRING            False 

   Click OK.
   
   Results: A new workbook is generated and cells C3:K4 of the first worksheet are populated with numeric values.

REFERENCES

For more information about automating Microsoft Excel using MFC, please see the following articles in the Microsoft Knowledge Base:

Q186122 HOWTO: Use MFC to Automate Excel & Obtain an Array from a Range

Q184663 HOWTO: Embed and Automate a Microsoft Excel Worksheet with MFC

Q179706 HOWTO: Use MFC to Automate Excel & Create/Format a New Workbook

Q178781 HOWTO: Automate Excel 97 Using MFC and Worksheet Functions

Q178783 HOWTO: Use MFC to Create a Microsoft Excel 97 Chart

Additional query words:

Keywords : kbcode kbinterop kbole kbAutomation kbMFC kbVC500 kbVC600
Version : winnt:5.0,6.0
Platform : winnt
Issue type : kbhowto