Generic code to export array of objects (table data) into excel using XLSX library

Consider we have an array of objects which is to exported into an excel using XLSX library we can use some simple code like below -

import * as XLSX from 'xlsx';

exportAsExcelFile(data: any[], fileName: string): void {
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
    const workbook: XLSX.WorkBook = {
      Sheets: { 'data': worksheet },
      SheetNames: ['data']
    };
  XLSX.writeFile(workbook, `${filename}.xlsx`);
}

Issues with above code

1. No type safety
2. Exported excel displays data in default cell width. If a cell contains data that exceeds the default cell width, it does not look good.

Let's solve above issues -

Use below interface and the code
interface ExcelRow {[key: string]: string;}

import * as XLSX from 'xlsx';

exportAsExcelFile(data: ExcelRow[], fileName: string): void {
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(data);
    const workbook: XLSX.WorkBook = {
      Sheets: { data: worksheet },
      SheetNames: ['data'],
    };

    const columnWidths: XLSX.ColInfo[] = Object.keys(data[0]).map(
      (key: keyof ExcelRow) => {
        const maxLength = Math.max(
          ('' + key).length,
          ...data.map((row: ExcelRow) =>
            row[key] ? row[key].toString().length : 0
          )
        );
        return { wch: maxLength + 2 }; // Adding some padding
      }
    );

    worksheet['!cols'] = columnWidths;

    XLSX.writeFile(workbook, `${fileName}.xlsx`);
  }

We added type safety and the exported excel has cell width adjusted dynamically as per the cell data.

We can use method exportAsExcelFile() by passing data like below in our component

const data: ExcelRow[] = [
      {
        ID: '007',
        NAME: 'John Doe',
        Work: 'Developer',
        Address: '7, Village Road, KA'
      },
      {
        ID: '008',
        NAME: 'Alice',
        Work: 'QA Engg',
        Address: '7, Village Road, LA'
      },
      {
        ID: '009',
        NAME: 'Maya',
        Work: 'Manager',
        Address: '7, Village Road, MH'
      },
    ];
    this.exportAsExcelFile(data, 'Employee data');

Above code works. Notice that the above code is available in a component; what if

1. This export functionality needs to be used in many other components in the app.
2. The type of the data value in the ExcelRowcan be something other than string; it can be a number, null etc.

This can be achieved by

1. Copy pasting the same code in all component wherever it is required - works but not a good standard
2. Create a utility class and use this code which then can be used by components who need it. And using generic type of data in ExcelRow

Let's create a utility method.

interface ExcelRow<T> {[key: string]: T;}

  exportAsExcelFile<T>(data: ExcelRow<T>[], fileName: string): void {
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(data);
    const workbook: XLSX.WorkBook = {
      Sheets: { data: worksheet },
      SheetNames: ['data'],
    };

    const columnWidths: XLSX.ColInfo[] = Object.keys(data[0]).map(
      (key: keyof ExcelRow<T>) => {
        const maxLength = Math.max(
          ('' + key).length,
          ...data.map((row: ExcelRow<T>) =>
            row[key] ? row[key].toString().length : 0
          )
        );
        return { wch: maxLength + 2 }; // Adding some padding
      }
    );

    worksheet['!cols'] = columnWidths;

    XLSX.writeFile(workbook, `${fileName}.xlsx`);
  }

Below are the examples of data -

    const data: ExcelRow<string | number | null>[] = [
      {
        ID: 7,
        NAME: 'John Doe',
        Work: 'Developer',
        Address: '7, Village Road, KA'
      },
      {
        ID: 8,
        NAME: 'Alice',
        Work: 'QA Engg',
        Address: '7, Village Road, LA'
      },
      {
        ID: '009',
        NAME: 'Maya',
        Work: 'Manager',
        Address: null
      },
    ];

    const data2: ExcelRow<number>[] = [
      {
        'Student ID': 1,
        Score: 90,
      },
      {
        'Student ID': 2,
        Score: 95,
      },
    ];

This way we have a utility that can be used to export array of objects into excel using XLSX library.

I hope this helps.

Thanks,
Jastya!