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 }
posted on 2010-07-03 09:29 Nick.Zeng 阅读(1097) 评论(0) 编辑 收藏 举报
