Detailed explanation of C + + reading excel code

Time:2021-11-28

How C + + reads Excel? The whole process of operating excel with C + + ODBC

If you want to read and write Excel files directly through ODBC, you should first ensure that Microsoft Excel driver (*. XLS) for Excel files is installed in ODBC. Then, proceed as follows:

1. Add in StdAfx. H file:


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

2. Create Excel files directly through ODBC (tentative file name: demo. XLS)

//Create and write Excel files
void CRWExcel::WriteToExcel()
{
CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; //  Excel installation driver
CString sExcelFile = "c:\\demo.xls"; //  Excel file to create
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 (an Excel spreadsheet file)
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
//Create 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 Jingzhou ', 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);
} 
//Close database
database.Close();
}
CATCH_ALL(e)
{
Trace1 ("excel driver not installed:% s", sdriver);
}
END_CATCH_ALL;
}

3. Read Excel files directly through ODBC (tentative file name: demo. XLS)

//Read excel file
void CRWExcel::ReadFromExcel() 
{
CDatabase database;
CString sSql;
CString sItem1, sItem2;
CString sDriver;
CString sDsn;
CString sFile = "Demo.xls"; //  The name of the excel file to be read
//Retrieves whether the Microsoft Excel driver (*. XLS) is installed 
sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
//No excel drivers found
AfxMessageBox ("excel driver is not installed!");
return;
}
//Create a string for access
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s", sDriver, sFile);
TRY
{
//Open database (Excel file)
database.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 values
recset.GetFieldValue("Name ", sItem1);
recset.GetFieldValue("Age", sItem2);
//Move to next line
recset.MoveNext();
}
//Close database
database.Close();
}
CATCH(CDBException, e)
{
//Exception occurred during database operation
AfxMessageBox ("database error:" + e - > m_strerror);
}
END_CATCH;
}
//Get excel driver in ODBC
CString CRWExcel::GetExcelDriver()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver;
//Get the name of the installed driver (the number is contained in ODBC inst.h)
if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
return "";
//Retrieving installed drivers for excel
do
{
if (strstr(pszBuf, "Excel") != 0)
{
//Find out!
sDriver = CString(pszBuf);
break;
}
pszBuf = strchr(pszBuf, '
//Read excel file
void CRWExcel::ReadFromExcel() 
{
CDatabase database;
CString sSql;
CString sItem1, sItem2;
CString sDriver;
CString sDsn;
CString sFile = "Demo.xls"; //  The name of the excel file to be read
//Retrieves whether the Microsoft Excel driver (*. XLS) is installed 
sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
//No excel drivers found
AfxMessageBox ("excel driver is not installed!");
return;
}
//Create a string for access
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s", sDriver, sFile);
TRY
{
//Open database (Excel file)
database.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 values
recset.GetFieldValue("Name ", sItem1);
recset.GetFieldValue("Age", sItem2);
//Move to next line
recset.MoveNext();
}
//Close database
database.Close();
}
CATCH(CDBException, e)
{
//Exception occurred during database operation
AfxMessageBox ("database error:" + e - > m_strerror);
}
END_CATCH;
}
//Get excel driver in ODBC
CString CRWExcel::GetExcelDriver()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver;
//Get the name of the installed driver (the number is contained in ODBC inst.h)
if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
return "";
//Retrieving installed drivers for excel
do
{
if (strstr(pszBuf, "Excel") != 0)
{
//Find out!
sDriver = CString(pszBuf);
break;
}
pszBuf = strchr(pszBuf, '\0') + 1;
}
while (pszBuf[1] != '\0');
return sDriver;
}
') + 1; } while (pszBuf[1] != '
//Read excel file
void CRWExcel::ReadFromExcel() 
{
CDatabase database;
CString sSql;
CString sItem1, sItem2;
CString sDriver;
CString sDsn;
CString sFile = "Demo.xls"; //  The name of the excel file to be read
//Retrieves whether the Microsoft Excel driver (*. XLS) is installed 
sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
//No excel drivers found
AfxMessageBox ("excel driver is not installed!");
return;
}
//Create a string for access
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s", sDriver, sFile);
TRY
{
//Open database (Excel file)
database.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 values
recset.GetFieldValue("Name ", sItem1);
recset.GetFieldValue("Age", sItem2);
//Move to next line
recset.MoveNext();
}
//Close database
database.Close();
}
CATCH(CDBException, e)
{
//Exception occurred during database operation
AfxMessageBox ("database error:" + e - > m_strerror);
}
END_CATCH;
}
//Get excel driver in ODBC
CString CRWExcel::GetExcelDriver()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver;
//Get the name of the installed driver (the number is contained in ODBC inst.h)
if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
return "";
//Retrieving installed drivers for excel
do
{
if (strstr(pszBuf, "Excel") != 0)
{
//Find out!
sDriver = CString(pszBuf);
break;
}
pszBuf = strchr(pszBuf, '\0') + 1;
}
while (pszBuf[1] != '\0');
return sDriver;
}
'); return sDriver; }

The above is how C + + reads the details of Excel. I hope the contents sorted out by developeppaer can help you.