該例子實現了:將上傳的Excel文件內容在頁面上顯示出來,或者加入到數據庫.
Default.aspx
- <html xmlns="http://www.w3.org/1999/xhtml">
- <head id="Head1" runat="server">
- <title>Untitled Page</title>
- </head>
- <body>
- <form id="form1" runat="server">
- <div>
- <asp:GridView ID="GridView1" runat="server" Height="177px" Width="911px">
- </asp:GridView>
- <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="读数据" />
- <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="导入数据库" />
- <asp:FileUpload ID="FileUpload1" runat="server" /></div>
- </form>
- </body>
- </html>
Default.aspx.cs
- using System;
- using System.Data;
- using System.Configuration;
- using System.Web;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Web.UI.HtmlControls;
- using System.Text;
- using System.IO;
- using System.Data.OleDb;
- public partial class _Default : System.Web.UI.Page
- {
- protected void Page_Load(object sender, EventArgs e)
- {
- }
- protected void Button1_Click(object sender, EventArgs e)
- {
- GridView1.DataSource = CreateDataSource();
- GridView1.DataBind();
- }
- protected void Button2_Click(object sender, EventArgs e)
- {
- DataSet ds = CreateDataSource();
- int intCount = ds.Tables[0].Rows.Count;
- string[] ArrayList = new string[intCount];
- // string intcountcel=ds.Tables[0].Rows[0].ToString();
- // Response.Write(intcountcel);
- string strVal = "insert into a(aa,bb,cc,dd,ee,ff) values (";
- for (int i = 0; i < intCount; i++)
- {
- string str_a = ds.Tables[0].Rows[i][0].ToString();
- string str_b = ds.Tables[0].Rows[i][1].ToString();
- string str_c = ds.Tables[0].Rows[i][2].ToString();
- string str_d = ds.Tables[0].Rows[i][3].ToString();
- string str_e = ds.Tables[0].Rows[i][4].ToString();
- ArrayList[i] = strVal + str_a + "," + str_b + "," + str_c + "," + str_d + "," + str_e + ")";
- }
- for (int i = 0; i < ArrayList.Length; i++)
- {
- Response.Write(ArrayList[i].ToString());
- Response.Write("<br>");
- }
- }
- private DataSet CreateDataSource()
- {
- string strCon;
- string excel = string.Empty;
- string Adress = FileUpload1.PostedFile.FileName.ToString();
- int intPos = Adress.LastIndexOf("//");
- string strFileName = Adress.Substring(intPos + 1);
- string strSavePath = HttpContext.Current.Server.MapPath("~/Sounce") + "/" + strFileName;
- if (!File.Exists(strSavePath))
- {
- FileUpload1.PostedFile.SaveAs(strSavePath);
- }
- strCon = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties=Excel 8.0;", strSavePath);
- OleDbConnection olecon = new OleDbConnection(strCon);
- OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", olecon);
- DataSet myds = new DataSet();
- myda.Fill(myds);
- return myds;
- }
- }