已经解决的使用JS从JSP页面到处Excel表格（需要自己保存） -

永恒的三号

浏览: 44847 次
性别:
来自: 深圳

最近访客更多访客>>

wo13892

ry315731

yangke9024

xiaoyanghapi

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

已经解决的使用JS从JSP页面到处Excel表格（需要自己保存）

Excel JSP JavaScript J#Web

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<html> 

<head> 
    <meta http-equiv="Content-Type" content="text/html; charset=gbk"/> 
    <title>WEB页面导出为EXCEL文档的方法</title>
    <SCRIPT LANGUAGE="javascript"> 
    
   
   
function method1(tableid) {
     var curTbl = document.getElementById(tableid); 
    var oXL;
   try{
    oXL = new ActiveXObject("Excel.Application"); 
    
   }catch (e) {

      alert("请将地址加入信任站点！");
    return;
   }
     var oWB = oXL.Workbooks.Add(); 
     var oSheet = oWB.ActiveSheet; 
    //控制行的高度
   // oSheet.Rows(1).EntireRow.RowHeight = 5;
    var lenRow = curTbl.rows.length;
    var rowId = 0;
    var maxLength = 0;
    //找出列的最大长度
    for(rowId=0;rowId<lenRow;rowId++){
    var cellsLength = curTbl.rows.item(rowId).cells;
    var colLength = cellsLength.length;
    if(colLength > maxLength){
       maxLength = colLength;
    }
    }
    //循环控制excel报表每列的宽度
    if(maxLength != null && maxLength != "" && maxLength > 0){
     for(var i=1;i<=maxLength;i++){
      oSheet.Columns(i).EntireColumn.ColumnWidth = 30;
      }
   }
     var sel = document.body.createTextRange(); 
     sel.moveToElementText(curTbl); 
     //sel.select(); 
     sel.execCommand("Copy"); 
     oSheet.Paste(); 
   //整个表格粘贴完毕后，再设置excel表格样式
   
   for(rowId=0;rowId<lenRow;rowId++){
   if(rowId == 0){
       oSheet.Rows("1:1").Interior.ColorIndex=10; //标题背景设置为绿色
       oSheet.Rows("1:1").Font.ColorIndex=2;     //标题字体颜色设置为白色
    }else{
       oSheet.Rows((rowId+1)+":"+(rowId+1)).Interior.ColorIndex=0; //除标题外，其它行把格式清除
    }
    }
     oXL.Visible = true; 
} 
//数字被自动以科学计数法的形式显示，搜了好多资料也没有解决，用NumberFormatLocal="@"好像不行，但是可以在数字后面加&nbsp;解决
function method2(tableid) //读取表格中每个单元到EXCEL中   
{   
    var curTbl = document.getElementById(tableid);   
    var oXL = new ActiveXObject("Excel.Application");   
    //创建AX对象excel   
    var oWB = oXL.Workbooks.Add();   
    //获取workbook对象   
    var oSheet = oWB.ActiveSheet;   
    //激活当前sheet   
    
    var rowLength = curTbl.rows.length;
    //得到表格行数
    if(rowLength != null && rowLength != "" && rowLength >= 1){
    var lastRowIndex = rowLength - 1;
    //得到最后一行序号
   }
    var colLength = curTbl.rows.item(lastRowIndex).cells.length;
    //根据最后一行获取表格列数
    if(colLength != null && colLength != ""){
       for(var i=1;i<=colLength;i++){
         oSheet.Columns(i).ColumnWidth = 20;
         //循环控制每一列的宽度
        }
    }
    
    var Lenr = curTbl.rows.length;   
    //取得表格行数   
    for (i = 0; i < Lenr; i++)   
    {   
        var Lenc = curTbl.rows(i).cells.length;   
        //取得每行的列数   
        for (j = 0; j < Lenc; j++)   
        {   
        
            oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText;   
            
            //赋值   
        }   
    }   
    oXL.Visible = true;   
    //设置excel可见属性   
}   

function getXlsFromTbl(inTblId, inWindow) {   
    try {   
        var allStr = "";   
        var curStr = "";   
        //alert("getXlsFromTbl");   
        if (inTblId != null && inTblId != "" && inTblId != "null") {   
            curStr = getTblData(inTblId, inWindow);   
        }   

        if (curStr != null) {   
            allStr += curStr;   
        }   
        else {   
            alert("你要导出的表不存在！");   
            return;   
        }   

        var fileName = getExcelFileName();   
        doFileExport(fileName, allStr);   
    }   
    catch(e) {   
        alert("导出发生异常:" + e.name + "->" + e.description + "!");   
    }   
}   

