Question

怎么用sql语句读取excel文件啊

Answer 1

//for get driver...
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;
CString sDriver;

//for read the file...
CDatabase database;
CString sSql;
CString sItem1,sItem2;
CString sDsn;
CString sFile;

//-------------------在这里把sFile赋值为你要读取的xls文件的文件名.

///////////////////////////////////////
// Get the names of the installed drivers
//////////////////////////////

if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))
sDriver="";
else
{
// Search for the driver...
do
{
if( strstr( pszBuf, "Excel" ) != 0 )
{
// Found !
sDriver = CString( pszBuf );
break;
}
pszBuf = strchr( pszBuf, '\0' ) + 1;
}
while( pszBuf[1] != '\0' );
}

// Retrieve the name of the Excel driver. This is
// necessary because Microsoft tends to use language
// specific names like "Microsoft Excel Driver (*.xls)" versus
// "Microsoft Excel Treiber (*.xls)"
// sDriver = GetExcelDriver();

if( sDriver.IsEmpty() )
{
AfxMessageBox("No Excel ODBC driver found");
exit(0);
}

sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,sFile);

TRY
{
// Open the database using the former created pseudo DSN
database.Open(NULL,false,false,sDsn);

// Allocate the recordset
CRecordset recset( &database );

// Build the SQL string
// Remember to name a section of data in the Excel sheet using "Insert->Names" to be
// able to work with the data like you would with a table in a "real" database. There
// may be more than one table contained in a worksheet.

sSql = "SELECT [A],[B]" //把A,B为xls文件里面的列，如果要读取多列的话，用,隔开
"FROM [Sheet1$] " //xls文件里面的第一个表，如果xls文件里的第一个表不是Sheet1这个名字的话，最好改为Sheet1
"ORDER BY [A],[B]"; //从小到大输出，A列为一级，B列为二级

// Execute that query (implicitly by opening the recordset)
recset.Open(CRecordset::forwardOnly,sSql,CRecordset::readOnly);

// Browse the result
while( !recset.IsEOF() )//不断读取A，B列的数据
{
//Read the result line
recset.GetFieldValue("A",sItem1); //把A列的数据传给sItem1
recset.GetFieldValue("B",sItem2); //把B列的数据传给sItem2
//注意！这里的sItem1，sItem2是不断更新的

// Close the database
database.Close();
}

CATCH(CDBException, e)
{
// A database exception occured. Pop out the details...
AfxMessageBox("Database error: "+e->m_strError);
}
END_CATCH;