html js 页面表格导出示例

朱雀 2023-09-27 21:32 144阅读 0赞

**简介：**

将html中的表格下载下来

**调用示例：**

// 调用示例
    exportTableToExcel('myTable', 'myFile.xls',[0,2],[]);

**参数解析：**

**核销代码：**

/**
     * 将表格数据导出为Excel文件
     * @param {string} tableID -要导出的表ID
     * @param {string} fileName -要保存的Excel文件的名称
     * @param {array} excludeColumns -要排除的列索引数组
     * @param {array} mathColumns - 数字科学计数法 前面加逗号处理
     */
    function exportTableToExcel(tableID, fileName, excludeColumns, mathColumns) {
    	var tab_text = "<table border='2px'>";
    	var textRange;
    	var j = 0;
    	var tab = document.getElementById(tableID); // table id
    
    	// Add title row to table HTML
    	tab_text += "<tr bgcolor='#87AFC6'>";
    	for (var i = 0; i < tab.rows[0].cells.length; i++) {
    		// Check if current column needs to be excluded
    		if (excludeColumns.includes(i)) {
    			continue;
    		}
    
    		// Remove HTML tags from cell content
    		var cellText = tab.rows[0].cells[i].textContent || tab.rows[0].cells[i].innerText;
    		tab_text += "<td>" + cellText + "</td>";
    	}
    	tab_text += "</tr>";
    
    	// Loop through table rows
    	for (j = 1; j < tab.rows.length; j++) {
    		tab_text = tab_text + "<tr>";
    
    		// Loop through table columns
    		for (var i = 0; i < tab.rows[j].cells.length; i++) {
    			// Check if current column needs to be excluded
    			if (excludeColumns.includes(i)) {
    				continue;
    			}
    
    			// Format cell value
    			var cellValue = tab.rows[j].cells[i].textContent || tab.rows[j].cells[i].innerText;
                if(cellValue == "" || cellValue == null || cellValue == undefined){
                    cellValue='';
                }else{
                    if (mathColumns.includes(i)) {
                        cellValue ="'"+ cellValue.toString();
                    }
                    else if (/\d{4}-\d{2}-\d{2}/.test(cellValue)) { // If cell value is a date in YYYY-MM-DD format
                        var dateParts = cellValue.split("-");
                        cellValue = dateParts[0] + "-" + dateParts[1] + "-" + dateParts[2];
                    }
                }
    
    			tab_text = tab_text + "<td>" + cellValue + "</td>";
    
    		}
    
    		tab_text = tab_text + "</tr>";
    	}
    
    	tab_text = tab_text + "</table>";
    	tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, ""); //remove if u want links in your table
    	tab_text = tab_text.replace(/<img[^>]*>/gi, ""); // remove if u want images in your table
    	tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // remove if u want input fields in your table
    
    	var ua = window.navigator.userAgent;
    	var msie = ua.indexOf("MSIE ");
    
    	if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
    		// If Internet Explorer
    		txtArea1.document.open("txt/html", "replace");
    		txtArea1.document.write(tab_text);
    		txtArea1.document.close();
    		txtArea1.focus();
    		sa = txtArea1.document.execCommand("SaveAs", true, fileName);
    	} else {
    		//other browser not tested on IE 11
    		var link = document.createElement("a");
    		link.download = fileName;
    		link.href = 'data:application/vnd.ms-excel,' + encodeURIComponent(tab_text);
    		link.click();
    	}
    }

**完整代码：**

