exampleview.cpp

c++源码工程文件 读取Excel数据库中的数据

// ExampleView.cpp : implementation of the CExampleView class
//

#include "stdafx.h"
#include "Example.h"

#include "ExampleDoc.h"
#include "ExampleView.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CExampleView

IMPLEMENT_DYNCREATE(CExampleView, CView)

BEGIN_MESSAGE_MAP(CExampleView, CView)
	//{{AFX_MSG_MAP(CExampleView)
	ON_COMMAND(ID_CREAT_EXCEL, OnCreatExcel)
	ON_COMMAND(ID_READ_DATA, OnReadData)
	//}}AFX_MSG_MAP
	// Standard printing commands
	ON_COMMAND(ID_FILE_PRINT, CView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_DIRECT, CView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_PREVIEW, CView::OnFilePrintPreview)
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CExampleView construction/destruction

CExampleView::CExampleView()
{
	// TODO: add construction code here
	m_Flag=FALSE;

}

CExampleView::~CExampleView()
{
}

BOOL CExampleView::PreCreateWindow(CREATESTRUCT& cs)
{
	// TODO: Modify the Window class or styles here by modifying
	//  the CREATESTRUCT cs

	return CView::PreCreateWindow(cs);
}

/////////////////////////////////////////////////////////////////////////////
// CExampleView drawing

void CExampleView::OnDraw(CDC* pDC)
{
	CExampleDoc* pDoc = GetDocument();
	ASSERT_VALID(pDoc);
	// TODO: add draw code for native data here 
	if(m_Flag==TRUE)
	{
	CDatabase m_database;
	CString strSQL,strDriver,strDSN;
	CString strFile = "D:\\ExcelFile.xls";// 将被读取的Excel文件名
	
	// 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)"
	strDriver = GetExcelDriver();
	if (strDriver.IsEmpty())
	{
		// 没有发现Excel驱动
		AfxMessageBox("没有安装Excel驱动!");
		return;
	}
	// 创建进行存取的字符串
	strDSN.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",
		strDriver,strFile);
	TRY
	{
		// 打开数据库(既Excel文件)
		m_database.Open(NULL, false, false, strDSN);

		// 设置读取的查询语句
		strSQL = "SELECT * FROM Table1 ORDER BY StuName";
	    CRecordset Recordset(&m_database);		
		//执行查询语句
		Recordset.Open(CRecordset::forwardOnly, strSQL, 
			           CRecordset::readOnly);
		//输出数据头
		CString strID,strName,strAge;
		strID="姓名";
		strName="科目";
		strAge="成绩";
		CString str;
		str.Format("%s          %s          %s",strID,strName,strAge);
		pDC->TextOut(20,40,str);
		
		// 获取查询结果
		int i=1,m_nLineHeight=20;
		CString m_strScore,m_strName,m_strCourse;
		while (!Recordset.IsEOF())
		{
			//读取Excel数据表中的记录
			Recordset.GetFieldValue("StuName", m_strName);
			Recordset.GetFieldValue("StuCourse", m_strCourse);
			Recordset.GetFieldValue("StuScore", m_strScore);
			// 输出数据
			CString strText;
			strText.Format("%s          %s          %s",m_strName,
				           m_strCourse,m_strScore);
			pDC->TextOut(20,50+m_nLineHeight*i,strText);
			i++;
			// 移到下一行
			Recordset.MoveNext();
		}
		// 关闭数据库
		m_database.Close();
	}
	CATCH(CDBException, e)
	{
		// 数据库操作产生异常时...
		AfxMessageBox("数据库错误: " + e->m_strError);
	}
	END_CATCH;
	}
}

/////////////////////////////////////////////////////////////////////////////
// CExampleView printing

BOOL CExampleView::OnPreparePrinting(CPrintInfo* pInfo)
{
	// default preparation
	return DoPreparePrinting(pInfo);
}

void CExampleView::OnBeginPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
	// TODO: add extra initialization before printing
}

void CExampleView::OnEndPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
	// TODO: add cleanup after printing
}

/////////////////////////////////////////////////////////////////////////////
// CExampleView diagnostics

#ifdef _DEBUG
void CExampleView::AssertValid() const
{
	CView::AssertValid();
}

void CExampleView::Dump(CDumpContext& dc) const
{
	CView::Dump(dc);
}

CExampleDoc* CExampleView::GetDocument() // non-debug version is inline
{
	ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CExampleDoc)));
	return (CExampleDoc*)m_pDocument;
}
#endif //_DEBUG

/////////////////////////////////////////////////////////////////////////////
// CExampleView message handlers

void CExampleView::OnCreatExcel() 
{
	// TODO: Add your command handler code here
	CDatabase m_database;
	CString strDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel驱动程序
	CString strExcelFile = "D:\\ExcelFile.xls";// 要建立的Excel文件
	CString strSQL;
	
	TRY
	{
		// 创建进行存取的字符串
		strSQL.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",
			strDriver,strExcelFile,strExcelFile);
		
		// 创建数据库 (既Excel表格文件)
		if( m_database.OpenEx(strSQL,CDatabase::noOdbcDialog) )
		{
			// 创建表结构(姓名、科目、成绩)			
			strSQL = "CREATE TABLE Table1 (StuName TEXT,StuCourse TEXT,StuScore NUMBER)";
			m_database.ExecuteSQL(strSQL);
			
			// 插入数值
			strSQL = "INSERT INTO Table1 (StuName,StuCourse,StuScore) VALUES ('张三','数学',80)";
			m_database.ExecuteSQL(strSQL);
			strSQL = "INSERT INTO Table1 (StuName,StuCourse,StuScore) VALUES ('李四','数学',85)";
			m_database.ExecuteSQL(strSQL);
			strSQL = "INSERT INTO Table1 (StuName,StuCourse,StuScore) VALUES ('王五','数学',78)";
			m_database.ExecuteSQL(strSQL);
			strSQL = "INSERT INTO Table1 (StuName,StuCourse,StuScore) VALUES ('钱六','数学',69.5)";
			m_database.ExecuteSQL(strSQL);
/*
			strSQL = "INSERT INTO Table1 (StuIName,Age) VALUES ('肖洪伟',24)";
			database.ExecuteSQL(sSql);
			sSql = "INSERT INTO demo (Name,Age) VALUES ('蒋渝',34)";
			database.ExecuteSQL(sSql);
*/
		}
		// 关闭数据库
		m_database.Close();
	}
	CATCH_ALL(e)
	{
		TRACE1("Excel驱动没有安装: %s",strDriver);
	}
	END_CATCH_ALL;
	
}

CString CExampleView::GetExcelDriver()
{
	char szBuf[2001];
	WORD cbBufMax = 2000;
	WORD cbBufOut;
	char *pszBuf = szBuf;
	CString sDriver;
	// 获取已安装驱动的名称(涵数在odbcinst.h里)
	if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
		return "";
	// 检索已安装的驱动是否有Excel...
	do
	{
		if (strstr(pszBuf, "Excel") != 0)
		{
			//找到
			sDriver = CString(pszBuf);
			break;
		}
		pszBuf = strchr(pszBuf, '\0') + 1;
	}
	while (pszBuf[1] != '\0');
	return sDriver;


}

void CExampleView::OnReadData() 
{
	// TODO: Add your command handler code here
	m_Flag=TRUE;
	Invalidate();
	
}