layout:     post
title:      Excel文件操作
subtitle:   如何对excel文件进行上传/下载，读取，解析等操作
date:       2022-4-18
author:     page
header-img: img/post-bg-excel.webp
catalog: true
tags:
    - 业务实现

Excel

导出文件

Blob

服务端响应为文件的Blob类型数据

// 导出Blob为Excel文件
export const blob2Excel = (data, res, name) => {
  if (!data) return;
  // fileName默认为headers中文件信息
  const fileName = res.headers?.['content-disposition'].split(';')?.[1].split('filename=')[1];
  // iconv-lite解决中文名称乱码
  // const iconv = require('iconv-lite');
  // iconv.skipDecodeWarning = true; // 忽略警告
  // fileName = name || iconv.decode(fileName, 'gbk');
  // 或 window.decodeURIComponent解码
  fileName = name || window.decodeURIComponent(fileName) + '.xlsx';
  // 转为Blob对象
  const blob = new Blob([data], {
    type: '' // application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8
  });
  // 下载至本地
  // a.click模拟下载
  // saveAs: import { saveAs } from 'file-saver'
  saveAs(blob, fileName);
};

注：Blob响应数据的请求头应声明Content-Type: blob以被正确被解析

JSON转Excel

纯前端的数组/json对象转为excel

const data = [{name: 'xxx', ...} ...];
const sheetHeader = new Map([
  ['name', '名称'],
  ['id', '身份Id'],
  ['url', '链接']
]);
const fileName = 'info';
const sheetName = '工作表1';
json2Excel(data, sheetHeader, fileName, sheetName);
​
import XLSX from "xlsx";
// Array 表头 文件名 工作表名
function json2Excel(data, sheetHeader, fileName, sheetName){
    const sheetHeaderKeys = Array.from(sheetHeader.keys());
    const sheetHeaderTitles = Array.from(sheetHeader.values());
    const sheetBody = data.map((row) => sheetHeaderKeys.map((key) => row[key]));
    const sheetData = [sheetHeaderTitles].concat(sheetBody);
    // 1. 创建sheet(工作表对象)
    sheetName = sheetName || 'sheet1';
    const sheet = XLSX.utils.aoa_to_sheet(sheetData);
    // 2. 创建workbook(工作簿对象)
    const workbook = {
      SheetNames: [sheetName],
      Sheets: { [sheetName]: sheet }
    };
    // 3. 生成xlsx文件数据
    // bookSST: 是否生成Shared String Table; 如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
     const wopts = {
      bookType: 'xlsx', // 要生成的文件类型
      bookSST: false,
      type: 'binary'
    };
    const wbout = XLSX.write(workbook, wopts);
    // 4. 导出为excel文件
    saveAs(new Blob([s2ab(wbout)], { type: '' }), `${fileName}.xlsx`);
}
​
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;
}

Excel静态资源

Excel文件本身作为静态资源存在

downloadTemplate() {
  window.open('/static/template.xlsx', '_self');
}

读取文件

Excel转JSON

const arr = XLSX.readExcel(file.raw).then((sheetData) => 
  sheetData.map((row) => ({
      name: row['姓名'],
      id: row['id'],
      url: row['链接']
  }));
).catch(e => console.log(e));

import XLSX from 'xlsx';
​
export const readExcel = function (blob_file) {
  const reader = new FileReader();
  return new Promise((resolve, reject) => {
    reader.onload = () => {
      // 解析文件为二进制
      const buffer = reader.result;
      const bytes = new Uint8Array(buffer);
      const length = bytes.byteLength;
      let binary = '';
      for (let i = 0; i < length; i++) {
        binary += String.fromCharCode(bytes[i]);
      }
      // 读取二进制数据
      const wb = XLSX.read(binary, { type: 'binary' });
      const sheetData = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
      resolve(sheetData);
    };
    reader.onerror = (err) => {reject(err) };
    reader.readAsArrayBuffer(blob_file);
  });
};