• JS操作Excel读取和写入(模板操作


    JS操作Excel读取和写入(模板操作)

                前一段时间一直在做报表,所以肯定会用到Excel的操作,但是在网上查阅资料有关JS操作excel较少,有的话,也都是老生常谈或很零碎的一些东西。本人是在实际项目中摸索出,JS读写Excel(模板)数据,包括怎么用JS把图片插入Excel中。

                首先,添加一个公用的模板地址JS,如下:

    AddZDaddress.js

    ///////////////////////////////////////////////////////////////////////////////////////////////////
    ////DocName:信任站点地址添加--AddZDaddress.js
    ////Author:lify
    ////Company:Wavenet
    ////Date:2009-11-04
    ////EditDate:2010-03-11
    ////MainContent:Findings,Ajax And So On;
    //////////////////////////////////////////////////////////////////////////////////////////////////
    var BelieveAddress='172.18.1.25/psc1';////配置地址配置程序时需要的配置地址

    ////**********************************Excel 导入到WEB界面模板地址集合*******************************************//
    //////////////////////////飞行检测excel模板地址配置/////////////////////////////////////////////////////////////
    var template_path_FxjcExcelDatasIntoSqlDatas = "http://%22+believeaddress+%22/NewReports/xls_template/飞行检测城镇污水厂进出水主要污染物浓度.xls";
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    ////***********************************************************************************************************//

    ////*********************************Excel 录入模板导入地址集合**********************************************//
    //////////////////////////月报导入excel地址配置//////////////////////////////////
    var template_path_month = "http://%22+believeaddress+%22/NewReports/xls_template/上海市污水处理企业生产运行表.xls";
    /////////////////////////////////////////////////////////////////////////////////
    //////////////////////////年报导入excel地址配置//////////////////////////////////
    var template_path_year = "http://%22+believeaddress+%22/NewReports/xls_template/上海市污水处理设施信息表.xls";
    ////*********************************************************************************************************//
    //图片地址
    var pic_path="http://%22+believeaddress+%22/NewReports/";
    /////////////////////////////////////////////////////////////////////////////////

                  再次,怎么样把页面数据读取到Excel中JS,如下:

    YearReportLuRu.jsYearReportLuRu.js

    ///////////////////////////////////////////////////////////////////////////////////////////////////

    ////DocName:污水处理设施信息表(年报)-录入报表--YearReportLuRu.js
    ////Author:lify
    ////Company:Wavenet
    ////Date:2009-08-14
    ////EditDate:2009-08-14
    ////MainContent:Findings,Ajax And So On;
    ///////////////////////////////////////////////////////////////////////////////////////////////////
    ///////////////////////////////////////添加正则表达式类/////////////////////////////////////////////////////////////
    document.write("<script language=\"javascript\" type=\"text/javascript\" src=\"js/RegularExpression.js\"><\/script>");
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    function AutomateExcel3()
    {

        //alert(pic_path+document.getElementById("ImgUrl").value);//测试
        var xls = new ActiveXObject("Excel.Application");
        xls.Visible = true;
        var newBook = xls.Workbooks.Open(template_path_year);//这里的Add方法里的参数是模板的路径
        var  oSheet=newBook.ActiveSheet;
    ////////////////////////////////////////////写入数据到模板中////////////////////////////////////////////////////////////
        ///////////////法人单位名称/////////////////////////////////////////////////////////////////////////////////////////////
        oSheet.Cells(2,3).value = Trim(PT.rows[0].cells[1].innerText);
        ///////////////法人单位代码以及代表姓名/////////////////////////////////////////////////////////////////////////////////////////////////
        oSheet.Cells(2,9).value = Trim(PT.rows[0].cells[3].innerText);
        oSheet.Cells(2,13).value = Trim(PT.rows[0].cells[5].innerText);
        //////////////进出口三张表/////////////////////////////////////////////////////////////////////////////////////////
        oSheet.Cells(3,2).value=Trim(PT.rows[1].cells[1].innerText);
        oSheet.Cells(3,6).value=Trim(PT.rows[1].cells[3].innerText);
        oSheet.Cells(3,8).value=Trim(PT.rows[1].cells[5].innerText);
        oSheet.Cells(3,10).value=Trim(PT.rows[1].cells[7].innerText);
        oSheet.Cells(3,13).value=Trim(PT.rows[1].cells[9].innerText);
       
        oSheet.Cells(4,2).value=Trim(PT.rows[2].cells[1].innerText);
        oSheet.Cells(4,4).value=Trim(PT.rows[2].cells[3].innerText);
        oSheet.Cells(4,6).value=Trim(PT.rows[2].cells[5].innerText);
        oSheet.Cells(4,8).value=Trim(PT.rows[2].cells[7].innerText);
        oSheet.Cells(4,10).value=Trim(PT.rows[2].cells[9].innerText);
        oSheet.Cells(4,12).value=Trim(PT.rows[2].cells[11].innerText);
        oSheet.Cells(4,14).value=Trim(PT.rows[2].cells[13].innerText);
       
        oSheet.Cells(5,2).value=Trim(PT.rows[3].cells[1].innerText);
        oSheet.Cells(5,5).value=Trim(PT.rows[3].cells[3].innerText);
        oSheet.Cells(5,8).value=Trim(PT.rows[3].cells[5].innerText);
        oSheet.Cells(5,10).value=Trim(PT.rows[3].cells[7].innerText);
        oSheet.Cells(5,12).value=Trim(PT.rows[3].cells[9].innerText);
        oSheet.Cells(5,14).value=Trim(PT.rows[3].cells[11].innerText);

        //////////////年运行简况///////////////////////
        oSheet.Cells(6,4).value = Trim(PT.rows[4].cells[3].innerText);
        oSheet.Cells(6,6).value = Trim(PT.rows[4].cells[5].innerText);
        oSheet.Cells(6,8).value = Trim(PT.rows[4].cells[7].innerText);
        oSheet.Cells(6,10).value = Trim(PT.rows[4].cells[9].innerText);
        oSheet.Cells(6,12).value = Trim(PT.rows[4].cells[11].innerText);
        oSheet.Cells(6,14).value = Trim(PT.rows[4].cells[13].innerText);
       
        oSheet.Cells(7,4).value = Trim(PT.rows[5].cells[2].innerText);
        oSheet.Cells(7,6).value = Trim(PT.rows[5].cells[4].innerText);
        oSheet.Cells(7,8).value = Trim(PT.rows[5].cells[6].innerText);
        oSheet.Cells(7,10).value = Trim(PT.rows[5].cells[8].innerText);
        oSheet.Cells(7,12).value = Trim(PT.rows[5].cells[10].innerText);
        oSheet.Cells(7,14).value = Trim(PT.rows[5].cells[12].innerText);
       
        //////////////年处理水量///////////////////////
        oSheet.Cells(8,2).value = Trim(PT.rows[6].cells[1].innerText);
        oSheet.Cells(8,5).value = Trim(PT.rows[6].cells[3].innerText);
        oSheet.Cells(8,8).value = Trim(PT.rows[6].cells[5].innerText);
        oSheet.Cells(8,11).value = Trim(PT.rows[6].cells[7].innerText);
        oSheet.Cells(8,14).value = Trim(PT.rows[6].cells[9].innerText);
       
        //////////////污水厂工艺概况///////////////////////
        oSheet.Cells(10,3).value = Trim(PT.rows[8].cells[1].innerText);
        oSheet.Cells(10,4).value = Trim(PT.rows[8].cells[2].innerText);
        oSheet.Cells(10,5).value = Trim(PT.rows[8].cells[3].innerText);
        oSheet.Cells(10,7).value = Trim(PT.rows[8].cells[4].innerText);
       
        oSheet.Cells(11,3).value = Trim(PT.rows[9].cells[1].innerText);
        oSheet.Cells(11,4).value = Trim(PT.rows[9].cells[2].innerText);
        oSheet.Cells(11,5).value = Trim(PT.rows[9].cells[3].innerText);
        oSheet.Cells(11,7).value = Trim(PT.rows[9].cells[4].innerText);
       
        oSheet.Cells(12,3).value = Trim(PT.rows[10].cells[1].innerText);
        oSheet.Cells(12,4).value = Trim(PT.rows[10].cells[2].innerText);
        oSheet.Cells(12,5).value = Trim(PT.rows[10].cells[3].innerText);
        oSheet.Cells(12,7).value = Trim(PT.rows[10].cells[4].innerText);
        //--------------------------运行费用分析-------------------------------------//
        oSheet.Cells(13,3).value = Trim(PT.rows[11].cells[2].innerText);
        oSheet.Cells(13,5).value = Trim(PT.rows[11].cells[4].innerText);
        oSheet.Cells(13,7).value = Trim(PT.rows[11].cells[6].innerText);
        oSheet.Cells(13,9).value = Trim(PT.rows[11].cells[8].innerText);
        ////////////////////////////////厂外输送泵站示意图////////////////////////////
        //copy(document.getElementById("ImgUrl"));
        //oSheet.Pictures.Insert(pic_path+document.getElementById("ImgUrl").value);///////.Cells(13,11)
        var msoShaoeRectangle = 1;//AddShape(透明度,左,上,宽度,高度)
        oSheet.Shapes.AddShape(msoShaoeRectangle, 560, 330, 200, 150).Fill.UserPicture(pic_path+document.getElementById("ImgUrl").value);
        //oSheet.Cell(13,11).select();//选中excel的单元格
        //oSheet.Pictures.Insert(PT.rows[11].cells[8].getElementById("ImgUrl").src);
        //////////////////////////////////////////////////////////////////////////////
       
        oSheet.Cells(14,3).value = Trim(PT.rows[12].cells[1].innerText);
        oSheet.Cells(14,5).value = Trim(PT.rows[12].cells[3].innerText);
        oSheet.Cells(14,7).value = Trim(PT.rows[12].cells[5].innerText);
        oSheet.Cells(14,9).value = Trim(PT.rows[12].cells[7].innerText);
       
        oSheet.Cells(15,3).value = Trim(PT.rows[13].cells[1].innerText);
        oSheet.Cells(15,5).value = Trim(PT.rows[13].cells[3].innerText);
        oSheet.Cells(15,7).value = Trim(PT.rows[13].cells[5].innerText);
        oSheet.Cells(15,9).value = Trim(PT.rows[13].cells[7].innerText);
       
        oSheet.Cells(16,5).value = Trim(PT.rows[14].cells[1].innerText);
        oSheet.Cells(16,7).value = Trim(PT.rows[14].cells[3].innerText);
        oSheet.Cells(16,9).value = Trim(PT.rows[14].cells[5].innerText);
       
        oSheet.Cells(17,3).value = Trim(PT.rows[15].cells[1].innerText);
        oSheet.Cells(17,5).value = Trim(PT.rows[15].cells[3].innerText);
        oSheet.Cells(17,7).value = Trim(PT.rows[15].cells[5].innerText);
        //---------------------------------------------------------------------------//
        //////////////污水厂表下方信息///////////////////////
        oSheet.Cells(18,2).value = Trim(RT.rows[0].cells[1].innerText);
        oSheet.Cells(18,7).value = Trim(RT.rows[0].cells[3].innerText);
        oSheet.Cells(18,11).value = Trim(RT.rows[0].cells[5].innerText);
        oSheet.Cells(18,14).value = Trim(RT.rows[0].cells[7].innerText);
    ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        xls.Visible = true;
        xls.UserControl = true;
        xls = null;  
        idTmr = window.setInterval("Cleanup();",1); 
        //xls.quit();
    }
    function Cleanup()
    {  
              window.clearInterval(idTmr);  
              CollectGarbage();  
    }
    function copy(tabid){
    var oControlRange = document.body.createControlRange();
    oControlRange.add(tabid,0);
    oControlRange.select();
    document.execCommand("Copy");
    }
    ///////////////////////////////////////////////////////////////////////////////////////////////////////

                  最后,如何读取到Excel模板中数据到页面上共客户编辑数据再保存到数据库中(此步可以用xml发送至后台交互,在此不进一步说明)JS,如下:

    Fxjc_ExcelDatasIntoSqlDatas.js///////////////////////////////////////////////////////////////////////////////////////////////////
    ////DocName:导入模板数据功能--Fxjc_ExcelDatasIntoSqlDatas.js
    ////Author:lify
    ////Company:Wavenet
    ////Date:2010-03-12
    ////EditDate:2010-03-12
    ////MainContent:Findings,Ajax And So On;
    //////////////////////////////////////////////////////////////////////////////////////////////////
    var excelFileName=""; //本地EXCEL地址
    var oWB;
    var idTmr="";

    function AutomateExcel3()
    {
        //删除PT表的除前2行的所有行
        DeleteTablePT();
        //alert(pic_path+document.getElementById("ImgUrl").value);//测试
        var xls=null;
        var newBook;
        try{
             xls = new ActiveXObject("Excel.Application");
            }catch(e){openBg(0);openTS(0);
            alert("请您打开ActiveX控件,具体操作:单击IE中的“工具”里“Internet 选项”,单击“安全”中的“自定义级别”中“ActiveX控件和插件”中,请选择第三项为启用即可。详细错误:"+e.message);
            return ;
        }
        if (xls == null){openBg(0);openTS(0);
         alert("创建Excel文件失败,可能是您的计算机上没有正确安装Microsoft Office Excel软件或浏览器的安全级别设置过高!");
         return;
        }
        try{
             newBook = xls.Workbooks.Open(excelFileName);//这里的Add方法里的参数是模板的路径
             }catch(e){openBg(0);openTS(0);
            alert("加载的Excel文件路径有错误!详细:"+e.message);
            return ;
        }
        var  oSheet=newBook.ActiveSheet;
        ////////////////////////////////////////////模板写入数据到WEB页面中////////////////////////////////////////////////////////////
        var xhnum=0;
        //alert("oSheet.Cells(3,18).value="+oSheet.Cells(3,18).value+"|"+(oSheet.Cells(3,5).value=="<30")+"|oSheet.Cells(3,20).value="+oSheet.Cells(3,20).value);
        for(var i=3;i<108;i++)
        {
       
            if(oSheet.Cells(i,1).value)
            {
                xhnum++;
                var newRow = document.getElementById("PT").insertRow(-1);
                var newCell;
                newRow.align = "center";
                newRow.height = "35px";
                for(var j=0;j<28;j++)
                {
                    if(j==4)
                    {
                        //什么都不做
                    }
                    else if(j<4)
                    {
                        newCell = newRow.insertCell(j);
                        if(j==0) newCell.innerHTML = (xhnum).toString();//序号
                        else if(j==1)
                        {
                            if(!oSheet.Cells(i,1).value)
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,1).value=="——"||oSheet.Cells(i,1).value=="--")
                                newCell.innerHTML = "&nbsp;";
                            else
                                newCell.innerHTML = (oSheet.Cells(i,1).value==""?"&nbsp;":oSheet.Cells(i,1).value);
                        }
                        else if(j==2)
                        {
                            if(!oSheet.Cells(i,2).value)
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,2).value=="——"||oSheet.Cells(i,2).value=="--")
                                newCell.innerHTML = "&nbsp;";
                            else
                                newCell.innerHTML = (oSheet.Cells(i,2).value==""?"&nbsp;":oSheet.Cells(i,2).value);
                            //newCell.innerHTML = (oSheet.Cells(i,2).value==""?"&nbsp;":oSheet.Cells(i,2).value);
                        }
                        else if(j==3)
                        {
                            if(!oSheet.Cells(i,4).value)
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,4).value=="——"||oSheet.Cells(i,4).value=="--")
                                newCell.innerHTML = "&nbsp;";
                            else
                                newCell.innerHTML = (oSheet.Cells(i,4).value==""?"&nbsp;":oSheet.Cells(i,4).value);
                            //newCell.innerHTML = (oSheet.Cells(i,4).value==""?"&nbsp;":oSheet.Cells(i,4).value);
                        }
                        else if(j==9)
                        {
                            if(!oSheet.Cells(i,j+1).value)
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,j+1).value=="<30")
                                newCell.innerHTML = "30";
                            else if(oSheet.Cells(i,j+1).value=="——"||oSheet.Cells(i,j+1).value=="--")
                                newCell.innerHTML = "&nbsp;";
                            else
                                newCell.innerHTML = (oSheet.Cells(i,j+1).value==""?"&nbsp;":oSheet.Cells(i,j+1).value);
                        }
                        else
                        {
                            if(!oSheet.Cells(i,j+1).value)
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,j+1).value=="<30")
                                newCell.innerHTML = "30";
                            else if(oSheet.Cells(i,j+1).value=="<DL")
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,j+1).value=="——"||oSheet.Cells(i,j+1).value=="--")
                                newCell.innerHTML = "&nbsp;";
                            else
                                newCell.innerHTML = (oSheet.Cells(i,j+1).value==""?"&nbsp;":oSheet.Cells(i,j+1).value);
                        }
                    }
                    else
                    {
                        newCell = newRow.insertCell(j-1);//少一个就可以了,少样品编号插入
                        if(j==0) newCell.innerHTML = (xhnum).toString();//序号
                        else if(j==1)
                        {
                            if(!oSheet.Cells(i,1).value)
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,1).value=="——"||oSheet.Cells(i,1).value=="--")
                                newCell.innerHTML = "&nbsp;";
                            else
                                newCell.innerHTML = (oSheet.Cells(i,1).value==""?"&nbsp;":oSheet.Cells(i,1).value);
                        }
                        else if(j==2)
                        {
                            if(!oSheet.Cells(i,2).value)
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,2).value=="——"||oSheet.Cells(i,2).value=="--")
                                newCell.innerHTML = "&nbsp;";
                            else
                                newCell.innerHTML = (oSheet.Cells(i,2).value==""?"&nbsp;":oSheet.Cells(i,2).value);
                            //newCell.innerHTML = (oSheet.Cells(i,2).value==""?"&nbsp;":oSheet.Cells(i,2).value);
                        }
                        else if(j==3)
                        {
                            if(!oSheet.Cells(i,4).value)
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,4).value=="——"||oSheet.Cells(i,4).value=="--")
                                newCell.innerHTML = "&nbsp;";
                            else
                                newCell.innerHTML = (oSheet.Cells(i,4).value==""?"&nbsp;":oSheet.Cells(i,4).value);
                            //newCell.innerHTML = (oSheet.Cells(i,4).value==""?"&nbsp;":oSheet.Cells(i,4).value);
                        }
                        else if(j==9)
                        {
                            if(!oSheet.Cells(i,j+1).value)
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,j+1).value=="<30")
                                newCell.innerHTML = "30";
                            else if(oSheet.Cells(i,j+1).value=="——"||oSheet.Cells(i,j+1).value=="--")
                                newCell.innerHTML = "&nbsp;";
                            else
                                newCell.innerHTML = (oSheet.Cells(i,j+1).value==""?"&nbsp;":oSheet.Cells(i,j+1).value);
                        }
                        else
                        {
                            if(!oSheet.Cells(i,j+1).value)
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,j+1).value=="<30")
                                newCell.innerHTML = "30";
                            else if(oSheet.Cells(i,j+1).value=="<DL")
                                newCell.innerHTML = "&nbsp;";
                            else if(oSheet.Cells(i,j+1).value=="——"||oSheet.Cells(i,j+1).value=="--")
                                newCell.innerHTML = "&nbsp;";
                            else
                                newCell.innerHTML = (oSheet.Cells(i,j+1).value==""?"&nbsp;":oSheet.Cells(i,j+1).value);
                        }
                    }
                }
            }
        }
        ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
        newBook.Close();
        newBook=null;
        xls = null;  
        openBg(0);openTS(0);
        ///////////////////////////////开启编辑和保存按钮功能///////////////////////////////////
        OnOrOffBtn(1);
        ////////////////////////////////////////////////////////////////////////////////////////
        idTmr = window.setInterval("Cleanup();",1);
        ////////////////////////////////表格数字和字符转化//////////////////////////////////////
        TableIntoDatas();////表格数字和字符转化
        ////////////////////////////////////////////////////////////////////////////////////////
    }

     其实做报表很痛苦,但是从中也能经历不少。很高兴以此文章让那些报表高手前来指导和拍砖。谢谢了!

     以上为针对excel模板总结的一些方法,欢迎各位前来拍砖!也欢迎大家来交流交流!

     

    作者:LiFuyun
    出处:http://lifuyun.cnblogs.com
    本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
  • 相关阅读:
    算法作业实验三
    牛客练习赛53 B 美味果冻
    牛客练习赛53 C 富豪凯匹配串
    bitmat
    牛客挑战赛33 B 鸽天的放鸽序列
    树状数组
    线段树
    2019牛客国庆集训派对day7 A 2016
    背包
    作业三 -并查集
  • 原文地址:https://www.cnblogs.com/bicabo/p/1708387.html
Copyright © 2020-2023  润新知