Excel开发之Sheet操作（二）
Excel开发之Sheet常用操作（二)
1.获取sheet总数
1 int MyExcel::CExcel::GetSheetCount()
2 {
3 _ASSERT(m_pSheets);
4
5 return m_pSheets->GetCount();
6 }
2.删除sheet
删除sheet
1 BOOL MyExcel::CExcel::DeleteSheet(int nIndex)
2 {
3 if (GetSheetCount() < nIndex || nIndex <= 0)
4 {
5 m_pLog->LOG_WriteLine(LOG_INFO_WARN, "DeleteSheet: (%d) invalid param", nIndex);
6 return FALSE;
7 }
8
9 _WorksheetPtr pSheet = m_pSheets->GetItem(nIndex);
10 if (!pSheet)
11 {
12 m_pLog->LOG_WriteLine(LOG_INFO_ERROR, "DeleteSheet: GetSheetPre fails");
13 return FALSE;
14 }
15
16 if (S_OK == pSheet->Delete())
17 {
18 m_pLog->LOG_WriteLine(LOG_INFO_HIT, "DeleteSheet: (%d) OK", nIndex);
19 return TRUE;
20 }
21 else
22 {
23 m_pLog->LOG_WriteLine(LOG_INFO_ERROR, "DeleteSheet: (%d) fails", nIndex);
24 return FALSE;
25 }
26
27 MyTrace("DeleteSheet(): index=%d OK", nIndex);
28 return TRUE;
29 }
3.设置Sheet标签页背景色
设置Sheet标签页背景色
1 BOOL MyExcel::CExcel::SetSheetTabColor(int nIndex, int nBkColor /* = 0 */)
2 {
3 int nSheetCount = m_pSheets->GetCount();
4 if (nSheetCount < nIndex || nIndex <= 0)
5 {
6 m_pLog->LOG_WriteLine(LOG_INFO_ERROR, "SetSheetTabColor: (%d, %d) invalid param", nIndex, nBkColor);
7 return FALSE;
8 }
9
10 _WorksheetPtr pSheet = m_pSheets->GetItem(nIndex);
11 if (!pSheet)
12 {
13 m_pLog->LOG_WriteLine(LOG_INFO_ERROR, "SetSheetTabColor: GetSheetPre fails");
14 return FALSE;
15 }
16
17 try
18 {
19 TabPtr pTab = pSheet->GetTab();
20 if (!pTab)
21 {
22 MyTrace("SetSheetTabColor(): GetTab error");
23 return FALSE;
24 }
25
26 if (nBkColor > 0)
27 {
28 pTab->PutColorIndex(XlColorIndex(nBkColor));
29 }
30 }
31 catch (_com_error& e)
32 {
33 sprintf_s(m_chLastError, 1024, "SetSheetTabColor: (%d, %d) %s", nIndex, nBkColor, CW2A(e.ErrorMessage()));
34 m_pLog->LOG_WriteLine(LOG_INFO_ERROR, m_chLastError);
35 MyTrace(m_chLastError);
36 return FALSE;
37 }
38
39 MyTrace("SetSheetTabColor(): index=%d color=%d", nIndex, nBkColor);
40 return TRUE;
41 }
42
4.移动sheet
移动sheet
1 BOOL MyExcel::CExcel::MoveSheet(char* szDestSheet, char* szBeforSheet, char* szAfterSheet)
2 {
3 _ASSERT(szDestSheet);
4 //要移动的sheet号
5 int iSheetIndex = 0;
6 int iSheetCount = m_pSheets->GetCount();
7 for (int i=1; i<=iSheetCount; i++)
8 {
9 _bstr_t btSheetName;
10 _WorksheetPtr pSheet = m_pSheets->GetItem(i);
11 btSheetName = pSheet->GetName();
12 if (strcmp((char*)btSheetName, szDestSheet) == 0)
13 {
14 iSheetIndex = i;
15 break;
16 }
17 }
18
19 _WorksheetPtr pSheet = m_pSheets->GetItem(iSheetIndex);
20 if (!pSheet)
21 {
22 return FALSE;
23 }
24
25 try
26 {
27 VARIANT var;
28 var.vt = VT_DISPATCH;
29
30 if (szBeforSheet && !szAfterSheet)
31 {
32 _WorksheetPtr pSheetBefore = m_pSheets->GetItem(szBeforSheet);
33 var.pdispVal = pSheetBefore;
34 pSheet->Move(var);
35 //移到BeforeSheet之前
36 }
37 else if (!szBeforSheet && szAfterSheet)
38 {
39 _WorksheetPtr pSheetAfter = m_pSheets->GetItem(szAfterSheet);
40 var.pdispVal = pSheetAfter;
41 pSheet->Move(var);
42 //移到AfterSheet之后
43 }
44 else
45 {
46 return FALSE;
47 }
48 }
49 catch (_com_error& e)
50 {
51 sprintf_s(m_chLastError, 1024, "MoveSheet:(%s, %s, %s) fails %s",
52 szDestSheet, szBeforSheet, szAfterSheet, e.Description());
53 MyTrace(m_chLastError);
54 m_pLog->LOG_WriteLine(LOG_INFO_ERROR, m_chLastError);
55 }
56
57 MyTrace("MoveSheet(): dest=%s before=%s after=%s OK",
58 szDestSheet, szBeforSheet, szAfterSheet);
59 return TRUE;
60 }
61
