如何使用动态名称将数据从Excel工作表(多个工作表)插入数据库

本文介绍了如何使用动态名称将数据从Excel工作表(多个工作表)插入数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的代码是:-

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尝试其他方法 support.microsoft/kb/321686 [ ^ ] Try a different approach using Openrowset here support.microsoft/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.. ;)