FileUpload控件实例应用 Excel数据导入数据库
HTML:
<html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <asp:Panel ID="TitlePanel" runat="server" CssClass="titlePadding"> <asp:Label ID="labTitle" runat="server"></asp:Label> </asp:Panel> <div class="divDetail" style="margin: 10px;"> <table> <tr> <td> 选择导入文件 </td> <td> <asp:FileUpload ID="FileUpload1" runat="server" /> </td> </tr> <tr> <td align="center" colspan="2"> <asp:Button ID="btn_import" runat="server" Text="导入" OnClick="btn_import_Click" /> </td> </tr> </table> </div> <div id="strinfo" runat="server" class="mbox pbox"> </div> </form> </body> </html>
Using
using System.IO; using System.Data.OleDb; using System.Collections.Generic;
后台代码：导入按钮事件：
protected void btn_import_Click(object sender, EventArgs e) { string ModuleTag = "cars_hr_recruit_pool_import"; DataTable dt = xsldata(FileUpload1, "人才档案模板", ModuleTag, "Yes"); SR.Model.hr_recruit_pool model = new SR.Model.hr_recruit_pool(); if (dt != null) { for (int i = 0; i < dt.Rows.Count; i++) { model.Plan_no = dt.Rows[i][0].ToString(); model.Plan_name = dt.Rows[i][1].ToString(); model.Position = dt.Rows[i][2].ToString(); //.... //新增数据记录函数 bll.AddItem(model); } strinfo.InnerHtml = "导入数据库成功"; } }
读取Excel文件数据函数：xlsdata
/// <summary> /// Excel数据转为datatable /// </summary> /// <param name="fileupload">上传控件名</param> /// <param name="sheet">Excel中sheet名</param> /// <param name="ModuleTag">模块名</param> /// <param name="HDR">Yes，这代表第一行是标题，不做为数据使用；NO，则表示第一行不是标题，做为数据来使用</param> protected DataTable xlsdata(FileUpload fileupload, string sheet, string ModuleTag,string HDR) { if (fileupload.FileName == "") { JScript.Alert("请选择文件"); return null; } string fileExtenSion; fileExtenSion = Path.GetExtension(fileupload.FileName); if (fileExtenSion.ToLower() != ".xls" && fileExtenSion.ToLower() != ".xlsx") { JScript.Alert("上传的文件格式不正确"); return null; } try { string url = AttachFilePath + ModuleTag + "\\"; string FileName = url + Path.GetFileName(fileupload.FileName); DeleteFile(FileName); if (!Directory.Exists(url)) Directory.CreateDirectory(url); fileupload.SaveAs(FileName); string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Excel 8.0;HDR=" + HDR + ";IMEX=1;'"; string connstr2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + "\""; OleDbConnection conn; if (fileExtenSion.ToLower() == ".xls") { conn = new OleDbConnection(connstr2003); } else { conn = new OleDbConnection(connstr2007); } conn.Open(); string sql = "select * from ["+sheet+"$]"; OleDbCommand cmd = new OleDbCommand(sql, conn); DataTable dt = new DataTable(); OleDbDataReader sdr = cmd.ExecuteReader(); dt.Load(sdr); sdr.Close(); conn.Close(); //删除服务器里上传的文件 DeleteFile(FileName); //循环删除空行 removeEmpty(dt); return dt; } catch { return null; } }
循环删除空行函数
//循环去除datatable中的空行 protected int removeEmpty(DataTable dt) { if (dt == null) return 0; List<DataRow> removelist = new List<DataRow>(); for (int i = 0; i < dt.Rows.Count; i++) { bool rowdataisnull = true; for (int j = 0; j < dt.Columns.Count; j++) { if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim())) { rowdataisnull = false; } } if (rowdataisnull) { removelist.Add(dt.Rows[i]); } } for (int i = 0; i < removelist.Count; i++) { dt.Rows.Remove(removelist[i]); } return 1; }
模板贴图：
