asp.net 导入excel显示进度
这几天在做个导入excel的上传页面，由于数据量太大，要显示个进度条，本人不懂jquery，所以百度完再经过调整之后完成了，如果告诉别人只是为了显示个进度条而弄个多线程，还要根据session的机制模拟一个具有session功能的泛型集合，还要为了防止用户多次上传海量数据而限制只能在上一次完成数据导入的情况下进行下一次导入，我猜我会让别人鄙视吧，因为刚开始我用静态变量存储结果和进度，后台就是为了防止多用户使用有冲突，但是多线程里不能用session和cookie，于是用上了HiddenField和模拟session的泛型集合，因为还需要测试，所以共享出来让大家提些意见。
效果：
前台
前台
1 <asp:ScriptManager ID="ScriptManager1" runat="server"> 2 </asp:ScriptManager> 3 <asp:HiddenField ID="hfGuid" runat="server" /> 4 <div id="mainFeild"> 5 <div id="inputFeild"> 6 请上传要导入数据的excel: <asp:FileUpload ID="fldUpload" runat="server" /> 7 <br /> 8 请输入要上传内容的表名: <asp:TextBox ID="txtSheetName" runat="server" Text="Sheet1"></asp:TextBox> 9 <br /> 10 <p> 11 <asp:Button ID="btnUpload" runat="server" Text="导入" OnClick="btnUpload_Click" OnClientClick="return confirm('请先确认表名是否正确!')" 12 CssClass="btn" /> 13 <input id="btnBack" type="button" value="返回" onclick="javascript:window.location.href='BeginFee.aspx'" 14 class="btn" /></p> 15 <hr /> 16 <asp:UpdatePanel ID="_staic_Div" runat="server"> 17 <ContentTemplate> 18 <asp:Timer ID="Timer1" runat="server" Interval="1000" OnTick="Timer1_Tick" Enabled="false"> 19 </asp:Timer> 20 <asp:Label ID="Label1" runat="server" Text=""></asp:Label> 21 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" PageSize="10" OnPageIndexChanging="GridView1_PageIndexChanging" 22 CellPadding="4" EnableModelValidation="True" ForeColor="#333333" GridLines="None" 23 Height="207px" Width="690px" Caption="以下数据有空格或有重复，请查看并更正！"> 24 <AlternatingRowStyle BackColor="White" /> 25 <EditRowStyle BackColor="#2461BF" /> 26 <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> 27 <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> 28 <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> 29 <RowStyle BackColor="#EFF3FB" /> 30 <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> 31 </asp:GridView> 32 </ContentTemplate> 33 </asp:UpdatePanel> 34 </div>
后台：
后台
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7 using System.Collections; 8 using System.IO; 9 using System.Data; 10 using System.Data.OleDb; 11 using System.Data.SqlClient; 12 using System.Threading; 13 using System.Text; 14 15 public partial class shouFeiXiTong_DataImport : System.Web.UI.Page 16 { 17 18 private void Page_Load(object sender, System.EventArgs e) 19 { 20 if (!IsPostBack) 21 { 22 Timer1.Enabled = false; 23 string SessionId = Guid.NewGuid().ToString(); 24 hfGuid.Value = SessionId; 25 } 26 } 27 28 29 #region 导入Excel 30 //导入excel 31 protected void btnUpload_Click(object sender, EventArgs e) 32 { 33 34 try 35 { 36 lock (this) 37 { 38 39 if (fldUpload.HasFile) 40 { 41 42 43 44 45 string sessionId = hfGuid.Value; 46 IDictionary<string, object> session = sessionManage.GetSession(sessionId); 47 48 if (session.ContainsKey("_state")) 49 { 50 if (Single.Parse(session["_state"].ToString()) != 0f || Single.Parse(session["_state"].ToString()) != 1f) 51 { 52 Page.ClientScript.RegisterStartupScript(GetType(), "js", "<script>alert('必须等上一步导入数据完成后才能进行下一步操作！')</script>"); 53 return; 54 } 55 } 56 else 57 { 58 session["_state"] = 0f; 59 DataTable dt = new DataTable(); 60 session["dt"] = dt; 61 Timer1.Enabled = true; 62 63 String fileExtension = System.IO.Path.GetExtension(fldUpload.FileName).ToLower(); 64 if (fileExtension.Equals(".xls")) 65 { 66 string filename = Path.Combine(Server.MapPath("~/Excel/"), DateTime.Now.ToString("yyyyMMddhhmm") + fileExtension); 67 fldUpload.PostedFile.SaveAs(filename); 68 Thread th = new Thread(() => 69 { 70 DataTable dts = InputExcel(filename, txtSheetName.Text.Trim()); 71 session["dt"] = ExcelToSql(dts, "db_StudentsFee.dbo.p_StuFeeInsert", session); 72 if (File.Exists(filename)) 73 File.Delete(filename); 74 } 75 ); 76 th.IsBackground = true; 77 th.Start(); 78 } 79 else 80 { 81 Label1.Text = "上传文件格式错误"; 82 return; 83 } 84 } 85 } 86 } 87 } 88 catch (Exception ex) 89 { 90 91 ClientScript.RegisterStartupScript(this.GetType(), "js", "<script>alert('" + ex.ToString() + "')</script>"); 92 return; 93 } 94 95 } 96 #endregion 97 98 #region 导入excel方法（读取Excel、导入excel数据） 99 /// <summary> 100 /// 导入excel 101 /// </summary> 102 /// <param name="filePatth">excel的路径</param> 103 /// <param name="sheetName">excel里的表名</param> 104 /// <returns>返回一个datatable</returns> 105 private DataTable InputExcel(string filePath, string sheetName) 106 { 107 108 109 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据，并且将数字和字符混合的单元格视为文本进行导入 110 using (OleDbConnection con = new OleDbConnection(strConn)) 111 { 112 113 con.Open(); 114 //DataTable dtE = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); 115 string sql = "select top 65534 * from [" + sheetName + "$]"; 116 DataSet ds = new DataSet(); 117 OleDbDataAdapter ap = new OleDbDataAdapter(sql, con); 118 ap.Fill(ds, "tb"); 119 con.Close(); 120 return ds.Tables[0]; 121 122 } 123 } 124 125 126 127 /// <summary> 128 /// 把datatable的数据插入数据库 129 /// </summary> 130 /// <param name="Exceldt"></param> 131 /// <param name="produce"></param> 132 private DataTable ExcelToSql(DataTable Exceldt, string produce, IDictionary<string, object> session) 133 { 134 #region 去掉空行 135 List<DataRow> removelist = new List<DataRow>(); 136 for (int i = 5; i < Exceldt.Rows.Count; i++) 137 { 138 bool rowdataisnull = true; 139 for (int j = 0; j < Exceldt.Columns.Count; j++) 140 { 141 142 if (Exceldt.Rows[i][j].ToString().Trim() != "") 143 { 144 145 rowdataisnull = false; 146 } 147 } 148 if (rowdataisnull) 149 { 150 removelist.Add(Exceldt.Rows[i]); 151 } 152 } 153 for (int i = 0; i < removelist.Count; i++) 154 { 155 Exceldt.Rows.Remove(removelist[i]); 156 } 157 #endregion 158 bool fileOk = true;//检测文件是否数据是否非空,是表示数据没有空，否表示数据有空格 159 Sqlhelper shp = new Sqlhelper(); 160 DataTable dt = new DataTable(); 161 dt.Columns.Add("行号", typeof(int)); 162 dt.Columns.Add("姓名", typeof(string)); 163 dt.Columns.Add("收费号", typeof(string)); 164 dt.Columns.Add("学费", typeof(decimal)); 165 dt.Columns.Add("住宿费", typeof(decimal)); 166 dt.Columns.Add("学年度", typeof(string)); 167 dt.Columns.Add("错误原因", typeof(string)); 168 int sucess = 0; 169 for (int i = 5; i < Exceldt.Rows.Count - 1; i++)//哪一行 170 { 171 for (int j = 0; j < Exceldt.Columns.Count; j++) 172 { 173 //以上循环检测没空格就执行上传,有空格的话就跳出当前循环，执行下一行的检测 174 if (Exceldt.Rows[i][j].ToString().Trim().Equals(string.Empty)) 175 { 176 dt.Rows.Add(i + 1, Exceldt.Rows[i][0], Exceldt.Rows[i][1], Exceldt.Rows[i][2], Exceldt.Rows[i][3], Exceldt.Rows[i][4], "该行数据有空格"); 177 fileOk = false; 178 continue; 179 } 180 181 } 182 if (fileOk) 183 { 184 SqlParameter[] Param = 185 { 186 new SqlParameter("@beStudentName",Exceldt.Rows[i][0].ToString()),//姓名 187 new SqlParameter("@bebankNum",Exceldt.Rows[i][1].ToString()),//卡号 188 new SqlParameter("@beTuition",Convert.ToDecimal( Exceldt.Rows[i][2].ToString())),//学费 189 new SqlParameter("@beAccomodation",Convert.ToDecimal(Exceldt.Rows[i][3].ToString()) ),//住宿费 190 new SqlParameter("@beFYear",Exceldt.Rows[i][4].ToString()) 191 };//执行存储过程 192 string sql = "select COUNT(*) from db_StudentsFee.dbo.tb_BeginFee where Be_StudentName=@beStudentName and Be_BankCardNum=@bebankNum and Be_Tuition=@beTuition and Be_Accomodation=@beAccomodation"; 193 int count = shp.ExecuteScalar(sql, Param); 194 if (count == 0) 195 { 196 shp.ExexutePro(produce, Param); 197 } 198 else 199 { 200 201 dt.Rows.Add(i + 1, Exceldt.Rows[i][0], Exceldt.Rows[i][1], Exceldt.Rows[i][2], Exceldt.Rows[i][3], Exceldt.Rows[i][4], "已存在该行数据"); 202 } 203 204 } 205 sucess++; 206 session["_state"] = (float)sucess / (float)(Exceldt.Rows.Count - 6);//百分比 207 } 208 209 return dt; 210 211 } 212 213 #endregion 214 215 216 #region 使用timer控件触发进度条 217 //触发进度条 218 protected void Timer1_Tick(object sender, EventArgs e) 219 { 220 string sessionId = hfGuid.Value; 221 IDictionary<string, object> session = sessionManage.GetSession(sessionId); 222 if (session.Count != 0) 223 { 224 Single _state = (Single)session["_state"]; 225 Session["dt"] = (DataTable)session["dt"]; 226 if (_state == 0f)// 未执行上传 227 { 228 Label1.Visible = false; 229 GridView1.DataSource = (DataTable)Session["dt"]; 230 GridView1.DataBind(); 231 } 232 else if (_state == 1f)//上传完成 233 { 234 Label1.Visible = true; ; 235 Label1.Text = " 上传完成！" + ((_state * 100)).ToString() + "%<br/>"; 236 GridView1.DataSource = (DataTable)Session["dt"]; 237 GridView1.DataBind(); 238 Timer1.Enabled = false; 239 sessionManage.Data.Remove(sessionId); 240 241 } 242 else//正在上传 243 { 244 Label1.Visible = true; 245 Label1.Text = " <img src=\"../images/clocks.gif\" style=\"height: 19px; margin-top: 18px\" />正在上传,已执行：" + ((_state * 100)).ToString() + "%<br/>"; 246 247 } 248 } 249 } 250 251 252 protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) 253 { 254 255 IDictionary<string, object> session = sessionManage.GetSession(hfGuid.Value); 256 DataTable dt = (DataTable)Session["dt"]; 257 GridView1.PageIndex = e.NewPageIndex; 258 GridView1.DataSource = dt; 259 GridView1.DataBind(); 260 } 261 #endregion 262 }
sessionManage类：
sessionManage
1 using System; 2 using System.Collections.Generic; 3 using System.Web; 4 5 /// <summary> 6 ///Session模拟器 7 /// </summary> 8 public class sessionManage 9 { 10 public sessionManage() 11 { 12 13 } 14 private static IDictionary<string, IDictionary<string, object>> data = new Dictionary<string, IDictionary<string,object>>(); 15 public static IDictionary<string, object>GetSession(string sessionId) 16 { 17 if (data.ContainsKey(sessionId)) 18 { 19 return data[sessionId]; 20 } 21 else 22 { 23 IDictionary<string,object> session=new Dictionary<string,object>(); 24 data[sessionId]=session; 25 return session; 26 27 } 28 } 29 30 public static IDictionary<string, IDictionary<string, object>> Data 31 { 32 get{return data;} 33 set {data=value;} 34 } 35 36 }
