Excel数据格式
用户姓名1 | 用户姓名2 | 用户姓名3 | 用户姓名4 | | 222 | 222 | 222 | 222 | | 333 | 333 | 333 | 333 | | 444 | 444 | 444 | 444 | | 888 | 888 | 888 | 888 | | 前台页面 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ReadDataFromExcel.aspx.cs" Inherits="ReadDataFromExcel" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div style="margin: 20px 0px 0px 20px;"> <asp:Label ID="Label1" runat="server" Text="请选择Excel文件:"></asp:Label> <asp:FileUpload ID="FileUpload1" runat="server" /> <asp:Button ID="Button1" runat="server" Text="导入" οnclick="Button1_Click" /> </div> </form> </body> </html> 后台.cs文件 using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.OleDb; public partial class ReadDataFromExcel : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { string name = FileUpload1.FileName;//获取文件名 string name1 = FileUpload1.PostedFile.FileName; //获取完整客户端文件路径 string type = FileUpload1.PostedFile.ContentType;//上传文件类型 string size = FileUpload1.PostedFile.ContentLength.ToString();//上传文件大小 string type2 = name.Substring(name.LastIndexOf(".") + 1);//上传文件后缀名 string ipath = Server.MapPath("upload") + "\\" + name; //上传到服务器上后的路径(实际路径),"\\"必须为两个斜杠,在C#中一个斜杠表示转义符. string ipath1 = Server.MapPath("upload");//创建文件夹时用 string wpath = "upload\\" + name; //虚拟路径 if (name1 != "") { if (type2.ToUpper() == "XLS")//根据后缀名来限制上传类型 { if (!System.IO.Directory.Exists(ipath1))//判断文件夹是否已经存在 { System.IO.Directory.CreateDirectory(ipath1);//创建文件夹 } FileUpload1.SaveAs(ipath);//上传文件到ipath这个路径里 string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + AppDomain.CurrentDomain.BaseDirectory + wpath; OleDbDataAdapter dr = new OleDbDataAdapter("select * from [Sheet1$]", connStr); DataSet dt = new DataSet(); dr.Fill(dt); dr.Dispose(); for (int i = 0; i < dt.Tables[0].Rows.Count; i++) { //可以拼成Insert的sql语句。(此处实现就省略了)。 string testSql = ""; testSql = dt.Tables[0].Rows[i]["用户姓名1"].ToString() + dt.Tables[0].Rows[i]["用户姓名2"].ToString() + dt.Tables[0].Rows[i]["用户姓名3"].ToString() + dt.Tables[0].Rows[i]["用户姓名4"].ToString(); Response.Write(testSql + " | "); //拼成Sql语句后,你可以把数据写入到数据库。(此处实现就省略了) } } else { Response.Write("<script>alert('文件格式不对')</script>"); } } else { Response.Write("<script>alert('请选择文件')</script>"); } } } 上面程序也实现了把exlce写出到页面上。 |