import * as XLSX from 'xlsx'
import FileSaver from 'file-saver'
import XLSXS from 'xlsx-style'
 
/**
 * 根據(jù)DOM進行導出
 * @param {Element} dom 
 * @param {String} fileName 
 */
export function exportExcelByDom(dom, fileName) {
  const book = XLSX.utils.book_new()
  const sheet = XLSX.utils.table_to_sheet(dom)
  XLSX.utils.book_append_sheet(book, sheet, 'Sheet1')
  addRangeBorder(sheet['!merges'], sheet) // 給合并行列賦值樣式
  setExcelStyle(sheet) // 設置樣式
  let wbout = XLSXS.write(book, {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  })
  try {
    FileSaver.saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), fileName);
  } catch (e) {
    console.error(e, wbout, '----->>>')
  }
}
 
// 設置導出Excel樣式（統(tǒng)一樣式）
function setExcelStyle(data, wpx = 80) {
  data["!cols"] = []
  const excludes = ['!cols', '!fullref', '!merges', '!ref', '!rows']
  for (let key in 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: "00a2ff" },
          // },
          font: {
            sz: 11,
          },
          bold: true,
          numFmt: 0
        }
        // 單元格寬度
        data["!cols"].push({ wpx });
        // 根據(jù)不同行添加單元格背景顏色
        let color = ''
        let num = Number(key.slice(1))
        if (num < 12) {
          color = 'f8cbad'
        } else if (num >= 12 && num < 22) {
          color = '70ad47'
        } else if (num >= 22 && num < 26) {
          color = '00b0f0'
        } else if (num >= 26 && num < 29) {
          color = 'fff2cc'
        } else if (num >= 29 && num < 44) {
          color = 'a9d08e'
        } else {
          color = 'bfbfbf'
        }
        data[key].s.fill = { fgColor: { rgb: color, patternType: 'solid' } }
      }
    }
  }
}
 
//給合并行列賦值樣式
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"];
  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('custom-table'), '生產(chǎn)日報表.xlsx')