使用JavaScript將表格導出為Excel文件是一種比較常見的導出方法，但復雜表格的導出比較麻煩（比如報表的導出），為此我專門寫了一段代碼專門處理表格導出，與各位分享一下。
util.js:
/**
* Utilities for exporting a table as an excel file
* @author Daniel.Sun(山風小子)
* @version 0.6
*/
var idTmr = "";
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
function exportAsXls(table) {
function ImpactedCell(row, col, offset) {
this.row = row;
this.col = col;
this.offset = offset;
}
function CurrentCell(row, col, text, colspan, rowspan) {
this.row = row;
this.col = col;
this.text = text;
this.colspan = colspan;
this.rowspan = rowspan;
this.getRow = function getRow() {
return this.row;
}
this.setRow = function setRow(row) {
this.row = row;
}
this.getCol = function getCol() {
return this.col;
}
this.setCol = function setCol(col) {
this.col = col;
}
this.setColspan = function setColspan(colspan) {
this.colspan = colspan;
}
this.getColspan = function getColspan() {
return this.colspan;
}
this.setRowspan = function setRowspan(rowspan) {
this.rowspan = rowspan;
}
this.getRowspan = function getRowspan() {
return this.rowspan;
}
}
function CellManager(originalRow, colOffset, impactedCells, currentCell) {
this.originalRow = originalRow;
this.colOffset = colOffset;
this.impactedCells = impactedCells;
this.currentCell = currentCell;
this.setCurrentCell = function setCurrentCell(currentCell) {
this.currentCell = currentCell;
}
this.setOriginalRow = function setOriginalRow(originalRow) {
this.originalRow = originalRow;
}
this.getCorrectedCol = function getCorrectedCol() {
return this.currentCell.getCol() + this.colOffset;
}
this.setColOffset = function setColOffset(colOffset) {
this.colOffset = colOffset;
}
this.getColOffset = function getColOffset() {
return this.colOffset;
}
this.initColOffset = function initColOffset() {
if (this.currentCell.getRow() != this.originalRow) {
this.colOffset = 0;
}
}
this.getImpactedCells = function getImpactedCells() {
return this.impactedCells;
}
this.addImpactedCell = function addImpactedCell(impactedCell) {
this.impactedCells.push(impactedCell);
}
this.addImpactedCells = function addImpactedCells() {
var currentCell = this.currentCell;
for (var i = 1; i < currentCell.getRowspan(); i++) {
var impactedRow = currentCell.getRow() + i;
this.calcOffset(impactedRow);
var impactedCol = this.getCorrectedCol();
var offset = 0;
if (currentCell.getColspan()) {
offset = currentCell.getColspan();
} else {
offset = 1;
}
this.addImpactedCell(new ImpactedCell(impactedRow, impactedCol, offset))
}
}
this.calcOffset = function calcOffset(row) {
var colOffset = this.colOffset;
var result = colOffset;
for (var i = 0; i < this.impactedCells.length; i++) {
var impactedCell = this.impactedCells[i];
if (row == impactedCell.row && this.getCorrectedCol() == impactedCell.col) {
colOffset += impactedCell.offset;
result = colOffset;
break;
}
}
this.colOffset = result;
return result;
}
this.correctColOffset = function correctColOffset() {
var currentCell = this.currentCell;
var tmpColOffset;
while (true) {
this.calcOffset(currentCell.getRow());
tmpColOffset = this.getColOffset();
this.calcOffset(currentCell.getRow());
if (this.getColOffset() == tmpColOffset) {
break;
}
}
}
this.mergeCells = function mergeCells(oSheet, row1, col1, row2, col2) {
oSheet.Range(oSheet.Cells(row1, col1), oSheet.Cells(row2, col2)).MergeCells = true;
}
this.mergeCellsConditionally = function mergeCellsConditionally(oSheet) {
var currentCell = this.currentCell;
var colsShouldMerge = currentCell.getColspan() > 1;
var rowsShouldMerge = currentCell.getRowspan() > 1;
if (colsShouldMerge && !rowsShouldMerge) {
this.mergeCells(
oSheet,
currentCell.getRow(), this.getCorrectedCol(),
currentCell.getRow(), this.getCorrectedCol() + currentCell.getColspan() - 1
);
} else if (!colsShouldMerge && rowsShouldMerge) {
this.mergeCells(
oSheet,
currentCell.getRow(), this.getCorrectedCol(),
currentCell.getRow() + currentCell.getRowspan() - 1, this.getCorrectedCol()
);
} else if (colsShouldMerge && rowsShouldMerge) {
this.mergeCells(
oSheet,
currentCell.getRow(), this.getCorrectedCol(),
currentCell.getRow() + currentCell.getRowspan() - 1, this.getCorrectedCol() + currentCell.getColspan() - 1
);
}
}
}
var oXL = new ActiveXObject("Excel.Application");
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var cellManager = new CellManager(0, 0, new Array());
traverseTable(
table,
function(i, j, cell) {
var text = cell.innerText;
if (null != text) {
var row = i + 1;
var col = j + 1;
var currentCell = new CurrentCell(row, col, text);
cellManager.setCurrentCell(currentCell);
cellManager.initColOffset();
cellManager.setOriginalRow(row);
var colspan = cell.getAttribute("colspan");
var rowspan = cell.getAttribute("rowspan");
currentCell.setColspan(colspan);
currentCell.setRowspan(rowspan);
var colsShouldMerge = currentCell.getColspan() > 1;
var rowsShouldMerge = currentCell.getRowspan() > 1;
cellManager.correctColOffset();
if (rowsShouldMerge) {
cellManager.addImpactedCells();
}
cellManager.mergeCellsConditionally(oSheet);
var cellInSheet = oSheet.Cells(currentCell.getRow(), cellManager.getCorrectedCol());
if (colsShouldMerge) {
var align = cell.getAttribute("align");
if ("center" == align) {
cellInSheet.HorizontalAlignment = 3;
}
cellManager.setColOffset(cellManager.getColOffset() + (colspan - 1));
}
cellInSheet.NumberFormatLocal = "@";
cellInSheet.value = text;
}
}
)
oXL.Visible = true;
oXL.UserControl = true;
oXL = null;
idTmr = window.setInterval("Cleanup();",1);
}
function traverseTable(table, action) {
for (var i = 0; i < table.rows.length; i++) {
var row = table.rows(i);
for (var j = 0; j < row.cells.length; j++) {
var cell = row.cells(j);
action(i, j, cell);
}
}
}
測試代碼：
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>無標題文檔</title>
<script src="util.js"></script>
</head>
<body>
<table width="200" border="1" onclick="exportAsXls(this)">
<tr>
<td colspan="4" rowspan="2">1234678t</td>
<td>5</td>
<td>k</td>
<td colspan="2">mr</td>
</tr>
<tr>
<td>9</td>
<td colspan="2" rowspan="3">wgbnpt</td>
<td>z</td>
</tr>
<tr>
<td rowspan="2">1013</td>
<td colspan="2" rowspan="3">eh14151920</td>
<td>11</td>
<td>12</td>
<td>c</td>
</tr>
<tr>
<td>16</td>
<td>17</td>
<td>a</td>
</tr>
<tr>
<td>18</td>
<td colspan="5">2122dsj</td>
</tr>
</table>
</body>
</html>
posted on 2009-09-13 22:06
山風小子 閱讀(3083)
評論(4) 編輯 收藏 所屬分類:
JavaScript