後台導出大量數據的時候出現導不出數據

公共c#後台方法

 public static string AllTableToArrayString(string sql, string dataSource = "datasource")
    {
        string dt1 = DateTime.Now.ToString("HH:mm:ss");
        string jsonString = "";
        StringBuilder strBuilder = new StringBuilder();
        var connectionString = ConfigurationManager.AppSettings["datasource"];
        using (var connection = new NpgsqlConnection(connectionString))
        {
            connection.Open();
            //执行查询操作
            using (var command = new NpgsqlCommand(sql, connection))
            {
                command.CommandTimeout = 300;
                using (var reader = command.ExecuteReader())
                {
                    var dataTable = new DataTable();
                    dataTable.Load(reader);
                    //将数据转换为JSON格式
                    if (dataTable.Rows.Count > 0)
                    {
                        for (int i = 0; i < dataTable.Rows.Count; i++)
                        {
                            strBuilder.Append("[");
                            for (int j = 0; j < dataTable.Columns.Count; j++)
                            {
                                string col = dataTable.Columns[j].ColumnName;
                                if (j != dataTable.Columns.Count - 1)
                                {
                                    strBuilder.Append("'" + dataTable.Rows[i][j] + "',");
                                }
                                else
                                {
                                    strBuilder.Append("'" + dataTable.Rows[i][j] + "'],");
                                }

                            }

                        }
                        jsonString = "[" + strBuilder.ToString().Trim(',') + "]";
                    }
                    else
                    {
                        jsonString = "\"NoData\"";
                    }
                }
            }
        }
        return jsonString;
    }

公共java後台方法

與上同理使用SqlSessionFactory配置超時時間或直接在yml中配置超時時間。

公用js實現

function exportSubmit() {
    
    $.ajax({
        type: "post",
        url: "",
        data: JSON.stringify({ 'data1':data1 , 'data2': data2}),
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        timeout: 180000,//設置超時時間3分鐘
        traditional: true,
        success: function (data) {
            var tempData = eval(data.d);
            if (tempData[0].rows != 'NoData') {
                exportExcelFile(tempData[0].rows);
            } else {
                ShowMsg('無數據');
            }
        },
        error: function (t1, t2, t3) {
            var m = t1 + t2 + t3;
            if (t2 === 'timeout') {
                ShowMsg('數據量太多,请求超时！！');
            } else {
                alert(m);
            }
            LoadingTip();
        }
    });
}
var exportHead = [
{ title: "title1" },
{ title: "title2" },
];

function exportExcelFile(data) {

    var queryTyp = "文件說明";
    var curDt = new Date();
    var fileName = queryTyp + curDt.Format('yyyyMMddHHmmss');//生成文件名
    var vRow0 = [];//標題
    var vRow1 = [];//數據項名稱
    var tHead = [];

    var tempArr = [];
    var wholeTable = [];
    var sheet = {};
    vRow0 = ['文件說明'];
    vRow1 = exportHead;

    tempArr = new Array(vRow1.length).fill(null);
    tempArr[0] = vRow0[0];
    tHead.push(tempArr);

    tempArr = [];
    for (var i = 0; i < vRow1.length; i++) {
        tempArr.push(vRow1[i]['title']);
    }
    tHead.push(tempArr);

    wholeTable = tHead.concat(data);
    sheet = XLSX.utils.aoa_to_sheet(wholeTable);
    sheet['!merges'] = [
        // 设置第一行與第二行相同列數的单元格合并
        { s: { r: 0, c: 0 }, e: { r: 0, c: vRow1.length - 1 } }
    ];
    openDownloadDialog(sheet2blob(sheet), fileName + '.xlsx');
}

注意：通過command.CommandTimeout配置數據庫延時時間，默認超時時間為20秒。並且需要在js中的ajax請求中設置timeout屬性，否則後台時間過長，前台也會因為超時出現超時的錯誤。