vue 前端生成 excel文件 XLSX和 ExcelJS

待完善

日期，下拉（动态），表头颜色，合并单元格，日期 整型，字符串，小数保留几位，整数最大长度，各种校验，给第几个单元格设置颜色

表头数据

// 下载excel表头
export const sheetColumn = [
  {
    header: 'Id',
    key: 'Id',
    width: 10
  },
  {
    header: '日期',
    key: 'BillNo',
    width: 20, 
    style: { numFmt: 'dd/mm/yyyy' }
 
  },
  {
    header: '服务商',
    key: 'ServiceCode',
    width: 20, 
    style: { font: { name: 'Arial Black' } }
 
  },
  {
    header: '客户分组',
    key: 'CustomerGroupName',
    width: 10
  },
  {
    header: '运单号',
    key: 'ForecastNumber',
    width: 20
  },
  {
    header: '跟踪号',
    key: 'TrackingNumber',
    width: 20
  },
  {
    header: '客户单号',
    key: 'CustomerOrderNumber',
    width: 20
  },
  {
    header: '系统金额',
    key: 'SystemMoney',
    width: 10
  },
  {
    header: '*勘误费用项',
    key: 'ErrataFeeItemName',
    width: 20
  },
  {
    header: '*勘误金额',
    key: 'ErrataFee',
    width: 10
  },
  {
    header: '*勘误后总金额',
    key: 'ErrataTotalFee',
    width: 15
  },
  {
    header: '*勘误原因',
    key: 'Note',
    width: 20
  }
]

import XLSX from 'xlsx'

import XLSX from 'xlsx'
import { Trim } from '@/utils'

// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
function sheet2blob(sheet, sheetName) {
    sheetName = sheetName || 'sheet1'
    var workbook = {
        SheetNames: [sheetName],
        Sheets: {}
    }
    workbook.Sheets[sheetName] = sheet
        // 生成excel的配置项
    var wopts = {
        bookType: 'xlsx', // 要生成的文件类型
        bookSST: false, // 是否生成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) {
    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)
}

// 读取，修改，保存-导出
export function updateExcel(data, name) {
    // 格式
    // let aoa = [
    //   ['姓名', '性别', '年龄', '注册时间'],
    //   ['张三', '男', 18, new Date()]
    // ]
    let sheet = XLSX.utils.aoa_to_sheet(data)
    openDownloadDialog(sheet2blob(sheet), `${name}`)
}


 

import ExcelJS from 'exceljs'

import XLSX from 'xlsx'
import ExcelJS from 'exceljs'
import { Trim } from '@/utils'
import { sheetColumn } from '@/utils/excelHeader.js'
import lodash from 'lodash'


export const createExcelFileExcelJS = async (arr, fileName = `下载文件.xlsx`) => {

  // 组装数据
  let list = lodash.cloneDeep(arr || [])
  let sheetData = []
  for (let i = 0, len = list.length; i < len; i++) {
    let v = list[i]
    let obj = {
      Id: 1,
      BillNo: 2,
      ServiceCode: 3,
      CustomerGroupName: 4,
      ForecastNumber: 5,
      TrackingNumber: 6,
      CustomerOrderNumber: 7,
      SystemMoney: 8,
      ErrataFeeItemName: '',
      ErrataFee: '',
      ErrataTotalFee: '',
      ErrataNote: 9,
      items: `111`
    }
    sheetData.push(obj)
  }

  // 生成sheet
  const wb = new ExcelJS.Workbook()
  const sheet = wb.addWorksheet('Sheet1')
  sheet.columns = sheetColumn
   
/ 将第2行设置为 Comic Sans 字体。
sheet.getRow(2).font = {
    name: 'Comic Sans MS', family: 4, size: 16, underline: 'double', bold: true 
};
sheet.getCell('A1').font = {
  name: 'Comic Sans MS',
  family: 4,
  size: 16,
  underline: true,
  bold: true
};
 
// 垂直对齐
sheet.getCell('A3').font = {
  vertAlign: 'superscript'
};

 
// 在A2中填充深黄色格子和蓝色背景
sheet.getCell('A2').fill = {
  type: 'pattern',
  pattern:'darkTrellis',
  fgColor:{argb:'FFFFFF00'},
  bgColor:{argb:'FF0000FF'}
};
 
 

 
// 显示为“ 1.60％”
sheet.getCell('B1').value = 0.016;
sheet.getCell('B1').numFmt = '0.00%';

 

 

\
 
 
    // 将第3列设置为“货币格式”
    sheet.getColumn(3).numFmt = '"£"#,##0.00;[Red]-"£"#,##0.00';
 
  sheet.addRows(sheetData)
  for (let i = 0, len = sheetData.length; i < len; i++) {
    sheet.getCell(i + 1, 9).dataValidation = {
      type: 'list',
      formulae: ['"One,Two,Three,Four"']
    }
    
    / 指定单元格必须为非5的整数。
    // 向用户显示适当的错误消息（如果他们弄错了）
    sheet.getCell('A1').dataValidation = {
      type: 'whole',
      operator: 'notEqual',
      showErrorMessage: true,
      formulae: [5],
      errorStyle: 'error',
      errorTitle: 'Five',
      error: 'The value must not be Five'
    }; 
    
    // 指定单元格必须为1.5到7之间的十进制数字。
    // 添加“工具提示”以帮助指导用户
    sheet.getCell('A1').dataValidation = {
      type: 'decimal',
      operator: 'between',
      allowBlank: true,
      showInputMessage: true,
      formulae: [1.5, 7],
      promptTitle: 'Decimal',
      prompt: 'The value must between 1.5 and 7'
    };

    // 指定单元格的文本长度必须小于15
    sheet.getCell('A1').dataValidation = {
      type: 'textLength',
      operator: 'lessThan',
      showErrorMessage: true,
      allowBlank: true,
      formulae: [15]
    };

    // 指定单元格必须是2016年1月1日之前的日期
    sheet.getCell('A1').dataValidation = {
      type: 'date',
      operator: 'lessThan',
      showErrorMessage: true,
      allowBlank: true,
      formulae: [new Date(2016,0,1)]
    }; 
    
    
  }
  const buffer = await wb.xlsx.writeBuffer()
  var blob = new Blob([buffer], {
    type: 'application/octet-stream'
  })
  var url = blob
  if (typeof url == 'object' && url instanceof Blob) {
    url = URL.createObjectURL(url) // 创建blob地址
  }
  var aLink = document.createElement('a')
  aLink.href = url
  aLink.download = fileName || '' // 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)
  return
}