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

1、JS操作Excel读取和写入（模板操作）            前一段时间一直在做报表，所以肯定会用到Excel的操作，但是在网上查阅资料有关JS操作excel较少，有的话，也都是老生常谈或很零碎的一些东西。本人是在实际项目中摸索出，JS读写Excel（模板）数据，包括怎么用JS把图片插入Excel中。            首先，添加一个公用的模板地址JS，如下：A

2、ddZDaddress.js/DocName:信任站点地址添加-AddZDaddress.js/Author:lify/Company:Wavenet/Date:2009-11-04/EditDate:2010-03-11/MainContent:Findings,Ajax And So On;/var BelieveAddress='5/psc1'/配置地址配置程序时需要的配置地址/*Excel 导入到WEB界面模板地址集合*/飞行检测excel模板地址配置/var template_path_FxjcExcelDatasIntoSqlDatas = &q

3、uot;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+believ

4、eaddress+%22/NewReports/xls_template/上海市污水处理设施信息表.xls"/*/图片地址var pic_path="http:/%22+believeaddress+%22/NewReports/"/              再次，怎么样把页面数据读取到Excel中JS，如下：YearReportLuRu.jsYearReportLuRu.js/DocName:污水处理设施信息表(年报)-录入报表-Y

5、earReportLuRu.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>");/ fun

6、ction 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);/

7、这里的Add方法里的参数是模板的路径    var  oSheet=newBook.ActiveSheet;/写入数据到模板中/    /法人单位名称/    oSheet.Cells(2,3).value = Trim(PT.rows0.cells1.innerText);    /法人单位代码以及代表姓名/    oSheet.Cells(2,9).value = Trim(PT.rows0.cells3.innerText);&#

8、160;   oSheet.Cells(2,13).value = Trim(PT.rows0.cells5.innerText);    /进出口三张表/    oSheet.Cells(3,2).value=Trim(PT.rows1.cells1.innerText);    oSheet.Cells(3,6).value=Trim(PT.rows1.cells3.innerText);    oSheet.Cells(3,8).value=Trim

9、(PT.rows1.cells5.innerText);    oSheet.Cells(3,10).value=Trim(PT.rows1.cells7.innerText);    oSheet.Cells(3,13).value=Trim(PT.rows1.cells9.innerText);        oSheet.Cells(4,2).value=Trim(PT.rows2.cells1.innerText);    oS

10、heet.Cells(4,4).value=Trim(PT.rows2.cells3.innerText);    oSheet.Cells(4,6).value=Trim(PT.rows2.cells5.innerText);    oSheet.Cells(4,8).value=Trim(PT.rows2.cells7.innerText);    oSheet.Cells(4,10).value=Trim(PT.rows2.cells9.innerText);    o

11、Sheet.Cells(4,12).value=Trim(PT.rows2.cells11.innerText);    oSheet.Cells(4,14).value=Trim(PT.rows2.cells13.innerText);        oSheet.Cells(5,2).value=Trim(PT.rows3.cells1.innerText);    oSheet.Cells(5,5).value=Trim(PT.rows3.cells3.inn

12、erText);    oSheet.Cells(5,8).value=Trim(PT.rows3.cells5.innerText);    oSheet.Cells(5,10).value=Trim(PT.rows3.cells7.innerText);    oSheet.Cells(5,12).value=Trim(PT.rows3.cells9.innerText);    oSheet.Cells(5,14).value=Trim(PT.rows3.cells11

13、.innerText);    /年运行简况/    oSheet.Cells(6,4).value = Trim(PT.rows4.cells3.innerText);    oSheet.Cells(6,6).value = Trim(PT.rows4.cells5.innerText);    oSheet.Cells(6,8).value = Trim(PT.rows4.cells7.innerText);    oSheet.Cells

