Directly read and write Excel table files through ODBC

Directly read and write Excel table files through ODBC
Translator: Xu Jingzhou (Original: Alexander mikula)

Download the sample code in this article

To directly read and write an Excel file through ODBC, make sure that the ODBC driver "Microsoft Excel Driver (*. xls)" is installed )". Then, follow these steps:

1. Add in stdafx. h file:

#include <afxdb.h> #include <odbcinst.h>

2. Use ODBC to directly create an Excel file and insert data into the table(Tentative file name: demo.xls)

// Create and write the Excel file void crwexcel: writetoexcel () {cdatabase database; cstring sdriver = "Microsoft Excel Driver (*. XLS) "; // Excel installation driver cstring sexcelfile =" C: // demo.xls "; // the Excel file cstring ssql to be created; try {// create the string ssql for access. format ("driver = {% s}; DSN = '''; firstrowhasnames = 1; readonly = false; create_db =/" % S/"; DBQ = % s ", sdriver, sexcelfile, sexcelfile); // create a database (both an Excel table 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 Jing zhou', 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 hui' ', 27)"; database. executesql (ssql);} // closes the database. close ();} catch_all (e) {trace1 ("Excel driver not installed: % s", sdriver);} end_catch_all ;}

3. Use ODBC to directly read Excel files(Tentative file name: demo.xls)

// Read the Excel file void crwexcel: readfromexcel () {cdatabase database; cstring ssql; cstring sitem1, sitem2; cstring sdriver; cstring sdsn; cstring sfile = "demo.xls "; // read the Excel file name // search whether the Excel driver is installed "Microsoft Excel Driver (*. XLS) "sdriver = getexceldriver (); If (sdriver. isempty () {// No Excel driver afxmessagebox ("No Excel Driver Installed! "); Return;} // create the string sdsn for access. format ("ODBC; driver ={% s}; DSN = '''; DBQ = % s", sdriver, sfile ); try {// open the database (both Excel files. open (null, false, false, sdsn); crecordset recset (& database); // sets the read query statement. ssql = "Select name, age" "From Demo" "order by name"; // execute the query statement recset. open (crecordset: forwardonly, ssql, crecordset: readonly); // obtain the query result while (! Recset. iseof () {// read the recset value in the Excel file. getfieldvalue ("name", sitem1); recset. getfieldvalue ("Age", sitem2); // move to the next recset. movenext ();} // closes the database. close ();} catch (cdbexception, e) {// when an exception occurs during database operations... afxmessagebox ("Database Error:" + E-> m_strerror) ;}end_catch ;}

4. Obtain the Excel-driven functions in ODBC

Cstring crwexcel: getexceldriver () {char szbuf [2001]; Word cbbufmax = 2000; Word cbbufout; char * pszbuf = szbuf; cstring sdriver; // obtain the name of the installed driver. h) if (! Sqlgetinstalleddrivers (szbuf, cbbufmax, & cbbufout) Return "; // retrieve if the installed driver has Excel... do {If (strstr (pszbuf," Excel ")! = 0) {// found! Sdriver = cstring (pszbuf); break;} pszbuf = strchr (pszbuf, ''/0'') + 1;} while (pszbuf [1]! = ''/0''); Return sdriver ;}

Author information:
Name: Xu Jingzhou (Future studio future studio)