function getTblData(inTbl, inWindow) {   
    var rows = 0;   
    //alert("getTblData is " + inWindow);   
    var tblDocument = document;   
    if (!!inWindow && inWindow != "") {   
        if (!document.all(inWindow)) {   
            return null;   
        }   
        else {   
            tblDocument = eval(inWindow).document;   
        }   
    }   

    var curTbl = tblDocument.getElementById(inTbl);   
    var outStr = "";   
    if (curTbl != null) {   
        for (var j = 0; j < curTbl.rows.length; j++) {   
            //alert("j is " + j);   
            for (var i = 0; i < curTbl.rows[j].cells.length; i++) {   
                //alert("i is " + i);   
                if (i == 0 && rows > 0) {   
                    outStr += " \t";   
                    rows -= 1;   
                }   
                outStr += curTbl.rows[j].cells[i].innerText + "\t";   
                if (curTbl.rows[j].cells[i].colSpan > 1) {   
                    for (var k = 0; k < curTbl.rows[j].cells[i].colSpan - 1; k++) {   
                        outStr += " \t";   
                    }   
                }   
                if (i == 0) {   
                    if (rows == 0 && curTbl.rows[j].cells[i].rowSpan > 1) {   
                        rows = curTbl.rows[j].cells[i].rowSpan - 1;   
                    }   
                }   
            }   
            outStr += "\r\n";   
        }   
    }   
    else {   
        outStr = null;   
        alert(inTbl + "不存在!");   
    }   
    return outStr;   
}   

function getExcelFileName() {   
    var d = new Date();   
    var curYear = d.getYear();   
    var curMonth = "" + (d.getMonth() + 1);   
    var curDate = "" + d.getDate();   
    var curHour = "" + d.getHours();   
    var curMinute = "" + d.getMinutes();   
    var curSecond = "" + d.getSeconds();   

    if (curMonth.length == 1) {   
        curMonth = "0" + curMonth;   
    }   
    if (curDate.length == 1) {   
        curDate = "0" + curDate;   
    }   
    if (curHour.length == 1) {   
        curHour = "0" + curHour;   
    }   
    if (curMinute.length == 1) {   
        curMinute = "0" + curMinute;   
    }   
    if (curSecond.length == 1) {   
        curSecond = "0" + curSecond;   
    }   
    
    var fileName = "leo_zhang" + "_" + curYear + curMonth + curDate + "_" + curHour + curMinute + curSecond + ".csv";   
    //alert(fileName);   
    return fileName;   
}   
    
function doFileExport(inName, inStr) {   
    var xlsWin = null;   
    if (!!document.all("glbHideFrm")) {   
        xlsWin = glbHideFrm;   
    }   
    else {   
        var width = 6;   
        var height = 4;   
        var openPara = "left=" + (window.screen.width / 2 - width / 2)   
                + ",top=" + (window.screen.height / 2 - height / 2)   
                + ",scrollbars=no,width=" + width + ",height=" + height;   
        xlsWin = window.open("", "_blank", openPara);   
    }   

    xlsWin.document.write(inStr);   
    xlsWin.document.close();   
    xlsWin.document.execCommand('Saveas', true, inName);   
    xlsWin.close();   
}   

</SCRIPT>   
</head> 
<body> 
<table id="tableExcel" width="100%" border="1" cellspacing="0" cellpadding="0"> 
    <tr> 
        <td colspan="5" align="center">WEB页面导出为EXCEL文档的方法</td> 
    </tr> 
    <tr> 
        <td>列标题1</td> 
        <td>列标题2</td> 
        <td>列标题3</td> 
        <td>列标题4</td> 
        <td>列标题5</td> 
    </tr> 
    <tr> 
        <td>aaaaaaaaaaaaa12</td> 
        <td>bbbbbbbbbbbbb45</td> 
        <td>ccccccccccccc67</td> 
        <td>ddddddddddddd89</td> 
        <td>eeeeeeeeeeeee99</td> 
    </tr> 
    <tr> 
        <td>AAAAAAAAAAAAA</td> 
        <td>BBBBBBBBBBBBB</td> 
        <td>CCCCCCCCCCCCC</td> 
        <td>DDDDDDDDDDDDD</td> 
        <td>EEE</td> 
    </tr> 
    <tr> 
        <td>1234567890123456&nbsp;</td> 
        <td>1234567890123456&nbsp;</td> 
        <td>1234567890123456&nbsp;</td> 
        <td>1234567890123456&nbsp;</td> 
        <td>1234567890123456&nbsp;</td>
    </tr> 
</table>

<input type="button" onclick="javascript:method1('tableExcel');" value="整体导入到EXCEL(适合任何表格)"> 
<input type="button" onclick="javascript:method2('tableExcel');" value="循环导入到EXCEL(适合简单表格)"> 
<input type="button" onclick="javascript:getXlsFromTbl('tableExcel',null);" value="第三种方法导入到EXCEL">

</body> 
</html>

使用很方便的。

分享到：