JS操作Excel讀取和寫入(模板操作)

1、JS操作Excel讀取和寫入（模板操作）            前一段時間一直在做報表，所以肯定會用到Excel的操作，但是在網(wǎng)上查閱資料有關(guān)JS操作excel較少，有的話，也都是老生常談或很零碎的一些東西。本人是在實(shí)際項目中摸索出，JS讀寫Excel（模板）數(shù)據(jù)，包括怎么用JS把圖片插入Excel中。            首先，添加一個公用的模板地址JS，如下：A

2、ddZDaddress.js/DocName:信任站點(diǎn)地址添加-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 導(dǎo)入到WEB界面模板地址集合*/飛行檢測excel模板地址配置/var template_path_FxjcExcelDatasIntoSqlDatas = &q

3、uot;http:/%22+believeaddress+%22/NewReports/xls_template/飛行檢測城鎮(zhèn)污水廠進(jìn)出水主要污染物濃度.xls"/*/ /*Excel 錄入模板導(dǎo)入地址集合*/月報導(dǎo)入excel地址配置/var template_path_month = "http:/%22+believeaddress+%22/NewReports/xls_template/上海市污水處理企業(yè)生產(chǎn)運(yùn)行表.xls"/年報導(dǎo)入excel地址配置/var template_path_year = "http:/%22+believ

4、eaddress+%22/NewReports/xls_template/上海市污水處理設(shè)施信息表.xls"/*/圖片地址var pic_path="http:/%22+believeaddress+%22/NewReports/"/              再次，怎么樣把頁面數(shù)據(jù)讀取到Excel中JS，如下：YearReportLuRu.jsYearReportLuRu.js/DocName:污水處理設(shè)施信息表(年報)-錄入報表-Y

5、earReportLuRu.js/Author:lify/Company:Wavenet/Date:2009-08-14/EditDate:2009-08-14/MainContent:Findings,Ajax And So On;/添加正則表達(dá)式類/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方法里的參數(shù)是模板的路徑    var  oSheet=newBook.ActiveSheet;/寫入數(shù)據(jù)到模板中/    /法人單位名稱/    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);    /進(jìn)出口三張表/    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);    /年運(yùn)行簡況/    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);    /-運(yùn)行費(fèi)用分析-/    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模板中數(shù)據(jù)到頁面上共客戶編輯數(shù)據(jù)再保存到數(shù)據(jù)庫中（此步可以用xml發(fā)送至后臺交互，在此不進(jìn)一步說明）JS，如下： Fxjc_ExcelDatasIntoSqlDatas.js/DocName:導(dǎo)入模板數(shù)據(jù)功能-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控件和插件”中，請選擇第三項為啟用即可。詳細(xì)錯誤："+e.message);        return ;         if (xls = null)openBg(0);openTS(0);     alert("創(chuàng)建Excel文件失敗,可能是您的計算機(jī)上沒有正確安裝Micros

36、oft Office Excel軟件或瀏覽器的安全級別設(shè)置過高!");     return;        try         newBook = xls.Workbooks.Open(excelFileName);/這里的Add方法里的參數(shù)是模板的路徑         catch(e)openBg(0);openTS(0

37、);        alert("加載的Excel文件路徑有錯誤!詳細(xì)："+e.message);        return ;         var  oSheet=newBook.ActiveSheet;    /模板寫入數(shù)據(jù)到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