转 js 实现纯前端将数据导出excel的3种方式_exporttoexcel-all.js-程序员宅基地

技术标签: JS  


由于项目需要,需要在不调用后台接口的情况下,将json数据导出到excel表格,参考了好多资料以及很多大佬写的博客终于实现,兼容chrome没问题,其他还没有测试过,这边介绍两种实现方式,并附上代码和gif动图,博主不才还望轻喷
方法一
将table标签,包括tr、td等对json数据进行拼接,将table输出到表格上实现,这种方法的弊端在于输出的是伪excel,虽说生成xls为后缀的文件,但文件形式上还是html,代码如下

<html>
<head>
  <p style="font-size: 20px;color: red;">使用table标签方式将json导出xls文件</p>
  <button onclick='tableToExcel()'>导出</button>
</head>
<body>
  <script>
            
    function tableToExcel(){
      //要导出的json数据
      const jsonData = [
        {
          name:'路人甲',
          phone:'123456',
          email:'[email protected]'
        },
        {
          name:'炮灰乙',
          phone:'123456',
          email:'[email protected]'
        },
        {
          name:'土匪丙',
          phone:'123456',
          email:'[email protected]'
        },
        {
          name:'流氓丁',
          phone:'123456',
          email:'[email protected]'
        },
      ]
      //列标题
      let str = '<tr><td>姓名</td><td>电话</td><td>邮箱</td></tr>';
      //循环遍历,每行加入tr标签,每个单元格加td标签
      for(let i = 0 ; i < jsonData.length ; i++ ){
        str+='<tr>';
        for(let item in jsonData[i]){
            //增加\t为了不让表格显示科学计数法或者其他格式
            str+=`<td>${ jsonData[i][item] + '\t'}</td>`;     
        }
        str+='</tr>';
      }
      //Worksheet名
      let worksheet = 'Sheet1'
      let uri = 'data:application/vnd.ms-excel;base64,';
 
      //下载的表格模板数据
      let template = `<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">
      <head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
        <x:Name>${worksheet}</x:Name>
        <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
        </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
        </head><body><table>${str}</table></body></html>`;
      //下载模板
      window.location.href = uri + base64(template)
    }
    //输出base64编码
    function base64 (s) { return window.btoa(unescape(encodeURIComponent(s))) }
</script>
</body>
</html>

方法二

通过将json遍历进行字符串拼接,将字符串输出到csv文件,代码如下

<html>
<head>
  <p style="font-size: 20px;color: red;">使用a标签方式将json导出csv文件</p>
  <button onclick='tableToExcel()'>导出</button>
</head>
<body>
  <script>
            
    function tableToExcel(){
      //要导出的json数据
      const jsonData = [
        {
          name:'路人甲',
          phone:'123456789',
          email:'[email protected]'
        },
        {
          name:'炮灰乙',
          phone:'123456789',
          email:'[email protected]'
        },
        {
          name:'土匪丙',
          phone:'123456789',
          email:'[email protected]'
        },
        {
          name:'流氓丁',
          phone:'123456789',
          email:'[email protected]'
        },
      ]
      //列标题,逗号隔开,每一个逗号就是隔开一个单元格
      let str = `姓名,电话,邮箱\n`;
      //增加\t为了不让表格显示科学计数法或者其他格式
      for(let i = 0 ; i < jsonData.length ; i++ ){
        for(let item in jsonData[i]){
            str+=`${jsonData[i][item] + '\t'},`;     
        }
        str+='\n';
      }
      //encodeURIComponent解决中文乱码
      let uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str);
      //通过创建a标签实现
      let link = document.createElement("a");
      link.href = uri;
      //对下载的文件命名
      link.download =  "json数据表.csv";
      document.body.appendChild(link);
      link.click();
      document.body.removeChild(link);
    }
 
</script>
</body>
</html>

参考了很多大佬的博客其中这两篇对我启发最大,感谢!


https://blog.csdn.net/oscar999/article/details/16342699
https://blog.csdn.net/aa122273328/article/details/50388673

转自https://blog.csdn.net/hhzzcc_/article/details/80419396

第三种方式

/srcvendor/Blob.js

/* eslint-disable */
/* Blob.js
 * A Blob implementation.
 * 2014-05-27
 *
 * By Eli Grey, http://eligrey.com
 * By Devin Samarin, https://github.com/eboyjr
 * License: X11/MIT
 *   See LICENSE.md
 */

/*global self, unescape */
/*jslint bitwise: true, regexp: true, confusion: true, es5: true, vars: true, white: true,
 plusplus: true */

/*! @source http://purl.eligrey.com/github/Blob.js/blob/master/Blob.js */

