使用sheet.js导出数据

下面是核心代码

let exportColData
let exportColHead
//excel工作簿全局变量
let workBook = null;
//excel sheet全局变量
let workSheet = null;
let exportData = []
let exportCount = 5000
let letterList = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"]
let totalCount=null

function getExportData(pageIndex, pageSize) {
new Promise(function (resolve,reject) {
var jsonobj = $('.row[data-key="@Key"]').getFormData();
jsonobj["SMT_TOOL_CODE"] = jsonobj["TOOLING_CODE"];
jsonobj["DATETIME_MODIFIED_BEGIN"] = $('#txt_datetime_begin_@Key').val();
jsonobj["DATETIME_MODIFIED_END"] = $('#txt_datetime_end_@Key').val();
jsonobj["WorkShopID"] = $('#workshop_WORKSHOP_ID').val();
jsonobj["ProcessId"] = $('#process_id_@ViewBag.Guid').val();
jsonobj["SHIFT_CODE"] = $('#ShiftCode_@Key').find("option:selected").val();

var json_str = JSON.stringify(jsonobj);
var start = (pageIndex - 1) * pageSize
if (totalCount =null||totalCount >= start) {
$.ajax({
url: '/RPT/@controller/LoadData?start=' + start + '&length=' + pageSize,
type: 'post',
timeout: 60000,
cache: false,
data: { postjson: json_str },
success: function (respData) {
totalCount=respData.recordsTotal
if (respData.data.length > 0) {
exportData.push(...respData.data)
}
let pre = Math.trunc( (exportData.length / respData.recordsTotal) * 100)
updateProgress(pre)
pageIndex++
//获取数据
getExportData(pageIndex, pageSize)

respData = null
},
error: function (err) {
alert(err)
}
})
}
else
resolve()
}).then(function () {
if (exportData.length > 0) {
workSheet = XLSX.utils.json_to_sheet(exportData, { header: exportColHead, dense: true })
//设置标头
for (let i = 0; i < exportColData.length; i++) {
workSheet["!data"][0][i].v = exportColData[i].Tit
}
var len = "A1:" + letterList[exportColData.length - 1] + exportData.length+1
//导出范围内的数据
workSheet['!ref'] = len
XLSX.utils.book_append_sheet(workBook, workSheet, "Sheet0")

let fileName = '部装缺陷记录报表_' + formatDate(new Date(), 'yyyyMMddhhmmss')+'.xlsx'
XLSX.writeFile(workBook, fileName, { dense: true, compression: true, type: 'binary' })
}
exportInit()
}).catch(function (ex) {
console.log(ex)
exportInit()
})
}

function updateProgress(pre) {
$("#exportProgress").text('导出进度：' + pre + '%')
}

function exportInit() {
workBook = null
workSheet = null
exportColData = null
exportColHead = null
totalCount = null
exportData=[]
$("#ExportDiv").prop('disabled', '')
$("#exportProgress").text('1%')
$("#myModal").modal('hide')

}

function exportAction() {
exportColData = sys_columns
//定义表头
let colHeader = []
for (let ii = 0; ii < sys_columns.length; ii++) {
colHeader.push(sys_columns[ii].Data)
}
exportColHead = colHeader
workBook = XLSX.utils.book_new();
//console.log(colHeader)
//根据头部数组创建excel sheet
//workSheet = XLSX.utils.aoa_to_sheet({ header: colHeader });

var pageIndex = 1
var pageSize = exportCount
getExportData(pageIndex, pageSize)

}

//导出Excel
$("#ExportDiv").click(function () {
$("#ExportDiv").prop('disabled', 'disabled')
$("#myModal").modal('show')
exportAction();
});

 

/**
* 格式化函数 ， 给日期格式化
* date为 new Date()对象， fmt为 'yyyy-MM-dd hh:mm:ss'的格式
*/
function formatDate(date, fmt) {
//获取年份
if (/(y+)/.test(fmt)) {
// 把数字变成字符串
let dateY = date.getFullYear() + "";
//RegExp.$1 在判断中出现过，且是括号括起来的，所以 RegExp.$1 就是 "yyyy"
fmt = fmt.replace(RegExp.$1, dateY.substr(4 - RegExp.$1.length));
}

//获取其他
let o = {
"M+": date.getMonth() + 1,
"d+": date.getDate(),
"h+": date.getHours(),
"m+": date.getMinutes(),
"s+": date.getSeconds(),
};
for (const k in o) {
if (new RegExp(`(${k})`).test(fmt)) {
let str = o[k] + "";
fmt = fmt.replace(
RegExp.$1,
RegExp.$1.length == 1 ? str : padLeftZero(str)
);
}
}
return fmt;
}