Asp.Net异步导入Excel
故事：用户在页面上传一个excel文件，程序把excel里的内容入库。
技术方案：保存文件在服务器，jquey Ajax 异步读取文件中的记录到数据库，在页面实时刷新导入情况
页面前端
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ImportUsers.aspx.cs" Inherits="Community.WebUI.zsclient.ImportUsers" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <script type="text/javascript" src="js/jquery.min.js"></script> <script type="text/javascript"> function importUserOnce(f, r) { $.ajax({ url: "ImportUsers.aspx/ImportUserOnce", type: "POST", contentType: "application/json;charset=utf-8", data: "{ 'f': '" + f + "' ,'r':'" + r + "'}", dataType: "json", cache: false, success: function (msg) { var m = msg.d.split(','); var rowNumber = m[0]; var rowCount = m[1]; $("#<%=txtResult.ClientID%>").append(m[2]); if (rowNumber != rowCount) { $("#spanMsg").text("正在导入用户，已导入"+rowNumber+"用户，根据用户多少可能需要一段时间，请您耐心等候……"); importUserOnce(f, rowNumber); } else { $("#<%=btnSubmitExcel.ClientID%>").removeAttr("disabled"); $("#spanMsg").text("完成，总用户数"+rowCount); } }, error: function (request, err,ex) { $("#spanMsg").text(err); $("#<%=btnSubmitExcel.ClientID%>").removeAttr("disabled"); } }) return; } </script> </head> <body> <form id="form1" runat="server"> <div> <table style="margin-left: auto; margin-right: auto;"> <tr> <th>从Excel导入用户</th> </tr> <tr> <td> <asp:FileUpload ID="FileUpload1" runat="server" /><asp:Button ID="btnSubmitExcel" runat="server" Text="确定" OnClick="btnSubmitExcel_Click" /> <input runat="server" id="txtFName" type="hidden" /> <span id="spanMsg"></span> </td> </tr> </table> <div style="width: 800px; height: 600px; margin-left: auto; margin-right: auto;"> <div id="txtResult" runat="server" style="width: 800px; height: 600px; overflow: scroll; border: 1px solid #ccc;"> </div> </div> </div> </form> <script type="text/javascript"> var fvalue = ""; var int = self.setInterval("clock()", 50) function clock() { fvalue = $("#<%=txtFName.ClientID%>").val(); if (fvalue != "") { int = window.clearInterval(int); importUserOnce(fvalue, 0); $("#<%=btnSubmitExcel.ClientID%>").attr("disabled", "disabled"); $("#spanMsg").text("正在导入用户，根据用户多少可能需要一段时间，请您耐心等候……"); } } </script> </body> </html>
后台代码
[WebMethod] public static string ImportUserOnce(string f, string r) { string fileName = AppDomain.CurrentDomain.BaseDirectory + "/zsclient/" + f; if (!System.IO.File.Exists(fileName)) { return fileName + "0,0,不存在"; } int rowIndex = 0; if (!int.TryParse(r, out rowIndex) || rowIndex < 0) { return "0,0,rowIndex格式错误"; } FileStream stream = File.OpenRead(fileName); IWorkbook workbook; if (fileName.EndsWith("xls")) { workbook = new HSSFWorkbook(stream);//从流内容创建Workbook对象 } else { workbook = new XSSFWorkbook(stream);//从流内容创建Workbook对象 } ISheet sheet = workbook.GetSheetAt(0);//获取第一个工作表 int rowCount = sheet.LastRowNum; string strStatus = ""; if (rowIndex <= rowCount) { int userNum = 0; while (rowIndex < rowCount && userNum < 2000) { if (sheet.GetRow(rowIndex).GetCell(0) == null || sheet.GetRow(rowIndex).GetCell(1) == null) { rowIndex++; userNum++; continue; } UserInfo objUser = new UserInfo() { Username = sheet.GetRow(rowIndex).GetCell(0).ToString(); Password = sheet.GetRow(rowIndex).GetCell(1).ToString(), }; UserCreateStatus userCreateStatus = CreateUser(ref objUser); if (userCreateStatus != UserCreateStatus.Success) { switch (userCreateStatus) { case UserCreateStatus.InvalidPassword: strStatus += "用户" + strName + "密码不符合要求，密码长度6-20位\r\n<br/>"; break; case UserCreateStatus.UserAlreadyRegistered: strStatus += "用户名" + strName + "已存在\r\n<br/>"; break; case UserCreateStatus.UsernameAlreadyExists: strStatus += "用户名" + strName + "已存在\r\n<br/>"; break; case UserCreateStatus.InvalidUserName: strStatus += "用户名" + strName + "不符合要求，4-20位字母或数字\r\n<br/>"; break; default: strStatus += UserController.GetUserCreateStatus(userCreateStatus); break; } } else { strStatus += strName + "导入成功\r\n<br/>"; } userNum++; rowIndex++; } } return rowIndex.ToString() + "," + rowCount.ToString() + "," + strStatus; }
读取excel文件采用NPOI
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
提供一个OLEDB方法
private static DataSet GetDataFromExcel(string fileName) { string connStr; if (fileName.EndsWith(".xls")) connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; else connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; string sql_F = "Select * FROM [{0}]"; OleDbConnection conn = null; OleDbDataAdapter da = null; DataTable dtSheetName = null; DataSet ds = new DataSet(); try { // 初始化连接，并打开 conn = new OleDbConnection(connStr); conn.Open(); // 获取数据源的表定义元数据 string SheetName = ""; dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); // 初始化适配器 da = new OleDbDataAdapter(); for (int i = 0; i < dtSheetName.Rows.Count; i++) { SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"]; if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$")) { continue; } da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn); DataSet dsItem = new DataSet(); da.Fill(dsItem, SheetName); ds.Tables.Add(dsItem.Tables[0].Copy()); } } catch (Exception ex) { } finally { // 关闭连接 if (conn.State == ConnectionState.Open) { conn.Close(); da.Dispose(); conn.Dispose(); } } return ds; }
