Use ODBC to access Excel

//////////////////////////////////////// //////////////////////////////////////
// Name: onreadexcel
// Function: Read and display corresponding data from an Excel file
// Author: Xu Jingzhou (jingzhou_xu@163.net)
// Organization: Future Studio)
// Date: 2002.9.1
//////////////////////////////////////// /////////////////////////////////////
Void crwexceldlg: onreadexcel ()
{
Cdatabase database;
Cstring ssql;
Cstring sitem1, sitem2;
Cstring sdriver;
Cstring sdsn;
Cstring sfile, Spath;
// Obtain the path of the main program, which exists in Spath.
Getmodulefilename (null, Spath. getbuffersetlength (max_path + 1), max_path );
Spath. releasebuffer ();
Int NPOs;
NPOs = Spath. reversefind ('//');
Spath = Spath. left (NPOs); sfile = Spath + "// demo.xls"; // check whether the Excel driver "Microsoft Excel Driver (*. XLS )"
Sdriver = getexceldriver (); // function written by the author
If (sdriver. isempty ())
{
// No Excel driver found
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 files)
Database. Open (null, false, false, sdsn );

Crecordset recset (& database); // sets the read query statement.
Ssql = "Select name, age"
"From exceldemo ";
"Order by name ";

// Execute the query statement
Recset. Open (crecordset: forwardonly, ssql, crecordset: readonly); // obtain the query result
While (! Recset. iseof ())
{
// Read the internal value of Excel
Recset. getfieldvalue ("name", sitem1 );
Recset. getfieldvalue ("Age", sitem2); // display the recorded content
M_excellist.addstring (sitem1 + "-->" + sitem2); // move to the next row
Recset. movenext ();
} // Close the database
Database. Close ();

}
Catch (cdbexception, E)
{
// When an exception occurs during database operations...
Afxmessagebox ("Database Error:" + E-> m_strerror );
}
End_catch;
}/////////////////////////////////////// ///////////////////////////////////////
// Name: getexceldriver
// Function: Obtain the Excel driver in ODBC
// Author: Xu Jingzhou (jingzhou_xu@163.net)
// Organization: Future Studio)
// Date: 2002.9.1
//////////////////////////////////////// /////////////////////////////////////
Cstring crwexceldlg: getexceldriver ()
{
Char szbuf [2001];
Word cbbufmax = 2000;
Word cbbufout;
Char * pszbuf = szbuf;
Cstring sdriver; // get the name of the installed Driver (the meaning is in odbcinst. h)
If (! Sqlgetinstalleddrivers (szbuf, cbbufmax, & cbbufout ))
Return "";


Do
{
If (strstr (pszbuf, "Excel ")! = 0) // retrieve whether the installed driver has Excel...
{
// Found!
Sdriver = cstring (pszbuf );
Break;
}
Pszbuf = strchr (pszbuf, '/0') + 1;
}
While (pszbuf [1]! = '/0'); Return sdriver;
}/////////////////////////////////////// ///////////////////////////////////////
// Name: onwriteexcel
// Function: Create and write data to an Excel file
// Author: Xu Jingzhou (jingzhou_xu@163.net)
// Organization: Future Studio)
// Date: 2002.9.1
//////////////////////////////////////// /////////////////////////////////////
Void crwexceldlg: onwriteexcel () // This function is called through a button in the crwexceldlg dialog box class
{
Cdatabase database;
Cstring sdriver = "Microsoft Excel Driver (*. xls)"; // install the driver in Excel
Cstring sexcelfile, Spath;
Cstring ssql;

// Obtain the path of the main program, which exists in Spath.
Getmodulefilename (null, Spath. getbuffersetlength (max_path + 1), max_path );
Spath. releasebuffer ();
Int NPOs;
NPOs = Spath. reversefind ('//');
Spath = Spath. Left (NPOs); sexcelfile = Spath + "// demo.xls"; // try the Excel file to be created
{
// 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 table files)
If (database. openex (ssql, cdatabase: noodbcdialog ))
{
// Create a table structure (name, age)
Ssql = "create table exceldemo (Name text, age Number )";
Database. executesql (ssql); // insert a value
Ssql = "insert into exceldemo (name, age) values ('xu Jingzhou ', 26 )";
Database. executesql (ssql); ssql = "insert into exceldemo (name, age) values ('xu Zhihui ', 22 )";
Database. executesql (ssql); ssql = "insert into exceldemo (name, age) values ('Guo hui', 27 )";
Database. executesql (ssql );
} // Close the database
Database. Close (); afxmessagebox ("Excel file written successfully! ");
}
Catch_all (E)
{
Trace1 ("Excel driver not installed: % s", sdriver );
}
End_catch_all;
 
} // The above code is reproduced ////////////// //////////////////////////