Cómo automatizar Excel con funciones MFC y la hoja de cálculo

Pasos para crear el proyecto

1. Siga los pasos 1 a 12 en el siguiente artículo de Knowledge Base para crear un proyecto de ejemplo que utiliza las interfaces IDispatch y funciones miembro definidas en Excel8.olb, o Excel9.olb o Excel.exe para Excel 2002 y biblioteca de tipo Excel 2003:
   178749

   (http://support.microsoft.com/kb/178749/EN-US/ )

   Cómo crear un proyecto de automatización mediante MFC y una biblioteca de tipos
2. En la parte superior de la AutoProjectDlg.cpp, agregue la siguiente línea:

         #include "excel8.h"
   					

   si está automatizando Excel 2000, incluya excel9.h. Si está automatizando Excel 2002 o Excel 2003, incluya excel.h
   
   
3. Agregue el código siguiente a CAutoProjectDlg:: OnRun () en el archivo AutoProjectDLG.cpp: código de ejemplo:

         try
         {
         _Application app;     // app is an _Application object.
         _Workbook book;       // More object declarations.
         _Worksheet sheet;
         Workbooks books;
         Worksheets sheets;
   
         Range range;          // Used for Microsoft Excel 97 components.
         LPDISPATCH lpDisp;    // Often reused variable.
   
         // Common OLE variants. Easy variants to use for calling arguments.
         COleVariant
           covTrue((short)TRUE),
           covFalse((short)FALSE),
           covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
   
         // Start Microsoft Excel, get _Application object,
         // and attach to app object.
         if(!app.CreateDispatch("Excel.Application"))
          {
           AfxMessageBox("Couldn't CreateDispatch() for Excel");
           return;
          }
   
   
         // Set visible.
         app.SetVisible(TRUE);
   
         // Register the Analysis ToolPak.
         CString sAppPath;
   
         sAppPath.Format ("%s\\Analysis\\Analys32.xll", app.GetLibraryPath());
   
         if(!app.RegisterXLL(sAppPath))
           AfxMessageBox("Didn't register the Analys32.xll");
   
         // Get the Workbooks collection.
         lpDisp = app.GetWorkbooks();     // Get an IDispatch pointer.
         ASSERT(lpDisp);
         books.AttachDispatch(lpDisp);    // Attach the IDispatch pointer
                                          // to the books object.
   
         // Open a new workbook and attach that IDispatch pointer to the
         // Workbook object.
         lpDisp = books.Add( covOptional );
         ASSERT(lpDisp);
         book.AttachDispatch( lpDisp );
   
            // To open an existing workbook, you need to provide all
            // arguments for the Open member function. In the case of 
            // Excel 2002 you must provide 16 arguments.
            // However in Excel 2003 you must provide 15 arguments.
            // The code below opens a workbook and adds it to the Workbook's
            // Collection object. It shows 13 arguments, required for Excel
            // 2000.
            // You need to modify the path and file name for your own
            // workbook.
   
         // 
         // lpDisp = books.Open("C:\\Test",     // Test.xls is a workbook.
         // covOptional, covOptional, covOptional, covOptional, covOptional,
         // covOptional, covOptional, covOptional, covOptional, covOptional,
         // covOptional, covOptional );   // Return Workbook's IDispatch
         // pointer.
   
         // Get the Sheets collection and attach the IDispatch pointer to your
         // sheets object.
         lpDisp = book.GetSheets();
         ASSERT(lpDisp);
         sheets.AttachDispatch(lpDisp);
   
         // Get sheet #1 and attach the IDispatch pointer to your sheet
         // object.
         lpDisp = sheets.GetItem( COleVariant((short)(1)) );
                                           //GetItem(const VARIANT &index)
         ASSERT(lpDisp);
         sheet.AttachDispatch(lpDisp);
   
         // Fill range A1 with "1/25/98", the settlement date.
         lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
         ASSERT(lpDisp);
         range.AttachDispatch(lpDisp);
         range.SetValue(COleVariant("1/25/98")); // Excel 97 & Excel 2000
   
   range.SetValue2(COleVariant("1/25/98")); // Excel 2002 and Excel 2003
   
   
         // Fill range A2 with "11/15/99", the maturity date.
         lpDisp = sheet.GetRange(COleVariant("A2"), COleVariant("A2"));
         ASSERT(lpDisp);
         range.AttachDispatch(lpDisp);
         range.SetValue(COleVariant("11/15/99"));  // Excel 97 & Excel 2000
   
   range.SetValue2(COleVariant("11/15/99")); // Excel 2002 and Excel 2003
   
   
         // Fill range A3 with "2", the frequency for semi-annual interest
         // payments.
         lpDisp = sheet.GetRange(COleVariant("A3"), COleVariant("A3"));
         ASSERT(lpDisp);
         range.AttachDispatch(lpDisp);
         range.SetValue(COleVariant("2"));  // Excel 97 & Excel 2000
   
   range.SetValue2(COleVariant("2"));  // Excel 2002 and Excel 2003
   
         // Fill range A4 with 1, the basis (actual/actual).
         lpDisp = sheet.GetRange(COleVariant("A4"), COleVariant("A4"));
         ASSERT(lpDisp);
         range.AttachDispatch(lpDisp);
         range.SetValue(COleVariant("1")); // Excel 97 & Excel 2000
   
   range.SetValue2(COleVariant("1")); // Excel 2002 and  Excel 2003
   
         // Fill range C1 with the formula "=COUPNCD(A1, A2, A3, A4)" and
         // format the cell with a Date type of the Number format.
         lpDisp = sheet.GetRange(COleVariant("C1"), COleVariant("C1"));
         ASSERT(lpDisp);
         range.AttachDispatch(lpDisp);
         range.SetNumberFormat(COleVariant("mm/dd/yy"));
         range.SetFormula(COleVariant("=COUPNCD(A1, A2, A3, A4)"));
   
         /* This is an alternative that works without placing variables on
         // the worksheet.
         // The values are arguments contained in the SetFormula() call.
         // range.SetFormula(COleVariant(
                          "=COUPNCD(\"09/15/96\",\"11/15/99\",2,1)"));
         */ 
   
         // *** The example in this block uses a built-in Microsoft Excel
         // function.
   
         // You do not have to register any add-in to use the built-in
   
         // Microsoft Excel worksheet functions.
         lpDisp = sheet.GetRange(COleVariant("C3"), COleVariant("C3"));
         ASSERT(lpDisp);
         range.AttachDispatch(lpDisp);
         range.SetFormula(COleVariant("=SUM(A3, A4)"));
         // or use:
         // range.SetFormula(COleVariant("=SUM(2,1)"));
   
         // *** End of example for built-in function usage.
   
         // Release dispatch pointers.
         range.ReleaseDispatch();
         sheet.ReleaseDispatch();
         // This is not really necessary because
         // the default second parameter of AttachDispatch releases
         // when the current scope is lost.
   
         } // End of processing.
   
           catch(COleException *e)
         {
           char buf[1024];     // For the Try...Catch error message.
           sprintf(buf, "COleException. SCODE: %08lx.", (long)e->m_sc);
           ::MessageBox(NULL, buf, "COleException", MB_SETFOREGROUND | MB_OK);
         }
   
         catch(COleDispatchException *e)
         {
           char buf[1024];     // For the Try...Catch error message.
           sprintf(buf,
                  "COleDispatchException. SCODE: %08lx, Description: \"%s\".",
                  (long)e->m_wCode,(LPSTR)e->m_strDescription.GetBuffer(512));
           ::MessageBox(NULL, buf, "COleDispatchException",
                              MB_SETFOREGROUND | MB_OK);
         }
   
         catch(...)
         {
           ::MessageBox(NULL, "General Exception caught.", "Catch-All",
                              MB_SETFOREGROUND | MB_OK);
         }
   					

Volver al principio | Enviar comentarios