1.读取方式
odbc+excel驱动
ocx+excel.exe(需要安装office)
2.使用倾向和注意事项
因为安装的win10 没有安装 office,所以选择了odbc方式。
在mfc方式下直接使用,会弹出 ODBC Excel驱动程序登入失败,处理方式是将 使用类封装成 mfc dll 即可。
3.读取注意
每个内容只能读取一次,读取多次会报错。应该是读写信息后对应的内存被释放了,产生野指针问题。
使用 CRecordset::snapshot 后方可前后移动游标。
3.odbc+ 示例代码
#include "Cdisease.h"
DLL_API KeyPersonnel* getDisease()
{
return new Cdisease;
}
//
Cdisease::Cdisease()
{
}
Cdisease::~Cdisease()
{
}
// Get ODBC Excel Driver
CString Cdisease::getExcelDriver()
{
wchar_t szBuf[2001];
wchar_t wcExcel[] = L"Excel";
WORD cbBufMax = 2000;
WORD cbBufOut;
wchar_t *pszBuf = szBuf;
CString sDriver;
// Get All Install Driver(Function in odbcinst.h)
if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
return L"";
do
{
if (wcsstr(pszBuf, wcExcel) != 0) // Find subStr "Excel"
{
sDriver = CString(pszBuf);
break;
}
pszBuf = wcschr(pszBuf, '\0') + 1;
} while (pszBuf[1] != '\0');
return sDriver;
}
keyPersonInfo *Cdisease::readExel(CString excelPath)
{
CDatabase DB;
CString sSQL;
CString sDataSourceConnString;
CString sDriver = getExcelDriver();
if (sDriver.IsEmpty())
{
return NULL;
}
// Create ODBC Data Source Connection String
sDataSourceConnString.Format(L"ODBC;DRIVER={%s};DBQ=%s", sDriver, excelPath);
int rst = 0;
TRY
{
//Open Excel File
if (DB.Open(NULL, false, true, sDataSourceConnString))
{
CRecordset DBSet(&DB);
CString sTableName = L"Sheet1";
sSQL.Format(L"SELECT * FROM [%s$]", sTableName);
if (DBSet.Open(CRecordset::snapshot, sSQL, CRecordset::readOnly))
{
auto rst1 = getDiseaseInfo(&DBSet);
DBSet.Close();
}
DB.Close();
}
}
CATCH(CDBException, e)
{
//MessageBox("Database Error:" + e->m_strError);
rst = 2;
return NULL;
}
END_CATCH;
return &m_diseaseInfo;
}
int Cdisease::getDiseaseInfo(CRecordset *set)
{
getBelongto(set, m_diseaseInfo.person.belongto);
getManInfo(set, m_diseaseInfo.person);
getVisits(set, m_diseaseInfo.person.visits);
return 1;
}
int Cdisease::getVisits(CRecordset *set, std::queue<CString> &visit)
{
int nameCol = 1;
roomInfo room;
while (!set->IsEOF()) // room name
{
CString tmp;
set->GetFieldValue(nameCol, tmp);
if (tmp != "") {
OutputDebugString(L"getVisits\n");
set->GetFieldValue(nameCol + 1, tmp);
if ("" != tmp)
{
visit.push(tmp);
}
}
else {
break;
}
set->MoveNext();
}
return visit.size();
}
int Cdisease::getBelongto(CRecordset *set, std::queue<CString> &belong)
{
CODBCFieldInfo fieldinfo;
short index = 4;
auto len = set->GetODBCFieldCount();
for (index = 0; index < len; index++) {
set->GetODBCFieldInfo(index, fieldinfo);
if (fieldinfo.m_strName.GetLength() > 4) {
belong.push(fieldinfo.m_strName);
}
}
return belong.size();
}
void Cdisease::recodeMoves(CRecordset *set) {
if (!set)
{
return;
}
t_recodeMove += 1;
if (t_recodeMove > m_recodeMove)
{
m_recodeMove = t_recodeMove;
}
set->MoveNext();
}
void Cdisease::eraseMoves(CRecordset *set) {
if (t_recodeMove > m_recodeMove) {
while (t_recodeMove - m_recodeMove > 1) { //移动指针到最末尾
t_recodeMove -= 1;
set->MovePrev();
}
}
else {
while (m_recodeMove - t_recodeMove > 1) { //移动指针到最末尾
m_recodeMove -= 1;
set->MoveNext();
}
}
m_recodeMove = 0;
t_recodeMove = 0;
}
void Cdisease::resetMoves(CRecordset *set) {
while (t_recodeMove--)
{
if (set) {
set->MovePrev();
}
}
}
int Cdisease::getManInfo(CRecordset *set, diseaseInfo &person)
{
bool findName = false;
int nameCol = 1;
while (!set->IsEOF())
{
if (!findName) {
CString tmp;
set->GetFieldValue(nameCol + 1, tmp);
if (tmp == "姓名")
{
findName = true;
}
recodeMoves(set);
}
else {
OutputDebugString(L"getManInfo\n");
set->GetFieldValue(nameCol , person.manType);
set->GetFieldValue(nameCol + 1, person.man.name);
set->GetFieldValue(nameCol + 2, person.sex);
set->GetFieldValue(nameCol + 3, person.man.id);
set->GetFieldValue(nameCol + 4, person.native);
set->GetFieldValue(nameCol + 5, person.man.dress);
set->GetFieldValue(nameCol + 6, person.man.tel);
set->GetFieldValue(nameCol + 7, person.level);
recodeMoves(set);
break;
}
}
return 1;
}