js操作Excel

最新推荐文章于 2024-01-29 09:03:51 发布

weixin_30681121

最新推荐文章于 2024-01-29 09:03:51 发布

阅读量58

点赞数

文章标签： javascript ViewUI

原文链接：http://www.cnblogs.com/czsl/archive/2012/09/06/2674228.html

版权

<HTML>

<BODY>

<div>
5             <input type="file" name="fileChose" id="file"/>
6             <input type="button" value="打开" οnclick="testX()"/>
7             <input type="button" value="创建表格" οnclick="create()"/>
8         </div>
9
10 <script type="text/javascript">
11
12     var names = new Array('userName','userPwd','email','mobile','address');
13     var vls1 = new Array();
14     var currentCell=0;
15     var ids = new Array();
16     //创建表单
17     function create()
18     {
19
20         var html="<table>";
21         for(var i=0;i<names.length;i++)
22         {
23             html+="<tr><td>"+names[i]+"</td><td><input type='txt' id='id"+i+"'/></td></tr>";
24             ids[i]="id"+i;
25         }
26         html+="</table><div><input type='submit' value='下一条' οnclick='nextData()'/>";
27         html+="<input type='button' value='保存' οnclick='createExcel()'/></div>";
28         var newRow = document.getElementById("rs2");
29         newRow.innerHTML="填写表单:<p>"+html;
30     }
31     //下一条
32     function nextData()
33     {
34        var vl = new Array();
35       for(var i=0;i<ids.length;i++)
36       {
37           var pt = document.getElementById(ids[i]);
38           vl[i]= pt.value;
39       //    pt.value="";
40
41       }
42       vls1[currentCell++]=vl;
43       document.getElementById(ids[0]).value="";
44       document.getElementById(ids[1]).value="";
45           document.getElementById(ids[0]).focus();
46     }
47
48     //建表
49     function createExcel()
50 {
51
52     try{
53
54         var xls = new ActiveXObject("Excel.Application");
55             xls.visible = false;
56             //var newBook = xls.Workbooks.Add;
57             var file=document.getElementById("file");
58         var filePath=file.value;
59             var newBook = xls.Workbooks.open(filePath);
60             var sheet =newBook.Worksheets(1);
61         //    newBook.Worksheets.Add;
62             sheet.Activate;
63             xls.ActiveWorkBook.ActiveSheet.PageSetup.Orientation = 2;
64             xls.ActiveWorkBook.ActiveSheet.PageSetup.PaperSize = 5;
65             //sheet.Columns("A").columnwidth=50;
66             sheet.Columns("A").WrapText = true;
67         //    sheet.Columns("B").columnwidth=50;
68             sheet.Columns("B").WrapText = true;
69             //sheet.Range("A1:B1000").NumberFormat = "0";
70             //sheet.Range("A1:B1000").HorizontalAlignment = -4131;
71             for(var i=1;i<=names.length;i++)
72             {
73                 sheet.Cells(1,i).Interior.ColorIndex="15";
74                 sheet.Cells(1,i).value=names[i-1];
75             }
76             var columscount = sheet.Columns.count;
77             var startColum=0;
78             //找到追加的起始行
79             for(var k=1;k<columscount;k++)
80           {
81                     startColum++;
82                     if(!sheet.Cells(k,1).value)
83                     {
84                        break;
85                     }
86            }
87           for(var i=1;i<=vls1.length;i++)
88           {
89               var values = vls1[i-1];
90               for(var j=1;j<=values.length;j++)
91               {
92                   sheet.Cells(startColum,j).value=values[j-1];
93               }
94               startColum++;
95           }
96             currentCell=0;//保存后清零
97             //sheet.Close(savechanges=true);
98             //newBook.Close(savechanges=true);
99             try
100             {
101                 sheet.Application.Quit();
102           //xls.Quit();
103           newBook=null;
104           xls = null;
105             }catch(e){}
106         //alert("保存成功");
107         //ExcelSheet.SaveAs("C:\\TEST.XLS");
108             //sheet.Name="体检表"
109
110     }catch(e)
111     {
112         alert(e.message);
113         return;
114     }
115 }
116     //读取表格
117     function testX()
118     {
119          var xls=null;
120      var newBook = null;
121      var file=document.getElementById("file");
122      var filePath=file.value;
123          try{
124              xls = new ActiveXObject("Excel.Application");
125              xls.DisplayAlerts = false;
126              if(xls!=null)
127              {
128                  newBook = xls.Workbooks.Open(filePath);
129                  var mySheet=newBook.ActiveSheet;
130                     var cs = mySheet.Columns.count;
131                     var rs = mySheet.Rows.count;
132                         cs=cs>20?20:cs;
133                         rs=rs>50?50:rs;
134                     var tb = getSheet(rs,cs,mySheet);
135                     var newRow = document.getElementById("rs");
136                       newRow.innerHTML="以下是表格数据:"+tb;
137                       mySheet.Application.Quit();
138                       //xls.Quit();
139                       //newBook.Close();
140                       mySheet=null;
141                     newBook=null;
142                     xls = null;
143                    // alert("表单关闭");
144              }
145             }catch(e){
146             alert(e.message);
147         }
148
149     }
150     //遍历一个sheet
151     function getSheet(rows,colums,oSheet)
152     {
153         var str="<table border='1'><tr>";
154         try{
155         for(var i=1;i<rows;i++){
156
157             for(var j=1;j<colums;j++)
158             {
159               var vl = oSheet.Cells(i,j);
160               if(!vl.value)
161               {
162                   vl="\t";
163               }
164                 str+="<td>"+vl+"</td>";
165             }
166             str+="<tr>";
167         }
168     }catch(e){
169         alert(e.message);
170     }
171     str+="</tr><table>";
172     return str;
173     }
174
175 </script>
176 <div id="fm"></div>
177 <div id="rs"></div>
178 <div id="rs2"></div>

<BODY>

</HTML>

转载于:https://www.cnblogs.com/czsl/archive/2012/09/06/2674228.html

weixin_30681121

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
js操作Excel

<HTML><BODY><div> 5 <input type="file" name="fileChose" id="file"/> 6 <input type="button" value="打开" onclick="testX()"/> 7 ...
复制链接

扫一扫