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