react js 前端的三种导出excal表格方式

一、下载插件   npm i js-export-excel

优点：使用简单，方便，可实现分sheet导出

缺点：可调节性低，文字位置，大小等不可调整

使用代码：

import ExportJsonExcel from 'js-export-excel';

        let dataTable = [{
         'Test1': 'hello',
         'Test2': 'tim'
       }]

       let colunms =['Test1', 'Test2']

        var option={};
        var col =[] 
        let dataTable = [];
        if (data) {
          for (let m in data) {
            if(data){
              let obj = {};
              let len =columns.length;
              for(var i=0;i<len;i++){
                obj[columns[i].title]=data[m][columns[i].dataIndex];
                if(m==0)col.push(columns[i].title); 
              }
              dataTable.push(obj);
            }
          }
        }
       
        option.fileName = '文件名'
        option.datas=[
          {
            sheetData:dataTable,  //导出数据
            sheetName:'sheet',   //sheet名
            sheetFilter:col,      //表头对应数据
            sheetHeader:col,     //表头
          }
        ];
        var toExcel = new ExportJsonExcel(option); 
        toExcel.saveExcel();

二、下载插件   npm i exceljs

优点：可调节性高，行高列宽，对齐方式均可调整,也可以分sheet下载

缺点：代码比较繁琐，遇到分组表头下载不方便；

代码：

import Excel from "exceljs";


const data = [{
         'Test1': 'hello',
         'Test2': 'tim'
       }];//表格数据
     
const columns=[{
    title:'日期',
    dataIndex:'Test1',
    width: 120,
    },{
    title:'游戏名',
    width: 120,
    dataIndex:'Test2'
    }  ]      
const workbook = new Excel.Workbook();
workbook.creator = "admin";
workbook.lastModifiedBy = "admin";
workbook.created = new Date();
workbook.modified = new Date();
// 添加工作表
let fileName ='文件名';
        
let sheet = workbook.addWorksheet("sheet");
let columnsList = [];
columns.map((item) => {
     let title;
     if(typeof item.title === 'string'){
          title=item.title;
     }else{
         title=item.title.props.children[0];
     }
     columnsList.push({
         header: title,
         key: item.key || item.dataIndex,
         width: 15,
      });
      return true;
});
// 添加表头
 sheet.columns = columnsList;
        
  if (Array.isArray(data)) {
          // 添加表格数据
      sheet.addRows(data);
       // 设置每一列样式 居中
       const row = sheet.getRow(1);
       row.eachCell((cell, rowNumber) => {
       sheet.getColumn(rowNumber).alignment = {
           vertical: "middle",
           horizontal: "right",
          };
        });
    
          // 将表格数据转为二进制
        workbook.xlsx.writeBuffer().then((buffer) => {
           writeFile(`${fileName}.xlsx`, buffer);
         });
        } else {
          alert("下载失败");
        }
     
       // 将二进制转为Excel并下载
        const writeFile = (fileName, content) => {
          let a = document.createElement("a");
          let blob = new Blob([content], { type: "text/plain" });
          a.download = fileName;
          a.href = URL.createObjectURL(blob);
          a.click();
 }; 

三，下载插件   npm i better-xlsx     npm i file-saver

优点：可调节性高，行高列宽，对齐方式均可调整,分组表头，分sheet下载均可实现

缺点：代码冗余，下载插件多

代码：

import { File } from  'better-xlsx';
import { saveAs } from  'file-saver';

const data = [{
         'Test1': 'hello',
         'Test2': 'tim'
      }];//表格数据
     