<!DOCTYPE html>
    <html>
    	<head>
    		<meta charset="utf-8">
    		<title>html页面表格导出示例</title>
    	</head>
    	<body>
    		<table id="myTable">
    			<thead>
    				<tr>
    					<th>Name</th>
    					<th>Age</th>
    					<th>City</th>
    					<th>City2</th>
    					<th>City3</th>
    					<th>City4</th>
    					<th>City5</th>
    				</tr>
    			</thead>
    			<tbody>
    				<tr>
    					<td>John</td>
    					<td>25</td>
    					<td>New York</td>
    					<td>New York2</td>
    					<td>New York3</td>
    					<td>New York4</td>
    					<td></td>
    				</tr>
    				<tr>
    					<td>Jane</td>
    					<td>30</td>
    					<td>Los Angeles</td>
    					<td>Los Angeles2</td>
    					<td>Los Angeles3</td>
    					<td>Los Angeles4</td>
    					<td></td>
    				</tr>
    				<tr>
    					<td>Bob</td>
    					<td>40</td>
    					<td>Chicago</td>
    					<td>Chicago2</td>
    					<td>Chicago3</td>
    					<td>Chicago4</td>
    					<td></td>
    				</tr>
    			</tbody>
    		</table>
    	</body>
    	<script>
    		/**
    		 * 将表格数据导出为Excel文件
    		 * @param {string} tableID -要导出的表ID
    		 * @param {string} fileName -要保存的Excel文件的名称
    		 * @param {array} excludeColumns -要排除的列索引数组
    		 * @param {array} mathColumns - 数字科学计数法 前面加逗号处理
    		 */
    		function exportTableToExcel(tableID, fileName, excludeColumns, mathColumns) {
    			var tab_text = "<table border='2px'>";
    			var textRange;
    			var j = 0;
    			var tab = document.getElementById(tableID); // table id
    
    			// Add title row to table HTML
    			tab_text += "<tr bgcolor='#87AFC6'>";
    			for (var i = 0; i < tab.rows[0].cells.length; i++) {
    				// Check if current column needs to be excluded
    				if (excludeColumns.includes(i)) {
    					continue;
    				}
    
    				// Remove HTML tags from cell content
    				var cellText = tab.rows[0].cells[i].textContent || tab.rows[0].cells[i].innerText;
    				tab_text += "<td>" + cellText + "</td>";
    			}
    			tab_text += "</tr>";
    
    			// Loop through table rows
    			for (j = 1; j < tab.rows.length; j++) {
    				tab_text = tab_text + "<tr>";
    
    				// Loop through table columns
    				for (var i = 0; i < tab.rows[j].cells.length; i++) {
    					// Check if current column needs to be excluded
    					if (excludeColumns.includes(i)) {
    						continue;
    					}
    
    					// Format cell value
    					var cellValue = tab.rows[j].cells[i].textContent || tab.rows[j].cells[i].innerText;
    					if (cellValue == "" || cellValue == null || cellValue == undefined) {
    						cellValue = '';
    					} else {
    						if (mathColumns.includes(i)) {
                                cellValue ="'"+ cellValue.toString();
                            } else if (/\d{4}-\d{2}-\d{2}/.test(cellValue)) { // If cell value is a date in YYYY-MM-DD format
    							var dateParts = cellValue.split("-");
    							cellValue = dateParts[0] + "-" + dateParts[1] + "-" + dateParts[2];
    						}
    					}
    					tab_text = tab_text + "<td>" + cellValue + "</td>";
    
    				}
    
    				tab_text = tab_text + "</tr>";
    			}
    
    			tab_text = tab_text + "</table>";
    			tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, ""); //remove if u want links in your table
    			tab_text = tab_text.replace(/<img[^>]*>/gi, ""); // remove if u want images in your table
    			tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // remove if u want input fields in your table
    
    			var ua = window.navigator.userAgent;
    			var msie = ua.indexOf("MSIE ");
    
    			if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
    				// If Internet Explorer
    				txtArea1.document.open("txt/html", "replace");
    				txtArea1.document.write(tab_text);
    				txtArea1.document.close();
    				txtArea1.focus();
    				sa = txtArea1.document.execCommand("SaveAs", true, fileName);
    			} else {
    				//other browser not tested on IE 11
    				var link = document.createElement("a");
    				link.download = fileName;
    				link.href = 'data:application/vnd.ms-excel,' + encodeURIComponent(tab_text);
    				link.click();
    			}
    		}
    	</script>
    	<script>
    		// 调用示例
    		exportTableToExcel('myTable', 'myFile.xls', [0, 2],[]);
    	</script>
    </html>