C#实现 Excel导入Sql数据库

joozh

protected void Submit1_ServerClick(object sender, EventArgs e)
{
string strPath = this.File1.PostedFile.FileName;

if (strPath == "")
{
Response.Write("<script>alert('请先选择文件')</script>");
return;
}


//定义连接字符串
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=' + strPath +';" +
"Extended Properties=Excel 8.0;";

try
{
OleDbConnection oleDbConnection = new OleDbConnection(sConnectionString);
oleDbConnection.Open();


//获取excel表
DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

//获取sheet名，其中[0][1]...[N]: 按名称排列的表单元素
string tableName = dataTable.Rows[0][2].ToString().Trim();
tableName = "[" + tableName.Replace("'", "") + "]";
//利用SQL语句从Excel文件里获取数据


string query = "SELECT 部门,全年任务,本年累计完成,上年同期完成,[完成全年任务的%],[可比增长%] FROM " + tableName;
DataSet dataset = new DataSet();

OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query, sConnectionString);

oleAdapter.Fill(dataset, "qssrzb");
//this.DataGrid1.DataBind();




//创建OldDbDataAdapter 执行sql语句
//OleDbDataAdapter excelDA = new OleDbDataAdapter("select * from [1全市总表$]", sConnectionString);
////创建DataSet对象
//DataSet ExcelDs = new DataSet();

// //执行Sql语句,并将结果填充到DataSet中
// excelDA.Fill(ExcelDs);





//从web.config中取得数据库连接
//string //con=System.Configuration.ConfigurationManager.AppSe//ttings["ConnectionString"].ToString();

SqlConnection sqlcon = new SqlConnection("server=localhost;database=hao;User ID=sa;");
sqlcon.Open();
//fot(i=0;if<)

//从excel文件获得数据后，插入记录到SQL Server的数据表
DataTable dataTable1 = new DataTable();

SqlDataAdapter sqlDA1 = new SqlDataAdapter(@"SELECT 部门,全年任务,
本年累计完成,上年同期完成,[完成全年任务的%],[可比增长%] FROM qssrzb", sqlcon);

SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);

sqlDA1.Fill(dataTable1);
foreach (DataRow dataRow in dataset.Tables["qssrzb"].Rows)
{
//sql里数据dataRow1
DataRow dataRow1 = dataTable1.NewRow();

dataRow1["部门"] = dataRow["部门"];
dataRow1["全年任务"] = dataRow["全年任务"];
dataRow1["本年累计完成"] = dataRow["本年累计完成"];
dataRow1["上年同期完成"] = dataRow["上年同期完成"];
dataRow1["完成全年任务的%"] = dataRow["完成全年任务的%"];
dataRow1["可比增长%"] = dataRow["可比增长%"];
dataTable1.Rows.Add(dataRow1);
}
Console.WriteLine("新插入 " + dataTable1.Rows.Count.ToString() + " 条记录");
sqlDA1.Update(dataTable1);

oleDbConnection.Close();

}

catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}

}
}


点击 保存到数据库 按钮 后 没变化 数据库里没纪录 也不提示 出错误 这是怎么回事 谢谢 谢谢大家 帮忙了