DIV CSS 佈局教程網

用 JS 導出具格式的 Excel 檔案
編輯:JavaScript綜合知識     

最常見的做法是用 JavaScript 直接操作 Excel 檔案,但這種方式容易因瀏覽器不同而出現相容性問題。這裡介紹另一種更簡便的思路:導出一個「Excel 能識別的 XML 格式檔案」,再用 Excel 打開即可。

核心函數如下:

function getTableDataByXML(inTable, inWindow) {
    var rows = 0;
    var tblDocument = document;
    if (!!inWindow && inWindow != "") {
        if (!document.all(inWindow)) {
            return null;
        } else {
            tblDocument = eval(inWindow).document;
        }
    }
    var inTbl = tblDocument.getElementById(inTable);
    var outStr = "<?xml version=\"1.0\"?>\n";
    outStr += "<?mso-application progid=\"Excel.Sheet\"?>\n";
    outStr += "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"";
    outStr += " xmlns:o=\"urn:schemas-microsoft-com:office:office\"";
    outStr += " xmlns:x=\"urn:schemas-microsoft-com:office:excel\"";
    outStr += " xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\n";
    outStr += "<Worksheet ss:Name=\"Sheet1\">\n";
    outStr += "<Table ss:ExpandedColumnCount=\"30\">\n";
    var re = /^[0-9]+.?[0-9]*$/; // 是否為數字
    if (inTbl != null) {
        for (var j = 0; j < inTbl.rows.length; j++) {
            outStr += "<Row ss:AutoFitHeight=\"0\">\n";
            for (var i = 0; i < inTbl.rows[j].cells.length; i++) {
                if (i == 0 && rows > 0) {
                    outStr += "<Cell><Data ss:Type=\"String\"></Data></Cell>\n";
                    rows -= 1;
                }
                var cellValue = inTbl.rows[j].cells[i].innerText;
                // 小於 11 位數字用 Number
                if (re.test(cellValue) && (new String(cellValue)).length < 11) {
                    outStr += "<Cell><Data ss:Type=\"Number\">" + cellValue + "</Data></Cell>\n";
                } else {
                    outStr += "<Cell><Data ss:Type=\"String\">" + cellValue + "</Data></Cell>\n";
                }
                if (inTbl.rows[j].cells[i].colSpan > 1) {
                    for (var k = 0; k < inTbl.rows[j].cells[i].colSpan - 1; k++) {
                        outStr += "<Cell><Data ss:Type=\"String\"></Data></Cell>\n";
                    }
                }
                if (i == 0) {
                    if (rows == 0 && inTbl.rows[j].cells[i].rowSpan > 1) {
                        rows = inTbl.rows[j].cells[i].rowSpan - 1;
                    }
                }
            }
            outStr += "</Row>\n";
        }
    } else {
        outStr = null;
        alert("你要導出的表不存在!!");
        return;
    }
    outStr += "</Table>\n</Worksheet>\n</Workbook>";
    return outStr;
}

這個函數原本是為了導出 txt 檔而寫的。只要把 Excel 檔另存為 XML,就能得知 Excel 可識別的標記格式,再依此組裝字串即可。它還能處理合併儲存格(colSpan/rowSpan),並自動判斷數值型別,相當實用。

XML學習教程| jQuery入門知識| AJAX入門| Dreamweaver教程| Fireworks入門知識| SEO技巧| SEO優化集錦|
Copyright © DIV+CSS佈局教程網 All Rights Reserved