javascript导出excel(可以导出表格线)

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/sfSam/article/details/84465872

<html>
    <head>
        <script language="javascript" type="text/javascript">
        </script>
        <script language="javascript" type="text/javascript">
            function MakeExcel(){
            var i,j;
                try {
                  var xls    = new ActiveXObject ( "Excel.Application" );
                 }
                catch(e) {
                     alert( "要打印该表,您必须安装Excel电子表格软件,同时浏览器须使用“ActiveX 控件”,您的浏览器须允许执行控件。 请点击【帮助】了解浏览器设置方法!");
                          return "";
                 }
           
                        xls.visible =true;  //设置excel为可见
           
                var xlBook = xls.Workbooks.Add;
                var xlsheet = xlBook.Worksheets(1);
                <!--合并-->
                  xlsheet.Range(xlsheet.Cells(1,1),xlsheet.Cells(1,7)).mergecells=true;
                  xlsheet.Range(xlsheet.Cells(1,1),xlsheet.Cells(1,7)).value="发卡记录";
                 //  xlsheet.Range(xlsheet.Cells(1,1),xlsheet.Cells(1,6)).Interior.ColorIndex=5;//设置底色为蓝色
                            //   xlsheet.Range(xlsheet.Cells(1,1),xlsheet.Cells(1,6)).Font.ColorIndex=4;//设置字体色        
               // xlsheet.Rows(1). Interior .ColorIndex = 5 ;//设置底色为蓝色  设置背景色 Rows(1).Font.ColorIndex=4 
           
                <!--设置行高-->
                xlsheet.Rows(1).RowHeight = 25;
                <!--设置字体 ws.Range(ws.Cells(i0+1,j0), ws.Cells(i0+1,j1)).Font.Size = 13 -->
                xlsheet.Rows(1).Font.Size=14;
                <!--设置字体 设置选定区的字体  xlsheet.Range(xlsheet.Cells(i0,j0), ws.Cells(i0,j0)).Font.Name = "黑体" -->
                xlsheet.Rows(1).Font.Name="黑体";
                <!--设置列宽 xlsheet.Columns(2)=14;-->
           
                xlsheet.Columns("A:D").ColumnWidth =18;
                 <!--设置显示字符而不是数字-->
                xlsheet.Columns(2).NumberFormatLocal="@";
                xlsheet.Columns(7).NumberFormatLocal="@";
           
           
                 //设置单元格内容自动换行 range.WrapText  =  true  ;
                 //设置单元格内容水平对齐方式 range.HorizontalAlignment  =  Excel.XlHAlign.xlHAlignCenter;//设置单元格内容竖直堆砌方式
                  //range.VerticalAlignment=Excel.XlVAlign.xlVAlignCenter
                //range.WrapText  =  true;  xlsheet.Rows(3).WrapText=true  自动换行
              
                //设置标题栏
           
                 xlsheet.Cells(2,1).Value="卡号";
                 xlsheet.Cells(2,2).Value="密码";
                 xlsheet.Cells(2,3).Value="计费方式";
                 xlsheet.Cells(2,4).Value="有效天数";
                 xlsheet.Cells(2,5).Value="金额";
                 xlsheet.Cells(2,6).Value="所属服务项目";
                   xlsheet.Cells(2,7).Value="发卡时间";
           
                 var oTable=document.all['fors:data'];
                 var rowNum=oTable.rows.length;
                 for(i=2;i<=rowNum;i++){
                 for (j=1;j<=7;j++){
            //html table类容写到excel
           
                   xlsheet.Cells(i+1,j).Value=oTable.rows(i-1).cells(j-1).innerHTML;
                        }
           
           
                }
                <!--   xlsheet.Range(xls.Cells(i+4,2),xls.Cells(rowNum,4)).Merge; -->
                // xlsheet.Range(xlsheet.Cells(i, 4), xlsheet.Cells(i-1, 6)).BorderAround , 4
                 // for(mn=1,mn<=6;mn++) .     xlsheet.Range(xlsheet.Cells(1, mn), xlsheet.Cells(i1, j)).Columns.AutoFit;
                  xlsheet.Columns.AutoFit;
                             xlsheet.Range( xlsheet.Cells(1,1),xlsheet.Cells(rowNum+1,7)).HorizontalAlignment =-4108;//居中
                               xlsheet.Range( xlsheet.Cells(1,1),xlsheet.Cells(1,7)).VerticalAlignment =-4108;
                             xlsheet.Range( xlsheet.Cells(2,1),xlsheet.Cells(rowNum+1,7)).Font.Size=10;
           
                  xlsheet.Range( xlsheet.Cells(2,1),xlsheet.Cells(rowNum+1,7)).Borders(3).Weight = 2; //设置左边距
                   xlsheet.Range( xlsheet.Cells(2,1),xlsheet.Cells(rowNum+1,7)).Borders(4).Weight = 2;//设置右边距
                         xlsheet.Range( xlsheet.Cells(2,1),xlsheet.Cells(rowNum+1,7)).Borders(1).Weight = 2;//设置顶边距
                   xlsheet.Range( xlsheet.Cells(2,1),xlsheet.Cells(rowNum+1,7)).Borders(2).Weight = 2;//设置底边距
           
            
           
                  
                    xls.UserControl = true;  //很重要,不能省略,不然会出问题 意思是excel交由用户控制
                   xls=null;
                   xlBook=null;
                   xlsheet=null;
           
            }
           
            
           
           
        </script>
        <link href="css/styles3.css" rel="stylesheet" type="text/css"/>
        <title>ziyuanweihu</title>
    </head>
    <body>
        <form id="fors" method="post" action="/WebModule/admins/card/showcard.faces" enctype="application/x-www-form-urlencoded">
            <table border="0" cellpadding="0" cellspacing="0" width="100%">
                <tbody>
                    <tr>
                        <td class="middleLeft">
                        </td>
                        <td class="btstyle">
                            <table id="fors:sort" border="0" cellpadding="0" cellspacing="0" style="valign:center" width="100%">
                                <tbody>
                                    <tr>
                                        <td class="btstyle">
                                            <input type="button" name="fors:_id7" value="&#29983;&#25104;excel&#25991;&#20214;" οnclick="MakeExcel()"/><input type="submit" name="fors:_id8" value="&#36820;&#22238;" />
                                        </td>
                                    </tr>
                                </tbody>
                            </table>
                            <table id="fors:data" border="1" cellpadding="0" cellspacing="1" width="100%">
                                <thead>
                                    <tr>
                                        <th scope="col">
                                            <span id="fors:data:headerText1">&#21345;&#21495;</span>
                                        </th>
                                        <th scope="col">
                                            <span id="fors:data:headerText2">&#23494;&#30721;</span>
                                        </th>
                                        <th scope="col">
                                            <span id="fors:data:headerText3">&#35745;&#36153;&#26041;&#24335;</span>
                                        </th>
                                        <th scope="col">
                                            <span id="fors:data:headerText4">&#26377;&#25928;&#22825;&#25968;</span>
                                        </th>
                                        <th scope="col">
                                            &#37329;&#39069;
                                        </th>
                                        <th scope="col">
                                            <span id="fors:data:headerText6">&#25152;&#23646;&#26381;&#21153;&#39033;&#30446;</span>
                                        </th>
                                        <th scope="col">
                                            <span id="fors:data:headerText7">&#21457;&#21345;&#26102;&#38388;</span>
                                        </th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <tr>
                                        <td>
                                            h000010010
                                        </td>
                                        <td>
                                            543860
                                        </td>
                                        <td>
                                            &#35745;&#28857;
                                        </td>
                                        <td>
                                        </td>
                                        <td>
                                            2.0
                                        </td>
                                        <td>
                                            &#27979;&#35797;&#39033;&#30446;
                                        </td>
                                        <td>
                                            2006-06-23 10:14:40.843
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            h000010011
                                        </td>
                                        <td>
                                            683352
                                        </td>
                                        <td>
                                            &#35745;&#28857;
                                        </td>
                                        <td>
                                        </td>
                                        <td>
                                            2.0
                                        </td>
                                        <td>
                                            &#27979;&#35797;&#39033;&#30446;
                                        </td>
                                        <td>
                                            2006-06-23 10:14:40.843
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            h000010012
                                        </td>
                                        <td>
                                            433215
                                        </td>
                                        <td>
                                            &#35745;&#28857;
                                        </td>
                                        <td>
                                        </td>
                                        <td>
                                            2.0
                                        </td>
                                        <td>
                                            &#27979;&#35797;&#39033;&#30446;
                                        </td>
                                        <td>
                                            2006-06-23 10:14:40.843
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            h000010013
                                        </td>
                                        <td>
                                            393899
                                        </td>
                                        <td>
                                            &#35745;&#28857;
                                        </td>
                                        <td>
                                        </td>
                                        <td>
                                            2.0
                                        </td>
                                        <td>
                                            &#27979;&#35797;&#39033;&#30446;
                                        </td>
                                        <td>
                                            2006-06-23 10:14:40.843
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            h000010014
                                        </td>
                                        <td>
                                            031736
                                        </td>
                                        <td>
                                            &#35745;&#28857;
                                        </td>
                                        <td>
                                        </td>
                                        <td>
                                            2.0
                                        </td>
                                        <td>
                                            &#27979;&#35797;&#39033;&#30446;
                                        </td>
                                        <td>
                                            2006-06-23 10:14:40.843
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            h000010015
                                        </td>
                                        <td>
                                            188600
                                        </td>
                                        <td>
                                            &#35745;&#28857;
                                        </td>
                                        <td>
                                        </td>
                                        <td>
                                            2.0
                                        </td>
                                        <td>
                                            &#27979;&#35797;&#39033;&#30446;
                                        </td>
                                        <td>
                                            2006-06-23 10:14:40.843
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            h000010016
                                        </td>
                                        <td>
                                            363407
                                        </td>
                                        <td>
                                            &#35745;&#28857;
                                        </td>
                                        <td>
                                        </td>
                                        <td>
                                            2.0
                                        </td>
                                        <td>
                                            &#27979;&#35797;&#39033;&#30446;
                                        </td>
                                        <td>
                                            2006-06-23 10:14:40.843
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            h000010017
                                        </td>
                                        <td>
                                            175315
                                        </td>
                                        <td>
                                            &#35745;&#28857;
                                        </td>
                                        <td>
                                        </td>
                                        <td>
                                            2.0
                                        </td>
                                        <td>
                                            &#27979;&#35797;&#39033;&#30446;
                                        </td>
                                        <td>
                                            2006-06-23 10:14:40.843
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            h000010018
                                        </td>
                                        <td>
                                            354437
                                        </td>
                                        <td>
                                            &#35745;&#28857;
                                        </td>
                                        <td>
                                        </td>
                                        <td>
                                            2.0
                                        </td>
                                        <td>
                                            &#27979;&#35797;&#39033;&#30446;
                                        </td>
                                        <td>
                                            2006-06-23 10:14:40.843
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            h000010019
                                        </td>
                                        <td>
                                            234750
                                        </td>
                                        <td>
                                            &#35745;&#28857;
                                        </td>
                                        <td>
                                        </td>
                                        <td>
                                            2.0
                                        </td>
                                        <td>
                                            &#27979;&#35797;&#39033;&#30446;
                                        </td>
                                        <td>
                                            2006-06-23 10:14:40.843
                                        </td>
                                    </tr>
                                </tbody>
                            </table>
                        </td>
                        <td class="middleRight">
                        </td>
                    </tr>
                </tbody>
            </table>
   <input type="hidden" name="fors" value="fors" />
        </form>
    </body>
