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::Delete;
140 iter->second->ResetCells();
141 }
142 else
143 {
144 CString strSQL;
145 strSQL.Format (_T("DROP TABLE [%s$A1:IV65536]"), strSheetName);
146 try
147 {
148 m_db.ExecuteSQL(strSQL);
149 }
150 catch (CDBException* e)
151 {
152 return ;
153 }
154 }
155}
156
157//////////////////////////////////////////////////////////////////////////
158CODBCExcelSheet::CODBCExcelSheet(CODBCExcel& Excel, const CString& strName):
159m_strName(strName),
160m_Excel(Excel),
161m_recordset(&Excel.m_db)
162{
163 m_dwCols = m_dwRows = 0;
164}
165
166bool CODBCExcelSheet::Init()
167{
168 DWORD dwCol;
169 m_ColHeaders.RemoveAll();
170 for (dwCol = 0; dwCol < m_recordset.m_nResultCols; ++dwCol)
171 {
172 m_recordset.m_rgODBCFieldInfos[dwCol].m_strName.Trim();
173 m_ColHeaders.Add(m_recordset.m_rgODBCFieldInfos[dwCol].m_strName);
174 }
175 m_dwCols = m_recordset.m_nResultCols;
176 m_nFlag = CODBCExcelSheet::Exist;
177 CString strText;
178 for (DWORD dwRow = 0;!m_recordset.IsEOF();)
179 {
180 try
181 {
182 CDBVariant dbVal;
183 for (dwCol = 0; dwCol < m_recordset.m_nResultCols; ++dwCol)
184 {
185 m_recordset.GetFieldValue(dwCol, dbVal);
186 Cell(dwRow, dwCol)->Set(dbVal);
187 /*m_recordset.GetFieldValue(dwCol, strText);
188 Cell(dwRow, dwCol)->Set(strText); */
189 }
190 ++dwRow; m_recordset.MoveNext();
191 }
192 catch (CDBException* e)
193 {
194 m_recordset.Close();
195 return false;
196 }
197 }
198 m_recordset.Close();
199 UpdateRowCount();
200 return true;
201}
202
203void CODBCExcelSheet::UpdateRowCount()
204{
205 for (;!m_Cells.empty();)
206 {
207 vector<CODBCExcelCell>& vecCol = m_Cells.back();
208 vector<CODBCExcelCell>::iterator iter;
209 for (iter = vecCol.begin(); iter != vecCol.end(); ++iter)
210 {
211 if (!(*iter).m_strVal.IsEmpty()) break;
212 }
213 if (iter == vecCol.end())
214 {
215 m_Cells.pop_back();
216 }
217 else
218 {
219 break;
220 }
221 }
222 m_dwRows = m_Cells.size();
223}
224
225bool CODBCExcelSheet::Save()
226{
227 CString strSQL;
228 switch(m_nFlag)
229 {
230 case Update:
231 {
232 strSQL.Format(_T("DROP TABLE [%s$A1:IV65536]"), m_strName);
233 try
234 {
235 m_Excel.m_db.ExecuteSQL(strSQL);
236 }
237 catch (CDBException* e)
238 {
239 return false;
240 }
241 //$A1:IV65536
242 strSQL.Format(_T("CREATE TABLE [%s$A1:IV65536] ("), m_strName);
243 for (int i = 0; i < m_dwCols; ++i)
244 {
245 strSQL += _T("[") + m_ColHeaders.GetAt(i) +_T("]") + _T(" char(255), ");
246 }
247 strSQL.Delete(strSQL.GetLength()-2, 2);
248 strSQL += _T(")");
249 try
250 {
251 m_Excel.m_db.ExecuteSQL(strSQL);
252 }
253 catch (CDBException* e)
254 {
255 return false;
256 }
257 UpdateCells();
258 }
259 break;
260
261 case Add:
262 {
263 strSQL.Format(_T("CREATE TABLE [%s] ("), m_strName);
264 for (int i = 0; i < m_dwCols; ++i)
265 {
266 strSQL += _T("[") + m_ColHeaders.GetAt(i) +_T("]") + _T(" char(255), ");
267 }
268 strSQL.Delete(strSQL.GetLength()-2, 2);
269 strSQL += _T(")");
270 try
271 {
272 m_Excel.m_db.ExecuteSQL(strSQL);
273 }
274 catch (CDBException* e)
275 {
276 return false;
277 }
278 UpdateCells();
279 }
280 break;
281
282 case Delete:
283 {
284 strSQL.Format (_T("DROP TABLE [%s$A1:IV65536]"), m_strName);
285 try
286 {
287 m_Excel.m_db.ExecuteSQL(strSQL);
288 }
289 catch (CDBException* e)
290 {
291 return false;
292 }
293 }
294 break;
295 }
296 return true;
297}
298
299bool CODBCExcelSheet::UpdateCells()
300{
301 CString strSQL, strSQLCol;
302 for (DWORD dwRow = 0, dwCol; dwRow < m_dwRows; ++dwRow)
303 {
304 strSQL.Format(_T("INSERT INTO [%s$A1:IV%d] ("), m_strName, dwRow+1);
305 for (dwCol = 0; dwCol < m_dwCols; ++dwCol)
306 {
307 strSQLCol.Format(_T("[%s], "), m_ColHeaders.GetAt(dwCol));
308 strSQL += strSQLCol;
309 }
310 strSQL.Delete(strSQL.GetLength()-2, 2);
311 strSQL += _T(") VALUES (");
312 for (dwCol = 0; dwCol < m_dwCols; ++dwCol)
313 {
314 strSQLCol.Format(_T("'%s', "), Cell(dwRow, dwCol)->GetText());
315 strSQL += strSQLCol;
316 }
317 strSQL.Delete(strSQL.GetLength()-2, 2);
318 strSQL += _T(")");
319 try
320 {
321 m_Excel.m_db.ExecuteSQL(strSQL);
322 }
323 catch (CDBException* e)
324 {
325 return false;
326 }
327 }
328 return true;
329}
330
331void CODBCExcelSheet::ResetCells()
332{
333 for (DWORD dwRow = 0; dwRow < m_dwRows; ++dwRow)
334 {
335 for (DWORD dwCol = 0; dwCol < m_dwCols; ++dwCol)
336 {
337 m_Cells[dwRow][dwCol].Empty();
338 }
339 }
340}
341
342//rowIndex >= 1 colIndex >= 0
343CODBCExcelCell* CODBCExcelSheet::Cell(UINT rowIndex, UINT colIndex)
344{
345 if (rowIndex > 65535 || colIndex > 255) return 0;
346 if (colIndex >= m_dwCols) return 0;
347
348 if (rowIndex >= m_dwRows)
349 {
350 m_dwRows += 1;
351 m_Cells.resize(m_dwRows);
352 for (DWORD dwRow = 0; dwRow < m_dwRows; ++dwRow)
353 {
354 m_Cells[dwRow].resize(m_dwCols);
355 for (DWORD dwCol = 0; dwCol < m_dwCols; ++dwCol)
356 {
357 m_Cells[dwRow][dwCol].SetParent(this);
358 }
359 }
360 }
361 return &m_Cells[rowIndex][colIndex];
362}
363
364CODBCExcelCell* CODBCExcelSheet::Cell(UINT rowIndex, const CString& strColName)
365{
366 if (rowIndex > 65535) return 0;
367
368 ASSERT(m_dwCols == m_ColHeaders.GetCount());
369 UINT colIndex;
370 for (colIndex = 0; colIndex < m_dwCols; ++colIndex)
371 {
372 if (m_ColHeaders[colIndex] == strColName)
373 {
374 break;
375 }
376 }
377 if (colIndex >= m_dwCols) return 0;
378 if (rowIndex >= m_dwRows)
379 {
380 m_dwRows += 1;
381 m_Cells.resize(m_dwRows);
382 for (DWORD dwRow = 0; dwRow < m_dwRows; ++dwRow)
383 {
384 m_Cells[dwRow].resize(m_dwCols);
385 for (DWORD dwCol = 0; dwCol < m_dwCols; ++dwCol)
386 {
387 m_Cells[dwRow][dwCol].SetParent(this);
388 }
389 }
390 }
391 return &m_Cells[rowIndex][colIndex];
392}
393
394int CODBCExcelSheet::GetColHeader(const CString& strColName) const
395{
396 for (int col = 0; col < m_dwCols; ++col)
397 {
398 if (m_ColHeaders[col] == strColName)
399 return col;
400 }
401 return -1;
402}
403
404int CODBCExcelSheet::GetRow(const CString& strColName, const CString& strCellText)
405{
406 int col = GetColHeader(strColName);
407 if (-1 == col) return -1;
408 for (int row = 0; row < m_dwRows; ++row)
409 {
410 if (m_Cells[row][col].m_strVal == strCellText)
411 return row;
412 }
413 return -1;
414}
415
416//////////////////////////////////////////////////////////////////////////
417CODBCExcelCell::CODBCExcelCell():
418m_Sheet(NULL)
419{
420}
421
422void CODBCExcelCell::Set(const CDBVariant& dbVal)
423{
424 switch(dbVal.m_dwType)
425 {
426 case DBVT_NULL:
427 case DBVT_DATE:
428 case DBVT_BINARY:
429 m_strVal.Empty();
430 break;
431
432 case DBVT_BOOL:
433 if (dbVal.m_boolVal) m_strVal = _T("0");
434 else m_strVal = _T("1");
435 break;
436
437 case DBVT_UCHAR:
438 m_strVal.Format(_T("%c"), dbVal.m_chVal);
439 break;
440
441 case DBVT_SHORT:
442 m_strVal.Format(_T("%d"), dbVal.m_iVal);
443 break;
444
445 case DBVT_LONG:
446 m_strVal.Format(_T("%ld"), dbVal.m_lVal);
447 break;
448
449 case DBVT_SINGLE:
450 {
451 long lVal = (long)dbVal.m_fltVal;
452 if (dbVal.m_fltVal != (float)lVal)
453 m_strVal.Format(_T("%f"), dbVal.m_fltVal);
454 else
455 m_strVal.Format(_T("%ld"), lVal);
456 }
457 break;
458
459 case DBVT_DOUBLE:
460 {
461 long lVal = (long)dbVal.m_dblVal;
462 if (dbVal.m_dblVal != (double)lVal)
463 m_strVal.Format(_T("%f"), dbVal.m_dblVal);
464 else
465 m_strVal.Format(_T("%ld"), lVal);
466 }
467 break;
468
469 case DBVT_STRING:
470 m_strVal = *dbVal.m_pstring; m_strVal.Trim();
471 break;
472
473 case DBVT_ASTRING:
474 m_strVal = *dbVal.m_pstringA; m_strVal.Trim();
475 break;
476
477 case DBVT_WSTRING:
478 m_strVal = *dbVal.m_pstringW; m_strVal.Trim();
479 break;
480
481 default: ASSERT(FALSE); break;
482 }
483}
484
485void CODBCExcelCell::Set(const CString& strVal)
486{
487 ASSERT(m_Sheet);
488 if (CODBCExcelSheet::Exist == m_Sheet->m_nFlag)
489 {
490 m_Sheet->m_nFlag = CODBCExcelSheet::Update;
491 }
492 m_strVal = strVal; m_strVal.Trim();
493}
494
495void CODBCExcelCell::Set(short sVal)
496{
497 ASSERT(m_Sheet);
498 if (CODBCExcelSheet::Exist == m_Sheet->m_nFlag)
499 {
500 m_Sheet->m_nFlag = CODBCExcelSheet::Update;
501 }
502 m_strVal.Format(_T("%d"), sVal);
503}
504
505void CODBCExcelCell::Set(long lVal)
506{
507 ASSERT(m_Sheet);
508 if (CODBCExcelSheet::Exist == m_Sheet->m_nFlag)
509 {
510 m_Sheet->m_nFlag = CODBCExcelSheet::Update;
511 }
512 m_strVal.Format(_T("%ld"), lVal);
513}
514
515void CODBCExcelCell::Set(float fVal)
516{
517 ASSERT(m_Sheet);
518 if (CODBCExcelSheet::Exist == m_Sheet->m_nFlag)
519 {
520 m_Sheet->m_nFlag = CODBCExcelSheet::Update;
521 }
522 m_strVal.Format(_T("%f"), fVal);
523}
524
525void CODBCExcelCell::Set(double dVal)
526{
527 ASSERT(m_Sheet);
528 if (CODBCExcelSheet::Exist == m_Sheet->m_nFlag)
529 {
530 m_Sheet->m_nFlag = CODBCExcelSheet::Update;
531 }
532 m_strVal.Format(_T("%f"), dVal);
533}
作者“天道酬勤”