(function (view) {
    "use strict";

    view.URL = view.URL || view.webkitURL;

    if (view.Blob && view.URL) {
        try {
            new Blob;
            return;
        } catch (e) {}
    }

    // Internally we use a BlobBuilder implementation to base Blob off of
    // in order to support older browsers that only have BlobBuilder
    var BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || (function(view) {
            var
                get_class = function(object) {
                    return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1];
                }
                , FakeBlobBuilder = function BlobBuilder() {
                    this.data = [];
                }
                , FakeBlob = function Blob(data, type, encoding) {
                    this.data = data;
                    this.size = data.length;
                    this.type = type;
                    this.encoding = encoding;
                }
                , FBB_proto = FakeBlobBuilder.prototype
                , FB_proto = FakeBlob.prototype
                , FileReaderSync = view.FileReaderSync
                , FileException = function(type) {
                    this.code = this[this.name = type];
                }
                , file_ex_codes = (
                    "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "
                    + "NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR"
                ).split(" ")
                , file_ex_code = file_ex_codes.length
                , real_URL = view.URL || view.webkitURL || view
                , real_create_object_URL = real_URL.createObjectURL
                , real_revoke_object_URL = real_URL.revokeObjectURL
                , URL = real_URL
                , btoa = view.btoa
                , atob = view.atob

                , ArrayBuffer = view.ArrayBuffer
                , Uint8Array = view.Uint8Array
                ;
            FakeBlob.fake = FB_proto.fake = true;
            while (file_ex_code--) {
                FileException.prototype[file_ex_codes[file_ex_code]] = file_ex_code + 1;
            }
            if (!real_URL.createObjectURL) {
                URL = view.URL = {};
            }
            URL.createObjectURL = function(blob) {
                var
                    type = blob.type
                    , data_URI_header
                    ;
                if (type === null) {
                    type = "application/octet-stream";
                }
                if (blob instanceof FakeBlob) {
                    data_URI_header = "data:" + type;
                    if (blob.encoding === "base64") {
                        return data_URI_header + ";base64," + blob.data;
                    } else if (blob.encoding === "URI") {
                        return data_URI_header + "," + decodeURIComponent(blob.data);
                    } if (btoa) {
                        return data_URI_header + ";base64," + btoa(blob.data);
                    } else {
                        return data_URI_header + "," + encodeURIComponent(blob.data);
                    }
                } else if (real_create_object_URL) {
                    return real_create_object_URL.call(real_URL, blob);
                }
            };
            URL.revokeObjectURL = function(object_URL) {
                if (object_URL.substring(0, 5) !== "data:" && real_revoke_object_URL) {
                    real_revoke_object_URL.call(real_URL, object_URL);
                }
            };
            FBB_proto.append = function(data/*, endings*/) {
                var bb = this.data;
                // decode data to a binary string
                if (Uint8Array && (data instanceof ArrayBuffer || data instanceof Uint8Array)) {
                    var
                        str = ""
                        , buf = new Uint8Array(data)
                        , i = 0
                        , buf_len = buf.length
                        ;
                    for (; i < buf_len; i++) {
                        str += String.fromCharCode(buf[i]);
                    }
                    bb.push(str);
                } else if (get_class(data) === "Blob" || get_class(data) === "File") {
                    if (FileReaderSync) {
                        var fr = new FileReaderSync;
                        bb.push(fr.readAsBinaryString(data));
                    } else {
                        // async FileReader won't work as BlobBuilder is sync
                        throw new FileException("NOT_READABLE_ERR");
                    }
                } else if (data instanceof FakeBlob) {
                    if (data.encoding === "base64" && atob) {
                        bb.push(atob(data.data));
                    } else if (data.encoding === "URI") {
                        bb.push(decodeURIComponent(data.data));
                    } else if (data.encoding === "raw") {
                        bb.push(data.data);
                    }
                } else {
                    if (typeof data !== "string") {
                        data += ""; // convert unsupported types to strings
                    }
                    // decode UTF-16 to binary string
                    bb.push(unescape(encodeURIComponent(data)));
                }
            };
            FBB_proto.getBlob = function(type) {
                if (!arguments.length) {
                    type = null;
                }
                return new FakeBlob(this.data.join(""), type, "raw");
            };
            FBB_proto.toString = function() {
                return "[object BlobBuilder]";
            };
            FB_proto.slice = function(start, end, type) {
                var args = arguments.length;
                if (args < 3) {
                    type = null;
                }
                return new FakeBlob(
                    this.data.slice(start, args > 1 ? end : this.data.length)
                    , type
                    , this.encoding
                );
            };
            FB_proto.toString = function() {
                return "[object Blob]";
            };
            FB_proto.close = function() {
                this.size = this.data.length = 0;
            };
            return FakeBlobBuilder;
        }(view));

    view.Blob = function Blob(blobParts, options) {
        var type = options ? (options.type || "") : "";
        var builder = new BlobBuilder();
        if (blobParts) {
            for (var i = 0, len = blobParts.length; i < len; i++) {
                builder.append(blobParts[i]);
            }
        }
        return builder.getBlob(type);
    };
}(typeof self !== "undefined" && self || typeof window !== "undefined" && window || this.content || this));

