Reading Table Data into Excel file.

[Vivek Indripala]

Purpose:
To Read Table data to excel.

Pre-Requisites:
1.Load wsoffice library load('wsoffice.dll');


Liquid UI Code :

/////////////////////////////////// SAPLSMTR_NAVIGATION.E0100 ////////////////////////////////


load('wsoffice.dll');
del("X[IMAGE_CONTAINER]"); 
inputfield( [1,2], "Order", [1,22],{ "name":"z_message_info","shname":"VBELN", "size":16, "searchhelp":"VMVA"});
pushbutton([1,45], "Get Data to Excel","?",{  "process":z_AssigntoSAPTbale,"size":[1,15],"using":{"z_message_info":z_message_info}});


table([5,5],[15,45],{"name":"va01_AllItems","title":"All items", "rows":10, "rowselection":true,"columnselection":true});
column('Item',{"table":"va01_AllItems","size":4,"name":"z_va01_item","position":1});
column('Material',{"table":"va01_AllItems","size":15,"name":"z_va01_material","position":2});
column('Order Quantity',{"table":"va01_AllItems","size":15,"name":"z_va01_Orderquantity","position":3});


function z_AssigntoSAPTbale(param){
 
   println(param.z_message_info); 
   temp_items=[];
   temp_material=[];
   temp_quantity=[];
   enter('/nva02');

   onscreen 'SAPMV45A.0102'
      set('F[Order]', "&V[z_message_info]");
      enter();
   
   
   onscreen 'SAPMSDYP.0010'
      enter();
   
   onscreen 'SAPMSDYP.0010'
      enter();
   
   onscreen 'SAPMV45A.4001'
      absrow = 1;
      relrow = 1;   
      gettableattribute("T[All items]", {"firstvisiblerow":"FVisRow", "lastvisiblerow":"LVisRow", "lastrow":"LastRow"});
      if(FVisRow==1) {
         goto new_row;
      }   
      enter("/ScrollToLine=&V[absrow]", {"table":"T[All items]"});       
      new_screen:;
   
   onscreen 'SAPMV45A.4001'   
      gettableattribute("T[All items]", {"firstvisiblerow":"FVisRow", "lastvisiblerow":"LVisRow", "lastrow":"LastRow"});   relrow = 1;
      new_row:;
     if(absrow>LVisRow){
         enter("/ScrollToLine=&V[absrow]", {"table":"T[All items]"});
         goto new_screen;
      }
      if(absrow>LastRow){
         goto end_of_table;
      }
                                                                  
      set("V[z_va01_item]","&cell[All items,Item,&V[relrow]]");
      set("V[z_va01_mat]","&cell[All items,Material,&V[relrow]]");
      set("V[z_va01_OQ]","&cell[All items,Order Quantity,&V[relrow]]");
      println('The item Data is '+z_va01_item+':');
      println('The Material Data is '+z_va01_mat+':');
      println('The Order Quantity Data is '+z_va01_OQ+':');                
      temp_items.push(z_va01_item);
      temp_material.push(z_va01_mat);
      temp_quantity.push(z_va01_OQ);   
   
     
      absrow++;
      relrow++;
      goto new_row;
      end_of_table:;     
      enter("/ScrollToLine=1", {"table":"T[All items]"});    
      enter("/n");    
   
   
   onscreen 'SAPLSMTR_NAVIGATION.0100'   
      gettableattribute("T[va01_AllItems]", {"firstvisiblerow":"FisRow", "lastvisiblerow":"LisRow", "lastrow":"LatRow"});   
      var z=1;
   
      for(var m=0;m<=temp_items.length;m++)
      {             
         va01_AllItems.z_va01_item[m] = temp_items[m];
         va01_AllItems.z_va01_material[m] = temp_material[m];
         va01_AllItems.z_va01_Orderquantity[m] = temp_quantity[m];         
         z=z+1;

      }        
      copy_To_Excel(temp_items,temp_material,temp_quantity)
      enter("?"); 
}

 function copy_To_Excel(temp_items,temp_material,temp_quantity)
{
   
   var ExcelApp = new ActiveXObject("Excel.Application");
   var ExcelSheet = new ActiveXObject("Excel.Sheet");   
   ExcelSheet.ActiveSheet.Cells(1,1).Value = "Items";
   ExcelSheet.ActiveSheet.Cells(1,2).Value = "Materials";
   ExcelSheet.ActiveSheet.Cells(1,3).Value = "Quantity";         
   var k=0;
   for(p=2;p<temp_items.length;p++) {     
      ExcelSheet.ActiveSheet.Cells(p,1).Value = temp_items[k];
      ExcelSheet.ActiveSheet.Cells(p,2).Value = temp_material[k];
      ExcelSheet.ActiveSheet.Cells(p,3).Value = temp_quantity[k];     
      k=k+1;
   }   
   var str = "C:\\LiquidUI\\scripts\\TEST.XLS";     
   var fso = new ActiveXObject("Scripting.FileSystemObject");   
   if(fso.FileExists(str)){     
      message("E:FILE ALREADY EXISTS PLEASE REMOVE OLD FILE");
   }
   else{     
      ExcelSheet.SaveAs(str);   
      ExcelSheet.Application.Quit();

   }     
}