萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網頁製作 >> HTML 腳本教學

ASP.NET HTMLTable 原樣匯出 Excel:JavaScript Automation 完整實作

功能特色

  • 保留表格合併儲存格 (rowspan/colspan)
  • 保留儲存格對齊 (align)、自動換行 (wrap)
  • 支援儲存格內表單元素 (select、input text、checkbox) 數值擷取
  • 自動調整欄寬 (AutoFit)
  • 邊框、垂直置中完整還原

核心函數 PrintTableToExcelEx

function PrintTableToExcelEx(objTab) {
    try {
        var xls = new ActiveXObject("Excel.Application");
    } catch(e) {
        alert("需安裝 Excel 並啟用 ActiveX 控制項");
        return false;
    }
    xls.visible = true;
    var xlBook = xls.Workbooks.Add;
    var xlsheet = xlBook.Worksheets(1);
    var CurX = 1, CurY = 1;

    for (var i = 0; i < objTab.rows.length; i++) {
        for (var j = 0; j < objTab.rows[i].cells.length; j++) {
            var cell = objTab.rows[i].cells[j];
            var v = cell.outerText;

            // 處理儲存格內表單元素
            if (cell.children != null && cell.children.length != 0) {
                v = "";
                for (var k = 0; k < cell.children.length; k++) {
                    var cc = cell.children[k];
                    switch (cc.type) {
                        case "select-one":
                            for (var l = 0; l < cc.options.length; l++)
                                if (cc.options[l].selected) v += cc.options[l].text;
                            break;
                        case "text": v += cc.value; break;
                        case "checkbox": v += cc.checked ? "是" : "否"; break;
                        case undefined: v += cell.outerText; break;
                        case "hidden": break;
                        default: v += cc.value; break;
                    }
                }
            }

            // 跳過合併儲存格已佔用位置
            while (xlsheet.Cells(CurX, CurY).MergeCells) CurY++;

            // 對齊設定
            xlsheet.Cells(CurX, CurY).HorizontalAlignment = 2; // 預設左
            var align = cell.getAttribute("align");
            switch (align) {
                case "left":   xlsheet.Cells(CurX, CurY).HorizontalAlignment = 2; break; // xlHAlignLeft
                case "center": xlsheet.Cells(CurX, CurY).HorizontalAlignment = 3; break; // xlHAlignCenter
                case "right":  xlsheet.Cells(CurX, CurY).HorizontalAlignment = 4; break; // xlHAlignRight
            }
            xlsheet.Cells(CurX, CurY).WrapText = true;
            xlsheet.Cells(CurX, CurY).VerticalAlignment = 2; // xlVAlignCenter
            xlsheet.Cells(CurX, CurY).Value = v;
            xlsheet.Cells(CurX, CurY).Borders.LineStyle = 1; // 連續線

            // 處理合併儲存格
            var rowSpan = cell.getAttribute("rowSpan");
            var colSpan = cell.getAttribute("colSpan");
            if (rowSpan >= 2 || colSpan >= 2) {
                var R = xls.Range(xls.Cells(CurX, CurY), xls.Cells(CurX + rowSpan - 1, CurY + colSpan - 1));
                R.MergeCells = true;
                R.Borders.LineStyle = 1;
            }
            CurY++;
        }
        CurX++; CurY = 1;
    }
    xlsheet.Columns.AutoFit;
}

呼叫方式

<input type="button" value="匯出" onclick="PrintTableToExcelEx(Table1)" class="input" style="width:107px">
<table id="Table1" border="1">...</table>

限制與注意

  • 僅限 IE 瀏覽器 (需 ActiveX)
  • 用戶端需安裝 Excel
  • 安全性設定需允許「對未標記為安全的 ActiveX 控制項初始化並編寫腳本」
  • 大量資料建議改用伺服端匯出 (NPOI, EPPlus, ClosedXML)
copyright © 萬盛學電腦網 all rights reserved