Export array of objects into Excel using Javascript

Question

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

Number Type Code State ABC121415 PA PA121 In Progress ABC171819 PS PS171 In Progress — GBI, Aug 14 '15 at 11:19
Can you provide a _full, human readable example_ of the data you want to add to Excel? Also, a part of the code doesn't look like JavaScript ...? What is behind `document.thisItem` object? It is not native, not even in JScript. Is it a library or have you implemented it by yourself? And please, __put the additional information to your question, not into comments__. — Teemu, Aug 14 '15 at 11:21
Teemu, I'm able to get the values from my array using the getProperty. I also tried to display the each value say Number, Type, etc with alert. The only issue now is when i write in excel, im getting "event handler failed with message";" ". — GBI, Aug 14 '15 at 11:28
[Formatting](http://stackoverflow.com/help/formatting)... but you should explain the problem in words, no images, if they aren't absolutely necessary. — Teemu, Aug 14 '15 at 11:29

score 12 · Answer 1 · edited Aug 02 '21 at 07:05

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');

Himesh Aadeshara · Answer 2 · 2015-08-14T12:08:02.490

1

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

edited Aug 14 '15 at 12:08

answered Aug 14 '15 at 11:19

Himesh Aadeshara

2,114
16
26

@GBI please look at the jsfiddle i have given though it's using jquery but the base it's javascript excel – Himesh Aadeshara Aug 14 '15 at 12:09
Thanks fo the link.I'm not aware how to use the code you suggested. Please let me know how o use it. – GBI Aug 14 '15 at 12:20
@GBI please give me the Data(Object) which you want to print in excel – Himesh Aadeshara Aug 14 '15 at 12:27
The data object is in the form of an array and I get the values with .getProperty(). Please have a look at the code, arrToExcel is an array from which im geting the values. The values stored in Number,Type,Code and State are to be printed in excel. – GBI Aug 14 '15 at 12:33
@for this when you were getting function error..? had you included the jquery liberary..? – Himesh Aadeshara Aug 14 '15 at 12:41
I have not included. what should I include? – GBI Aug 14 '15 at 13:06
@GBI are you aware with the jQuery Framework.? **** just add boled line in head section of your html – Himesh Aadeshara Aug 14 '15 at 13:20

score -2 · Accepted Answer · answered Aug 18 '15 at 06:30

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;

Export array of objects into Excel using Javascript

3 Answers3

Linked