程序虽简单,但很多细节的地方不亲手做是体会不到其中的滋味的。
直接上图:
下载地址(百度网盘)(已更新,支持XP及以下系统系统,内含VC支持库)
核心代码:(V1.0版,部分细节已修改,未更新)
//ExcelHelper.h
#pragma once
#include "stdafx.h"
#include "afxdb.h"
//struct
struct ExcHelp{
CString ID1;
CString Name1;
CString ID2;
CString Name2;
CString Content2;
};
//
class CExcelHelper
{
private:
CDatabase m_db;
public:
ExcHelp m_ExcHelp;
CString m_FilePath;
CString m_strSQLcon;
CString m_SheetName[3];
CString m_Title[5];
public:
void InitHelper(CString m_FilePath);
void Test(CString a,int b);
CString getString(ExcHelp EH);
public:
CExcelHelper(void);
~CExcelHelper(void);
};
//ExcelHelper.cpp
#include "stdafx.h"
#include "ExcelHelper.h"
CExcelHelper::CExcelHelper(void)
{
m_SheetName[0]=_T("[Sheet1$]");
m_SheetName[1]=_T("[Sheet2$]");
m_SheetName[2]=_T("[Sheet3$]");
m_Title[0]=_T("ID1");
m_Title[1]=_T("Name1");
m_Title[2]=_T("ID2");
m_Title[3]=_T("Name2");
m_Title[4]=_T("Content2");
}
CExcelHelper::~CExcelHelper(void)
{
if(m_db.IsOpen()){
m_db.Close();
}
}
void CExcelHelper::InitHelper(CString m_FilePath)
{
//Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;
//"Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 5.0;Data Source=c:\book1.xls"
BOOL ret;
m_strSQLcon.Format(_T("Driver={Microsoft Excel Driver (*.xls)};FIRSTROWHASNAMES=1;READONLY=FALSE;Dbq=%s;"),m_FilePath);
//m_strSQLcon.Format(_T("Driver={Provider=Microsoft.Jet.OLEDB.4.0;Extended properties=Excel 5.0;Data Source=%s;"),m_FilePath);
ret = m_db.OpenEx(m_strSQLcon);
if(!ret) AfxMessageBox(_T("数据库打开失败"));
}
CString CExcelHelper::getString(ExcHelp EH)
{
CString temp;
temp.Format(_T("ID1: %s\tName1: %s\nID2: %s\tName2: %s\tContent2: %s"),EH.ID1,EH.Name1,EH.ID2,EH.Name2,EH.Content2);
return temp;
}
void CExcelHelper::Test(CString a,int b)
{
CRecordset m_rs(&m_db);
CRecordset m_rs0(&m_db);
CString temp,sqlstr,sqlstr2;
//读取Sheet1
sqlstr.Format(_T("select * from %s"),m_SheetName[0]);
m_rs0.Open(CRecordset::dynaset,sqlstr,CRecordset::readOnly);
//第一次查询(第一个表)
for(int a=0;a<m_rs0.GetRecordCount();a++)
{
m_rs0.GetFieldValue((short)0,temp);
m_ExcHelp.ID1=temp;
m_rs0.GetFieldValue((short)1,temp);
m_ExcHelp.Name1=temp;
//AfxMessageBox(getString(m_ExcHelp));
//第二次查询,第二个表
//查询Sheet2
sqlstr2.Format(_T("select * from %s where %s='%s'"),m_SheetName[1],m_Title[3],m_ExcHelp.Name1);
//AfxMessageBox(sqlstr2);
m_rs.Open(CRecordset::dynaset,sqlstr2,CRecordset::readOnly);
for(int i=0;i<m_rs.GetRecordCount();i++)
{
m_rs.GetFieldValue((short)0,temp);
m_ExcHelp.ID2=temp;
m_rs.GetFieldValue((short)1,temp);
m_ExcHelp.Name2=temp;
m_rs.GetFieldValue((short)2,temp);
m_ExcHelp.Content2=temp;
//AfxMessageBox(getString(m_ExcHelp));
temp.Format(_T("insert into %s(%s,%s,%s,%s,%s) values('%s','%s','%s','%s','%s')"),
m_SheetName[2],m_Title[0],m_Title[1],m_Title[2],m_Title[3],m_Title[4],
m_ExcHelp.ID1,m_ExcHelp.Name1,m_ExcHelp.ID2,m_ExcHelp.Name2,m_ExcHelp.Content2
);
// AfxMessageBox(temp);
m_db.ExecuteSQL(temp);
m_rs.MoveNext();
}
m_rs.Close();
m_rs0.MoveNext();
}
m_rs0.Close();
m_db.Close();
AfxMessageBox(_T("处理完成,结果存在[Sheet3]中,谢谢使用!"));
}