Excel开发之Sheet操作（一）

　　Excel开发之Sheet常用操作篇：环境：Win7 + VS08, MS Office 2003

　　//----------------------------------------------

　　注：

　　　　　　_ApplicationPtr		m_pApp;
		WorkbooksPtr		m_pBooks;
		_WorkbookPtr		m_pBook;
		SheetsPtr			m_pSheets;

　　//----------------------------------------------

 

 

　　1.插入一张新Sheet：

插入新Sheet

1 BOOL MyExcel::CExcel::InsertSheet( char * pSheetName, int iAfterPos)
2 {
3 long lNum = m_pSheets -> GetCount();
4 BOOL bIsNew = FALSE;
5
6 if (pSheetName == 0 )
7 {
8 pSheetName = new char [ 8 ];
9 bIsNew = TRUE;
10 wsprintfA(pSheetName, " %s%d " , " sheet " ,lNum);
11 }
12
13 _WorksheetPtr pSheet = m_pSheets -> GetItem(lNum);
14
15 VARIANT var;
16 var.vt = VT_DISPATCH;
17 var.pdispVal = pSheet;
18 m_pSheets -> Add(vtMissing, var);
19 pSheet = m_pSheets -> GetItem(m_pSheets -> GetCount());
20 pSheet -> PutName(pSheetName);
21
22 try
23 {
24 if (iAfterPos <= 0 )
25 {
26 // 最前面
27   _WorksheetPtr pSheetBefore = m_pSheets -> GetItem( 1 );
28 var.pdispVal = pSheetBefore;
29 pSheet -> Move(var);
30 }
31 else if (iAfterPos < lNum)
32 {
33 // 中间
34   _WorksheetPtr pSheetAfter = m_pSheets -> GetItem(iAfterPos);
35 var.pdispVal = pSheetAfter;
36 pSheet -> Move(vtMissing, var);
37 }
38 }
39 catch (_com_error & e)
40 {
41 sprintf_s(m_chLastError, 1024 , " InsertSheet: (%s) Pos: %d " , pSheetName, iAfterPos);
42 strcat_s(m_chLastError, 1024 , CW2A(e.ErrorMessage()));
43 MyTrace(m_chLastError);
44 m_pLog -> LOG_WriteLine(LOG_INFO_ERROR, " %s " , m_chLastError);
45 }
46
47 if (bIsNew)
48 {
49 delete pSheetName;
50 }
51
52 pSheet -> Activate();
53 MyTrace( " InsertSheet(): path=%s afterpos=%d " , pSheetName, iAfterPos);
54 return TRUE;
55 }
56  

 

 

      2.设置Sheet名称：

 

    

设置Sheet名称

1 BOOL MyExcel::CExcel::SetSheetName( int nIndex, char * pSheetName)
2 {
3 if (nIndex <= 0 )
4 {
5 m_pLog -> LOG_WriteLine(LOG_INFO_WARN, " SetSheetName: (%d, %s) invalid param " , nIndex, pSheetName);
6 return FALSE;
7 }
8
9 if (pSheetName == NULL)
10 {
11 m_pLog -> LOG_WriteLine(LOG_INFO_WARN, " SetSheetName: (%d, %s) invalid param " , nIndex, pSheetName);
12 return FALSE;
13 }
14
15 if (nIndex > ( int )m_pSheets -> GetCount())
16 {
17 m_pLog -> LOG_WriteLine(LOG_INFO_WARN, " SetSheetName: (%d, %s) invalid param " , nIndex, pSheetName);
18 return FALSE;
19 }
20
21 _WorksheetPtr pSheet = m_pSheets -> GetItem(nIndex);
22 pSheet -> PutName(pSheetName);
23 MyTrace( " SetSheetName(): index=%d Name=%s " , nIndex, pSheetName);
24 return TRUE;
25 }

 

 

      3.激活Sheet

　　　 a).按索引

 

    

按索引激活Sheet

1 BOOL MyExcel::CExcel::SetSheetActive( int nIndex)
2 {
3 if (nIndex <= 0 )
4 {
5 return FALSE;
6 }
7
8 if (nIndex > ( int )m_pSheets -> GetCount())
9 {
10 return FALSE;
11 }
12
13 _WorksheetPtr pSheet = m_pSheets -> GetItem(nIndex);
14 if (NULL == pSheet)
15 {
16 m_pLog -> LOG_WriteLine(LOG_INFO_ERROR, " SetSheetActive:(%d) GetSheetPre fails " , nIndex);
17 return FALSE;
18 }
19 pSheet -> Activate();
20 MyTrace( " SetSheetActive(): index=%d OK " , nIndex);
21 return TRUE;
22 }

 

 

          b).按Sheet名

 

按Sheet名激活Sheet

1 BOOL MyExcel::CExcel::SetSheetActive( char * pSheetName)
2 {
3 if (pSheetName == NULL)
4 {
5 return TRUE;
6 }
7
8 _WorksheetPtr pSheet = m_pSheets -> GetItem(pSheetName);
9 pSheet -> Activate();
10 MyTrace( " SetSheetActive(): name=%s OK " , pSheetName);
11 return TRUE;
12 }

 

 

      4.获取当前激活Sheet的名称

 

1 _bstr_t MyExcel::CExcel::GetActiveSheetName()
2 {
3 _WorksheetPtr pSheet = m_pBook -> GetActiveSheet();
4 return pSheet -> GetName();
5 }

 

      5.获得当前激活工作表的索引

 

1 long MyExcel::CExcel::GetActiveSheetIndex( long lcid /* =0 */ )
2 {
3 _WorksheetPtr pSheet = m_pBook -> GetActiveSheet();
4 return pSheet -> GetIndex(lcid);
5 }

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/gray1121/archive/2010/07/03/1770337.html