使用odbc读写excel类: (2) 源文件
1#include "stdafx.h"
2#include "odbcexcel.h"
3using namespace std;
4
5/*************************************************************************************************
6 2009-11-2 修改了整型数据显示不一致的问题,如5454,读取时是5454.0,在excel表格显示是5454
7
8**************************************************************************************************/
9
10CString CODBCExcel::GetExcelDriver()
11{
12 TCHAR szBuf[2001];
13 WORD cbBufMax = 2000;
14 WORD cbBufOut;
15 TCHAR *pszBuf = szBuf;
16
17 CString strExcelDriver;
18 // Get the names of the installed drivers ("odbcinst.h" has to be included )
19 if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))
20 {
21 strExcelDriver = "";
22 }
23
24 // Search for the driver
25 do
26 {
27 if( _tcsstr( pszBuf, _T("Excel") ) != 0 )
28 {
29 // Found !
30 strExcelDriver = CString( pszBuf );
31 break;
32 }
33 pszBuf = _tcschr( pszBuf, _T('\0')) + 1;
34 }
35 while( pszBuf[1] != '\0' );
36
37 return strExcelDriver;
38}
39
40//////////////////////////////////////////////////////////////////////////
41CODBCExcel::CODBCExcel()
42{
43}
44
45CODBCExcel::~CODBCExcel()
46{
47 m_db.Close();
48 for (map<CString,CODBCExcelSheet*>::iterator iter = m_Sheets.begin(); iter != m_Sheets.end();)
49 {
50 delete iter->second; iter = m_Sheets.erase(iter);
51 }
52}
53
54bool CODBCExcel::Open(const CString& strFileName)
55{
56 if (m_db.IsOpen()) return true;
57
58 CString strDSN;
59 strDSN.Format(_T("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s"),
60 GetExcelDriver(), strFileName, strFileName);
61 return m_db.OpenEx(strDSN, CDatabase::noOdbcDialog) == TRUE;
62}
63
64bool CODBCExcel::Save()
65{
66 CString strSQL;
67 for (map<CString,CODBCExcelSheet*>::iterator iter = m_Sheets.begin(); iter != m_Sheets.end();++iter)
68 {
69 iter->second->Save();
70 }
71 return true;
72}
73
74void CODBCExcel::Close()
75{
76 m_db.Close();
77}
78
79CODBCExcelSheet* CODBCExcel::GetWorkSheet(const CString& strSheetName)
80{
81 CString strSQL;
82 strSQL.Format(_T("SELECT * FROM [%s$A1:IV65536]"), strSheetName);
83 CODBCExcelSheet* sheet = 0;
84 bool bNew = false;
85 map<CString,CODBCExcelSheet*>::iterator iter = m_Sheets.find(strSheetName);
86 if (iter != m_Sheets.end())
87 {
88 sheet = iter->second;
89 if (CODBCExcelSheet::Add == sheet->m_nFlag || CODBCExcelSheet::Update == sheet->m_nFlag)
90 return sheet;
91 }
92 else
93 {
94 sheet = new CODBCExcelSheet(*this, strSheetName);
95 bNew = true;
96 }
97 try
98 {
99 sheet->m_recordset.Open(CRecordset::forwardOnly, strSQL, CRecordset::readOnly);
100 }
101 catch()
102 {
103 if (sheet) sheet->m_recordset.Close();
104 if (!bNew) return sheet;
105 delete sheet; return 0;
106 }
107 sheet->Init();
108 m_Sheets.insert(make_pair(strSheetName, sheet));
109 return sheet;
110}
111
112CODBCExcelSheet* CODBCExcel::AddWorkSheet(const CString &strSheetName, const CStringArray& ColHeaders)
113{
114 CODBCExcelSheet* sheet = GetWorkSheet(strSheetName);
115 if (!sheet)
116 {
117 sheet = new CODBCExcelSheet(*this, strSheetName);
118 if (!sheet) return 0;
119 sheet->m_nFlag = CODBCExcelSheet::Add;
120 sheet->m_ColHeaders.Copy(ColHeaders);
121 m_Sheets.insert(make_pair(strSheetName, sheet));
122 }
123 else
124 {
125 sheet->m_dwRows = 0;
126 sheet->m_nFlag = CODBCExcelSheet::Update;
127 sheet->m_ColHeaders.RemoveAll();
128 sheet->m_ColHeaders.Copy(ColHeaders);
129 }
130 sheet->m_dwCols = ColHeaders.GetCount();
131 return sheet;
132}
133
134void CODBCExcel::DeleteWorkSheet(const CString& strSheetName)
135{
136 map<CString,CODBCExcelSheet*>::iterator iter = m_Sheets.find(strSheetName);
137 if (iter != m_Sheets.end())
138 {
139 iter->second->m_nFlag = CODBCExcelSheet::De
补充：软件开发 , C语言 ,
