Export Array of Objects into Excel Using JavaScript

Export array of objects into Excel using Javascript

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 multiple array of object in excel as multiple sheets

Try creating workbook in below manner using var wb = XLSX.utils.book_new();.

Create worksheets with XLSX.utils.json_to_sheet & append both worksheeet objects to workbook with XLSX.utils.book_append_sheet.

Reference : https://www.npmjs.com/package/xlsx#working-with-the-workbook

import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';

function downloadExcel() {
    
    /* create a new blank workbook */
    var wb = XLSX.utils.book_new();

    /* create a worksheet for books */
    var wsBooks = XLSX.utils.json_to_sheet(books);

    /* Add the worksheet to the workbook */
    XLSX.utils.book_append_sheet(wb, wsBooks, "Books");

    /* create a worksheet for person details */
    var wsPersonDetails = XLSX.utils.json_to_sheet(personDetails);

    /* Add the worksheet to the workbook */
    XLSX.utils.book_append_sheet(wb, wsPersonDetails, "PersonDetails");
    
    
    const fileType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
    const excelBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
    const data1 = new Blob([excelBuffer], { type: fileType });
    FileSaver.saveAs(data1, "BookDetail Summary.xlsx");    
}

How to export array of objects to an excel in angular 8

Install this module : https://www.npmjs.com/package/file-saver

Create one shared service :

excel.service.ts

import { Injectable } from '@angular/core';
import * as fileSaver from 'file-saver';
import * as XLSX from 'xlsx';
const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';
@Injectable()
export class ExcelService {
constructor() { }
public exportAsExcelFile(json: any[], excelFileName: string): void {
  const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
  const workbook: XLSX.WorkBook = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
  const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
  this.saveAsExcelFile(excelBuffer, excelFileName);
}
private saveAsExcelFile(buffer: any, fileName: string): void {
   const data: Blob = new Blob([buffer], {type: EXCEL_TYPE});
   fileSaver.saveAs(data, fileName + '_export_' + new  Date().getTime() + EXCEL_EXTENSION);
}
}

component.ts

excelService.exportAsExcelFile(excelJsonData, 'sample');

You have to convert data into below format :

studentData = [
    {
      names: "jhon",ages: 19, address: pattan,subjects: Eng,emails:"jeo@email.com"
    },
 {
      names: "doe",ages: 20, address: usa,subjects: Math,emails:"doe@email.com"
    }
  ]

You can convert by using following function :

convertData() {
    let elemCount;
    let finalData = [];
    for (const [key, value] of Object.entries(this.studentData[0])) {
      console.log(key, value);

      elemCount = value.length;

      for (let i = 0; i < elemCount; i++) {
        if (!finalData[i]) {
          finalData[i] = {
            names: "",
            ages: "",
            address: "",
            subjects: "",
            emails: ""
          };
        }
        finalData[i][key] = value[i];
      }
    }
    console.log(finalData);
    return finalData;
  }

More details : https://medium.com/@madhavmahesh/exporting-an-excel-file-in-angular-927756ac9857

https://stackblitz.com/edit/angular6-export-xlsx-balbt5?file=src/app/app.component.html

Export 2D javascript array to Excel sheet

Finaly after 2 full workdays of searching and trying, I found the solution, which I would like to share here, to help anybody with the same problem:

Simply include an invisible element, which gives the file an usefull name using its download="somedata.csv" attribute:

Here is my final and fully functional fiddle:

https://jsfiddle.net/3an24jmw/25/

var Results = [
["Col1", "Col2", "Col3", "Col4"],
["Data", 50, 100, 500],
["Data", -100, 20, 100],
];

exportToCsv = function() {
  var CsvString = "";
  Results.forEach(function(RowItem, RowIndex) {
    RowItem.forEach(function(ColItem, ColIndex) {
      CsvString += ColItem + ',';
    });
    CsvString += "\r\n";
  });
  CsvString = "data:application/csv," + encodeURIComponent(CsvString);
  var x = document.createElement("A");
  x.setAttribute("href", CsvString );
  x.setAttribute("download","somedata.csv");
  document.body.appendChild(x);
  x.click();
}

---

Related Topics