Reading Excel files using ODBC使用ODBC读Excel文件

// ReadExcelDlg.cpp : Implementierungsdatei // #include "stdafx.h" #include "ReadExcel.h" #include "ReadExcelDlg.h" #include "odbcinst.h" #ifdef _DEBUG #define new DEBUG_NEW #undef THIS_FILE static char THIS_FILE[] = __FILE__; #endif ///////////////////////////////////////////////////////////////////////////// // CReadExcelDlg Dialogfeld CReadExcelDlg::CReadExcelDlg(CWnd* pParent /*=NULL*/) : CDialog(CReadExcelDlg::IDD, pParent) { //{{AFX_DATA_INIT(CReadExcelDlg) // HINWEIS: Der Klassenassistent f黦t hier Member-Initialisierung ein //}}AFX_DATA_INIT m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME); } void CReadExcelDlg::DoDataExchange(CDataExchange* pDX) { CDialog::DoDataExchange(pDX); //{{AFX_DATA_MAP(CReadExcelDlg) DDX_Control(pDX, IDC_LIST1, m_ctrlList); //}}AFX_DATA_MAP } BEGIN_MESSAGE_MAP(CReadExcelDlg, CDialog) //{{AFX_MSG_MAP(CReadExcelDlg) ON_WM_PAINT() ON_WM_QUERYDRAGICON() ON_BN_CLICKED(IDC_BUTTON1, OnButton1) //}}AFX_MSG_MAP END_MESSAGE_MAP() ///////////////////////////////////////////////////////////////////////////// // CReadExcelDlg Nachrichten-Handler BOOL CReadExcelDlg::OnInitDialog() { CDialog::OnInitDialog(); SetIcon(m_hIcon, TRUE); // Gro遝s Symbol verwenden SetIcon(m_hIcon, FALSE); // Kleines Symbol verwenden // ZU ERLEDIGEN: Hier zus鋞zliche Initialisierung einf黦en return TRUE; // Geben Sie TRUE zur點k, au遝r ein Steuerelement soll den Fokus erhalten } // Wollen Sie Ihrem Dialogfeld eine Schaltfl鋍he "Minimieren" hinzuf黦en, ben鰐igen Sie // den nachstehenden Code, um das Symbol zu zeichnen. F黵 MFC-Anwendungen, die das // Dokument/Ansicht-Modell verwenden, wird dies automatisch f黵 Sie erledigt. void CReadExcelDlg::OnPaint() { if (IsIconic()) { CPaintDC dc(this); // Ger鋞ekontext f黵 Zeichnen SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0); // Symbol in Client-Rechteck zentrieren int cxIcon = GetSystemMetrics(SM_CXICON); int cyIcon = GetSystemMetrics(SM_CYICON); CRect rect; GetClientRect(&rect); int x = (rect.Width() - cxIcon + 1) / 2; int y = (rect.Height() - cyIcon + 1) / 2; // Symbol zeichnen dc.DrawIcon(x, y, m_hIcon); } else { CDialog::OnPaint(); } } HCURSOR CReadExcelDlg::OnQueryDragIcon() { return (HCURSOR) m_hIcon; } //****************************************************************** // Read that Excel Sheet //****************************************************************** // The method OnButton1() and GetExcelDriver() demonstrate how // an Excel file can be read. Besides that two more interesting // features are demonstrated: // 1) The use of ODBC without having a complete DSN // installed in the ODBC manager // 2) The use of CRecordset without having a class // derived from it // // But there have to be preparations: // You must have an Excel ODBC Driver installed (you // wouldn磘 have guessed..). And there has to be database support, // so including <afxdb.h> is really not a bad idea. Last but // not least, if you want to determine the full name of that // Excel driver automagically (like I did in GetExcelDriver() ) // you need "odbcinst.h" to be included also. // // And now for the drawbacks: // Feature 1) only works with ODBC Admin V3.51 and higher. // Earlier versions will not be able to use a DSN that actually // isn磘 installed. // Feature 2) needs to be a readonly, foreward only recset. // So any attempts to change the data or to move back will // fail horribly. If you need to do something like that you磖e // bound to use CRecordset the "usual" way. Another drawback is // that the tremendous overhead of CRecordset does in fact make // it rather slow. A solution to this would be using the class // CSQLDirect contributed by Dave Merner at codeguru磗 // http://www.codeguru.com/mfc_database/direct_sql_with_odbc.shtml // // Corresponding articles: // For more stuff about writing into an Excel file or using a not // registered DSN please refer my article // http://www.codeguru.com/mfc_database/excel_sheets_using_odbc.shtml // // There磗 still work to do: // One unsolved mystery in reading those files is how to get the // data WITHOUT having a name defined for it. That means // how can the structure of the data be retrieved, how many // "tables" are in there, and so on. If you have any idea about // that I磀 be glad to read it under almikula@EUnet.at (please // make a CC to alexander.mikula@siemens.at) // // // After my article at CodeGuru磗 concerning how to write into an Excel // file I got tons of requests about how to read from such a file. // Well in fact I do hope this - however enhancable - example sorts // out the basic questions. // // Have fun! // Alexander Mikula - The Famous CyberRat //****************************************************************** void CReadExcelDlg::OnButton1() { CDatabase database; CString sSql; CString sItem1, sItem2; CString sDriver; CString sDsn; CString sFile = "ReadExcel.xls"; // the file name. Could also be something like C:\\Sheets\\WhatDoIKnow.xls // Clear the contents of the listbox m_ctrlList.ResetContent(); // Retrieve the name of the Excel driver. This is // necessary because Microsoft tends to use language // specific names like "Microsoft Excel Driver (*.xls)" versus // "Microsoft Excel Treiber (*.xls)" sDriver = GetExcelDriver(); if( sDriver.IsEmpty() ) { // Blast! We didn磘 find that driver! AfxMessageBox("No Excel ODBC driver found"); return; } // Create a pseudo DSN including the name of the Driver and the Excel file // so we don磘 have to have an explicit DSN installed in our ODBC admin sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,sFile); TRY { // Open the database using the former created pseudo DSN database.Open(NULL,false,false,sDsn); // Allocate the recordset CRecordset recset( &database ); // Build the SQL string // Remember to name a section of data in the Excel sheet using "Insert->Names" to be // able to work with the data like you would with a table in a "real" database. There // may be more than one table contained in a worksheet. sSql = "SELECT field_1, field_2 " "FROM demo_table " "ORDER BY field_1"; // Execute that query (implicitly by opening the recordset) recset.Open(CRecordset::forwardOnly,sSql,CRecordset::readOnly); // Browse the result while( !recset.IsEOF() ) { // Read the result line recset.GetFieldValue("field_1",sItem1); recset.GetFieldValue("field_2",sItem2); // Insert result into the list m_ctrlList.AddString( sItem1 + " --> "+sItem2 ); // Skip to the next resultline recset.MoveNext(); } // Close the database database.Close(); } CATCH(CDBException, e) { // A database exception occured. Pop out the details... AfxMessageBox("Database error: "+e->m_strError); } END_CATCH; } // Get the name of the Excel-ODBC driver CString CReadExcelDlg::GetExcelDriver() { char szBuf[2001]; WORD cbBufMax = 2000; WORD cbBufOut; char *pszBuf = szBuf; CString sDriver; // Get the names of the installed drivers ("odbcinst.h" has to be included ) if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut)) return ""; // Search for the driver... do { if( strstr( pszBuf, "Excel" ) != 0 ) { // Found ! sDriver = CString( pszBuf ); break; } pszBuf = strchr( pszBuf, '\0' ) + 1; } while( pszBuf[1] != '\0' ); return sDriver; }