之前不知设计原理，发生了大量数据（超过100w行）直接从数据库读取加载到网页中，直接导致内存溢出。
Rediculous！
所以，现在改为分页查询到页面中。
由于其有全局逻辑，故折中每次加载1w条数据进行计算，网页打开速度大大加快。
所需耗时皆来自count的统计。
下载时，可做每5w行进行一波读取与写入excel。
但需注意两点：
1.前台ajax的响应时间经测试不超过1小时。需要缩短后台执行的时间以达到及时响应的效果。前台也可稍作提示，预估响应时间超过1小时则提示用户重新选择下载时间段和数据量。
2.Excel的最大行数为Excel2007开始最大行是1048576行。注意不可超出这个范围，可在前台做相应提示用户重新选择。
具体代码：
前台js：
1 //数据透视下载 2 $("#download").on("click", function () { 3 var pivottime=<%=pivotcount%>; 4 if (pivottime>3600) 5 { 6 alert("It will take more than 1 hour,please choose the date again."); 7 } 8 else 9 { 10 if(confirm("It will take about <%=pivotcount%> seconds to download the file. Confirm to download?")) 11 { 12 $("#download_center").show(); 13 i=0; 14 doProgress(); 15 $.ajax({ 16 type: "Post", 17 url: "DataPivotList.aspx/DownloadPivot", 18 contentType: "application/json; charset=utf-8", 19 dataType: "json", 20 success: function (res) { 21 //返回的数据用data.d获取内容 22 var r = eval("(" + res.d + ")"); 23 $("#download_loading > div").css("width", "0px"); //控制#loading div宽度 24 $("#download_loading > div").html(""); //显示百分比 25 $("#download_center").hide(); 26 if (r.message) { 27 window.open('/ExportFiles/' + r.value); 28 } 29 else { alert(r.value+" Please choose the date again."); } 30 }, 31 error: function (err) { 32 alert(err); 33 } 34 }); 35 36 //禁用按钮的提交 37 return false; 38 } 39 } 40 });
下载进度条js：
1 var progress_id = "download_loading"; 2 function SetProgress(progress) { 3 if (progress) { 4 $("#" + progress_id + " > div").css("width", String(progress) + "%"); //控制#loading div宽度 5 $("#" + progress_id + " > div").html(String(progress) + "%"); //显示百分比 6 } 7 } 8 var i = 0; 9 function doProgress() { 10 var time=0; 11 time=<%=pivotcount%>*1000/100; 12 if (i > 99) { 13 //$("#download_message").html("加载完毕！").fadeIn("slow");//加载完毕提示 14 return; 15 } 16 if (i <= 99) { 17 setTimeout("doProgress()", time); 18 SetProgress(i); 19 i++; 20 } 21 }
div+css：
1 <div id="download_center" style="display: none;"> 2 <div id="download_message">It will take about <%=pivotcount%> seconds to download the file.</div> 3 <div id="download_loading"><div style="0px;"></div></div> 4 </div> 5 6 /*-----------------------------------------processing----------------------------------------------------*/ 7 #download_center{ margin:0 auto;float:left; position:absolute; top: 0%; left: 0%; 100%; height: 100%;background-color:rgba(0,0,0,0.5);z-index:9999; } 8 #download_loading{ margin:0 auto;float:left; position:absolute;top: 45%;left: 40%;397px; height:49px; /*background:url(bak.png) no-repeat;*/ } 9 #download_loading div{ 0px; height:48px; background:url(process.png) no-repeat; color:#535a73; text-align:center; font-size:18px; line-height:48px; } 10 #download_message {margin:0 auto;float:left; position:absolute;top: 40%;left: 35%; 600px;height: 35px;font-size: 18px;color:#e4ebf6;line-height: 35px;text-align: center;margin-bottom: 10px;}
后台计算所需时间：
1 pivotCount = GetTotalCount(); 2 //显示时间 3 var ipivotCount = Convert.ToInt32(pivotCount); 4 if (ipivotCount / 50000 == 0) 5 DataPivotList.pivotcount = "10"; 6 else 7 DataPivotList.pivotcount = (Math.Pow(Convert.ToDouble((ipivotCount / 50000 + 1) / 2), Convert.ToDouble(2)) * 18).ToString();
下载：
1 [WebMethod] 2 public static string DownloadPivot() 3 { 4 DataTable dt = new DataTable(); 5 DataTable dtTemp = new DataTable(); 6 int pageCount = 1; 7 int currentCount = 0; 8 string uploadPath = string.Empty; 9 string sFileName = string.Empty; 10 string sTagName = string.Empty; 11 string sReturn = string.Empty; 12 13 var mo = Convert.ToInt32(pivotCount) % 50000; 14 if (mo == 0) 15 pageCount = Convert.ToInt32(pivotCount) / 50000; 16 else 17 pageCount = Convert.ToInt32(pivotCount) / 50000 + 1; 18 19 dt.Columns.Add("url"); 20 dt.Columns.Add("标题"); 21 dt.Columns.Add("正文"); 22 dt.Columns.Add("发布时间"); 23 dt.Columns.Add("作者"); 24 25 if (Convert.ToInt32(pivotCount) > 1000000)//超出excel最大行数限制 26 { 27 sReturn = "{"message":false,"value":"Beyond the excel maximum number of rows"}"; 28 } 29 else 30 { 31 for (int i = 0; i < pageCount; i++) 32 { 33 dt.Clear(); 34 currentCount = 50000 * i; 35 36 dtTemp = GetArticleByPage(currentCount); 37 38 if (dtTemp.Rows.Count > 0) 39 foreach (DataRow dr in dtTemp.Rows) 40 { 41 DataRow drInsert = dt.NewRow(); 42 drInsert["url"] = dr["URL"].ToString(); 43 drInsert["标题"] = dr["Title"].ToString(); 44 drInsert["正文"] = dr["Content"].ToString(); 45 drInsert["发布时间"] = dr["ReleaseDate"].ToString(); 46 drInsert["作者"] = dr["Author"].ToString(); 47 。。。 48 49 dt.Rows.Add(drInsert); 50 } 51 dt.AcceptChanges(); 52 53 try 54 { 55 if (dt.Rows.Count > 0) 56 { 57 if (currentCount == 0) 58 { 59 sTagName = "DataPivot"; 60 uploadPath = HttpContext.Current.Server.MapPath(ExcelHelper.GetWebKeyValue()) + "\"; 61 sFileName = sTagName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; 62 ExcelHelper.ExportExcel(dt, uploadPath, sFileName); 63 } 64 else if (dt.Rows.Count > 0 && currentCount > 0) 65 { 66 ExcelHelper.AppendToExcel(dt, uploadPath, sFileName, currentCount); 67 } 68 69 sReturn = "{"message":true,"value":"" + sFileName + ""}"; 70 } 71 else 72 { 73 sReturn = "{"message":false,"value":"No result can be exported"}"; 74 } 75 } 76 catch (Exception ex) 77 { 78 sReturn = "{"message":false,"value":"" + ex.Message + ""}"; 79 } 80 } 81 } 82 return sReturn; 83 }