14、(6,10).value = Trim(PT.rows4.cells9.innerText);    oSheet.Cells(6,12).value = Trim(PT.rows4.cells11.innerText);    oSheet.Cells(6,14).value = Trim(PT.rows4.cells13.innerText);        oSheet.Cells(7,4).value = Trim(PT.rows5.cells2.inner

15、Text);    oSheet.Cells(7,6).value = Trim(PT.rows5.cells4.innerText);    oSheet.Cells(7,8).value = Trim(PT.rows5.cells6.innerText);    oSheet.Cells(7,10).value = Trim(PT.rows5.cells8.innerText);    oSheet.Cells(7,12).value = Trim(PT.rows5.ce

16、lls10.innerText);    oSheet.Cells(7,14).value = Trim(PT.rows5.cells12.innerText);        /年处理水量/    oSheet.Cells(8,2).value = Trim(PT.rows6.cells1.innerText);    oSheet.Cells(8,5).value = Trim(PT.rows6.cells3.innerText);

17、    oSheet.Cells(8,8).value = Trim(PT.rows6.cells5.innerText);    oSheet.Cells(8,11).value = Trim(PT.rows6.cells7.innerText);    oSheet.Cells(8,14).value = Trim(PT.rows6.cells9.innerText);        /污水厂工艺概况/  

18、60; oSheet.Cells(10,3).value = Trim(PT.rows8.cells1.innerText);    oSheet.Cells(10,4).value = Trim(PT.rows8.cells2.innerText);    oSheet.Cells(10,5).value = Trim(PT.rows8.cells3.innerText);    oSheet.Cells(10,7).value = Trim(PT.rows8.cells4.innerText);

19、60;       oSheet.Cells(11,3).value = Trim(PT.rows9.cells1.innerText);    oSheet.Cells(11,4).value = Trim(PT.rows9.cells2.innerText);    oSheet.Cells(11,5).value = Trim(PT.rows9.cells3.innerText);    oSheet.Cells(11,7).value =

20、 Trim(PT.rows9.cells4.innerText);        oSheet.Cells(12,3).value = Trim(PT.rows10.cells1.innerText);    oSheet.Cells(12,4).value = Trim(PT.rows10.cells2.innerText);    oSheet.Cells(12,5).value = Trim(PT.rows10.cells3.innerText); 

21、   oSheet.Cells(12,7).value = Trim(PT.rows10.cells4.innerText);    /-运行费用分析-/    oSheet.Cells(13,3).value = Trim(PT.rows11.cells2.innerText);    oSheet.Cells(13,5).value = Trim(PT.rows11.cells4.innerText);    oSheet.Cells(13,7).va

