CODEWITHSUNDEEP
javascriptarraysexcel
GBI
GBI

Reputation: 75

Export array of objects into Excel using Javascript

I'm writing a client side method, that creates an array of objects.I open an existing excel to write the values from the array. I get the values using getProperty and store in a variable. When I try to write those in the excel, I get "event handler failed with message";" ".

Code:

var getItemtoExcel = document.thisItem.newItem("ToExcel", "get");
getItemtoExcel = getItemtoExcel.apply();

var arrToExcel = Array();
for (var j = 0; j < getItemtoExcel.getItemCount(); j++) {
    var gotItemForExcel = getItemtoExcel.getItemByIndex(j);
    arrToExcel.push(gotItemForExcel);
}

var Excel = new ActiveXObject("Excel.Application");
Excel.Visible = true;
Excel.Workbooks.Open("C:\\test.xls");

var offset = 0;
var row = 2;
for (var c = 0; c < arrToExcel.length; c++) {
    var createExcel = arrToExcel[c];
    var Number = createExcel.getProperty("nb");
    var Type = createExcel.getProperty("type");
    var Code = createExcel.getProperty("code");
    var State = createExcel.getProperty("state");

    Excel.Worksheets("sheet11").Range("A" & row + 1 + offset).Value = Number;
    Excel.Worksheets("sheet11").Range("B" & row + 1 + offset).Value = Type;
    Excel.Worksheets("sheet11").Range("C" & row + 1 + offset).Value = Code;
    Excel.Worksheets("sheet11").Range("D" & row + 1 + offset).Value = State;
    row = row + 1;
}

offset = offset + 1;
return this;

document.thisItem.newItem() is from ARASPLM. Its the standard used to call an ItemType(Item) in ARAS

Upvotes: 4

Views: 25068

Answers (3)

Shankar ARUL
Shankar ARUL

Reputation: 13710

If you have an opportunity to use SheetJS, it's pretty straightforward

Firstly, Install xlsx package npm install --save xlsx

  const XLSX = require('xlsx')
  
  // array of objects to save in Excel
  let binary_univers = [{'name': 'Hi','value':1},{'name':'Bye','value':0}]

  let binaryWS = XLSX.utils.json_to_sheet(binary_univers); 
  
  // Create a new Workbook
  var wb = XLSX.utils.book_new() 

  // Name your sheet
  XLSX.utils.book_append_sheet(wb, binaryWS, 'Binary values') 

  // export your excel
  XLSX.writeFile(wb, 'Binaire.xlsx');

Upvotes: 14

GBI
GBI

Reputation: 75

Thanks for all your suggestions on this question. I have done with exporting the array into a .csv file successfully. Here's the code, for others who will need.

var getItemtoExcel=this.newItem("MyForm", "get");
getItemtoExcel=getItemtoExcel.apply();

var arrToExcel = Array();
for (var j=0; j<getItemtoExcel.getItemCount(); j++) 
{
var gotItemForExcel=getItemtoExcel.getItemByIndex(j);
arrToExcel.push(gotItemForExcel);
}

var fso = new ActiveXObject("Scripting.FileSystemObject"); 
var s = fso.CreateTextFile("C:\\REPORT.csv", true); 
var title="Report";
s.WriteLine(title);
var header="Number" + ";" + "Type" + ";" + "Code"  + ";" + "Created On"  + ";" +  "State" +  '\n' ;
s.WriteLine(header);

for (var c=0; c<arrToExcel.length; c++){
var createExcel = arrToExcel[c];

var Number =createExcel.getProperty("nb");
var Type=createExcel.getProperty("type");
if(Type===undefined){Type="";}
var Code=createExcel.getProperty("code");
if(Code===undefined){Code="";}
var Date=createExcel.getProperty("created_on");
var State=createExcel.getProperty("created_by_id/@keyed_name");

var value=Number + ";" + Type + ";" + Code + ";" + Date + ";" + State;
s.WriteLine(value);
}
s.Close();
alert("Report Saved as C:\\REPORT.csv");
return this;

Upvotes: -2

Himesh Aadeshara
Himesh Aadeshara

Reputation: 2121

i think using this you can get what you want but you need to pass the your Object's value with this that i have mentioned here as (Your Data(Object))

window.open('data:application/vnd.ms-excel,' + **(Your Data(Object))**);

here i'm providing simple code for get data into excel format with jquery

SAMPLE DEMO

Upvotes: 1

Related Questions