js操作Excel

最新推荐文章于 2023-12-04 13:09:28 发布

xjinza

最新推荐文章于 2023-12-04 13:09:28 发布

阅读量493

点赞数

分类专栏： javascript

javascript 专栏收录该内容

56 篇文章 0 订阅

订阅专栏

<html>
     <head><title>456</title></head>
     <body>
         <div>
             <input type="file" name="fileChose" id="file"/>
             <input type="button" value="打开" οnclick="testX()"/>
             <input type="button" value="创建表格" οnclick="create()"/>
         </div>
         
 <script type="text/javascript">
     
     var names = new Array('userName','userPwd','email','mobile','address');
     var vls1 = new Array();      
     var currentCell=0;
     var ids = new Array();
     //创建表单
     function create()
     {
         
         var html="<table>";
         for(var i=0;i<names.length;i++)
         {
             html+="<tr><td>"+names[i]+"</td><td><input type='txt' id='id"+i+"'/></td></tr>";
             ids[i]="id"+i;
         }
         html+="</table><div><input  type='submit' value='下一条' οnclick='nextData()'/>";
         html+="<input  type='button' value='保存' οnclick='createExcel()'/></div>";
         var newRow = document.getElementById("rs2");
         newRow.innerHTML="填写表单:<p>"+html;
     }
     //下一条
     function nextData()
     {
        var vl = new Array();
       for(var i=0;i<ids.length;i++)
       {
           var pt = document.getElementById(ids[i]);
           vl[i]= pt.value;
       //    pt.value="";
           
       }
       vls1[currentCell++]=vl;
       document.getElementById(ids[0]).value="";
       document.getElementById(ids[1]).value="";
           document.getElementById(ids[0]).focus();
     }
     
     //建表
     function createExcel()
   {
       
     try{
         
         var xls = new ActiveXObject("Excel.Application"); 
             xls.visible = false; 
             //var newBook = xls.Workbooks.Add; 
             var file=document.getElementById("file");
         var filePath=file.value;
             var newBook = xls.Workbooks.open(filePath);
             var sheet =newBook.Worksheets(1);
         //    newBook.Worksheets.Add; 
             sheet.Activate; 
             xls.ActiveWorkBook.ActiveSheet.PageSetup.Orientation = 2; 
             xls.ActiveWorkBook.ActiveSheet.PageSetup.PaperSize = 5; 
             //sheet.Columns("A").columnwidth=50; 
             sheet.Columns("A").WrapText = true; 
         //    sheet.Columns("B").columnwidth=50; 
             sheet.Columns("B").WrapText = true; 
             //sheet.Range("A1:B1000").NumberFormat = "0"; 
             //sheet.Range("A1:B1000").HorizontalAlignment = -4131; 
             for(var i=1;i<=names.length;i++)
             {
                 sheet.Cells(1,i).Interior.ColorIndex="15";
                 sheet.Cells(1,i).value=names[i-1];
             }
             var columscount = sheet.Columns.count;
             var startColum=0;
             //找到追加的起始行
             for(var k=1;k<columscount;k++)
           {
                     startColum++;
                     if(!sheet.Cells(k,1).value)
                     {
                        break;       
                     }
            }
           for(var i=1;i<=vls1.length;i++)
           {
               var values = vls1[i-1];
               for(var j=1;j<=values.length;j++)
               {
                   sheet.Cells(startColum,j).value=values[j-1];
               }
               startColum++;
           }
             currentCell=0;//保存后清零
             //sheet.Close(savechanges=true);
             //newBook.Close(savechanges=true);
             try
             {
                 sheet.Application.Quit();
           //xls.Quit();
           newBook=null;
           xls = null;
             }catch(e){}
         //alert("保存成功");   
         //ExcelSheet.SaveAs("C:\\TEST.XLS");
             //sheet.Name="体检表"
             
     }catch(e)
     {
         alert(e.message);
         return;
     }
   }
     //读取表格
     function testX()
     {
          var xls=null;
      var newBook = null;
      var file=document.getElementById("file");
      var filePath=file.value;
          try{
              xls = new ActiveXObject("Excel.Application");
              xls.DisplayAlerts = false;
              if(xls!=null)
              {
                  newBook = xls.Workbooks.Open(filePath);   
                  var  mySheet=newBook.ActiveSheet;
                     var cs = mySheet.Columns.count;
                     var rs = mySheet.Rows.count; 
                         cs=cs>20?20:cs;
                         rs=rs>50?50:rs;
                     var tb = getSheet(rs,cs,mySheet);
                     var newRow = document.getElementById("rs");
                       newRow.innerHTML="以下是表格数据:"+tb; 
                       mySheet.Application.Quit();
                       //xls.Quit();
                       //newBook.Close();
                       mySheet=null;
                     newBook=null;
                     xls = null;     
                    // alert("表单关闭");
              }
             }catch(e){
             alert(e.message);
         } 
         
     }
     //遍历一个sheet
     function getSheet(rows,colums,oSheet)
     {
         var str="<table border='1'><tr>";
         try{
         for(var i=1;i<rows;i++){
             
             for(var j=1;j<colums;j++)
             {
               var vl = oSheet.Cells(i,j);
               if(!vl.value)
               {
                   vl="\t";
               }
                 str+="<td>"+vl+"</td>";
             }
             str+="<tr>";
         }
     }catch(e){
         alert(e.message);
     }
     str+="</tr><table>";
     return str;
     }
     
 </script>
 <div id="fm"></div>
 <div id="rs"></div>
 <div id="rs2"></div>
 </body>
 </html>

转自: http://www.cnblogs.com/czpblog/archive/2012/09/06/js%E6%93%8D%E4%BD%9Cexcel.html