如何直接连接EXCEL文件，取得它的所有表名，以及所有字段名？？？重谢，本人从没有提问了不结贴过。

[wkoji]

最好有代码，分不够可再加

[yljiangang]

你可以到www.vckbase.com，或者到www.codeproject.com网站，以关键字excel做检索，查找相关文章或代码。这方面的资料很多

[DalyQiao]

好像只有使用ODBC之类的东西

[wkoji]

有联结给我一个也行啊
基本上的代码我看过都只能通过ODBC操作EXCEL读取或者写入记录
但是没有关于读取表名和列名的

[wkoji]

UP

[wkoji]

UP

[cnweboy]

set cn=CreateObject("adodb.connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx.xls;Extended Properties=""Excel 8.0"";"
Set cat=CreateObject("ADOX.Catalog")
cat.ActiveConnection = cn
for each tbn in cat.Tables
if right(tbn.name,1)="$" or (left(tbn.name,1)="'" and right(tbn.name,1)="'") then
msgbox tbn.name
end if
next
set cat=nothing

[cnweboy]

字段名一时没有好的方法，只能用笨办法，根据前面的表名返回数据集（recordset）取好了！

[cnweboy]

啊，刚刚查了一下Tables的属性，好象cat.Tables.Item(x).Columns可以取字段名，但现在比较忙，你自己去试吧，应该可以的。

[myblind]

干吗在这里发vb的代码？

[smartlu]

我的这段程序没问题，简单的访问和修改，你可以参考
void doexcel()
}
_ConnectionPtr m_pConnection;
_CommandPtr m_commandptr;
_RecordsetPtr m_pRecordset;

try
{

_bstr_t conn("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\data\\data.xls;Extended Properties=""Excel 8.0;""" );
_bstr_t sqltext="SELECT * from [table] ";

m_pRecordset.CreateInstance (__uuidof(Recordset));
m_pConnection.CreateInstance (__uuidof(Connection));
m_pConnection->Open (conn,"","",-1);


m_pRecordset->Open(sqltext,conn,adOpenKeyset,adLockOptimistic,-1);

int count = m_pRecordset->GetFields()->GetCount();
cout<<count<<endl;


while(!(m_pRecordset->adoEOF))
{

_bstr_t Field_A = m_pRecordset->Fields->GetItem("test1")->Value;
printf("Field A: %s\n",(LPCSTR) Field_A);
_bstr_t Field_B = m_pRecordset->Fields->GetItem("test2")->Value;
printf("Field_B: %s\n\n",(LPCSTR) Field_B);
m_pRecordset->MoveNext();
}
m_pRecordset->MoveFirst();
char str[20] = "";
cin>>str;

m_pRecordset->Fields->GetItem("test1")->Value = _bstr_t(str);
cout<<"ok!next..."<<endl;
cin>>str;
m_pRecordset->Fields->GetItem("test2")->Value = _bstr_t(str);
m_pRecordset->Update();
cout<<"update ok!"<<endl;
}

catch(_com_error &e)
{

printf("Error:\n");
printf("Code = %08lx\n", e.Error());
printf("Message = %s\n", e.ErrorMessage());
printf("Source = %s\n", (LPCSTR) e.Source());
printf("Description = %s\n", (LPCSTR) e.Description());
}
}

[kingbird]

写入excel
CDatabase database;
CString sSql;
CString sDriver;
CString sDsn;
CString sFile = "WriteExcel.xls";

// 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 = "Microsoft Excel Driver (*.xls)";
if( sDriver.IsEmpty() )
{
// Blast! We didn磘 find that driver!
AfxMessageBox("No Excel ODBC driver found");
return;
}

// Create a pseudo DSN including the name of the Driver and the Excel file
// so we don磘 have to have an explicit DSN installed in our ODBC admin
sDsn.Format("ODBC;DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",
sDriver, sFile, sFile);

TRY
{
// Open the database using the former created pseudo DSN
database.Open(NULL, FALSE, FALSE, sDsn);
//database.OpenEx(sSql,CDatabase::noOdbcDialog);

sSql = "CREATE TABLE demo (Name TEXT,Age NUMBER)";
database.ExecuteSQL(sSql);

// Insert data
sSql = "INSERT INTO demo (Name,Age) VALUES ('Bruno Brutalinsky',45)";
database.ExecuteSQL(sSql);

sSql = "INSERT INTO demo (Name,Age) VALUES ('Fritz Pappenheimer',30)";
database.ExecuteSQL(sSql);

sSql = "INSERT INTO demo (Name,Age) VALUES ('Hella Wahnsinn',28)";
database.ExecuteSQL(sSql);

// Close the database
database.Close();

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

//read

CRecordset resBasicInfo(&xlsDB);

strSelectSql.Format("SELECT [Material Code], [English Name], [Chinese Name], [Model] \
FROM [%s$A1:IV65536]", m_strBasicInfoTable);

resBasicInfo.Open(CRecordset::forwardOnly, strSelectSql, CRecordset::readOnly);

while( !resBasicInfo.IsEOF() )
{
resBasicInfo.GetFieldValue("Material Code", strItem[0]);
resBasicInfo.GetFieldValue("English Name", strItem[1]);
resBasicInfo.GetFieldValue("Chinese Name", strItem[2]);
resBasicInfo.GetFieldValue("Model", strItem[3]);

strItem[1].Replace("'", "''");
strItem[2].Replace("'", "''");

if (!strItem[0].IsEmpty())
{
strItem[0].TrimLeft('0');
strItem[0].TrimLeft();
strItem[0].TrimRight();
strItem[3].TrimLeft();
strItem[3].TrimRight();

strInsertSql.Format("INSERT INTO [Basic Information] ([Material Code], [English Name], [Chinese Name], [Model]) \
VALUES ('%s', '%s', '%s', '%s')", strItem[0], strItem[1], strItem[2], strItem[3]);
mdbDB.ExecuteSQL(strInsertSql);
}

resBasicInfo.MoveNext();
}
}