js 导出table表格为Excel、CSV

本是古典何须时尚 2021-07-25 17:31 889阅读 0赞

### 使用table2excel.js 插件快速导出table表格为Excel ###

<!DOCTYPE html>
    
    <html>
    <head>
        <meta name="viewport" content="width=device-width" />
        <title>download</title>
        @*引入jquery*@
        <script src="~/Scripts/jquery-3.3.1.js"></script>
        @*引入table2excel*@
        <script src="~/Scripts/jquery.table2excel.js"></script>
    
        <style>
            #down {
                width: 100px;
                height: 26px;
                background-color: #2ca2d0;
                border: 1px solid #2ca2d0;
                margin: 1%;
            }
        </style>
    </head>
    <body>
        <table id="example" border="1"cellspacing="0">
            <thead>
                <tr>
                    <th>标题1</th>
                    <th>标题2</th>
                    <th>标题3</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>一行一列</td>
                    <td>一行二列</td>
                    <td>一行三列</td>
                </tr>
                <tr>
                    <td>二行一列</td>
                    <td>二行二列</td>
                    <td>二行三列</td>
                </tr>
            </tbody>
        </table>
        <button id="down" onclick="download()">下载</button>
    
        <script>
            function download() {
                $("#example").table2excel({
                    exclude: ".noExl",
                    name: "Excel Document Name",
                    // Excel文件的名称
                    filename: "表名",
                    exclude_img: true,
                    exclude_links: true,
                    exclude_inputs: true
                });
            }
        </script>
    </body>
    </html>

**jquery.table2excel.js**

/*
     *  jQuery table2excel - v1.0.2
     *  jQuery plugin to export an .xls file in browser from an HTML table
     *  https://github.com/rainabba/jquery-table2excel
     *
     *  Made by rainabba
     *  Under MIT License
     */
    //table2excel.js
    ;(function ( $, window, document, undefined ) {
    	var pluginName = "table2excel",
    
    	defaults = {
    		exclude: ".noExl",
        			name: "Table2Excel"
    	};
    
    	// 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: "<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><meta charset=\"UTF-8\"></head><body>",
    				table: {
    					head: "<table>",
    					tail: "</table>"
    				},
    				foot: "</body></html>"
    			};
    
    			e.tableRows = [];
    
    			// get contents of table except for exclude
    			$(e.element).each( function(i,o) {
    				var tempRows = "";
    				$(o).find("tr").not(e.settings.exclude).each(function (i,o) {
    					tempRows += "<tr>" + $(o).html() + "</tr>";
    				});
    				e.tableRows.push(tempRows);
    			});
    
    			// exclude img tags
                if(e.settings.exclude_img) {
                    e.tableRows[0] = exclude_img(e.tableRows[0]);
                }
    
                // exclude link tags
                if(e.settings.exclude_links) {
                    e.tableRows[0] = exclude_links(e.tableRows[0]);
                }
    
                // exclude input tags
                if(e.settings.exclude_inputs) {
                    e.tableRows[0] = exclude_inputs(e.tableRows[0])
                }
    
    			e.tableToExcel(e.tableRows, e.settings.name);
    		},
    
    		tableToExcel: function (table, name) {
    			var e = this, fullTemplate="", i, link, a;
    
    			e.uri = "data:application/vnd.ms-excel;base64,";
    			e.base64 = function (s) {
    				return window.btoa(unescape(encodeURIComponent(s)));
    			};
    			e.format = function (s, c) {
    				return s.replace(/{(\w+)}/g, function (m, p) {
    					return c[p];
    				});
    			};
    			e.ctx = {
    				worksheet: name || "Worksheet",
    				table: table
    			};
    
    			fullTemplate= e.template.head;
    
    			if ( $.isArray(table) ) {
    				for (i in table) {
    					//fullTemplate += e.template.sheet.head + "{worksheet" + i + "}" + e.template.sheet.tail;
    					fullTemplate += e.template.sheet.head + "Table" + i + "" + e.template.sheet.tail;
    				}
    			}
    
    			fullTemplate += e.template.mid;
    
    			if ( $.isArray(table) ) {
    				for (i in table) {
    					fullTemplate += e.template.table.head + "{table" + i + "}" + e.template.table.tail;
    				}
    			}
    
    			fullTemplate += e.template.foot;
    
    			for (i in table) {
    				e.ctx["table" + i] = table[i];
    			}
    			delete e.ctx.table;
    
    
    	        if (typeof msie !== "undefined" && msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
    	        {
    	            if (typeof Blob !== "undefined") {
    	                //use blobs if we can
    	                fullTemplate = [fullTemplate];
    	                //convert to array
    	                var blob1 = new Blob(fullTemplate, { type: "text/html" });
    	                window.navigator.msSaveBlob(blob1, getFileName(e.settings) );
    	            } else {
    	                //otherwise use the iframe and save
    	                //requires a blank iframe on page called txtArea1
    	                txtArea1.document.open("text/html", "replace");
    	                txtArea1.document.write(fullTemplate);
    	                txtArea1.document.close();
    	                txtArea1.focus();
    	                sa = txtArea1.document.execCommand("SaveAs", true, getFileName(e.settings) );
    	            }
    
    	        } else {
    	            link = e.uri + e.base64(e.format(fullTemplate, e.ctx));
    				a = document.createElement("a");
    				a.download = getFileName(e.settings);
    				a.href = link;
    				a.click();
    	        }
    
    			return true;
    
    		}
    	};
    
    	function getFileName(settings) {
    		return ( settings.filename ? settings.filename : "table2excel") + ".xlsx";
    	}
    
    	// Removes all img tags
    	function exclude_img(string) {
    		return string.replace(/<img[^>]*>/gi,"");
    	}
    
    	// Removes all link tags
    	function exclude_links(string) {
    		return string.replace(/<A[^>]*>|<\/A>/g, "");
    	}
    
    	// Removes input params
    	function exclude_inputs(string) {
    		return string.replace(/<input[^>]*>|<\/input>/gi, "");
    	}
    
    	$.fn[ pluginName ] = function ( options ) {
    		var e = this;
    			e.each(function() {
    					if ( !$.data( e, "plugin_" + pluginName ) ) {
    							$.data( e, "plugin_" + pluginName, new Plugin( this, options ) );
    					}
    			});
    
    			// chain jQuery functions
    			return e;
    	};
    
    })( jQuery, window, document );

