投递文章投递文章 投稿指南 RSS订阅RSS订阅

用JavaScript将复杂表格导出为 Excel

来源:IT堂 JavaScript 发布时间:2009-09-17 收藏 投稿 字体:【
使用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);
        }
    }
}
顶一下
(3)
75%
踩一下
(1)
25%
本文Tags:
  • 表情:
  •    
  • 评价:
用户名: 密码: 匿名 注册
最新评论 查看所有评论
About iTtang - 联系我们  - 专题列表 - 友情链接  -  高级搜索  -  帮助中心  -  您的意见