Excel数据到SqlserverDatabase [英] Excel data to SqlserverDatabase
本文介绍了Excel数据到SqlserverDatabase的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
public void importDataFromExcel(string excelFilePath)
{
string myExcelDataQuery = "select * from [Sheet1$]";
//string myExcelDataQuery = "select Date,BankCode,Ref.Id,Name,Fees from [Sheet1$]";
try
{
//string sExcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 12.0;HDR=YES;\"";
string sExcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + excelFilePath + @";Extended Properties=""Excel 8.0;HDR=YES;""";
//string sSqlConnectionString="Data Source=.;Initial Catalog=RecCrc;User ID=sa;Password=nic123";
string Cn1 = ConfigurationManager.ConnectionStrings["ANIL"].ConnectionString;
string sSqlConnectionString = ConfigurationManager.ConnectionStrings["ANIL"].ConnectionString;
string sClearSQL = "DELETE FROM " + "ErrorBankList";
string sClearSqlDuplicate = "DELETE FROM " + "BankList";
SqlConnection SqlConn = new SqlConnection(Cn1);
SqlConn.Open();
SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);
SqlCommand SqlcmdDupl = new SqlCommand(sClearSqlDuplicate, SqlConn);
SqlCmd.ExecuteNonQuery();
SqlcmdDupl.ExecuteNonQuery();
SqlConn.Close();
OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
OleDbConn.Open();
DataSet ds1E = new DataSet();
OleDbDataAdapter ad1E = new OleDbDataAdapter(myExcelDataQuery, OleDbConn);
ad1E.Fill(ds1E);
int TotalRows = ds1E.Tables[0].Rows.Count;
if (ds1E.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds1E.Tables[0].Rows.Count; i++)
{
string Sno = ds1E.Tables[0].Rows[i][0].ToString().Replace("''", "");
string BranchCd = ds1E.Tables[0].Rows[i][1].ToString().Replace("''", "");
string BranchNm = ds1E.Tables[0].Rows[i][2].ToString().Replace("''", "");
string Name = "State Bank Of Mysore";
string NameOfBank = Name +","+ BranchNm;
if (Sno.Trim() == "" || BranchCd.Trim() == "" || BranchNm.Trim() == "")
{
}
else if (Sno.Trim() != "" || BranchCd.Trim() != "" || BranchNm.Trim() != "")
{
try{
string Query = "insert into BankList values (''" + Sno.Trim() + "'',''" + BranchCd.Trim() + "'',''" + BranchNm.Trim() + "'') ";
SqlConnection Cn = new SqlConnection(sSqlConnectionString);
Cn.Open();
SqlCommand cmdT = new SqlCommand(Query, Cn);
cmdT.ExecuteNonQuery();
Cn.Close();
}
catch(Exception ex)
{
string Query = "insert into ErrorBankList values (''" + Sno.Trim() + "'',''" + BranchCd.Trim() + "'',''" + BranchNm.Trim() + "'') ";
SqlConnection Cn = new SqlConnection(sSqlConnectionString);
Cn.Open();
SqlCommand cmdT = new SqlCommand(Query, Cn);
cmdT.ExecuteNonQuery();
Cn.Close();
}
}
}
}
LblMsg.Text = "Data saved without errors";
OleDbConn.Close();
LoadDetails_Grid();
}
catch (Exception ex)
{
string Error = ex.ToString();
//Save_Error(Error);
// Response.Write(ex.ToString());
LblMsg.Text = "Invalied Excel";
GridView1.Visible = false;
}
}
但是Iam出现异常:Microsoft Office Access数据库引擎无法打开或写入文件''.它已经由另一个用户专门打开，或者您需要权限才能查看和写入其数据.
谁能建议我.
But Iam Getting Exception:The Microsoft Office Access database engine cannot open or write to the file ''''. It is already opened exclusively by another user, or you need permission to view and write its data.
Can anyone Suggest me.
推荐答案
; //string myExcelDataQuery =从[Sheet1
"; //string myExcelDataQuery = "select Date,BankCode,Ref.Id,Name,Fees from [Sheet1
中选择日期，银行代码，Ref.Id，名称，费用"； 尝试 { //string sExcelConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + excelFilePath +;扩展属性=" +"\" Excel 12.0; HDR = YES; \"; 字符串sExcelConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0;数据源=" + excelFilePath + @;扩展属性=""Excel 8.0; HDR = YES;""; //string sSqlConnectionString ="Data Source = .; Initial Catalog = RecCrc; User ID = sa; Password = nic123"; 字符串Cn1 = ConfigurationManager.ConnectionStrings ["ANIL"].ConnectionString; 字符串sSqlConnectionString = ConfigurationManager.ConnectionStrings ["ANIL"].ConnectionString; 字符串sClearSQL ="DELETE FROM" +"ErrorBankList"; 字符串sClearSqlDuplicate =删除自" +银行清单"; SqlConnection SqlConn =新的SqlConnection(Cn1); SqlConn.Open(); SqlCommand SqlCmd =新的SqlCommand(sClearSQL，SqlConn); SqlCommand SqlcmdDupl =新的SqlCommand(sClearSqlDuplicate，SqlConn); SqlCmd.ExecuteNonQuery(); SqlcmdDupl.ExecuteNonQuery(); SqlConn.Close(); OleDbConnection OleDbConn =新的OleDbConnection(sExcelConnectionString); OleDbCommand OleDbCmd =新的OleDbCommand(myExcelDataQuery，OleDbConn); OleDbConn.Open(); DataSet ds1E = new DataSet(); OleDbDataAdapter ad1E =新的OleDbDataAdapter(myExcelDataQuery，OleDbConn); ad1E.Fill(ds1E); int TotalRows = ds1E.Tables [0] .Rows.Count; 如果(ds1E.Tables [0] .Rows.Count> 0) { for(int i = 0; i< ds1E.Tables [0] .Rows.Count; i ++) { 字符串Sno = ds1E.Tables [0] .Rows [i] [0] .ToString().Replace(''"，"); 字符串BranchCd = ds1E.Tables [0] .Rows [i] [1] .ToString().Replace(''"，"); 字符串BranchNm = ds1E.Tables [0] .Rows [i] [2] .ToString().Replace(''"，"); 字符串名称=国家银行迈索尔"; 字符串NameOfBank =名称+，" + BranchNm; 如果(Sno.Trim()==" || BranchCd.Trim()==" || BranchNm.Trim()==") { } 否则，如果(Sno.Trim()！=" || BranchCd.Trim()！=" || BranchNm.Trim()！=") { 尝试{ 字符串Query =插入BankList值(""+ Sno.Trim()+"''，''"+ BranchCd.Trim()+"''，''"+ BranchNm.Trim()+"'' ); SqlConnection Cn =新的SqlConnection(sSqlConnectionString); Cn.Open(); SqlCommand cmdT =新的SqlCommand(Query，Cn); cmdT.ExecuteNonQuery(); Cn.Close(); } 抓住(例外) { 字符串Query =插入ErrorBankList值(""+ Sno.Trim()+"''，''"+ BranchCd.Trim()+"''，''"+ BranchNm.Trim()+"'' ); SqlConnection Cn =新的SqlConnection(sSqlConnectionString); Cn.Open(); SqlCommand cmdT =新的SqlCommand(Query，Cn); cmdT.ExecuteNonQuery(); Cn.Close(); } } } } LblMsg.Text =数据保存无误"; OleDbConn.Close(); LoadDetails_Grid(); } 抓住(前例外) { 字符串错误= ex.ToString(); //Save_Error(Error); //Response.Write(ex.ToString()); LblMsg.Text ="Invalied Excel"; GridView1.Visible = false; } }
"; try { //string sExcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 12.0;HDR=YES;\""; string sExcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + excelFilePath + @";Extended Properties=""Excel 8.0;HDR=YES;"""; //string sSqlConnectionString="Data Source=.;Initial Catalog=RecCrc;User ID=sa;Password=nic123"; string Cn1 = ConfigurationManager.ConnectionStrings["ANIL"].ConnectionString; string sSqlConnectionString = ConfigurationManager.ConnectionStrings["ANIL"].ConnectionString; string sClearSQL = "DELETE FROM " + "ErrorBankList"; string sClearSqlDuplicate = "DELETE FROM " + "BankList"; SqlConnection SqlConn = new SqlConnection(Cn1); SqlConn.Open(); SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn); SqlCommand SqlcmdDupl = new SqlCommand(sClearSqlDuplicate, SqlConn); SqlCmd.ExecuteNonQuery(); SqlcmdDupl.ExecuteNonQuery(); SqlConn.Close(); OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString); OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn); OleDbConn.Open(); DataSet ds1E = new DataSet(); OleDbDataAdapter ad1E = new OleDbDataAdapter(myExcelDataQuery, OleDbConn); ad1E.Fill(ds1E); int TotalRows = ds1E.Tables[0].Rows.Count; if (ds1E.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds1E.Tables[0].Rows.Count; i++) { string Sno = ds1E.Tables[0].Rows[i][0].ToString().Replace("''", ""); string BranchCd = ds1E.Tables[0].Rows[i][1].ToString().Replace("''", ""); string BranchNm = ds1E.Tables[0].Rows[i][2].ToString().Replace("''", ""); string Name = "State Bank Of Mysore"; string NameOfBank = Name +","+ BranchNm; if (Sno.Trim() == "" || BranchCd.Trim() == "" || BranchNm.Trim() == "") { } else if (Sno.Trim() != "" || BranchCd.Trim() != "" || BranchNm.Trim() != "") { try{ string Query = "insert into BankList values (''" + Sno.Trim() + "'',''" + BranchCd.Trim() + "'',''" + BranchNm.Trim() + "'') "; SqlConnection Cn = new SqlConnection(sSqlConnectionString); Cn.Open(); SqlCommand cmdT = new SqlCommand(Query, Cn); cmdT.ExecuteNonQuery(); Cn.Close(); } catch(Exception ex) { string Query = "insert into ErrorBankList values (''" + Sno.Trim() + "'',''" + BranchCd.Trim() + "'',''" + BranchNm.Trim() + "'') "; SqlConnection Cn = new SqlConnection(sSqlConnectionString); Cn.Open(); SqlCommand cmdT = new SqlCommand(Query, Cn); cmdT.ExecuteNonQuery(); Cn.Close(); } } } } LblMsg.Text = "Data saved without errors"; OleDbConn.Close(); LoadDetails_Grid(); } catch (Exception ex) { string Error = ex.ToString(); //Save_Error(Error); // Response.Write(ex.ToString()); LblMsg.Text = "Invalied Excel"; GridView1.Visible = false; } }
但是Iam出现异常:Microsoft Office Access数据库引擎无法打开或写入文件''.它已经由另一个用户专门打开，或者您需要权限才能查看和写入其数据.
谁能建议我.
But Iam Getting Exception:The Microsoft Office Access database engine cannot open or write to the file ''''. It is already opened exclusively by another user, or you need permission to view and write its data.
Can anyone Suggest me.
确保文件尚未在另一个Excel窗口中打开.
Make sure the file is not already open in another Excel window.
这篇关于Excel数据到SqlserverDatabase的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