22、lue = Trim(PT.rows11.cells6.innerText);    oSheet.Cells(13,9).value = Trim(PT.rows11.cells8.innerText);    /厂外输送泵站示意图/    /copy(document.getElementById("ImgUrl");    /oSheet.Pictures.Insert(pic_path+document.getElementById("I

23、mgUrl").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).

24、select();/选中excel的单元格    /oSheet.Pictures.Insert(PT.rows11.cells8.getElementById("ImgUrl").src);    /        oSheet.Cells(14,3).value = Trim(PT.rows12.cells1.innerText);    oSheet.Cells(14,5).value = Trim(PT.ro

25、ws12.cells3.innerText);    oSheet.Cells(14,7).value = Trim(PT.rows12.cells5.innerText);    oSheet.Cells(14,9).value = Trim(PT.rows12.cells7.innerText);        oSheet.Cells(15,3).value = Trim(PT.rows13.cells1.innerText);  

26、0; oSheet.Cells(15,5).value = Trim(PT.rows13.cells3.innerText);    oSheet.Cells(15,7).value = Trim(PT.rows13.cells5.innerText);    oSheet.Cells(15,9).value = Trim(PT.rows13.cells7.innerText);        oSheet.Cells(16,5).value = Trim(PT.r

27、ows14.cells1.innerText);    oSheet.Cells(16,7).value = Trim(PT.rows14.cells3.innerText);    oSheet.Cells(16,9).value = Trim(PT.rows14.cells5.innerText);        oSheet.Cells(17,3).value = Trim(PT.rows15.cells1.innerText);  

28、60; oSheet.Cells(17,5).value = Trim(PT.rows15.cells3.innerText);    oSheet.Cells(17,7).value = Trim(PT.rows15.cells5.innerText);    /-/    /污水厂表下方信息/    oSheet.Cells(18,2).value = Trim(RT.rows0.cells1.innerText);    oSheet.Ce

29、lls(18,7).value = Trim(RT.rows0.cells3.innerText);    oSheet.Cells(18,11).value = Trim(RT.rows0.cells5.innerText);    oSheet.Cells(18,14).value = Trim(RT.rows0.cells7.innerText);/    xls.Visible = true;    xls.UserControl = true;  

30、;  xls = null;       idTmr = window.setInterval("Cleanup();",1);      /xls.quit();function Cleanup()             window.clearInterval(idTmr);     

31、;        CollectGarbage();    function copy(tabid) var oControlRange = document.body.createControlRange(); oControlRange.add(tabid,0); oControlRange.select(); document.execCommand("Copy");  /  &#

32、160;           最后，如何读取到Excel模板中数据到页面上共客户编辑数据再保存到数据库中（此步可以用xml发送至后台交互，在此不进一步说明）JS，如下： Fxjc_ExcelDatasIntoSqlDatas.js/DocName:导入模板数据功能-Fxjc_ExcelDatasIntoSqlDatas.js/Author:lify/Company:Wavenet/Date:2010-03-12/EditDate:2010-03-12/MainContent:Findi

33、ngs,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

34、=null;    var newBook;    try         xls = new ActiveXObject("Excel.Application");        catch(e)openBg(0);openTS(0);        alert("请您打开ActiveX

35、控件，具体操作：单击IE中的“工具”里“Internet 选项”，单击“安全”中的“自定义级别”中“ActiveX控件和插件”中，请选择第三项为启用即可。详细错误："+e.message);        return ;         if (xls = null)openBg(0);openTS(0);     alert("创建Excel文件失败,可能是您的计算机上没有正确安装Micros

36、oft Office Excel软件或浏览器的安全级别设置过高!");     return;        try         newBook = xls.Workbooks.Open(excelFileName);/这里的Add方法里的参数是模板的路径         catch(e)openBg(0);openTS(0

37、);        alert("加载的Excel文件路径有错误!详细："+e.message);        return ;         var  oSheet=newBook.ActiveSheet;    /模板写入数据到WEB页面中/    var xhnum=0; 

38、60;  /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+)         

39、0;      if(oSheet.Cells(i,1).value)                    xhnum+;            var newRow = document.getElementById("PT").ins

40、ertRow(-1);            var newCell;            newRow.align = "center"            newRow.height = "35px"  

41、;          for(var j=0;j<28;j+)                            if(j=4)         &#

42、160;                          /什么都不做                         

43、;       else if(j<4)                                    newCell = newRow.insertCell(j); 

44、0;                  if(j=0) newCell.innerHTML = (xhnum).toString();/序号                    else if(j=1)   

45、                                         if(!oSheet.Cells(i,1).value)      

46、                      newCell.innerHTML = "&nbsp;"                      

47、;  else if(oSheet.Cells(i,1).value=""|oSheet.Cells(i,1).value="-")                            newCell.innerHTML = "&nbsp;"

48、0;                       else                           

49、; newCell.innerHTML = (oSheet.Cells(i,1).value=""?"&nbsp;":oSheet.Cells(i,1).value);                                 

50、;       else if(j=2)                                          

51、60; if(!oSheet.Cells(i,2).value)                            newCell.innerHTML = "&nbsp;"          

52、60;             else if(oSheet.Cells(i,2).value=""|oSheet.Cells(i,2).value="-")                       

53、60;    newCell.innerHTML = "&nbsp;"                        else               

54、60;            newCell.innerHTML = (oSheet.Cells(i,2).value=""?"&nbsp;":oSheet.Cells(i,2).value);                    

55、0;   /newCell.innerHTML = (oSheet.Cells(i,2).value=""?"&nbsp;":oSheet.Cells(i,2).value);                              

56、60;         else if(j=3)                                        &

57、#160;   if(!oSheet.Cells(i,4).value)                            newCell.innerHTML = "&nbsp;"        &

58、#160;               else if(oSheet.Cells(i,4).value=""|oSheet.Cells(i,4).value="-")                     &

59、#160;      newCell.innerHTML = "&nbsp;"                        else             &

60、#160;              newCell.innerHTML = (oSheet.Cells(i,4).value=""?"&nbsp;":oSheet.Cells(i,4).value);                  &#

61、160;     /newCell.innerHTML = (oSheet.Cells(i,4).value=""?"&nbsp;":oSheet.Cells(i,4).value);                            &

62、#160;           else if(j=9)                                      

63、;      if(!oSheet.Cells(i,j+1).value)                            newCell.innerHTML = "&nbsp;"     

64、60;                  else if(oSheet.Cells(i,j+1).value="<30")                       

65、60;    newCell.innerHTML = "30"                        else if(oSheet.Cells(i,j+1).value=""|oSheet.Cells(i,j+1).value="-")   

66、                         newCell.innerHTML = "&nbsp;"                   

67、;     else                            newCell.innerHTML = (oSheet.Cells(i,j+1).value=""?"&nbsp;":oSheet.Cells(i,j+1).valu

68、e);                                        else          

69、60;                                 if(!oSheet.Cells(i,j+1).value)             &

70、#160;              newCell.innerHTML = "&nbsp;"                        else if(oSheet.Cells(i,j+1).value=&

71、quot;<30")                            newCell.innerHTML = "30"              &

72、#160;         else if(oSheet.Cells(i,j+1).value="<DL")                            newCell.innerHTML = "&

73、;nbsp;"                        else if(oSheet.Cells(i,j+1).value=""|oSheet.Cells(i,j+1).value="-")          &#

74、160;                 newCell.innerHTML = "&nbsp;"                        else  &#

75、160;                         newCell.innerHTML = (oSheet.Cells(i,j+1).value=""?"&nbsp;":oSheet.Cells(i,j+1).value);       

76、;                                             else      

77、0;                             newCell = newRow.insertCell(j-1);/少一个就可以了,少样品编号插入              &

78、#160;     if(j=0) newCell.innerHTML = (xhnum).toString();/序号                    else if(j=1)               

79、60;                            if(!oSheet.Cells(i,1).value)                  

80、60;         newCell.innerHTML = "&nbsp;"                        else if(oSheet.Cells(i,1).value=""|oSheet.Cells(i,1).valu

81、e="-")                            newCell.innerHTML = "&nbsp;"             &

82、#160;          else                            newCell.innerHTML = (oSheet.Cells(i,1).value=""?"&nbsp

83、;":oSheet.Cells(i,1).value);                                        else if(j=2)    &

84、#160;                                       if(!oSheet.Cells(i,2).value)       &

85、#160;                    newCell.innerHTML = "&nbsp;"                       

86、 else if(oSheet.Cells(i,2).value=""|oSheet.Cells(i,2).value="-")                            newCell.innerHTML = "&nbsp;"  

87、;                      else                            newCe

88、ll.innerHTML = (oSheet.Cells(i,2).value=""?"&nbsp;":oSheet.Cells(i,2).value);                        /newCell.innerHTML = (oSheet.Cells(i,2).value=""?&

89、quot;&nbsp;":oSheet.Cells(i,2).value);                                        else if(j=3)  

90、;                                          if(!oSheet.Cells(i,4).value)     

91、;                       newCell.innerHTML = "&nbsp;"                    

92、0;   else if(oSheet.Cells(i,4).value=""|oSheet.Cells(i,4).value="-")                            newCell.innerHTML = "&nbsp;&qu

93、ot;                        else                         

94、0;  newCell.innerHTML = (oSheet.Cells(i,4).value=""?"&nbsp;":oSheet.Cells(i,4).value);                        /newCell.innerHTML = (oSheet.Cells(i,4).value=

95、""?"&nbsp;":oSheet.Cells(i,4).value);                                        else if(j

96、=9)                                            if(!oSheet.Cells(i,j+1).value)  &

97、#160;                         newCell.innerHTML = "&nbsp;"                  

98、      else if(oSheet.Cells(i,j+1).value="<30")                            newCell.innerHTML = "30"   &

99、#160;                    else if(oSheet.Cells(i,j+1).value=""|oSheet.Cells(i,j+1).value="-")               

100、60;            newCell.innerHTML = "&nbsp;"                        else