jquery导出excel表格插件升级

基于table2excel插件，在原来简单的table导出成excel的基础上增加了一些自己需要的功能。

1.可将两个不同的table分别导出到同一个excel中的两个sheet内；
2.增加合并单元格的导出；

3.支持字体颜色(color)，背景颜色(background-color)，对齐方式(text-align)，字体大小(font-size)一同导出。

table2excel.js和对应的demo

/* *  jQuery table2excel - v1.1.1 *  jQuery plugin to export an .xls file in browser from an HTML table *  https://github.com/rainabba/jquery-table2excel * *  Made by rainabba *  Update by pagekpang *  Under MIT License *///table2excel.js;(function ( $, window, document, undefined ) {    var pluginName = "table2excel",    defaults = {        exclude: "noExl",        name: "Table2Excel",        filename: "table2excel",        fileext: ".xls",        exclude_img: true,        exclude_links: true,        exclude_inputs: true,        withoutFontColor : false,        withoutBGColor : false,        withoutFontSize : false,        withoutAlignment : false,    };    // The actual plugin constructor    function Plugin ( element, options ) {            this.element = element;            // jQuery has an extend method which merges the contents of two or            // more objects, storing the result in the first object. The first object            // is generally empty as we don't want to alter the default options for            // future instances of the plugin            //            this.settings = $.extend( {}, defaults, options );            this._defaults = defaults;            this._name = pluginName;            this.init();    }    Plugin.prototype = {        init: function () {            var e = this;            e.template = {                head: '{allStyles}',                sheet: function(name,index){                    return '{table' + index + '}';                },                foot: ""            };            e.mtag = {                table : 'Table',                content : 'Data',                row : 'Row',                col : 'Cell',            };            e.mfunc = {                buildTag : function(tagName,context,attr){                    if (tagName == null) {                        return context;                    }                    var retStr = '<' + tagName;                    if (attr) {                        if ($.isPlainObject(attr)) {                            $.each(attr,function(k,v){                                retStr += ' ' + k + '=\"';                                retStr += v + '\"';                            });                        }else{                            retStr += ' ' + attr;                        }                    }                    if (context) {                        retStr += '>' + context + '';                    }else{                        retStr += '/>';                    }                    return retStr;                },                rgb2hex : function(rgb) {                    //console.log(rgb);                    rgbArry = rgb.match(/^rgb\((\d+),\s*(\d+),\s*(\d+)\)$/);                    function hex(x) {                        return ("0" + parseInt(x).toString(16)).slice(-2);                    }                    //增加rgba的支持                    if (rgbArry == null) {                        rgbArry = rgb.match(/^rgba\((\d+),\s*(\d+),\s*(\d+),\s*(\d+)\)$/);                        if (rgbArry == null) {                            return rgbArry;                        }                        if (rgbArry[4] == 0) {                            return null;                        }                        var percent = parseFloat(rgbArry[4]);                        rgbArry[1] = parseInt(rgbArry[1]) * percent;                        rgbArry[2] = parseInt(rgbArry[2]) * percent;                        rgbArry[3] = parseInt(rgbArry[3]) * percent;                    }                    return "#" + hex(rgbArry[1]) + hex(rgbArry[2]) + hex(rgbArry[3]);                },                findSameObjInArrayCb : function(obj,index){                    function isObj(object) {                        return object && typeof (object) == 'object' && Object.prototype.toString.call(object).toLowerCase() == "[object object]";                    }                    function getLength(object) {                        var count = 0;                        for (var i in object) count++;                        return count;                    }                    if (!isObj(obj) || !isObj(this)) return false; //判断类型是否正确                    if (getLength(obj) != getLength(this)) return false; //判断长度是否一致                    var flag = true;                    for(var subObj in obj){                        if (obj[subObj] != this[subObj]) {                            flag = false;                        }                    }                    return flag;                },                findIndex : function(mArray,cb,pt){                    for (var i = 0; i < mArray.length; i++) {                        if (cb.call(pt,mArray[i],i)) {                            return i;                        }                    }                    return -1;                }            };            e.tableRows = [];            allStyles = [];            if ( $.isPlainObject(e.element) ) {                e.settings.sheetName = [];                for(o in e.element){                    var tempRows = "";                    e.settings.sheetName.push(o.replace('/',' '));                    var rowDatas = e.element[o];                    for (var i = 0; i < rowDatas.length; i++) {                        var tempOneRowStr = '';                        var colDatas = rowDatas[i];                        for (var j = 0; j < colDatas.length; j++) {                            var tdata = colDatas[j];                            var attrStr = [];                            if ($.isPlainObject(tdata)) {//做属性支持                            }else{                                                            }                            var cellData = e.mfunc.buildTag(e.mtag.content,tdata,'ss:Type="String"');                            tempOneRowStr += e.mfunc.buildTag(e.mtag.col,cellData,attrStr);                        }                        tempRows += e.mfunc.buildTag(e.mtag.row,tempOneRowStr);                    }                    e.tableRows.push(tempRows);                }            }else{                // get contents of table except for exclude                $(e.element).each( function(i,o) {                    var tempRows = "";                    var tempThExclude = [];                    var tempRowSpans = [];                    $(o).find("tr").not('.' + e.settings.exclude).each(function (i,p) {                        var colTrueIndex = 0;                        var tempOneRowStr = '';                        $(p).find("td,th").each(function (i,q) { // p did not exist, I corrected                                                        var rc = {                                rows: $(this).attr("rowspan"),                                cols: $(this).attr("colspan"),                                flag: $(q).hasClass(e.settings.exclude),                                index : null,                            };                            var cellCss = {                                color : e.mfunc.rgb2hex($(q).css("color")),                                bgcolor : e.mfunc.rgb2hex($(q).css("background-color")),                                textalign : $(q).css("text-align"),                                fontsize : $(q).css("font-size").replace('px',''),                            };                            //对齐方式转换                            if (cellCss.textalign == "center") {                                cellCss.textalign = 'Center';                            }else if (cellCss.textalign == "right") {                                cellCss.textalign = 'Right';                            }else{                                cellCss.textalign = 'Left';                            }                            var stypeID = e.mfunc.findIndex(allStyles,e.mfunc.findSameObjInArrayCb,cellCss);                            //var stypeID = allStyles.findIndex(e.mfunc.findSameObjInArrayCb,cellCss);                            if (stypeID < 0) {                                stypeID = allStyles.length;                                allStyles.push(cellCss);                            }                            //若是th中有排除的列，就以th为准                            if ($(q).get(0).tagName == 'TH') {                                tempThExclude.push(rc.flag);                            }else if (tempThExclude.length != 0) {                                rc.flag = rc.flag | tempThExclude[i];                            }                            if (tempRowSpans[colTrueIndex] != null && tempRowSpans[colTrueIndex] != 0) {                                tempRowSpans[colTrueIndex]--;                                colTrueIndex++;                                rc.index = colTrueIndex + 1;                            }                                                        if( rc.flag ) {                                tempOneRowStr += e.mfunc.buildTag(e.mtag.col,' ');                            } else {                                var attrStr = {};                                attrStr['ss:StyleID'] = 's' + stypeID;                                if( rc.rows > 0) {                                    rc.rows = (rc.rows - 1);                                    attrStr['ss:MergeDown'] = rc.rows;                                    if (!tempRowSpans[colTrueIndex]) {                                        tempRowSpans[colTrueIndex] = 0;                                    }                                    tempRowSpans[colTrueIndex] += rc.rows;                                }                                if( rc.cols > 0) {                                    rc.cols = rc.cols - 1;                                    attrStr['ss:MergeAcross'] = rc.cols;                                }                                if (rc.index) {                                    attrStr['ss:Index'] = rc.index;                                }                                var cellData = e.mfunc.buildTag(e.mtag.content,$(q).text(),'ss:Type="String"');                                tempOneRowStr += e.mfunc.buildTag(e.mtag.col,cellData,attrStr);                            }                            colTrueIndex++;                        });                        tempRows += e.mfunc.buildTag(e.mtag.row,tempOneRowStr);                                            });                    // exclude img tags                    if(e.settings.exclude_img) {                        tempRows = exclude_img(tempRows);                    }                    // exclude link tags                    if(e.settings.exclude_links) {                        tempRows = exclude_links(tempRows);                    }                    // exclude input tags                    if(e.settings.exclude_inputs) {                        tempRows = exclude_inputs(tempRows);                    }                    e.tableRows.push(tempRows);                });            }            var tallStyles = '';            for (var i = 0; i < allStyles.length; i++) {                var oneObj = allStyles[i];                var allStr = '';                fontObj = {};                if (!e.settings.withoutFontColor) {                    fontObj['ss:Color'] = oneObj.color;                }                if (!e.settings.withoutFontSize) {                    fontObj['ss:Size'] = oneObj.fontsize;                }                allStr += e.mfunc.buildTag('Font',null,fontObj);                if (oneObj.bgcolor && !e.settings.withoutBGColor) {                    allStr += e.mfunc.buildTag('Interior',null,{                        'ss:Color' : oneObj.bgcolor,                        'ss:Pattern' : 'Solid'                    });                }                if (!e.settings.withoutAlignment) {                    allStr += e.mfunc.buildTag('Alignment',null,{                        'ss:Horizontal' : oneObj.textalign,                    });                }                tallStyles += e.mfunc.buildTag('Style',allStr,{                    'ss:ID' : 's' + i                });            }            e.tableToExcel(e.tableRows, e.settings.name, e.settings.sheetName, tallStyles);        },        tableToExcel: function (table, name, sheetName , styles) {            var e = this, fullTemplate="", i, link, a;            e.format = function (s, c) {                return s.replace(/{(\w+)}/g, function (m, p) {                    return c[p];                });            };            sheetName = typeof sheetName === "undefined" ? "Sheet" : sheetName;            e.ctx = {                worksheet: name || "Worksheet",                table: table,                sheetName: sheetName,                allStyles : styles            };            fullTemplate= e.template.head;            if ( $.isArray(table) ) {                for (var i = 0;i < table.length; i++) {                      //fullTemplate += e.template.sheet.head + "{worksheet" + i + "}" + e.template.sheet.tail;                      if ($.isArray(sheetName)) {                        fullTemplate += e.template.sheet(sheetName[i],i);                      }else{                        fullTemplate += e.template.sheet(sheetName + i,i);                      }                                      }            }            fullTemplate += e.template.foot;            for (var i = 0;i < table.length; i++) {                e.ctx["table" + i] = e.mfunc.buildTag(e.mtag.table,table[i]);            }            delete e.ctx.table;            var isIE = /*@cc_on!@*/false || !!document.documentMode; // this works with IE10 and IE11 both :)                        //if (typeof msie !== "undefined" && msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // this works ONLY with IE 11!!!            if (isIE) {                if (typeof Blob !== "undefined") {                    //use blobs if we can                    fullTemplate = e.format(fullTemplate, e.ctx); // with this, works with IE                    fullTemplate = [fullTemplate];                    //convert to array                    var blob1 = new Blob(fullTemplate, { type: "text/html" });                    window.navigator.msSaveBlob(blob1, getFileName(e.settings) + e.settings.fileext );                } else {                    //otherwise use the iframe and save                    //requires a blank iframe on page called txtArea1                    txtArea1.document.open("text/html", "replace");                    txtArea1.document.write(e.format(fullTemplate, e.ctx));                    txtArea1.document.close();                    txtArea1.focus();                    sa = txtArea1.document.execCommand("SaveAs", true, getFileName(e.settings) + e.settings.fileext );                }            } else {                fullTemplate = e.format(fullTemplate, e.ctx);                console.log(fullTemplate);                var blob = new Blob([fullTemplate], {type: "application/vnd.ms-excel"});                window.URL = window.URL || window.webkitURL;                link = window.URL.createObjectURL(blob);                a = document.createElement("a");                a.download = getFileName(e.settings);                a.href = link;                document.body.appendChild(a);                a.click();                document.body.removeChild(a);            }            return true;        }    };    function getFileName(settings) {        return ( settings.filename ? settings.filename : "table2excel" );    }    // Removes all img tags    function exclude_img(string) {        var _patt = /(\s+alt\s*=\s*"([^"]*)"|\s+alt\s*=\s*'([^']*)')/i;        return string.replace(/]*>/gi, function myFunction(x){            var res = _patt.exec(x);            if (res !== null && res.length >=2) {                return res[2];            } else {                return "";            }        });    }    // Removes all link tags    function exclude_links(string) {        return string.replace(/]*>|<\/a>/gi, "");    }    // Removes input params    function exclude_inputs(string) {        var _patt = /(\s+value\s*=\s*"([^"]*)"|\s+value\s*=\s*'([^']*)')/i;        return string.replace(/]*>|<\/input>/gi, function myFunction(x){            var res = _patt.exec(x);            if (res !== null && res.length >=2) {                return res[2];            } else {                return "";            }        });    }    $.fn[ pluginName ] = function ( options , exportData) {        var e = this;        if (e.length) {            // e.each(function() {            //     if ( !$.data( e, "plugin_" + pluginName ) ) {            //         $.data( e, "plugin_" + pluginName, new Plugin( this, options ) );            //     }            // });            $.data( e, "plugin_" + pluginName, new Plugin( this, options ) );        }else{            $.data( e, "plugin_" + pluginName, new Plugin( exportData, options ) );        }                    // chain jQuery functions        return e;    };})( jQuery, window, document );        body {margin-left: 20px;margin-right: 10px;}table,table tr th, table tr td { border:1px solid #000000; }
普通表格导出
序号学号姓名性别1201012365478罗四夕女2201012365479黄三胖男3201012365480李二狗女4201012365481张一山男5201012365482龙零吟女导出Excel
两个普通表格导出到两个sheet
sheet0
序号学号姓名性别1201012365478罗四夕女2201012365479黄三胖男3201012365480李二狗女
sheet1
序号学号姓名性别4201012365481张一山男5201012365482龙零吟女导出Excel
排除指定数据的导出
在th上加noExl可排除整列数据，在tr上加noExl可排除整行数据，在单个td加noExl可排除某个td的数据
序号学号姓名性别操作1201012365478罗四夕女修改2201012365479黄三胖男修改3201012365480李二狗女修改4201012365481张一山男修改5201012365482龙零吟女修改导出Excel
合并单元格表格导出
学生信息表制表日期2017年9月6日序号学号姓名籍贯性别1班1201012365478罗四夕广西南宁女2201012365479黄三胖广西梧州男3201012365480李二狗女2班4201012365481张一山广西百色男5201012365482龙零吟女导出Excel
带有格式表格导出
支持字体颜色(color)，背景颜色(background-color)，对齐方式(text-align)，字体大小(font-size)，同时可通过withoutXXX控制是否转换该属性,具体见代码
学生信息表制表日期2017年9月6日序号学号姓名籍贯性别1班1201012365478罗四夕广西南宁女2201012365479黄三胖广西梧州男3201012365480李二狗女2班4201012365481张一山广西百色男5201012365482龙零吟女导出Excel
bootstrap表格
学生信息表制表日期2017年9月6日序号学号姓名籍贯性别1班1201012365478罗四夕广西南宁女2201012365479黄三胖广西梧州男3201012365480李二狗女2班4201012365481张一山广西百色男5201012365482龙零吟女导出Excel
数组导出excel
var exportExcelData = {'sheet0' : [['序号','学号','姓名','籍贯','性别'],['1','201012365478','罗四夕','广西南宁','女'],['2','201012365479','黄三胖','广西梧州','男'],['3','201012365480','李二狗','广西梧州','女'],],'sheet1' : [['序号','学号','姓名','籍贯','性别'],['4','201012365481','张一山','广西百色','男'],['5','201012365482','龙零吟','广西百色','女'],],};$().table2excel({filename : 'bootstrap表格' + new Date().toISOString().replace(/[\-\:\.]/g, ""),//withoutFontColor : true, //关闭字体颜色转换//withoutBGColor : true, //关闭背景颜色转换//withoutFontSize : true, //关闭字体大小转换//withoutAlignment : true,//关闭对齐方式转换},exportExcelData);
导出Excel