VC中如何用ADO访问EXCEL表格

[漂行者]

VC中如何用ADO访问EXCEL表格

[导工]

使用这个连接字符串
Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DBQ=文件名.xls;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=0;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

[cdwy411]

strConnection = _T("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;"
"Dbq=C:\\DatabasePath\\DBSpreadSheet.xls;DefaultDir=c:\\databasepath;");

连接后可以和一般的数据库一样的操作来读取，添加，修改.........!!!

[漂行者]

我已经试过了，可是还会报错的，说什么sam卡没有安装

[zhangnanonnet]

给你一个用ODBC的吧
CFileDialog filedlg(TRUE,NULL,NULL,OFN_HIDEREADONLY,
"XLS Files (*.xls)|*.xls",NULL);
CString filename;
CDatabase database;
CString sSql;
CString sItem1, sItem2;
CString sDriver;
CString sDsn;
char cDefaultfile[MAX_PATH], num[100], name[100], msg[100];
CDBVariant varValue;
int iCount;

memset(msg, 0, 100);
memset(num, 0, 100);
memset(name, 0, 100);
memset(cDefaultfile, 0, MAX_PATH);

sDriver = theApp.GetExcelDriver();
if( sDriver.IsEmpty() )
{

AfxMessageBox("No Excel ODBC driver found");
return;
}

sprintf(cDefaultfile, "%s\\temple\\联系簿\\联系簿模板.xls", theApp.GetAppPath());
filedlg.m_ofn.lpstrFile = cDefaultfile;
filedlg.m_ofn.nMaxFile = MAX_PATH;
filedlg.m_ofn.lpstrDefExt = "XLS";
if (filedlg.DoModal() == IDOK)
{
// m_List.DeleteAllItems();
filename = filedlg.GetPathName();
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,filename);

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 * from [Sheet1$]";

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

// Browse the result
iCount = 0;
short nFields = recset.GetODBCFieldCount( );

while( !recset.IsEOF() )
{
// Read the result line
for( short index = 0; index < nFields; index++ )
{
// do something with varValu
switch (index)
{
case 0:
recset.GetFieldValue(index, sItem1);
sprintf(name, "%s", sItem1);
break;
case 1:
recset.GetFieldValue(index, sItem2);
sprintf(num, "%s", sItem2.Left(sItem2.GetLength()-2));
break;
default:
break;
}
}
if (strlen(num)!=11)
{
MessageBox("导入的手机号位数不正确，将不允许被导入","联系簿导入");
}
else
{
InsertList(num, name, "", "");

iCount++;
}

// Skip to the next resultline
recset.MoveNext();
}

// Close the database
sprintf(msg, "导入成功,本次共计导入%d条记录", iCount);
MessageBox(msg,"批量发送");
database.Close();

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

[zlxcjy]

请问还能像访问access那样这样写sql语句吗？
sql=”select * from Sheet1“
还有我这样打开纪录怎么不对？在access中正确的
CString sql="select * from Sheet1";
m_pRecordset.CreateInstance("ADODB.Recordset");
m_pRecordset->Open(
(_bstr_t)sql,
_variant_t(pConn,true),
adOpenStatic,adLockOptimistic,
adCmdText); ///打开记录