這幾天在做個導(dǎo)入excel的上傳頁面，由于數(shù)據(jù)量太大，要顯示個進(jìn)度條，本人不懂jquery，所以百度完再經(jīng)過調(diào)整之后完成了，如果告訴別人只是為了顯示個進(jìn)度條而弄個多線程，還要根據(jù)session的機(jī)制模擬一個具有session功能的泛型集合，還要為了防止用戶多次上傳海量數(shù)據(jù)而限制只能在上一次完成數(shù)據(jù)導(dǎo)入的情況下進(jìn)行下一次導(dǎo)入，我猜我會讓別人鄙視吧，因?yàn)閯傞_始我用靜態(tài)變量存儲結(jié)果和進(jìn)度，后臺就是為了防止多用戶使用有沖突，但是多線程里不能用session和cookie，于是用上了HiddenField和模擬session的泛型集合，因?yàn)檫€需要測試，所以共享出來讓大家提些意見。

?

效果：

?

前臺

?

前臺 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 請上傳要導(dǎo)入數(shù)據(jù)的excel:&nbsp;<asp:FileUpload ID="fldUpload" runat="server" /> 7 <br /> 8 請輸入要上傳內(nèi)容的表名:&nbsp;<asp:TextBox ID="txtSheetName" runat="server" Text="Sheet1"></asp:TextBox> 9 <br /> 10 <p> 11 <asp:Button ID="btnUpload" runat="server" Text="導(dǎo)入" OnClick="btnUpload_Click" OnClientClick="return confirm('請先確認(rèn)表名是否正確!')" 12 CssClass="btn" /> 13 <input id="btnBack" type="button" value="返回" οnclick="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="以下數(shù)據(jù)有空格或有重復(fù)，請查看并更正！"> 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 導(dǎo)入Excel 30 //導(dǎo)入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('必須等上一步導(dǎo)入數(shù)據(jù)完成后才能進(jìn)行下一步操作！')</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 導(dǎo)入excel方法（讀取Excel、導(dǎo)入excel數(shù)據(jù)） 99 /// <summary> 100 /// 導(dǎo)入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'";//導(dǎo)入時包含Excel中的第一行數(shù)據(jù)，并且將數(shù)字和字符混合的單元格視為文本進(jìn)行導(dǎo)入 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的數(shù)據(jù)插入數(shù)據(jù)庫 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;//檢測文件是否數(shù)據(jù)是否非空,是表示數(shù)據(jù)沒有空，否表示數(shù)據(jù)有空格 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("收費(fèi)號", typeof(string)); 164 dt.Columns.Add("學(xué)費(fèi)", typeof(decimal)); 165 dt.Columns.Add("住宿費(fèi)", typeof(decimal)); 166 dt.Columns.Add("學(xué)年度", 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 //以上循環(huán)檢測沒空格就執(zhí)行上傳,有空格的話就跳出當(dāng)前循環(huán)，執(zhí)行下一行的檢測 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], "該行數(shù)據(jù)有空格"); 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())),//學(xué)費(fèi) 189 new SqlParameter("@beAccomodation",Convert.ToDecimal(Exceldt.Rows[i][3].ToString()) ),//住宿費(fèi) 190 new SqlParameter("@beFYear",Exceldt.Rows[i][4].ToString()) 191 };//執(zhí)行存儲過程 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], "已存在該行數(shù)據(jù)"); 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控件觸發(fā)進(jìn)度條 217 //觸發(fā)進(jìn)度條 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)// 未執(zhí)行上傳 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\" />正在上傳,已執(zhí)行：" + ((_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 }

?

?

?

轉(zhuǎn)載于:https://www.cnblogs.com/Stephenchao/archive/2012/08/03/2621696.html

總結(jié)

以上是生活随笔為你收集整理的asp.net 导入excel显示进度的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。