前端excel文件处理，vue2 、file-saver、xlsx, excel文件生成与excel文件链接数据导出

1、前端excel文件生成

安装插件

npm install file-saver --save

如使用TS开发，可安装file-saver的TypeScript类型定义

npm install @types/file-saver --save-dev

下载文件流

import { saveAs } from 'file-saver'
/**
 *
 * @param {*} fileStream // 文件流
 * @param {*} saveFileName // 文件下载名称
 * @returns
 */
export const downloadFile = (fileStream, saveFileName = '.xlsx') => {
  return new Promise((resolve, reject) => {
    const blob = new Blob([fileStream])
    // 兼容IE
    if (navigator.msSaveBlob) {
      navigator.msSaveBlob(blob, saveFileName)
    } else {
      const url = window.URL.createObjectURL(blob)
      saveAs(url, saveFileName + '.xlsx')
    }
    resolve()
  })
}
/**
 * @description 方法调用
 */
downloadFile(fileStream, saveFileName).then(res => {
  console.log('下载成功！')
})

本地文件下载

import { saveAs } from 'file-saver'
/**
 *
 * @param {*} localFileUrl 本地地址
 * @param {*} saveFileName 文件名称
 * @returns
 */
export const downloadFile = (localFileUrl, saveFileName) => {
  return new Promise((resolve, reject) => {
    axios({
      url: localFileUrl, // 文件地址
      method: 'get',
      responseType: 'blob'
    })
      .then(res => {
        const blob = new Blob([res.data])
        if (navigator.msSaveBlob) {
          navigator.msSaveBlob(blob, saveFileName)
        } else {
          const url = window.URL.createObjectURL(blob)
          saveAs(url, saveFileName)
        }
        resolve()
      })
      .catch(err => {
        if (err.message === 'Request failed with status code 404') {
          // 未找到相关文件
        } else {
          // 下载失败
        }
        reject(err)
      })
  })
}

/**
 * @description 方法调用
 */
downloadFile(localFileUrl, saveFileName).then(res => {
  console.log('下载成功！')
})

文件下载（列宽自适应）

import fs from 'file-saver'
import XLSX from 'xlsx'
export function xlsxGenerate(json, fields, filename = '.xlsx') {
  json.forEach(item => {
    for (let i in item) {
      if (fields.hasOwnProperty(i)) {
        item[fields[i]] = item[i]
      }
      delete item[i]
    }
  })
  let sheetName = filename
  let wb = XLSX.utils.book_new()
  let ws = XLSX.utils.json_to_sheet(json, {
    header: Object.values(fields)
  })
  const defaultCellStyle = {
    font: {
      name: 'Verdana',
      sz: 16,
      color: 'FF00FF88'
    },
    alignment: {
      horizontal: 'center',
      vertical: 'center'
    },
    fill: {
      fgColor: {
        rgb: 'FFFFAA00'
      }
    }
  }
  let headsArr = Object.values(fields)
  const headsWidth = headsArr.map(value => {
    if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
      return parseFloat(value.toString().length * 2.1)
    } else {
      return parseFloat(value.toString().length * 1.1)
    }
  })
  // console.log("所有表头的宽度：", headsWidth);
  // 2.所有表体值的宽度
  json.unshift(fields)
  const rowsWidth = json.map(item => {
    const maxValue = Object.values(item).map((value, index) => {
      let valueWidth
      if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
        valueWidth = parseFloat(value.toString().length * 2.1)
      } else {
        valueWidth = parseFloat(value.toString().length * 1.1)
      }
      return Math.max(valueWidth, headsWidth[index])
    })
    return maxValue
  })
  // console.log("每行数据对比出的最大宽度：", rowsWidth)
  // 3.对比每列最大值
  let aotuWidth = []
  rowsWidth.map((row, index) => {
    let maxWidth = []
    row.map((value, i) => {
      if (index === 0) {
        maxWidth.push({
          wch: value
        })
      } else {
        maxWidth.push({
          wch: Math.max(value, aotuWidth[i].wch)
        })
      }
    })
    aotuWidth = maxWidth
  })
  // ws["!rows"] // 杭高
  ws['!cols'] = aotuWidth // 列宽

  wb.SheetNames.push(sheetName)
  wb.Sheets[sheetName] = ws
  let wopts = {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary',
    cellStyles: true,
    defaultCellStyle: defaultCellStyle,
    showGridLines: false
  }
  let wbout = XLSX.write(wb, wopts)
  let blob = new Blob([s2ab(wbout)], {
    type: 'application/octet-stream'
  })
  fs.saveAs(blob, filename + '.xlsx')
}

/**
 * 数据下载
 * @param {*} header 表头
 * @param {*} tableData 数据
 */
dataDownload() {
  let headerArr = ['Name', 'Age', 'Address']
  let header = {
    title0: 'Name',
    title1: 'Age',
    title2: 'Address'
  }
  let tableData = [
    {
      Name: '喜洋洋',
      Age: '108',
      Address: '青青草原-羊村-2幢'
    },
    {
      Name: '美羊羊',
      Age: '108',
      Address: '青青草原-羊村-1幢'
    }
  ]
  let tableDataList = []
  tableData.forEach((item, index) => {
    let obj = {}
    for (let i = 0; i < headerArr.length; i++) {
      if (i === 0) {
        obj['title0'] = item.Name
      } else if (i === 1) {
        obj['title1'] = item.Age
      } else if (i === 2) {
        obj['title2'] = item.Address
      }
    }
    tableDataList.push(obj)
  })
  xlsxGenerate(tableDataList, header, '羊村登记表')
}

表格显示，每列列宽自适应

xlsx文件链接数据导出

import fs from 'file-saver'
import XLSX from 'xlsx'
export 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 tableData = new Uint8Array(xhr.response)
      var workbook = XLSX.read(tableData, { type: 'array' }) // 该方法接收一个二进制的数据,和数据的类型,返回解析完成后的对象
      if (callback) callback(workbook)
    }
  }
  xhr.send()
}

export function readWorkbook(workbook) {
  let sheetNames = workbook.SheetNames // 工作表名称集合
  let worksheet = workbook.Sheets[sheetNames[0]] // 这里我们只读取第一张sheet
  let csv = XLSX.utils.sheet_to_csv(worksheet)
  return csv2table(csv)
}

// 将csv转换成简单的表格，会忽略单元格合并，在第一行和第一列追加类似excel的索引
export function csv2table(csv) {
  let arrs = []
  let rows = csv.split('\n')
  let first_rows = rows.shift().split(',') // 第一行没用的
  rows.pop() // 最后一行没用的
  rows.forEach(function(row, idx) {
    let columns = row.split(',')
    let obj = {}
    columns.forEach(function(col, idx) {
      obj[first_rows[idx]] = col
    })
    arrs.push(obj)
  })
  return { first_rows, arrs }
}

方法调用

let urlNew = url.replace(/http:/, 'https:') // url xlsx文件链接,转https
readWorkbookFromRemoteFile(
  urlNew,
  workbook => {
    let param = readWorkbook(workbook)
    param.arrs // 表格数据
    param.first_rows // 表头
  }
)