</html>

展开阅读全文

[讨论]javascript导出excel

08-28

下面是在网上找的一段js导excel代码,我做了一些改动。rn function htmlToExcel(elTableOut,elDiv) rn try rn //设置导出前的数据,为导出后返回格式而设置 rn var elDivStrBak = elDiv.innerHTML; rn //设置table的border=1,这样到excel中就有表格线rn elTableOut.border=1; rn //过滤elDiv内容 rn var elDivStr = elDiv.innerHTML; rn elDivStr = replaceHtml(elDivStr,""); rn elDivStr = replaceHtml(elDivStr,""); rn elDiv.innerHTML=elDivStr;rn var oRangeRef = document.body.createTextRange(); rn oRangeRef.moveToElementText( elDiv ); rn oRangeRef.execCommand("Copy"); //复制内容到剪切板rn rn //返回格式变换以前的内容 rn elDiv.innerHTML = elDivStrBak; rn //内容数据可能很大,所以赋空 rn elDivStrBak = ""; rn elDivStr = ""; rn try rn var oXL = new ActiveXObject("Excel.Application") rn rn catch(e) rn alert( "需要安装Excel电子表格软件,同时浏览器须使用“ActiveX 控件”,您的浏览器须允许执行控件。");rn return "";rn rn var oWB = oXL.Workbooks.Add ; rn var oSheet = oWB.ActiveSheet ; rn oSheet.Paste(); rn// oSheet.Cells.NumberFormatLocal = "@";#,##0.00rn oSheet.Columns("A:A").ColumnWidth=35;rn oSheet.Columns("B:B").ColumnWidth=10;rn oSheet.Columns("C:C").ColumnWidth=35;rn oSheet.Columns("D:D").ColumnWidth=10;rn oSheet.Columns(1).NumberFormatLocal="@";rn oSheet.Columns(3).NumberFormatLocal="@";rn oSheet.Columns(2).NumberFormatLocal="0.00";//设置格式rn oSheet.Columns(4).NumberFormatLocal="0.00";rn// oXL.Visible = true;rn// oXL.UserControl = true;rn var fileName;rn// dorn fileName = oXL.Application.GetSaveAsFilename("save.xls", "Excel Spreadsheets (*.xls), *.xls");rn// while(fileName==null||fileName=="")rn oWB.SaveAs(fileName);rn rn oXL.DisplayAlerts = "False";rn oXL.Quit();rn oXL = null;rn oSheet = null; rn oWB = null;rn idTmr = window.setInterval("Cleanup();",1000); //回收内存rn catch(e)rn oXL = null;rn oSheet = null; rn oWB = null;rn alert(e.description)rn oXL.DisplayAlerts = "False";rn oXL.Quit();rn CollectGarbage();rn rnrnfunction Cleanup() rnwindow.clearInterval(idTmr); rnCollectGarbage(); rnrnfunction replaceHtml(replacedStr,repStr,endStr) rn var replacedStrF = ""; rn var replacedStrB = ""; rn var repStrIndex = replacedStr.indexOf(repStr); rn while(repStrIndex != -1) rn replacedStrF = replacedStr.substring(0,repStrIndex); rn replacedStrB = replacedStr.substring(repStrIndex,replacedStr.length); rn replacedStrB = replacedStrB.substring(replacedStrB.indexOf(endStr)+1,replacedStrB.length); rn replacedStr = replacedStrF + replacedStrB; rn repStrIndex = replacedStr.indexOf(repStr); rn rn return replacedStr; rnrn在js里用oXL.Application.GetSaveAsFilename("save.xls", "Excel Spreadsheets (*.xls), *.xls");rn确实可以调出excel的保存文件对话框,但是这个对话框不是活动的,必须是“显示桌面”,再打开改IE页面,才能看到这个保存对话框。不知道有没有办法让它直接得到焦点。excel进程可以退出,CollectGarbage()回收js内存。rn可以直接用SaveAs()方法用固定文件名固定路径保存。只是用户体验不好。欢迎大家讨论。 论坛

