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
– GBI, Commented Aug 14, 2015 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
– Teemu, Commented Aug 14, 2015 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
– GBI, Commented Aug 14, 2015 at 11:28
Formatting... but you should explain the problem in words, no images, if they aren't absolutely necessary. — Teemu
– Teemu, Commented Aug 14, 2015 at 11:29

Abdallah Okasha · Accepted Answer · 2021-08-02 07:05:50Z

15

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

edited Aug 2, 2021 at 7:05

Abdallah Okasha

2,19920 silver badges20 bronze badges

answered Nov 28, 2019 at 14:21

Shankar ARUL

13.9k14 gold badges74 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Himesh Aadeshara · Accepted Answer · 2015-08-14 12:08:02Z

0

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, 2015 at 12:08

answered Aug 14, 2015 at 11:19

Himesh Aadeshara

2,2281 gold badge18 silver badges31 bronze badges

7 Comments

Himesh Aadeshara Over a year ago

@GBI please look at the jsfiddle i have given though it's using jquery but the base it's javascript excel

GBI Over a year ago

Thanks fo the link.I'm not aware how to use the code you suggested. Please let me know how o use it.

Himesh Aadeshara Over a year ago

@GBI please give me the Data(Object) which you want to print in excel

GBI Over a year ago

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.

Himesh Aadeshara Over a year ago

@for this when you were getting function error..? had you included the jquery liberary..?

|

GBI · Accepted Answer · 2015-08-18 06:30:56Z

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;

Collectives™ on Stack Overflow

Export array of objects into Excel using Javascript

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related