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 );来找到。