JavaScript EXCEl 导出问题

08-20

我使用别人的方法实现excel 导出问题,但是点击导出的时候出现两个弹框,一个是数据内容弹框,一个是excel存储选择的弹框,请教大神,如何把数据内容的弹框隐藏掉,rn问题图片rn[img=https://img-bbs.csdn.net/upload/201708/20/1503240380_187194.png][/img]rn相关的js代码:rn[code=javascript]rn function Save_Excel(e, c) //导出Excel文件rn var data = $(e).datagrid('getExcelXml', title: 'datagrid import to excel', hidde: c,hidden:true ); var curr_time = new Date();rn var strDate = curr_time.getFullYear() + "-";rn strDate += curr_time.getMonth() + 1 + "-";rn strDate += curr_time.getDate() + "-";rn strDate += curr_time.getHours() + "-";rn strDate += curr_time.getMinutes() + "-";rn strDate += curr_time.getSeconds();rnrn//[color=#FF0000]问题主要是这个地方[/color]rn var screen = window.screen, fSaveWindow = window.open('', '_blank', 'width=' + Math.round(screen.width * 0.5) + ',height=' + Math.round(screen.height * 0.5) + ',left=' + Math.round(screen.width * 0.05));rn fSaveWindow.document.write(data);rn if ((navigator.userAgent.indexOf('MSIE') >= 0)rn && (navigator.userAgent.indexOf('Opera') < 0)rn || (!!window.ActiveXObject || "ActiveXObject" in window)rn || (navigator.userAgent.indexOf('Edge') >= 0))//包括Microsoft IE和Microsoft Edgern rn fSaveWindow.document.execCommand("SaveAs", true, strDate + ".xls");rn //Firefox浏览器的支持rn else if (navigator.userAgent.indexOf('Firefox') >= 0) rn var aLink = document.createElement('a');rn var blob = new Blob([data]);rn var evt = document.createEvent("HTMLEvents");rn evt.initEvent("click", true, true);rn aLink.download = strDate + ".xls";rn aLink.href = URL.createObjectURL(blob);rn aLink.dispatchEvent(evt);rn document.body.appendChild(aLink);rn aLink.click();rn rn //其他如Chrome和360浏览器的支持rn else rn var aLink = fSaveWindow.document.createElement('a');rn var blob = new Blob([data]);rn var evt = fSaveWindow.document.createEvent("HTMLEvents");rn evt.initEvent("click", false, false);rn aLink.download = strDate + ".xls";rn aLink.href = URL.createObjectURL(blob);rn aLink.dispatchEvent(evt);rn rnrn fSaveWindow.close();rn rnrn[/code] 论坛

没有更多推荐了,返回首页