『解疑』前端通过json数据导出excel表格文件（支持多级表头、复杂表头）（json转excel）

问题

如何友好的通过规范的json数据导出成excel文件。

解题

1. 制定规范的json数据结构：
   ( 只要将现有的数据处理成以下格式即可导出 )

{
	headerList: [ // 多级表头(2选1即可)
		[{name:'a',colspan:1,rowspan:1}],
		[{name:'b',colspan:1,rowspan:1}]
	],
	headers: [ // 单表头(2选1即可)
		{name:'c',id:'c'},
		{name:'d',id:'d'}
	],
	rows: [
		{c:1,d:1},
		{c:2,d:{name:2,colspan:1,rowspan:1}}
	]
}
// 或
{
	headerList: [ // 多级表头(2选1即可)
		[{name:'a',colspan:1,rowspan:1}],
		[{name:'b',colspan:1,rowspan:1}]
	],
	headers: [ // 单表头(2选1即可)
		'c',
		'd'
	],
	rows: [
		[1,1],
		[2,{name:2,colspan:1,rowspan:1}]
	]
}

2. json转excel导出类介绍

参数	介绍	类型
jsonData	传入的json数据	Object
options	配置项	Object
options.fileName	导出的文件名称( 默认sheet )	String
options.parseDataFun( [传入的json数据] )	json数据前处理方法，返回{headerList,headers,rows}数据	Function
options.callback()	导出成功后回调	Function

3. 模拟数据

var data = {
  "status": 0,
  "error": "",
  "data": {
    "headers": [
      [
        {
          "name": "分支行名称",
          "rowspan": 2
        },
        {
          "name": "全量客户",
          "colspan": 3
        }
      ],
      [
        {
          "name": "较昨日"
        },
        {
          "name": "较去年"
        },
        {
          "name": "较上月"
        }
      ]
    ],
    "rows": [
      [
        {
          "name": "营业部",
          "fontWeight": "bold"
        },
        {
          "name": "0.1%"
        },
        {
          "name": "10.5%"
        },
        {
          "name": "10.5%"
        }
      ],
      [
        {
          "name": "两中分行",
          "fontWeight": "bold"
        },
        {
          "name": "0.1%"
        },
        {
          "name": "10.5%"
        },
        {
          "name": "10.5%"
        }
      ],
      [
        {
          "name": "渝中支行",
          "fontWeight": "bold"
        },
        {
          "name": "0.1%"
        },
        {
          "name": "10.5%"
        },
        {
          "name": "10.5%"
        }
      ]
    ]
  }
}

4. 导出示例

try {
	new JSONToExcelConvertor(data.data, {
	    fileName: 'test',
	    parseDataFun: function (data) {
		    return {
		        headerList: data.headers,
		        rows: data.rows.map(o=>o.map(r=>r.name))
		    }
	    },
	    callback: function () {
	        console.log('导出文件成功！')
	    }
	})
} catch(e){
	console.log('导出文件失败!', e)
}

5. 导出效果：
   
6. json转excel导出类实现源码：

/**
 * @demoJx(2021/4/19) json转excel文件
 * @param {Object} jsonData 传入的json数据
 * @param {String} options.fileName 导出的文件名称
 * @param {Function} options.parseDataFun([传入的json数据]) json数据前处理方法,返回{headerList,headers,rows}数据
 * @param {Function} options.callback 回调方法
 */
var JSONToExcelConvertor = function (jsonData, options = {}) {
  this.jsonData = jsonData || {
    headerList: [],
    headers: [],
    rows: []
  }
  this.fileName = options.fileName || 'sheet'
  this.parseDataFun = options.parseDataFun
  this.callback = options.callback
  this.init()
}
JSONToExcelConvertor.prototype = {
  constructor: JSONToExcelConvertor,
  init() {
    var exportJson = this._parseData(this.jsonData)
    var excel = this._createExcel(exportJson)
    this._exportExcel(excel, this.fileName)
  },
  _parseData(jsonData) {
    this.parseDataFun && (jsonData = this.parseDataFun(jsonData))
    var headerKeys = []
    var headers = []
    var rows = []
    // 组装表头数据
    jsonData.headers && jsonData.headers.forEach(function (item) {
      if (Object.prototype.toString.call(item) === '[object Object]') {
        headers.push(item.name)
        headerKeys.push(item.id)
      } else {
        headers.push(item)
      }
    })
    // 组装行数据
    jsonData.rows && jsonData.rows.forEach(function (item, index) {
      if (Object.prototype.toString.call(item) === '[object Object]') {
        !rows[index] && (rows[index] = []);
        headerKeys.forEach(function (key) {
          rows[index].push(item[key])
        })
      } else {
        rows.push(item)
      }
    })
    return {
      headerList: jsonData.headerList || [],
      headers,
      rows
    }
  },
  _createExcel(jsonData) {
    var excel = '<table>';
    var row = "";

    // 设置多表头
    if (jsonData.headerList.length) {
      jsonData.headerList.forEach(function (header) {
        row = "<tr>"
        header.forEach(function (item) {
          row += "<td colspan='" + item.colspan + "' rowspan='" + item.rowspan + "'>" + item.name + "</td>";
        })
        excel += row + "</tr>";
      })
    }

    // 设置表头
    if (jsonData.headers.length) {
      row = "<tr>"
      jsonData.headers.forEach(function (item) {
        row += "<td>" + item + "</td>";
      });
      //换行
      excel += row + "</tr>";
    }

    //设置数据
    if (jsonData.rows.length) {
      jsonData.rows.forEach(function (rowList) {
        row = "<tr>";
        rowList.forEach(function (item) {
          if (Object.prototype.toString.call(item) === '[object Object]') {
            row += "<td colspan='" + item.colspan + "' rowspan='" + item.rowspan + "'>" + item.name + "</td>";
          } else {
            row += '<td>' + item + '</td>';
          }
        })
        //换行
        excel += row + "</tr>";
      })
    }

    excel += "</table>";
    return excel
  },
  _exportExcel(excel, fileName) {
    var excelFile = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>";
    excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">';
    excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel';
    excelFile += '; charset=UTF-8">';
    excelFile += "<head>";
    excelFile += "<!--[if gte mso 9]>";
    excelFile += "<xml>";
    excelFile += "<x:ExcelWorkbook>";
    excelFile += "<x:ExcelWorksheets>";
    excelFile += "<x:ExcelWorksheet>";
    excelFile += "<x:Name>";
    excelFile += "sheet";
    excelFile += "</x:Name>";
    excelFile += "<x:WorksheetOptions>";
    excelFile += "<x:DisplayGridlines/>";
    excelFile += "</x:WorksheetOptions>";
    excelFile += "</x:ExcelWorksheet>";
    excelFile += "</x:ExcelWorksheets>";
    excelFile += "</x:ExcelWorkbook>";
    excelFile += "</xml>";
    excelFile += "<![endif]-->";
    excelFile += "</head>";
    excelFile += "<body>";
    excelFile += excel;
    excelFile += "</body>";
    excelFile += "</html>";

    var uri = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(excelFile);

    var link = document.createElement("a");
    link.href = uri;

    link.style = "visibility:hidden";
    link.download = fileName + ".xlsx";

    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
    this.callback && this.callback();
  }
}

7. 动手试试
   将代码直接放入 在线工具 中试试吧！
   若对您有用，支持一下！