function ExportDetail()
{
if(dataModel.AssuranceDetailList.length!=0)
{
//导出报表
readWorkbookFromRemoteFile("https://sites.ey.com/sites/GDSChinaOpsSupport/GDS_Portal/GDS_Portal_Dev/Shared%20Documents/AssuranceReportTemplateOT%20Detail.xlsx", readWorkbook);
}
}
function readWorkbookFromRemoteFile(url, callback) {
var xhr = new XMLHttpRequest();
xhr.open('get', url, true);
xhr.responseType = 'arraybuffer';
xhr.onload = function (e) {
if (xhr.status == 200) {
var data = new Uint8Array(xhr.response)
var workbook = XLSX.read(data, { type: 'array' });
if (callback) callback(workbook);
}
};
xhr.send();
}
function readWorkbook(workbook) {
debugger;
// outputWorkbook(workbook);
var sheetNames = workbook.SheetNames; // 工作表名称集合
var worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheet
var metaData = dataModel.AssuranceDetailList;
for (var i = 0; i < metaData.length; i++) {
var subItem = metaData[i];
worksheet["A" + (2 + i)] = { h: subItem.RequestNo, r: '<t>' + subItem.RequestNo + '</t>', t: "s", v: subItem.RequestNo, w: subItem.RequestNo};
worksheet["B" + (2 + i)] = { h: subItem.RequesterSkypeName, r: '<t>' + subItem.RequesterSkypeName + '</t>', t: "s", v: subItem.RequesterSkypeName, w: subItem.RequesterSkypeName };
worksheet["C" + (2 + i)] = { h: subItem.RequesterGPN, r: '<t>' + subItem.RequesterGPN + '</t>', t: "s", v: subItem.RequesterGPN, w: subItem.RequesterGPN };
worksheet["D" + (2 + i)] = { h: subItem.RequesterChineseName, r: '<t>' + subItem.RequesterChineseName + '</t>', t: "s", v: subItem.RequesterChineseName, w: subItem.RequesterChineseName };
worksheet["E" + (2 + i)] = { h: subItem.RequesterRank, r: '<t>' + subItem.RequesterRank + '</t>', t: "s", v: subItem.RequesterRank, w: subItem.RequesterRank };
worksheet["F" + (2 + i)] = { h: subItem.RequesterGrade, r: '<t>' + subItem.RequesterGrade + '</t>', t: "s", v: subItem.RequesterGrade, w: subItem.RequesterGrade};
worksheet["G" + (2 + i)] = { h: subItem.RequesterRegion, r: '<t>' + subItem.RequesterRegion + '</t>', t: "s", v: subItem.RequesterRegion, w: subItem.RequesterRegion };
worksheet["H" + (2 + i)] = { h: subItem.RequesterEmail, r: '<t>' + subItem.RequesterEmail + '</t>', t: "s", v: subItem.RequesterEmail, w: subItem.RequesterEmail };
worksheet["I" + (2 + i)] = { h: subItem.ServiceLine, r: '<t>' + subItem.ServiceLine + '</t>', t: "s", v: subItem.ServiceLine, w: subItem.ServiceLine };
worksheet["J" + (2 + i)] = { h: subItem.RequesterActive, r: '<t>' + subItem.RequesterActive + '</t>', t: "s", v: subItem.RequesterActive, w: subItem.RequesterActive };
worksheet["K" + (2 + i)] = { h: subItem.DepartmentDescription, r: '<t>' + subItem.DepartmentDescription + '</t>', t: "s", v: subItem.DepartmentDescription, w: subItem.DepartmentDescription };
worksheet["K" + (2 + i)] = { h: subItem.ActualOTStartDate, r: '<t>' + subItem.ActualOTStartDate + '</t>', t: "d", v: subItem.ActualOTStartDate, w: subItem.ActualOTStartDate };
worksheet["L" + (2 + i)] = { h: subItem.ActualOTEndDate, r: '<t>' + subItem.ActualOTEndDate + '</t>', t: "d", v: subItem.ActualOTEndDate, w: subItem.ActualOTEndDate };
worksheet["M" + (2 + i)] = { h: subItem.Workday, r: '<t>' + subItem.Workday + '</t>', t: "s", v: subItem.Workday, w: subItem.Workday };
worksheet["N" + (2 + i)] = { h: subItem.PreApplicationNo, r: '<t>' + subItem.PreApplicationNo + '</t>', t: "s", v: subItem.PreApplicationNo, w: subItem.PreApplicationNo };
worksheet["O" + (2 + i)] = { h: subItem.PreOThours, r: '<t>' + subItem.PreOThours + '</t>', t: "n", v: subItem.PreOThours, w: subItem.PreOThours };
worksheet["P" + (2 + i)] = { h: subItem.ActualOTHours, r: '<t>' + subItem.ActualOTHours + '</t>', t: "n", v: subItem.ActualOTHours, w: subItem.ActualOTHours };
worksheet["Q" + (2 + i)] = { h: subItem.OTleave, r: '<t>' + subItem.OTleave + '</t>', t: "n", v: subItem.OTleave, w: subItem.OTleave };
worksheet["R" + (2 + i)] = { h: subItem.OTBalance, r: '<t>' + subItem.OTBalance + '</t>', t: "n", v: subItem.OTBalance, w: subItem.OTBalance };
worksheet["S" + (2 + i)] = { h: subItem.OTLocation, r: '<t>' + subItem.OTLocation + '</t>', t: "s", v: subItem.OTLocation, w: subItem.OTLocation };
worksheet["T" + (2 + i)] = { h: subItem.ApplicationReason, r: '<t>' + subItem.ApplicationReason + '</t>', t: "s", v: subItem.ApplicationReason, w: subItem.ApplicationReason };
worksheet["U" + (2 + i)] = { h: subItem.CreateDate, r: '<t>' + subItem.CreateDate + '</t>', t: "d", v: subItem.CreateDate, w: subItem.CreateDate };
worksheet["V" + (2 + i)] = { h: subItem.RejectComments, r: '<t>' + subItem.RejectComments + '</t>', t: "s", v: subItem.RejectComments, w: subItem.RejectComments };
worksheet["W" + (2 + i)] = { h: subItem.Status, r: '<t>' + subItem.Status + '</t>', t: "s", v: subItem.Status, w: subItem.Status };
worksheet["X" + (2 + i)] = { h: subItem.HolidayOvertime, r: '<t>' + subItem.HolidayOvertime + '</t>', t: "n", v: subItem.HolidayOvertime, w: subItem.HolidayOvertime };
worksheet["Y" + (2 + i)] = { h: subItem.Engagement, r: '<t>' + subItem.Engagement + '</t>', t: "s", v: subItem.Engagement, w: subItem.Engagement };
worksheet["Z" + (2 + i)] = { h: subItem.GDSApprover, r: '<t>' + subItem.GDSApprover + '</t>', t: "s", v: subItem.GDSApprover, w: subItem.GDSApprover };
worksheet["AA" + (2 + i)] = { h: subItem.SLLeaderapproval, r: '<t>' + subItem.SLLeaderapproval + '</t>', t: "s", v: subItem.SLLeaderapproval, w: subItem.SLLeaderapproval };
worksheet["AB" + (2 + i)] = { h: subItem.OTSupplement, r: '<t>' + subItem.OTSupplement + '</t>', t: "s", v: subItem.OTSupplement, w: subItem.OTSupplement };
worksheet["AC" + (2 + i)] = { h: subItem.SystemImport, r: '<t>' + subItem.SystemImport + '</t>', t: "s", v: subItem.SystemImport, w: subItem.SystemImport };
}
setExcelStyle(worksheet);
if (metaData.length > 0) {
var blob = sheet2blob(worksheet, "AssuranceDetailExport");
$("#preloader").fadeOut("slow");
openDownloadDialog(blob, 'AssuranceDetailExport.xlsx');
} else {
alert("There is no data that meets the requirements!");
$("#preloader").fadeOut("slow");
}
}
function setExcelStyle(worksheet)
{
var range=XLSX.utils.decode_range(worksheet["!ref"]);
var borderStyle = {
top: { style: "thin",color: { rgb: "000000" } },
bottom: { style: "thin",color: { rgb: "000000" }},
left: { style: "thin",color: { rgb: "000000" }},
right: { style: "thin",color: { rgb: "000000" }
}
};
for (let C = range.s.c; C <= range.e.c; ++C) {
for (let R = range.s.r; R <= range.e.r; ++R) {
let cell = { c: C, r: R };
let cell_ref = XLSX.utils.encode_cell(cell);
if (worksheet[cell_ref]){
worksheet[cell_ref].s = {
alignment: {
horizontal: "center",
vertical: "center"
},
font: {
name: "黑体",
sz: "15",
color: {rgb: "#9370DB"},
bold: true
},
border: borderStyle,
};
}
}
}
}
function sheet2blob(sheet, sheetName) {
sheetName = sheetName || 'sheet1';
var workbook = {
SheetNames: [sheetName],
Sheets: {}
};
workbook.Sheets[sheetName] = sheet;
// 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: true, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = XLSX.write(workbook, wopts);
var blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" });
// 字符串转ArrayBuffer
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
return blob;
}
function openDownloadDialog(url, saveName) {
// for IE
if (window.navigator && window.navigator.msSaveOrOpenBlob) {
window.navigator.msSaveOrOpenBlob(url, saveName);
}
// for Non-IE (chrome, firefox etc.)
else {
if (typeof url == 'object' && url instanceof Blob) {
url = URL.createObjectURL(url); // 创建blob地址
}
var aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
var event;
if (window.MouseEvent) event = new MouseEvent('click');
else {
event = document.createEvent('MouseEvents');
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
}
aLink.dispatchEvent(event);
}
}
