javascript - 使用 javascript 将 html 表导出到 Excel 中
问题描述
我有 HTML 表格,例如:
<table id="prehledPlatidel" class="table table-hover">
<tr>
<th title="Zařízení na kterém účtenka vznikla.">Zařízení</th>
<th title="Název platidla.">Platidlo</th>
<th title="Přijato celkem.">Celkem</th>
</tr>
<tbody>
<?php
$sumaCelkemZaklad = 0;
while ($dataPlatidla = $vysledek->fetch_assoc()) {
$sumaCelkemZaklad = $sumaCelkemZaklad + $dataPlatidla['SumaCelkemZaklad'];
echo('<tr>
<td>' . $dataPlatidla['Zarizeni']. ' </td>
<td>' . $dataPlatidla['Platidlo'].'</td>
<td>' . number_format($dataPlatidla['SumaCelkem'],2,',', ' '). ' ' .$dataPlatidla['Zkratka'].'</td>
</tr>'
);
}
echo ('
<tr><td><b>Celkem v základní měně</b></td><td></td>
<td><b>' .number_format($sumaCelkemZaklad,2,',', ' '). ' Kč</b></td></tr>
</tbody></table>
<script type="text/javascript">//<![CDATA[
function fnExcelReport() {
var tab_text = '<html xmlns:x="urn:schemas-microsoft-com:office:excel">';
tab_text = tab_text + '<head><meta charset="UTF-8" /><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>';
tab_text = tab_text + '<x:Name>Platidla</x:Name>';
tab_text = tab_text + '<x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet>';
tab_text = tab_text + '</x:ExcelWorksheets></x:ExcelWorkbook></xml></head><body>';
tab_text = tab_text + "<table border='1px'>";
tab_text = tab_text + $('#prehledPlatidel').html();
tab_text = tab_text + '</table></body></html>';
var data_type = 'data:application/vnd.ms-excel';
var ua = window.navigator.userAgent;
var msie = ua.indexOf("MSIE ");
if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
if (window.navigator.msSaveBlob) {
var blob = new Blob([tab_text], {
type: "application/csv;charset=utf-8;"
});
navigator.msSaveBlob(blob, 'PPlatidel<?php echo('PPlatidel_'.$nazevZarizeni.'_'.$datumOd.'-'.$datumDo);?>.xls');
}
} else {
$('#test').attr('href', data_type + ', ' + encodeURIComponent(tab_text));
$('#test').attr('download', '<?php echo('PPlatidel_'.$nazevZarizeni.'_'.$datumOd.'-'.$datumDo);?>.xls');
}
}
//]]></script>
一切看起来都很好,下载了 xls 文件,但是当我尝试在 Excel 2010 中打开它时,会显示一个错误对话框,指出文件格式不同,然后是后缀 (xls)。确认对话框后,将显示数据 OK。感谢帮助。M。
解决方案
function exportToExcel(table) {
debugger;
tableToExcel(table,'Rapor','Excell_Rapor');
}
function getIEVersion()
// Returns the version of Windows Internet Explorer or a -1
// (indicating the use of another browser).
{
var rv = -1; // Return value assumes failure.
if (navigator.appName == 'Microsoft Internet Explorer') {
var ua = navigator.userAgent;
var re = new RegExp("MSIE ([0-9]{1,}[\.0-9]{0,})");
if (re.exec(ua) != null)
rv = parseFloat(RegExp.$1);
}
return rv;
}
function tableToExcel(table, sheetName, fileName) {
var ua = window.navigator.userAgent;
var msie = ua.indexOf("MSIE ");
if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // If Internet Explorer
{
return fnExcelReport(table, fileName);
}
var uri = 'data:application/vnd.ms-excel;base64,',
templateData = '<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]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></head><body><table>{table}</table></body></html>',
base64Conversion = function (s) { return window.btoa(unescape(encodeURIComponent(s))) },
formatExcelData = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
$("tbody > tr[data-level='0']").show();
if (!table.nodeType)
table = document.getElementById(table)
var ctx = { worksheet: sheetName || 'Worksheet', table: table.innerHTML }
var element = document.createElement('a');
element.setAttribute('href', 'data:application/vnd.ms-excel;base64,' + base64Conversion(formatExcelData(templateData, ctx)));
element.setAttribute('download', fileName);
element.style.display = 'none';
document.body.appendChild(element);
element.click();
document.body.removeChild(element);
$("tbody > tr[data-level='0']").hide();
}
function fnExcelReport(table, fileName) {
var tab_text = "<table border='2px'>";
var textRange;
if (!table.nodeType)
table = document.getElementById(table)
$("tbody > tr[data-level='0']").show();
tab_text = tab_text + table.innerHTML;
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, ""); // reomves input params
txtArea1.document.open("txt/html", "replace");
txtArea1.document.write(tab_text);
txtArea1.document.close();
txtArea1.focus();
sa = txtArea1.document.execCommand("SaveAs", false, fileName + ".xls");
$("tbody > tr[data-level='0']").hide();
return (sa);
}
推荐阅读
- c# - 如何使用 stringbuilder 在 c# 中创建和下载 pdf 文件?
- python - 深度学习:验证损失波动剧烈,但训练损失稳定
- java - 按字母顺序排序会更改 ArrayList 中项目的位置,因此单击侦听器不匹配
- javascript - javascript 中的方法承诺不在对话框流上运行
- json - 在休眠中显示 JSON 双向映射
- python - 从熊猫数据框中删除重复的列索引
- mongodb - 查询以查找所有非零毫秒的文档
- webrtc - 实现类似whatsapp的视频聊天的最佳SDK?
- ruby - 访问 iframe 中的元素
- django - Django,如何只渲染一次页面