src/vendor/Export2Excel.js

/* eslint-disable */
require('script-loader!file-saver');
require('./Blob');
require('script-loader!xlsx/dist/xlsx.core.min');
function generateArray(table) {
    var out = [];
    var rows = table.querySelectorAll('tr');
    var ranges = [];
    for (var R = 0; R < rows.length; ++R) {
        var outRow = [];
        var row = rows[R];
        var columns = row.querySelectorAll('td');
        for (var C = 0; C < columns.length; ++C) {
            var cell = columns[C];
            var colspan = cell.getAttribute('colspan');
            var rowspan = cell.getAttribute('rowspan');
            var cellValue = cell.innerText;
            if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;

            //Skip ranges
            ranges.forEach(function (range) {
                if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
                    for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
                }
            });

            //Handle Row Span
            if (rowspan || colspan) {
                rowspan = rowspan || 1;
                colspan = colspan || 1;
                ranges.push({s: {r: R, c: outRow.length}, e: {r: R + rowspan - 1, c: outRow.length + colspan - 1}});
            }
            ;

            //Handle Value
            outRow.push(cellValue !== "" ? cellValue : null);

            //Handle Colspan
            if (colspan) for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
        }
        out.push(outRow);
    }
    return [out, ranges];
};

function datenum(v, date1904) {
    if (date1904) v += 1462;
    var epoch = Date.parse(v);
    return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data, opts) {
    var ws = {};
    var range = {s: {c: 10000000, r: 10000000}, e: {c: 0, r: 0}};
    for (var R = 0; R != data.length; ++R) {
        for (var C = 0; C != data[R].length; ++C) {
            if (range.s.r > R) range.s.r = R;
            if (range.s.c > C) range.s.c = C;
            if (range.e.r < R) range.e.r = R;
            if (range.e.c < C) range.e.c = C;
            var cell = {v: data[R][C]};
            if (cell.v == null) continue;
            var cell_ref = XLSX.utils.encode_cell({c: C, r: R});

            if (typeof cell.v === 'number') cell.t = 'n';
            else if (typeof cell.v === 'boolean') cell.t = 'b';
            else if (cell.v instanceof Date) {
                cell.t = 'n';
                cell.z = XLSX.SSF._table[14];
                cell.v = datenum(cell.v);
            }
            else cell.t = 's';

            ws[cell_ref] = cell;
        }
    }
    if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    return ws;
}

function Workbook() {
    if (!(this instanceof Workbook)) return new Workbook();
    this.SheetNames = [];
    this.Sheets = {};
}

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;
}

export function export_table_to_excel(id) {
    var theTable = document.getElementById(id);
    console.log('a')
    var oo = generateArray(theTable);
    var ranges = oo[1];

    /* original data */
    var data = oo[0];
    var ws_name = "SheetJS";
    console.log(data);

    var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);

    /* add ranges to worksheet */
    // ws['!cols'] = ['apple', 'banan'];
    ws['!merges'] = ranges;

    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;

    var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'});

    saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), "test.xlsx")
}

function formatJson(jsonData) {
    console.log(jsonData)
}
export function export_json_to_excel(th, jsonData, defaultTitle) {

    /* original data */

    var data = jsonData;
    data.unshift(th);
    var ws_name = "SheetJS";

    var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);


    /* add worksheet to workbook */
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;

    var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'});
    var title = defaultTitle || '列表'
    saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), title + ".xlsx")
}

公共JS导出方法 

export const exportXls=(list)=>{
    const formatJson=(filterVal, jsonData)=>{
          return jsonData.map(v => filterVal.map(j => v[j]))
     }
    const {export_json_to_excel} = require('@/vendor/Export2Excel.js')
    let tHeader = [ '编码', '名称', ]; //表头
    let filterVal = ['ID', 'Name',];//table表格中对应的属性名  
    let data =formatJson(list.filterVal, list.data); //表格绑定数据positionData转json  
    export_json_to_excel(list.tHeader, data, list.name);
}

 

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_20087231/article/details/92776464

智能推荐

维护库存地点的装运点确认_sd 如何确定storage location-程序员宅基地

文章浏览阅读1.4k次。一.说明外向交货中,发货需要确认装运点(Shipping Point),后台配置有两个,其中一个是工厂级(参见《维护工厂的装运点确认》),本文是库存地点级,具体配置是给一个组合(装运条件/装载组/工厂/库存地点 Shipping Conditions / Loading Group / Plant / Storage Location)指定一个或多个计划装运点。主数据层面,装运_sd 如何确定storage location

