Read directly, write Excel form files through odbc
Translator: Xu Jingzhou
Want to read the Excel table file through odbc, first, you should ensure that the drive "Microsoft Excel Driver (* .xls) has been installed in ODBC. Then, according to the following steps:
1. Add: in the stdafx.h file:
INCLUDE
Include
2. Create an Excel file directly through ODBC (tentative file name: DEMO.XLS)
// Create and write an Excel file
Void CRWEXCEL :: WriteToexcel ()
{
CDATABASE DATABASE;
CString SDRIVER = "Microsoft Excel Driver (* .xls)"; // Excel Install Driver
CString Sexcelfile = "c: //demo.xls"; // To create an Excel file
CString SSQL;
Try
{
// Create a string for access
SSQL.Format ("driver = {% s}; DSN = '; firStrowhasNames = 1; readonly = false; create_db = /"% s / "; dbq =% s", sdriver, sexcelfile, sexcelfile;
// Create a database (both EXCEL form file)
IF (Database.openex (SSQL, CDATABASE :: NoodbcDialog))
{
// Create a table structure (name, age)
SSQL = "CREATE TABLE DEMO (Name Text, Age Number);
Database.executesql (SSQL);
// Insert value
SSQL = "INSERT INTO DEMO (Name, AGE) VALUES ('Xu Jingze', 26)"
Database.executesql (SSQL);
SSQL = "INSERT INTO DEMO (Name, AGE) VALUES ('Xu Zhihui', 22)"
Database.executesql (SSQL);
SSQL = "INSERT INTO DEMO (Name, AGE) VALUES ('Guo Huo', 27)"
Database.executesql (SSQL);
}
// Close the database
Database.Close ();
}
Catch_all (e)
{
Trace1 ("Excel driver is not installed:% s", SDRIVER);
}
END_CATCH_ALL;
}
3. Read the Excel file directly (tentative file name: demo.xls) through odbc
// Read the Excel file
Void CRWEXCEL :: readFromExcel ()
{
CDATABASE DATABASE;
CString SSQL;
CString Sitem1, Sitem2;
Cstring SDRIVER;
CString SDSN;
CString sfile = "demo.xls"; // will read the Excel file name
// Retrieve whether there is an Excel driver "Microsoft Excel Driver (* .xls)"
SDriver = getExceldriver (); if (SDriver.Isempty ())
{
// No Excel driver
AfxMessageBox ("No Excel Driver is installed!");
Return;
}
// Create a string for access
SDSN.Format ("ODBC; driver = {% s}; DSN = ''; DBQ =% S", SDRIVER, SFILE);
Try
{
// Open the database (both EXCEL file)
Database.open (NULL, FALSE, FALSE, SDSN);
CRecordset Recset (& Database);
/ / Set the query statement read.
SSQL = "SELECT NAME, AGE"
"From demo"
"ORDER BY Name";
// Execute Query Strategy
Recset.open (CRecordset :: Forwardonly, SSQL, CRecordset :: Readonly);
// Get the results of the query
While (! recset.iseof ())
{
// Read the EXCEL internal value
RecSet.GetfieldValue ("Name", Sitem1);
Recset.GetfieldValue ("age", sitem2);
// Move to the next line
Recset.movenext ();
}
// Close the database
Database.Close ();
}
Catch (CDBEXCEPTION, E)
{
// Database operation When an exception is generated ...
AfxMessageBox ("Database Error:" E-> m_strerror);
}
End_catch;
}
/ / Get an Excel driver in ODBC
CString CRWEXCEL :: getExceldriver ()
{
Char szbuf [2001];
Word CBBUFMAX = 2000;
Word cbbuffout;
Char * pszbuf = szbuf;
Cstring SDRIVER;
// Get the name of the installed driver (the number of miles in ODBCINST.H)
IF (! SqlgetInstalledDrivers (SZBUF, CBBUFMAX, & CBBUFOUT)
""; "
// Retrieve if the installed driver has Excel ...
DO
{
IF (strstr (PSZBUF, "Excel")! = 0)
{
//Find !
SDRIVER = CSTRING (PSZBUF);
Break;
}
PSZBUF = StrChr (pszbuf, '/ 0') 1;
}
While (pszbuf [1]! = '/ 0');
Return SDRIVER;
}
Contact information:
Author email: jingzhou_xu@163.net
Future Studio (Future Studio)
