function exportExcelByDom(dom, fileName) {
var rowspan=2
var selfArr=[]
if (dom){
rowspan=parseInt($("#to_excel_table thead tr th:first-child").attr('rowspan'))+1
$("#to_excel_table tbody tr").each(function (i,v) {
if ($(this).hasClass('class_0')){
selfArr.push({
color:'006699',
i:parseInt(i)+rowspan,
fontColor:'FFFFFF'
})
}
if ($(this).hasClass('class_1')){
selfArr.push({
color:'006699',
i:parseInt(i)+rowspan,
fontColor:'FFFFFF'
})
}
if ($(this).hasClass('class_2')){
selfArr.push({
color:'6699CC',
i:parseInt(i)+rowspan,
fontColor:'FFFFFF'
})
}
if ($(this).hasClass('class_3')){
selfArr.push({
color:'BFD2F9',
i:parseInt(i)+rowspan,
fontColor:'333333'
})
}
if ($(this).hasClass('class_4')){
selfArr.push({
color:'666666',
i:parseInt(i)+rowspan,
fontColor:'FFFFFF'
})
}
if ($(this).hasClass('class_5')){
selfArr.push({
color:'777777',
i:parseInt(i)+rowspan,
fontColor:'FFFFFF'
})
}
if ($(this).hasClass('class_6')){
selfArr.push({
color:'888888',
i:parseInt(i)+rowspan,
fontColor:'444444'
})
}
if ($(this).hasClass('class_7')){
selfArr.push({
color:'999999',
i:parseInt(i)+rowspan,
fontColor:'333333'
})
}
if ($(this).hasClass('class_8')){
selfArr.push({
color:'aaaaaa',
i:parseInt(i)+rowspan,
fontColor:'222222'
})
}
var data =XEUtils.clone(tbody_tr_list, true)
if (data && data.length) {
let len = data[0]
let index = []
let str=[]
for (let i =7; i < len.length; i++) {
if (typeof len[i]==='number'){
index.push(i-7)
}
if (typeof len[i]==='string'){
str.push(i-7)
}
}
$(this).find('td').each(function(k,doc){
if (index.includes(k)&&$(this).text()==='-'){
$(this).text('0')
}
if (str.includes(k)&&$(this).text()==='-'){
$(this).text('')
}
});
}
})
}
//获取颜色结束
let book =XLSX.utils.book_new()
function convertTableToSheet(table) {
const ws =XLSX.utils.table_to_sheet(table, {raw:true});
// 遍历工作表中的所有单元格
Object.keys(ws).forEach(cellRef => {
const cell = ws[cellRef];
// // 如果单元格是数字类型的文本
if (!isNaN(Number(cell.v))) {
// 转换为数值类型
ws[cellRef].t ='n'
}
});
return ws;
}
// 使用方法
const table = dom; // 替换为你的表格ID
const sheet =convertTableToSheet(table);
XLSX.utils.book_append_sheet(book, sheet, 'Sheet1')
addRangeBorder(sheet['!merges'], sheet)// 给合并行列赋值样式
setExcelStyle(sheet,100,selfArr,rowspan)// 设置样式
let wbout = XLSXS.write(book, {
bookType:'xlsx',
bookSST:false,
type:'binary'
})
try {
var blob =new Blob([s2ab(wbout)], {type:'application/octet-stream' });
// 使用URL.createObjectURL创建一个下载链接
var url =URL.createObjectURL(blob);
// 创建一个a标签，用于触发下载
var a =document.createElement('a');
a.href = url;
a.download = fileName
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
}catch (e) {
console.error(e, wbout, '----->>>')
}
}
// 设置导出Excel样式（统一样式）
function setExcelStyle(data, wpx =80,selfArr,rowspan) {
data["!cols"] = []
const excludes = ['!cols', '!fullref', '!merges', '!ref', '!rows']
for (let keyin data) {
if (data.hasOwnProperty(key)) {
if (!excludes.includes(key)) {
data[key].s = {
alignment: {
horizontal:"center", //水平居中对齐
vertical:"center", // 垂直居中
wrapText:true,
},
border: {
top: {
style:'thin',
color: {rgb:'000000' }
},
bottom: {
style:'thin',
color: {rgb:'000000' }
},
left: {
style:'thin',
color: {rgb:'000000' }
},
right: {
style:'thin',
color: {rgb:'000000' }
}
},
fill: {
fgColor: {rgb:"FFFFFF" },
},
font: {
sz:11,
// color: { rgb: "FFFFFF" }
},
bold:true,
numFmt:0,
}
// 单元格宽度
data["!cols"].push({ wpx });
// 根据不同行添加单元格背景颜色
let color =''
let fontColor =''
let num =Number(key.slice(1))
var index=0
let letter = key[0]
const leftCol =other_config.isCellArr||'A'
if (num < rowspan) {
color ='FFFFFF'
}
while (index < selfArr.length-1) {
if (num >= selfArr[index].i && num <= selfArr[index +1].i&&leftCol.includes(letter)) {
color = selfArr[index].color
fontColor=selfArr[index].fontColor
if (index===selfArr.length-2){
color = selfArr[index +1].color
fontColor=selfArr[index +1].fontColor
}
}
index++
}
if (!leftCol.includes(letter)){
color ='FFFFFF'
}
var leftCol2 = ["AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ","BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ","CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ","DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ","EA", "EB", "EC", "ED", "EE", "EF", "EG", "EH", "EI", "EJ", "EK", "EL", "EM", "EN", "EO", "EP", "EQ", "ER", "ES", "ET", "EU", "EV", "EW", "EX", "EY", "EZ"];
var letter2=key.substring(0, 2)
if (leftCol2.includes(letter2)){
color ='FFFFFF'
}
data[key].s.fill = {fgColor: {rgb: color||'FFFFFF', patternType:'solid' } }
if (leftCol.includes(letter)&&num >= rowspan){
data[key].s.font = {color: {rgb: fontColor||'000000' }}
}
}
}
}
}
//给合并行列赋值样式
function addRangeBorder (range, ws) {
let cols = ["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","AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ","BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ","CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ","DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ","EA", "EB", "EC", "ED", "EE", "EF", "EG", "EH", "EI", "EJ", "EK", "EL", "EM", "EN", "EO", "EP", "EQ", "ER", "ES", "ET", "EU", "EV", "EW", "EX", "EY", "EZ"];
if (range&&range.length){
range.forEach(item => {
let style = {
s: {
border: {
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 i = item.s.c; i <= item.e.c; i++) {
ws[`${cols[i]}${Number(item.e.r) +1}`] = ws[`${cols[i]}${Number(item.e.r) +1}`] || style
// 处理合并列
for (let k = item.s.r +2; k <= item.e.r +1; k++) {
ws[cols[i] + k] = ws[cols[k] + item.e.r] || style
}
}
})
}
return ws;
}
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
}
exportExcelByDom(document.getElementById('to_excel_table'), sheetname+'.xlsx')