html中在一个页面内实现多张图片的轮流播放_html多张照片轮播图-程序员宅基地

文章浏览阅读2.8w次,点赞17次,收藏96次。主要功能是实现6张图片的轮流播放,牵扯到的核心知识点有函数的使用,document.getElementById(),document.getElementByClass();_html多张照片轮播图

Linux LittlevGL 使用_littlevgl linux-程序员宅基地

文章浏览阅读2.3k次。Linux LittlevGL 使用 LittlevGL 是一个开源免费的GUI,支持触摸屏操作,移植简单方便,开发者一直在不断完善更新。LittlevGL 自带了丰富的控件:窗口、按键、标签、list、图表等,还可以自定义控件;支持很多特效:透明、阴影、自动显示隐藏滚动条、界面切换动画、图标打开关闭动画、平滑的拖拽控件、分层显示、反锯齿、仅耗少量内存的字体等等。 ..._littlevgl linux

QT某一个cpp提示 无法用“UTF-8”编码解码_qt无法用utf-8编码解码-程序员宅基地

文章浏览阅读721次。解决办法:下载NotePad++,将这个CPP文件用这个软件打开点击Encoding选择covert to UTF-8-BOM_qt无法用utf-8编码解码

计网复习1-程序员宅基地

文章浏览阅读167次。1.2.1 Access networkDSL 连接 DSLAM,类似拨号上网,占用电话线路。客户端:splitter seoarates the data and telephone signals arriving to the home and forwards the data signal to the DSL modem.telco side: in CO, D..._cuit计网

SQL改名的语句_sql改名语句-程序员宅基地

文章浏览阅读618次。First database rename alter database 数据库名 modify name=新数据库名 exec sp_rename 数据库名,新数据库名,databaseSecond table rename 不能修改非当前数据库上的表名 ALTER TABLE table_name RENAME TO new_table_name _sql改名语句

随便推点

bzoj 2809 [Apio2012]dispatching-程序员宅基地

文章浏览阅读214次。可并堆(斜堆)

NOIP2015普及组 金币_金币(noip普及组2015)-程序员宅基地

文章浏览阅读4k次。题目描述国王将金币作为工资,发放给忠诚的骑士。第一天,骑士收到一枚金币;之后两天(第二天和第三天),每天收到两枚金币;之后三天(第四、五、六天),每天收到三枚金币;之后四天(第七、八、九、十天),每天收到四枚金币……;这种工资发放模式会一直这样延续下去:当连续N天每天收到N枚金币后,骑士会在之后的连续N+1天里,每天收到N+1枚金币。请计算在前K天里,骑士一共获得了多少金币。输入输出格式输入格式:_金币(noip普及组2015)

Qt 用指定字符、指定间距分割字符串-程序员宅基地

文章浏览阅读451次。//参数说明: //org要修改的字符串 //ch要插入的字符,默认为空格 //nSpacing指定间距,默认为1,即一个字符中间插入一个ch voidformatStringByChar(QString&org,intnSpaci..._qt怎么将输入的数字碰到空格隔开

thinkphp获取星期_ThinkPHP中获取指定日期后工作日的具体日期方法-程序员宅基地

文章浏览阅读298次。思路:1、获取到查询年份内所有工作日数据数组2、获取到查询开始日期在工作日的索引3、计算需查询日期索引4、获得查询日期/*创建日期类型记录表格*/CREATE TABLE `tb_workday` (`did` int(11) NOT NULL AUTO_INCREMENT,`exact_date` varchar(32) NOT NULL COMMENT '具体日期:格式date("Ymd");..._thinkphp查询周几的数据

C语言:从键盘输入两个字符串,输出第一个字符串在第二个字符串中的位置_从键盘输入两个字符串,输出第一个字符串在第二个字符串中的位置(第一个字符串的首-程序员宅基地

文章浏览阅读2.3w次。从键盘输入两个字符串,输出第一个字符串的首字母在第二个字符串中的位置。如“abc”在“bbbacccabcddaw3”的位置为7。悬赏分:50 | 解决时间:2011-12-14 11:50:11//========================================最佳答案:试试如下程序:#include #includevoid main(){ char M[100], C[_从键盘输入两个字符串,输出第一个字符串在第二个字符串中的位置(第一个字符串的首

windows 安装配置 pycharm 创建项目并运行代码_window安装pycharm运行代码-程序员宅基地

文章浏览阅读10w+次,点赞2次,收藏37次。windows 安装配置 pycharm 创建项目并运行代码_window安装pycharm运行代码