Excel编程——复制WorkSheet

Excel在复制WorkSheet时有多种选择，Excel中“move or copy”的功能界面如下图所示：

       可以在同一个Workbook中移动，也可以复制到新的Workbook中。而我需要的功能是将Worksheet保存到新的Workbook中，然后将Workbook存成新的文件。

       实现代码如下：

       int nRetVal = 0;

 

         //  打开文件，获取激活的 Sheet

         LPDISPATCH lpDisp = NULL;

         COleVariant covTrue((short)TRUE);

    COleVariant covFalse((short)FALSE);

    COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); 

         Range                  oCurCell;

 

         //  打开文件

         lpDisp = m_oWorkBooks.Open( strFilePath,            //  打开的 Excel 文件路径

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing),

                                                  _variant_t(vtMissing) );

         //  获得活动的 WorkBook(  工作簿  )

         m_oWorkBook.AttachDispatch( lpDisp, TRUE );

         //  获得活动的 WorkSheet(  工作表  )

         m_oWorkSheet.AttachDispatch( m_oWorkBook.GetActiveSheet(), TRUE );

        

         //  将 sheet 保存到临时的 Excel 文件中，然后读取整个文件数据

         CVNEOcommon oCommon;

         CString strTempPath;

         CString strTempFilePath;

         strTempPath = oCommon.getTemporaryPath();

         strTempFilePath = strTempPath + oReportData.m_strTemplateName + _T( ".xls" );

         _Workbook oWorkBook;

         Workbooks oWorkBooks;

         _Worksheet oWorkSheet;

         Worksheets oWorkSheets;

         m_oWorkSheet.Copy( _variant_t(vtMissing), _variant_t(vtMissing) ); //  一个新工作簿

         m_oExcelApp.SetVisible( FALSE );

         oWorkBooks.AttachDispatch( m_oExcelApp.GetWorkbooks(), TRUE );

         oWorkBook.AttachDispatch( oWorkBooks.GetItem( COleVariant(oWorkBooks.GetCount()) ), TRUE );

         oWorkBook.SaveAs( COleVariant( strTempFilePath ),

                                                _variant_t(vtMissing),

                                                _variant_t(vtMissing),

                                                _variant_t(vtMissing),

                                                _variant_t(vtMissing),

                                                _variant_t(vtMissing),

                                                0,

                                                _variant_t(vtMissing),

                                                _variant_t(vtMissing),

                                                _variant_t(vtMissing),

                                                _variant_t(vtMissing),

                                                _variant_t(vtMissing) );

         oWorkBook.Close( covOptional, COleVariant( strTempFilePath ), covOptional );

 

          解析： Worksheet 的 copy 函数的两个参数是移动的位置，在哪个 Worksheet 前，在哪个 Worksheet 后。如果两个参数都不指定，则是复制到新的 Workbook 中，这个新的 Workbook 是所有 Workbook 的最后一个，所以就可以用 oWorkBook.AttachDispatch( oWorkBooks.GetItem( COleVariant(oWorkBooks.GetCount()) ), TRUE ); 来找到。