const columns=[{
   title: '基础数据',
   children: [
    {title:'日期',
    dataIndex:'Test1',
    width: 120,
    },{
    title:'游戏名',
    width: 120,
    dataIndex:'Test2'
    }  ]  
}]


    let fileName ='文件名'
    //  新建工作谱
    const file = new File();
    // 新建表
    let sheet = file.addSheet('sheet名');
    excelGet(columns, data ,sheet);
 

    function excelGet(column,dataSource,sheet){
        let data =dataSource?dataSource:[]
        // 获取表头行数
        let depth = getDepth(column);
        // 获取表头的列数
        let columnNum = getColumns(column);
        // 新建表头行数
        let rowArr = [];
        for (let k = 0; k < depth; k++) {
            rowArr.push(sheet.addRow());
        }
        // 根据列数填充单元格
        rowArr.map(ele => {
            for (let j = 0; j < columnNum; j++) {
            let cell = ele.addCell();
            cell.value = j;
            }
        });
        // 初始化表头
        init(column, 0, 0);
        // 按顺序展平column
        let columnLineArr = [];
        columnLine(column);
        // 根据column,将dataSource里面的数据排序，并且转化为二维数组
        let dataSourceArr = [];
       
        data.map(ele => {
            let dataTemp = [];
            columnLineArr.map(item => {
              
            dataTemp.push({
                [item.dataIndex]: ele[item.dataIndex],
                value: ele[item.dataIndex],
            });
            });
            dataSourceArr.push(dataTemp);
        });
        // debugger;
        // 绘画表格数据
        dataSourceArr.forEach((item, index) => {
            //根据数据,创建对应个数的行
            let row = sheet.addRow();
            row.setHeightCM(0.8);
            //创建对应个数的单元格
            item.map(ele => {
            let cell = row.addCell();
            if (ele.hasOwnProperty('num')) {
                cell.value = index + 1;
            } else {
                cell.value = ele.value;
            }
            cell.style.align.v = 'center';
            cell.style.align.h = 'right';
            });
        });
        //设置每列的宽度
        for (var i = 0; i < 50; i++) {
            sheet.col(i).width = 15;
        }
      
        // 按顺序展平column
        function columnLine(column) {
            column.map(ele => {

            if (ele.children === undefined || ele.children.length === 0) {
                columnLineArr.push(ele);
            } else {
                columnLine(ele.children);
            }
            });
        }
        // 初始化表头
        function init(column, rowIndex, columnIndex) {
            // console.log(column)
            column.map((item, index) => {
            let hCell = sheet.cell(rowIndex, columnIndex);
            // 如果没有子元素, 撑满列
            if (item.title === '操作') {
                hCell.value = '';
                return;
            } else if (item.children === undefined || item.children.length === 0) {
                // 第一行加一个单元格
              
                let title="";
                if(typeof item.title === 'string'){
                  title=item.title;
                }else{
                  title=item.title.props.children[0];
                }
                hCell.value = title;
                hCell.vMerge = depth - rowIndex - 1;
                hCell.style.align.h = 'center';
                hCell.style.align.v = 'center';
                columnIndex++;
                // rowIndex++
            } else {
                let childrenNum = 0;
                function getColumns(arr) {
                arr.map(ele => {
                    if (ele.children) {
                    getColumns(ele.children);
                    } else {
                    childrenNum++;
                    }
                });
                }
                let title="";
                if(typeof item.title === 'string'){
                  title=item.title;
                }else{
                  // console.log()
                  title=item.title.props.children[0];
                }
                getColumns(item.children);
                hCell.hMerge = childrenNum - 1;
                hCell.value = title;
                hCell.style.align.h = 'center';
                hCell.style.align.v = 'center';
                let rowCopy = rowIndex;
                rowCopy++;
                init(item.children, rowCopy, columnIndex);
                // 下次单元格起点
                columnIndex = columnIndex + childrenNum;
            }
            });
        }
        // 获取表头rows
        function getDepth(arr) {
            const eleDepths = [];
            arr.forEach(ele => {
            let depth = 0;
            if (Array.isArray(ele.children)) {
                depth = getDepth(ele.children);
            }
            eleDepths.push(depth);
            });
            return 1 + max(eleDepths);
        }

        function max(arr) {
            return arr.reduce((accu, curr) => {
            if (curr > accu) return curr;
            return accu;
            });
        }
        // 计算表头列数
        function getColumns(arr) {
            let columnNum = 0;
            arr.map(ele => {
            if (ele.children) {
                getColumns(ele.children);
            } else {
                columnNum++;
            }
            });
            return columnNum;
        }
    }

    file.saveAs('blob').then(function(content) {
        saveAs(content, fileName + '.xlsx');
    });