web form excel导入

前台

代码

<div class="left"> <ul> <li> <asp:FileUpload ID="fuload" runat="server" /> </li> <asp:Label ID="lbmsg" runat="server"></asp:Label> <li> <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="导入信息" /> </li> <li> </li> </ul> </div> </div> </form>

后台代码

代码 using System; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.IO; using System.Text; using System.Web; using System.Web.UI;

namespace gene { public partial class drexcel : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) { } private DataTable xsldata() { if (fuload.FileName == "") { lbmsg.Text = "请选择文件"; return null; } string fileExtenSion; fileExtenSion = Path.GetExtension(fuload.FileName); if (fileExtenSion.ToLower() != ".xls" && fileExtenSion.ToLower() != ".xlsx") { lbmsg.Text = "上传的文件格式不正确"; return null; } try { string FileName = "App_Data/" + Path.GetFileName(fuload.FileName); if (File.Exists(Server.MapPath(FileName))) { File.Delete(Server.MapPath(FileName)); } fuload.SaveAs(Server.MapPath(FileName)); //HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; string connstr2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties=\"Excel 12.0;HDR=YES\""; OleDbConnection conn; if (fileExtenSion.ToLower() == ".xls") { conn = new OleDbConnection(connstr2003); } else { conn = new OleDbConnection(connstr2007); } conn.Open(); string sql = "select * from [Sheet1$]"; OleDbCommand cmd = new OleDbCommand(sql, conn); DataTable dt = new DataTable(); OleDbDataReader sdr = cmd.ExecuteReader(); dt.Load(sdr); sdr.Close(); conn.Close(); //删除服务器里上传的文件 if (File.Exists(Server.MapPath(FileName))) { File.Delete(Server.MapPath(FileName)); } return dt; } catch (Exception e) { return null; } } protected void Button1_Click(object sender, EventArgs e) { try { DataTable dt = xsldata(); int insertcount = 0;//记录插入成功条数 for (int i = 0; i < dt.Columns.Count; i++) //列数 { string infoid = dt.Rows[0][i].ToString();//获取编号 for (int j = 1; j < 16; j++) { int baifen = Convert.ToInt32(dt.Rows[j][i].ToString());//获取每个编号下的百分比 string sql = "update chkitem set baifen=" + baifen + " where infoid='" + infoid + "' and itemid=" + j; System.Data.DataTable dw = DB.getDataTable(sql); insertcount++; } } Response.Write(insertcount + "条数据导入成功！"); //Response.Write("<script languge='javascript'>alert('信息导入成功');window.location.href='checkzi.aspx'</script>"); //Response.End(); //Response.Write("<script>alert('导入成功！')</script>"); } catch (Exception ex) { } } }

}