更新时间:2023-10-07 23:06:58
,xlconn); DataTable xldt =新的DataTable(); xlda.Fill(xldt);
", xlconn); DataTable xldt = new DataTable(); xlda.Fill(xldt);
它的工作正常,但我的问题是此代码仅适用于sheet1
我的要求是多张纸,并且名称应该是动态的.
等待答复.....
Its working fine but my problem is this code work only for sheet1
and my requirement is multiple sheet and name should be dynamic.
Awaiting for reply.....
在此处使用Openrowset
尝试其他方法
http://support.microsoft.com/kb/321686 [
Try a different approach usingOpenrowset
here
http://support.microsoft.com/kb/321686[^]
命名空间->使用System.Data.OleDb;
//用于连接
字符串sConnection ="Provider = Microsoft.Jet.OLEDB.4.0; DataSource =" +您的文件名+;扩展属性= Excel 8.0;";
OleDbConnection dbCon = new OleDbConnection(sConnection);
//连接打开
dbCon.Open();
//获取所有工作表名称
DataTable dtSheetName = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
//通过Sheetwise检索数据
数据集dsOutput = new DataSet();
for(int nCount = 0; nCount< dtsheetname.rows.count; ncount ++)>
{
字符串sSheetName = dtSheetName.Rows [nCount] ["TABLE_NAME"].ToString();
字符串sQuery ="Select *从[" + sSheetName +];
OleDbCommand dbCmd = new OleDbCommand(sQuery,dbCon);
OleDbDataAdapter dbDa =新的OleDbDataAdapter(dbCmd);
DataTable dtData = new DataTable();
dbDa.Fill(dtData);
dsOutput.Tables.Add(dtData);
}
//连接关闭
dbCon.Close();
返回dsOutput;
我认为它对您非常有用.
干杯..;)
Hi,
Namespace --> Using System.Data.OleDb;
// For Connection
string sConnection="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+Your File Name+";Extended Properties=Excel 8.0;";
OleDbConnection dbCon=new OleDbConnection(sConnection);
//Connection Open
dbCon.Open();
// Get All Sheets Name
DataTable dtSheetName=dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
// Retrive the Data by Sheetwise
Dataset dsOutput=new DataSet();
for(int nCount=0;nCount<dtsheetname.rows.count;ncount++)>
{
string sSheetName=dtSheetName.Rows[nCount]["TABLE_NAME"].ToString();
string sQuery="Select * From ["+sSheetName+"];
OleDbCommand dbCmd=new OleDbCommand(sQuery,dbCon);
OleDbDataAdapter dbDa=new OleDbDataAdapter(dbCmd);
DataTable dtData=new DataTable();
dbDa.Fill(dtData);
dsOutput.Tables.Add(dtData);
}
//Connection Close
dbCon.Close();
return dsOutput;
I think its Very Useful to You.
Cheers.. ;)