如何使用动态名称将数据从Excel工作表(多个工作表)插入数据库 [英] How to insert data from Excel sheet (Multiple sheet) into database with dynamic name

问题描述

我的代码是:-

My code is:-

OleDbConnection xlconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=''"+ strFileName + "'';;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
OleDbDataAdapter xlda = new OleDbDataAdapter("select * from [sheet1$]", 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.....

推荐答案

，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 using Openrowset 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.. ;)

这篇关于如何使用动态名称将数据从Excel工作表(多个工作表)插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！