### 导出table表格为CSV格式文件 ###

<!DOCTYPE html>
    
    <html>
    <head>
        <meta name="viewport" content="width=device-width" />
        <title>download</title>
        @*引入jquery*@
        <script src="~/Scripts/jquery-3.3.1.js"></script>
    
        <style>
            #down {
                width: 100px;
                height: 26px;
                background-color: #2ca2d0;
                border: 1px solid #2ca2d0;
                margin: 1%;
            }
        </style>
    </head>
    <body>
        <table id="example" border="1"cellspacing="0">
            <thead>
                <tr>
                    <th>标题1</th>
                    <th>标题2</th>
                    <th>标题3</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>一行一列</td>
                    <td>一行二列</td>
                    <td>一行三列</td>
                </tr>
                <tr>
                    <td>二行一列</td>
                    <td>二行二列</td>
                    <td>二行三列</td>
                </tr>
            </tbody>
        </table>
        <button id="down" onclick="download()">下载</button>
    
        <script>
            function download() {
                 $(document).on("click", "#down", function () {//按钮点击事件
    		            var $trs = $("#example").find("tr");//表格id元素
    		            var str = "";
    		            for (var i = 0; i < $trs.length; i++) {
    		                var $tds = $trs.eq(i).find("td,th");
    		                for (var j = 0; j < $tds.length; j++) {
    		                    str += $tds.eq(j).text() + ",";
    		                }
    		                str += "\n";
    		            }
    		
    		            var aaaa = "data:text/csv;charset=utf-8,\ufeff" + str;
    		            var link = document.createElement("a");
    		            link.setAttribute("href", aaaa);
    		            link.setAttribute("download", "xxx.csv");//表名
    		            link.click();
    		        });
                });
            }
        </script>
    </body>
    </html>