想实现通过ODBC读取Excel表,并根据读取的数据做后续处理主要代码如下:
void CDialog1::OnBnClickedButton1()
{
// TODO: 在此添加控件通知处理程序代码
CDatabase database;
CString sSql;
CString sItem1, sItem2, sItem3, sItem4, sItem5;
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 开始器件, 结尾器件,开始器件名称,结尾器件名称,检测标志 "
"FROM [Sheet1$] ";
// 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("开始器件",sItem1);
recset.GetFieldValue("结尾器件",sItem2);
recset.GetFieldValue("开始器件名称",sItem3);
recset.GetFieldValue("结尾器件名称",sItem4);
recset.GetFieldValue("检测标志",sItem5);
// Insert result into the list
if(sItem5=_T("yes")) //**************
recset.MoveNext();//*********
if(sItem5==_T("no"))
{ POINT point,nextitem;
point.x=0;
point.y=0;
nextitem=ChooseDraw(point,sItem3,sItem1);
ChooseDraw( nextitem,sItem4,sItem2);
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;
}
POINT CDialog1::ChooseDraw(POINT posation, CString name,CString Item)
{ POINT newposation;
if(Item=_T("空开断路器"))
{ newposation=Draw1(posation,name);
return newposation;} if(Item=_T("端子排"))
{ newposation=Draw2(posation,name);
return newposation;}
if(Item=_T("接口连接器"))
{ newposation=Draw3(posation,name);
return newposation;
}
if(Item=_T("设备接线器"))
{ newposation=Draw4(posation,name);
return newposation;
}
if(Item=_T("继电器"))
{newposation=Draw5(posation,name);
return newposation;
}
if(Item=_T("指示灯"))
{ newposation=Draw6(posation,name);
return newposation;
}
if(Item=_T("变压器"))
{ newposation=Draw7(posation,name);
return newposation;
}
if(Item=_T("转换开关"))
{ newposation=Draw8(posation,name);
return newposation;
}
if(Item=_T("接触器"))
{newposation=Draw9(posation,name);
return newposation;
}
if(Item=_T("方便插座"))
{newposation=Draw10(posation,name);
return newposation;
}
if(Item=_T("主机机体"))
{ newposation=Draw11(posation,name);
return newposation;
}
}
读取Excel表中的内容并根据sItem5的内容来选择是否把前几项表示的元器件和名称画出来,其中Draw1()--Draw11()是画元器件的函数,ChooseDraw()是根据Excel表选择画哪个元器件,其中参数Item是用来判断元器件种类,name式元器件名称。程序编译没有错误运行时单击按钮没有反应
当我把带*号的两行注释掉后程序能画出图形的元器件名称是对的,但是元器件画出的全是第一个空开断路器的图形。希望各位大侠能帮忙看一下,谢谢了。
void CDialog1::OnBnClickedButton1()
{
// TODO: 在此添加控件通知处理程序代码
CDatabase database;
CString sSql;
CString sItem1, sItem2, sItem3, sItem4, sItem5;
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 开始器件, 结尾器件,开始器件名称,结尾器件名称,检测标志 "
"FROM [Sheet1$] ";
// 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("开始器件",sItem1);
recset.GetFieldValue("结尾器件",sItem2);
recset.GetFieldValue("开始器件名称",sItem3);
recset.GetFieldValue("结尾器件名称",sItem4);
recset.GetFieldValue("检测标志",sItem5);
// Insert result into the list
if(sItem5=_T("yes")) //**************
recset.MoveNext();//*********
if(sItem5==_T("no"))
{ POINT point,nextitem;
point.x=0;
point.y=0;
nextitem=ChooseDraw(point,sItem3,sItem1);
ChooseDraw( nextitem,sItem4,sItem2);
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;
}
POINT CDialog1::ChooseDraw(POINT posation, CString name,CString Item)
{ POINT newposation;
if(Item=_T("空开断路器"))
{ newposation=Draw1(posation,name);
return newposation;} if(Item=_T("端子排"))
{ newposation=Draw2(posation,name);
return newposation;}
if(Item=_T("接口连接器"))
{ newposation=Draw3(posation,name);
return newposation;
}
if(Item=_T("设备接线器"))
{ newposation=Draw4(posation,name);
return newposation;
}
if(Item=_T("继电器"))
{newposation=Draw5(posation,name);
return newposation;
}
if(Item=_T("指示灯"))
{ newposation=Draw6(posation,name);
return newposation;
}
if(Item=_T("变压器"))
{ newposation=Draw7(posation,name);
return newposation;
}
if(Item=_T("转换开关"))
{ newposation=Draw8(posation,name);
return newposation;
}
if(Item=_T("接触器"))
{newposation=Draw9(posation,name);
return newposation;
}
if(Item=_T("方便插座"))
{newposation=Draw10(posation,name);
return newposation;
}
if(Item=_T("主机机体"))
{ newposation=Draw11(posation,name);
return newposation;
}
}
读取Excel表中的内容并根据sItem5的内容来选择是否把前几项表示的元器件和名称画出来,其中Draw1()--Draw11()是画元器件的函数,ChooseDraw()是根据Excel表选择画哪个元器件,其中参数Item是用来判断元器件种类,name式元器件名称。程序编译没有错误运行时单击按钮没有反应
当我把带*号的两行注释掉后程序能画出图形的元器件名称是对的,但是元器件画出的全是第一个空开断路器的图形。希望各位大侠能帮忙看一下,谢谢了。
void CDialog1::OnBnClickedButton1()
{
// TODO: 在此添加控件通知处理程序代码
CDatabase database;
CString sSql;
CString sItem1, sItem2, sItem3, sItem4, sItem5;
CString sDriver;
CString sDsn;
CString sFile = "ReadExcel.xls"; // the file name. Could also be somethin 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 开始器件, 结尾器件,开始器件名称,结尾器件名称,检测标志 "
"FROM [Sheet1$] ";
// 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("开始器件",sItem1);
recset.GetFieldValue("结尾器件",sItem2);
recset.GetFieldValue("开始器件名称",sItem3);
recset.GetFieldValue("结尾器件名称",sItem4);
recset.GetFieldValue("检测标志",sItem5);
// Insert result into the list
if(sItem5==_T("no"))
{ POINT point,nextitem;
point.x=0;
point.y=0;
nextitem=ChooseDraw(point,sItem3,sItem1);//****************
ChooseDraw( nextitem,sItem4,sItem2);
}
recset.MoveNext();
database.Close();
}
CATCH(CDBException, e)
{
// A database exception occured. Pop out the details...
AfxMessageBox("Database error: "+e->m_strError);
}
END_CATCH;
}
POINT CDialog1::ChooseDraw(POINT posation, CString name,CString Item)
{ POINT newposation;
if(Item==_T("空开断路器"))
{ newposation=Draw1(posation,name);
return newposation;} if(Item==_T("端子排"))
{ newposation=Draw2(posation,name);
return newposation;}
if(Item==_T("接口连接器"))
{ newposation=Draw3(posation,name);
return newposation;
}
if(Item==_T("设备接线器"))
{ newposation=Draw4(posation,name);
return newposation;
}
if(Item==_T("继电器"))
{newposation=Draw5(posation,name);
return newposation;
}
if(Item==_T("指示灯"))
{ newposation=Draw6(posation,name);
return newposation;
}
if(Item==_T("变压器"))
{ newposation=Draw7(posation,name);
return newposation;
}
if(Item==_T("转换开关"))
{ newposation==Draw8(posation,name);
return newposation;
}
if(Item==_T("接触器"))
{newposation=Draw9(posation,name);
return newposation;
}
if(Item==_T("方便插座"))
{newposation=Draw10(posation,name);
return newposation;
}
if(Item==_T("主机机体"))
{ newposation=Draw11(posation,name);
return newposation;
}
}
我把程序改了如上,运行时总是到带*号的那句出问题,意思说nextitem没定义却在接下来一句调用了,如果sItem在Excel第一行中为NO就会出现上述错误,在其他行则不会出错