asp.net實現excel導入到sql

首先這個文件必須上傳到服務器，然後讀取裏面的內容放在ds裏面，然後在導入sql

具體做法：

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Data.OleDb;

using System.Configuration;

namespace ETable

{

/// <summary>

/// ETable 的摘要說明。

/// </summary>

public class ETable : System.Web.UI.Page

{

protected System.Web.UI.HtmlControls.HtmlInputFile File1;

protected System.Web.UI.WebControls.Label Label1;

protected System.Web.UI.WebControls.Button Button1;

protected System.Web.UI.WebControls.DataGrid DataGrid1;

public Random rd;

private void Page_Load(object sender, System.EventArgs e)

{

// 在此處放置用戶代碼以初始化頁面

}

#region Web 窗體設計器生成的代碼

override protected void OnInit(EventArgs e)

{

//

// CODEGEN: 該調用是 ASP.NET Web 窗體設計器所必需的。

//

InitializeComponent();

base.OnInit(e);

}

/// <summary>

/// 設計器支持所需的方法 - 不要使用代碼編輯器修改

/// 此方法的內容。

/// </summary>

private void InitializeComponent()

{

this.Button1.Click += new System.EventHandler(this.Button1_Click);

this.Load += new System.EventHandler(this.Page_Load);

}

#endregion

private void Button1_Click(object sender, System.EventArgs e)

{

if (File1.PostedFile!=null)

{ rd=new Random(1);

string filename=DateTime.Now.Date.ToString("yyyymmdd")+DateTime.Now.ToLongTimeString().Replace(":","")+rd.Next(9999).ToString()+".xls";

File1.PostedFile.SaveAs(@Server.MapPath("file/")+filename);

//Response.Write(File1.PostedFile.FileName.ToString());

//Response.Write("上傳成功");

Label1.Text="文件名為"+filename;

string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+Server.MapPath("file")+"/"+filename+";Extended Properties=Excel 8.0" ;

OleDbConnection thisconnection=new OleDbConnection(conn);

thisconnection.Open();

string Sql="select * from [Sheet1$]";

OleDbDataAdapter mycommand=new OleDbDataAdapter(Sql,thisconnection);

DataSet ds=new DataSet();

mycommand.Fill(ds,"[Sheet1$]");

thisconnection.Close();

DataGrid1.DataSource=ds;

DataGrid1.DataBind();

string conn1="User ID=sa;Data Source=127.0.0.1;Password=sa;Initial Catalog=index;Provider=SQLOLEDB.1;";

OleDbConnection thisconnection1=new OleDbConnection(conn1);

thisconnection1.Open();

int count=ds.Tables["[Sheet1$]"].Rows.Count;

for (int i=0;i<count;i++)

{

string id,id_1,id_2,id_3;

id=ds.Tables["[Sheet1$]"].Rows[i]["id"].ToString();

id_1=ds.Tables["[Sheet1$]"].Rows[i]["id_1"].ToString();

id_2=ds.Tables["[Sheet1$]"].Rows[i]["id_2"].ToString();

id_3=ds.Tables["[Sheet1$]"].Rows[i]["id_3"].ToString();

string excelsql="insert into excel(id,id_1,id_2,id_3) values ('"+id+"','"+id_1+"','"+id_2+"','"+id_3+"') ";

OleDbCommand mycommand1=new OleDbCommand(excelsql,thisconnection1);

mycommand1.ExecuteNonQuery();

}

Response.Write("更新成功");

thisconnection1.Close();

//FileStream fileStream = new FileStream(@Server.MapPath("file/")+filename);

System.IO.File.Delete(@Server.MapPath("file/")+filename);

}

}

}

}

C#

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Data.OleDb;

using System.Configuration;

namespace ETable

{

/// <summary>

/// ETable 的摘要說明。

/// </summary>

public class ETable : System.Web.UI.Page

{

protected System.Web.UI.HtmlControls.HtmlInputFile File1;

protected System.Web.UI.WebControls.Label Label1;

protected System.Web.UI.WebControls.Button Button1;

protected System.Web.UI.WebControls.DataGrid DataGrid1;

public Random rd;

private void Page_Load(object sender, System.EventArgs e)

{

// 在此處放置用戶代碼以初始化頁面

}

#region Web 窗體設計器生成的代碼

override protected void OnInit(EventArgs e)

{

//

// CODEGEN: 該調用是 ASP.NET Web 窗體設計器所必需的。

//

InitializeComponent();

base.OnInit(e);

}

/// <summary>

/// 設計器支持所需的方法 - 不要使用代碼編輯器修改

/// 此方法的內容。

/// </summary>

private void InitializeComponent()

{

this.Button1.Click += new System.EventHandler(this.Button1_Click);

this.Load += new System.EventHandler(this.Page_Load);

}

#endregion

private void Button1_Click(object sender, System.EventArgs e)

{

if (File1.PostedFile!=null)

{ rd=new Random(1);

string filename=DateTime.Now.Date.ToString("yyyymmdd")+DateTime.Now.ToLongTimeString().Replace(":","")+rd.Next(9999).ToString()+".xls";

File1.PostedFile.SaveAs(@Server.MapPath("file/")+filename);

//Response.Write(File1.PostedFile.FileName.ToString());

//Response.Write("上傳成功");

Label1.Text="文件名為"+filename;

string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+Server.MapPath("file")+"/"+filename+";Extended Properties=Excel 8.0" ;

OleDbConnection thisconnection=new OleDbConnection(conn);

thisconnection.Open();

string Sql="select * from [Sheet1$]";

OleDbDataAdapter mycommand=new OleDbDataAdapter(Sql,thisconnection);

DataSet ds=new DataSet();

mycommand.Fill(ds,"[Sheet1$]");

thisconnection.Close();

DataGrid1.DataSource=ds;

DataGrid1.DataBind();

string conn1="User ID=sa;Data Source=127.0.0.1;Password=sa;Initial Catalog=index;Provider=SQLOLEDB.1;";

OleDbConnection thisconnection1=new OleDbConnection(conn1);

thisconnection1.Open();

int count=ds.Tables["[Sheet1$]"].Rows.Count;

for (int i=0;i<count;i++)

{

string id,id_1,id_2,id_3;

id=ds.Tables["[Sheet1$]"].Rows[i]["id"].ToString();

id_1=ds.Tables["[Sheet1$]"].Rows[i]["id_1"].ToString();

id_2=ds.Tables["[Sheet1$]"].Rows[i]["id_2"].ToString();

id_3=ds.Tables["[Sheet1$]"].Rows[i]["id_3"].ToString();

string excelsql="insert into excel(id,id_1,id_2,id_3) values ('"+id+"','"+id_1+"','"+id_2+"','"+id_3+"') ";

OleDbCommand mycommand1=new OleDbCommand(excelsql,thisconnection1);

mycommand1.ExecuteNonQuery();

}

Response.Write("更新成功");

thisconnection1.Close();

}

}

}

}

上一篇:Fota源代碼

下一篇:誰給我《龍族》所有言靈排序。（118個）