readexceldlg.cpp

使用VC++对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)
	ON_BN_CLICKED(IDC_BUTTON2, Requiry)
	//}}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 [e-mail] (please 
//    make a CC to [e-mail])
//
//
// 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;
}


void CReadExcelDlg::Requiry()
{
	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 where field_1 = aaa "					
				 "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;
}