js-xlsx excel导出学习

Excel导出工具类

可直接使用，对外开发两个API，分别为exportExcel（单个表导出）、exportExcels（同时导出多个表）

import XLSX from 'xlsx';

//转换成字母 （当excel的列大于26列时）
const fromCharCode = (num) => {

  const charCount = 26;
  let charCode = ''
  if (num < charCount) {
    charCode = String.fromCharCode(65 + num);
  } else if (num < charCount * charCount + charCount) {
    // 26*26 = 676

    const divisor = num / charCount
    const remainder = num % charCount

    const diviCharCode = String.fromCharCode(65 + divisor - 1);
    const remaCharCode = String.fromCharCode(65 + remainder);

    charCode = diviCharCode + remaCharCode;
  }

  return charCode;
}

const configHeaders = (headers) => {
  const headers_s = headers
    .map((item, i) => Object.assign({}, { key: item.key, title: item.title, position: fromCharCode(i) + 1 }))
    .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { key: next.key, v: next.title } }), {});
  return headers_s;
}

const configData = (headers, data) => {
  if (headers && data && headers.length && data.length) {
    // console.log(da);
    const data_s = data
      .map((item, i) => headers.map((key, j) =>
        Object.assign({}, { content: item[key.key], position: fromCharCode(j) + (i + 2) })
      ))
      // 对刚才的结果进行降维处理（二维数组变成一维数组）
      .reduce((prev, next) => prev.concat(next))
      // 转换成 worksheet 需要的结构
      .reduce((prev, next) => {
        const textType = typeof (next.content) //数值类型
        return Object.assign({}, prev, { [next.position]: { v: next.content, t: textType.charAt(0) } })
      }, {});

    return data_s;
  }

  return {};
}

const configCols = (length) => {
  const cols = [];
  if (length) {
    for (let index = 0; index < length; index++) {
      const element = { wpx: 90 };
      cols.push(element);
    }
  }
  return cols;
}

function handleExcelsItem(headers, data) {
  if (headers && data && headers.length) {

    const headers_s = configHeaders(headers);
    const data_s = configData(headers, data);

    // 合并 headers 和 data
    const output = Object.assign({}, headers_s, data_s);
    // 获取所有单元格的位置
    const outputPos = Object.keys(output);
    // 计算出范围 ,["A1",..., "H2"]
    const ref = `${outputPos[0]}:${outputPos[outputPos.length - 1]}`;
    const cols = configCols(headers.length);
    console.log('data_ref ====== ', ref);

    const sheet = Object.assign(
      {},
      output,
      {
        '!ref': ref,
        '!cols': cols,
      },
    );
    return sheet;
  }

  return null;
}

/**
 * 导出单个表
 * @param {*} headers 
 * @param {*} data 
 * @param {*} fileName 
 */
function exportExcel(headers, data, fileName = '数据表') {
  const sheet = handleExcelsItem(headers, data);

  // 构建 workbook 对象
  const wb = {
    SheetNames: ['mySheet'],
    Sheets: {
      mySheet: sheet,
    },
  };

  // 导出 Excel
  XLSX.writeFile(wb, `${fileName}.xlsx`);
}

/**
 * 
 * @param {*} dataInfo =[{headers, data,title}]
 */
function handleExcels(dataInfo) {

  if (dataInfo && Array.isArray(dataInfo)) {
    const SheetNames = [];
    const Sheets = {};
    dataInfo.forEach(item => {
      if (item.headers && item.data && item.title) {

        const sheet = handleExcelsItem(item.headers, item.data);
        if (sheet) {
          SheetNames.push(item.title);
          Sheets[item.title] = sheet;
        }
      }
    });

    return { SheetNames, Sheets }
  }

  return null;
}

/**
 * 同时导出多个表
 * @param {*} dataInfo =[{headers, data, title}]
 * @param {*} fileName 
 */
function exportExcels(dataInfo, fileName = '数据表') {

  const wb = handleExcels(dataInfo);
  // 导出 Excel
  if (wb) {
    console.log('wb ==== ', wb);
    XLSX.writeFile(wb, `${fileName}.xlsx`);
  }
}

export {
  exportExcel,
  exportExcels
};

使用

/**
 *  导出Excel[{headers, data, title}]
 * @param {*} assetData antd中table的sourceData
 * @param {*} valueData antd中table的sourceData
 * @param {*} assetCol antd中table的columns
 * @param {*} valueCol antd中table的columns
 * @param {*} tabInfo 页面中的table信息
 */
const assetExportExcels = (assetData, valueData, assetCol, valueCol, tabInfo) => {

  const dataInfo = [
    { headers: assetCol, data: assetData, title: tabInfo.A.title },
    { headers: valueCol, data: valueData, title: tabInfo.B.title },
  ];

  exportExcels(dataInfo, '资产负债数据表');
}

基础了解

1、安装

npm install xlsx --save

2、引用

import XLSX from 'xlsx';

3、使用库之前先理解一下xlsx的一些概念

workbook [excel文档]

const workbook = {
  SheetNames: [],           //工作表名数组
  Sheets: {}                //工作表对象 键名对应SheetNames的key
}

sheet [工作表]

const workbook = {
  SheetNames: [          //工作表名数组
    'Sheet1', 'Sheet2', 'Sheet2',
  ],
  Sheets: {               //工作表对象
    Sheet1: {  //工作表1
      '!ref': 'A1:C2',         //工作表的范围 如 必须 否则不显示
      '!margins': [             //工作表单元格合并配置项 可选
        {
          s: { //s start 开始
            c: 1,//cols 开始列
            r: 0 //rows 开始行
          },
          e: {//e end  结束
            c: 4,//cols 结束列
            r: 0 //rows 结束行
          }
        }
      ],
      '!cols': [                //工作表列宽配置项 可选
        {
          /* visibility */
          hidden? : boolean, // if true, the column is hidden
          /* column width is specified in one of the following ways: */
          wpx? : number, // width in screen pixels
          width? : number,  // width in Excel's "Max Digit Width", width*256 is integral
          wch? : number,  // width in characters
          /* other fields for preserving features from files */
          MDW? : number,  // Excel's "Max Digit Width" unit, always integral
        }
      ],
      '!rows': [                //工作表列高配置项 可选
        {
          /* visibility */
          hidden? : boolean, // if true, the row is hidden
          /* row height is specified in one of the following ways: */
          hpx? : number,  // height in screen pixels
          hpt? : number,  // height in points
          level? : number,  // 0-indexed outline / group level
        }
      ],
    },
    Sheet2: {}, //工作表2
    Sheet3: {}  //工作表3
  }
}

cell [单元格]

从sheet 对象下面延伸出来的是cell 对象，对应的是工作表下的单元格，cell 对象键名是A1,B1这样与excel一致的键名

const workbook = {
  FileName: 'export.xlsx',
  SheetNames: ['Sheet1'],
  Sheets: {
    Sheet1:{
      '!ref': 'A1:B2',
      A1:{},
      A2:{},
      B1:{}, 
      B2:{}
    }
  }
}

cell单元格对象的配置项

XLSX.write(workbook, options)生成excel数据

write options 配置项

Output Type

以上是本人做项目时的一些记录与积累，如有问题或其他实现方式请留言交流。
谢谢！

参考