1#ifndef _ODBCEXCEL_H
2#define _ODBCEXCEL_H
3
4/*************************************************************************************************
5 @brief ODBC讀寫Excel的C++類
6 * Author qinqing
7 * Date 2009-10-31
8 * 本類依賴於MFC,要求excel中同一列數據必須為相同類型,單元格格式設置為常規,否則會出現讀不到的情況
9 如果輸入純數值,在其前面加'符號即當做作字符串類型處理
10**************************************************************************************************/
11
12#include <odbcinst.h>
13#include <afxdb.h>
14#include <vector>
15#include <map>
16
17class CODBCExcelSheet;
18class CODBCExcelCell;
19
20//Excel文件
21class CODBCExcel
22{
23 friend class CODBCExcelSheet;
24public:
25 CODBCExcel();
26 ~CODBCExcel();
27
28 bool Open(const CString& strFileName);
29 bool Save();
30 void Close();
31
32 CODBCExcelSheet* GetWorkSheet(const CString& strSheetName);
33 CODBCExcelSheet* AddWorkSheet(const CString& strSheetName, const CStringArray& ColHeaders);
34 void DeleteWorkSheet(const CString& strSheetName);
35
36protected:
37 static CString GetExcelDriver();
38
39private:
40 std::map<CString, CODBCExcelSheet*> m_Sheets;
41 std::vector<CString> m_DeleteSheets;
42 CDatabase m_db;
43};
44
45//工作表
46class CODBCExcelSheet
47{
48 friend class CODBCExcel;
49 friend class CODBCExcelCell;
50 enum
51 {
52 Exist,
53 Update,
54 Add,
55 Delete
56 };
57
58public:
59 CODBCExcelSheet(CODBCExcel& Excel, const CString& strName);
60 CODBCExcelCell* Cell(UINT rowIndex, UINT colIndex);
61 CODBCExcelCell* Cell(UINT rowIndex, const CString& strColName);
62
63 const CStringArray& GetColHeader() const { return m_ColHeaders; }
64 int GetColHeader(const CString& strColName) const;
65 int GetRow(const CString& strColName, const CString& strCellText);
66 DWORD GetTotalRow() const { return m_dwRows; }
67 DWORD GetTotalCol() const { return m_dwCols; }
68 const CString& GetName() const { return m_strName;}
69
70protected:
71 bool Init();
72 void UpdateRowCount();
73 bool UpdateCells();
74 void ResetCells();
75 bool Save();
76
77private:
78 CRecordset m_recordset;
79 CStringArray m_ColHeaders;
80
81private:
82 DWORD m_dwRows;
83 DWORD m_dwCols;
84 CString m_strName;
85 int m_nFlag;
86
87private:
88 CODBCExcel& m_Excel;
89 std::vector<std::vector<CODBCExcelCell> > m_Cells;
90};
91
92//單元格 其內容僅純文本格式 暫時不考慮二進制和時間日期類型
93class CODBCExcelCell
94{
95 friend class CODBCExcelSheet;
96public:
97 CODBCExcelCell() ;
98
99 void Set(short sVal);
100 void Set(long lVal);
101 void Set(float fVal);
102 void Set(double dVal);
103 void Set(const CString& strVal);
104
105 CString& GetText() { return m_strVal; }
106 short GetShort() const { return (short)_ttoi(m_strVal);}
107 long GetLong() const { return _ttol(m_strVal); }
108 float GetFloat() const { return _tstof(m_strVal); }
109 double GetDouble() const { return (double)_tstof(m_strVal); }
110
111 void Empty() { m_strVal.Empty(); }
112
113protected:
114 void Set(const CDBVariant& dbVal);
115 void SetParent(CODBCExcelSheet* Sheet) { m_Sheet = Sheet; }
116 CODBCExcelSheet* m_Sheet;
117
118private:
119 CString m_strVal; //show value as string type
120};
121
122#endif
作者“天道酬勤”