First they ignore you
then they ridicule you
then they fight you
then you win
    -- Mahatma Gandhi
Chinese => English     英文 => 中文             
随笔-219  评论-1047  文章-0  trackbacks-0
使用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() - 1this.getCorrectedCol()
                );
            } 
else if (colsShouldMerge && rowsShouldMerge) {
                
this.mergeCells(
                    oSheet,
                    currentCell.getRow(), 
this.getCorrectedCol(),
                    currentCell.getRow() 
+ currentCell.getRowspan() - 1this.getCorrectedCol() + currentCell.getColspan() - 1
                );
            }
        }
    }

    
var oXL = new ActiveXObject("Excel.Application");
    
var oWB = oXL.Workbooks.Add();
    
var oSheet = oWB.ActiveSheet;

    
var cellManager = new CellManager(00new 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 山风小子 阅读(2819) 评论(4)  编辑  收藏 所属分类: JavaScript