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 }
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
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
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