1. Create a VS Project, select Windows Console application, project name input Cpprwexcel
2. Add the following code at the bottom of the StdAfx.h file:
#include <iostream> #include <afxdb.h> #include <odbcinst.h>using namespace std;
3. Write the following code in CppRWexcel.cpp:
CppRWexcel.cpp: Defines the entry point of the console application. #include "stdafx.h"//Create and write to Excel file void Writetoexcel () {CDatabase database; CString sdriver = "Microsoft Excel Driver (*.xls)"; Excel installation driver CString sexcelfile = "D:\\demo.xls"; To create an Excel file CString sSQL; try{//Create the Access string Ssql.format ("driver={%s};D sn="; Firstrowhasnames=1; Readonly=false; Create_db=\ "%s\";D bq=%s ", Sdriver, Sexcelfile, sexcelfile);//CREATE DATABASE (Excel table file) if (db. OpenEx (Ssql,cdatabase::noodbcdialog)) {//CREATE table structure (name, age) sSQL = "CREATE TABLE demo (name text,age number)";d atabase. ExecuteSQL (sSQL);//Insert value sSQL = "INSERT into demo (name,age) VALUES (' Qinshujin ', +)";d atabase. ExecuteSQL (sSQL); sSQL = "INSERT into demo (name,age) VALUES (' Jiyingjun ', ')";d atabase. ExecuteSQL (sSQL); sSQL = "INSERT into demo (name,age) VALUES (' Zhangqi ', ')";d atabase. ExecuteSQL (sSQL);} Close the database. Close ();} Catch_all (e) {TRACE1 ("Excel driver not installed:%s", sdriver);} End_catch_all;} Get ODBC in Excel driver CString getexceldriver () {char szbuf[2001]; WORD Cbbufmax = 2000; WORD Cbbufout;char *PSZBUF = szbuf; CString sdriver;//Gets the name of the installed driver (culvert number in odbcinst.h) if (! Sqlgetinstalleddrivers (Szbuf, Cbbufmax, &cbbufout)) return "";//retrieves whether the installed driver has excel...do{if (STRSTR (Pszbuf, "Excel" ) = 0) {//Discover!sdriver = CString (PSZBUF); Pszbuf = STRCHR (pszbuf, ' + ') + 1;} while (pszbuf[1]! = ' + '); return sdriver;} Read Excel file void Readfromexcel () {CDatabase database; CString sSQL; CString sItem1, sItem2; CString Sdriver; CString SDSN; CString sFile = "D:\\demo.xls"; The Excel file name that will be read//retrieved whether the Excel driver is installed "Microsoft Excel Driver (*.xls)" sdriver = Getexceldriver (); if (Sdriver.isempty ()) {//No have found Excel driver cout << "no Excel driver installed!" << Endl;return;} Create a string to access Sdsn.format ("odbc;driver={%s};D sn= ';D bq=%s", Sdriver, SFile); try{//Open the database (both Excel files). Open (NULL, False, False, SDSN); CRecordset Recset (&database);//sets the query statement to read. sSQL = "SELECT name, Age" "from Demo" "ORDER by Name";//Execute Query statement recset. Open (CRecordset::forwardOnly, sSQL, crecordset::readonly);//Get query results while (!recset. IsEOF ()) {//Read Excel internal numeric recset.geTfieldvalue ("Name", sItem1); Recset. GetFieldValue ("Age", sItem2), cout << sItem1 << "\ t" << sItem2 << endl;//move to the next line of Recset. MoveNext ();} Close the database. Close ();} CATCH (CDBException, E) {//database operation when an exception is generated ... AfxMessageBox ("Database error:" + e->m_strerror);} End_catch;} int main () {writetoexcel (); Readfromexcel (); return 0;}
4. Changes to the project properties (as appropriate, which error to change which)
Menu bar Item->cpprwexcel Properties, configuration Properties, general--Project default value, using MFC in a shared DLL,->mfc.
Menu bar Items--Configuration properties, general--project defaults, character set--use multibyte character sets.
(vs2017 may need to be modified) add "legacy_stdio_definitions.lib" without double quotation marks to the additional dependencies, input----->cpprwexcel properties, configuration Properties, and so on, menu bar items.
(vs2010 may need to be modified) the menu bar item->cpprwexcel Properties------list tools, and so on, input and output, embedded list, No.
5. Compile run, you can see the D packing directory created in the Excel file, read and write the above code, are SQL statements.
VS Create a console program to read and write Excel files using C + + (ODBC mode)