js使用XLSX實現excel數據縱向導出

 c#後台

[WebMethod]
    public static string exportData(objectItem object)
    {
        string id1= "", starttime = "", endtime = "", exportUpData = "", exportDownData = "", jsonString = "", id2= "", id3= "";
        id1= object.id1;
        id2= object.id2;
        id3= object.id3;
        starttime = object.starttime;
        endtime = object.endtime;
        JavaScriptSerializer jss = new JavaScriptSerializer();
        var dbObj = new Postgre();
        string sql1 = string.Format(@"select t.* from schmea.function('{3}', '{4}') t
                                      where t.id1= '{0}'  
                                      order by t.id1"
                                      , id1, id2, id3, starttime, endtime);
        
        exportUpData = JsonHelper.TableToArrayString(sql1);
        string sql2 = string.Format(@"select t.colum1,t.colum2,
                                             string_agg(cast(t.colum3 as varchar), ',' order by t.start_time,t.colum4,t.colum5) as colum3 
                                      from schmea.function('{3}', '{4}') t
                                      where t.id1= '{0}' {1} {2} 
                                      group by t.id1,t.id2,t.colum1,t.colum2,t.start_time
								      order by t.start_time"
                                    , id1, id2, id3, starttime, endtime);
        
        exportDownData = JsonHelper.TableToArrayString(sql2);
        exportUpData = exportUpData != "NoData" ? exportUpData : "\"NoData\"";
        exportDownData = exportDownData != "NoData" ? exportDownData : "\"NoData\"";
        jsonString += "[{\"exportUpData\":" + exportUpData + ",\"exportDownData\":" + exportDownData + "}]";
        return jsonString;
    }

java後台同理，sql相同，用mybatis或者jpa實現

js實現

function exportSubmit() {
    var id1= '';
    var id2 = '';
    var id3 = '';
    var starttime = '';
    var endtime = '';
    var objectItem={ 'id1': id1, 'id2 ': id2 , 'id3 ': id3 , 'starttime': starttime, 'endtime': endtime };
    $.ajax({
        type: "post",
        url: "../../..",
        data: JSON.stringify({ 'objectItem': objectItem }),
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        traditional: true,
        success: function (data) {
            var tempData = eval(data.d);
            if (tempData[0].exportUpData != 'NoData') {
                exportExcelFile(tempData[0]);
            } else {
                ShowMsg('無數據');
            }
        },
        error: function (t1, t2, t3) {
            var m = t1 + t2 + t3;
            alert(m);
        }
    });
}
function normSDist(x) {//实现正态分布
    const erf = (x) => {
        const t = 1.0 / (1.0 + 0.5 * Math.abs(x));
        const tau = t * Math.exp(-Math.pow(x, 2) - 1.26551223 +
          t * (1.00002368 +
            t * (0.37409196 +
              t * (0.09678418 +
                t * (-0.18628806 +
                  t * (0.27886807 +
                    t * (-1.13520398 +
                      t * (1.48851587 +
                        t * (-0.82215223 +
                          t * 0.17087277
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        );
        return x >= 0 ? 1 - tau : tau - 1;
    };

    return (1 + erf(x / Math.sqrt(2))) / 2;
}
var exportHead = [
    { title: "title1" },
    { title: "title2" },
    { title: "title3" },
    { title: "" },
    { title: "" },
];
var lastTwoHead = [
    { title: "开始时间" },
    { title: "结束时间" },
    { title: "title1" },
    { title: "title2" },
    { title: "title3" },
];
function exportExcelFile(data) {
    var exportUpData = data.exportUpData;
    var exportDownData = data.exportDownData != "NoData"?data.exportDownData:[];
    var queryTyp = exportUpData[0][0] + " 报表";
    var curDt = new Date();
    var fileName = queryTyp + curDt.Format('yyyyMMddHHmmss');//生成文件名
    var vRow0 = [];//标题
    var vRow1 = [];//数据项名称
    var tHead = [];
    var lastHead = [];
    var tempArr = [];
    var wholeTable = [];
    var sheet = {};
    var message = '首行说明信息';
    vRow0 = [message];
    var excelUpData =[];
    var excelDownData =[];
    excelUpData = exportUpData.map(dataArray => dataArray.slice(3, -2));
    excelDownData = exportDownData.map(subArr => {//将二维数组总的每一项都变成字符串数组
        let flatSubArr = subArr.map(item => item.includes(',') ? item.split(',') : item).flat();
        return flatSubArr;
    });

    vRow1 = exportHead;
    tempArr[4] = vRow0[0];
    tHead.push(tempArr);
    // 将表头也转换为纵向数据
    var transposedHeader = [];
    var lastTwoData = [];
    for (var i = 0; i < vRow1.length; i++) {
        transposedHeader.push([vRow1[i]['title']]);
    }
    // 将数据转换为纵向数据
    var transposedData = [];
    for (var j = 0; j < excelUpData[0].length; j++) {
        var rowData = ['','','',''];
        // 将表头的每个元素做为纵向数据的第一个元素
        rowData.push(vRow1[j]['title']);
        // 将横向数据转换为纵向数据
        for (var k = 0; k < excelUpData.length; k++) {
            rowData.push(excelUpData[k][j]);
        }
        transposedData.push(rowData);
    }
    for (var col = 5; col < transposedData[0].length; col++) {
        //var formula = '=NORMSDIST(3*$' + String.fromCharCode(65 + col) + '$16)+NORMSDIST(6*$' + String.fromCharCode(65 + col) + '13-3*$' + String.fromCharCode(65 + col) + '$16)-1';//excel实现公式
        var formula = ((normSDist(3 * transposedData[14][col]) + normSDist(6 * transposedData[11][col] - 3 * transposedData[14][col]) - 1) * 100).toFixed(2) + '%';
        transposedData[16][col] = formula;
    }
    // 将最后两列的数组数据按逗号分割并纵向输出到excel中去
    /*lastTwoData = excelDownData[0][0].split(',').map(item =>[item,'']);
    for (var m = 0; m < excelDownData.length; m++) {
        var lastData = excelDownData[m][1].split(',');
        for (var n = 0; n < lastData.length; n++) {
            lastTwoData[n].push(lastData[n]);
        }
    }
    for (var i = 0; i < lastTwoHead.length; i++) {
        lastHead.push([lastTwoHead[i]['title']]);
    }*/
    for (var i = 0; i < lastTwoHead.length; i++) {
        lastHead[i]=lastTwoHead[i]['title'];
    }
    transposedData.push(lastHead); 
    //transposedData = transposedData.concat(lastTwoData);
    transposedData = transposedData.concat(excelDownData);
    wholeTable = tHead.concat(transposedData);

    sheet = XLSX.utils.aoa_to_sheet(wholeTable);
    sheet['!merges'] = [
    // 设置单元格合并
    { s: { r: 0, c: 4 }, e: { r: 0, c: excelUpData.length + 1 } }
    ];
    openDownloadDialog(sheet2blob(sheet), fileName + '.xlsx');
}

说明：由于需要纵向数据的excel，并且会去使用excel中的某些公式，所以可以通过构造数据来实现纵向输出。