功能特色
- 保留表格合併儲存格 (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)