Node.js Excel Export

Common errors

• TypeError: excel.buildExport is not a function

  cause Attempting to use `buildExport` as a named import or calling it on a non-module object, often due to incorrect CommonJS import syntax in an ESM context.
  fix Ensure you are importing the module using `const excel = require('node-excel-export');` and then calling `excel.buildExport(...)`.

• Error: Invalid data or specification provided. Check your dataset and specification objects.

  cause The `dataset` is not an array of objects, or the `specification` object is malformed or missing required properties, leading to `node-excel-export` failing to process the input correctly.
  fix Review your `dataset` to confirm it's an `Array<Object>` and your `specification` to ensure all properties like `displayName`, `headerStyle`, and `width` are correctly defined as shown in the documentation.

• The file cannot be opened because there are problems with the contents.

  cause Generated Excel file is corrupt, often due to invalid data types passed to `xlsx` library, errors in custom `cellStyle` or `cellFormat` functions, or incorrect merge ranges.
  fix Double-check the data types in your `dataset`, ensure custom style/format functions return valid output, and confirm `merges` array defines valid row/column ranges without overlaps that could cause structural issues.

Warnings

• gotcha This package is CommonJS-only. Attempting to use `import` syntax directly in an ESM module without proper configuration (like `createRequire` or `type: "commonjs"` in package.json for the entry file) will result in errors.
  Fix: Use `const excel = require('node-excel-export');` for importing the module in CommonJS environments.
• gotcha Dynamic styling or formatting functions (`cellStyle`, `cellFormat`) must return valid style objects or formatted strings. Errors within these functions can lead to malformed Excel files or runtime exceptions during report generation.
  Fix: Thoroughly test `cellStyle` and `cellFormat` functions with various data inputs to ensure they always return expected values or style objects according to the `xlsx` library's specifications. Always include a fallback or default style.
• gotcha The `specification` object's keys must exactly match the keys in your `dataset` objects for data to be correctly mapped to columns. Mismatched keys will result in empty cells.
  Fix: Verify that all keys defined in the `specification` object (e.g., `customer_name`, `status_id`) correspond precisely to the property names in your `dataset` array of objects.

Install

• npm install node-excel-export npm
• yarn add node-excel-export yarn
• pnpm add node-excel-export pnpm

Imports

• excel
  wrong:

  import excel from 'node-excel-export';

  correct:

  const excel = require('node-excel-export');

  This package is CommonJS-only. Direct ESM imports are not supported without transpilation or Node.js's createRequire utility.
• buildExport
  wrong:

  import { buildExport } from 'node-excel-export';

  correct:

  const reportBuffer = excel.buildExport([...]);

  buildExport is a method on the default CommonJS export, not a named export. Ensure you import the entire module first.

Quickstart

This quickstart demonstrates how to define styles, create heading rows, specify column structures, and generate an Excel (.xlsx) file from a dataset, saving it to disk.

const excel = require('node-excel-export');
const fs = require('fs');

// You can define styles as json object
const styles = {
  headerDark: {
    fill: {
      fgColor: {
        rgb: 'FF000000'
      }
    },
    font: {
      color: {
        rgb: 'FFFFFFFF'
      },
      sz: 14,
      bold: true,
      underline: true
    }
  },
  cellPink: {
    fill: {
      fgColor: {
        rgb: 'FFFFCCFF'
      }
    }
  },
  cellGreen: {
    fill: {
      fgColor: {
        rgb: 'FF00FF00'
      }
    }
  }
};

//Array of objects representing heading rows (very top)
const heading = [
  [{value: 'a1', style: styles.headerDark}, {value: 'b1', style: styles.headerDark}, {value: 'c1', style: styles.headerDark}],
  ['a2', 'b2', 'c2'] // <-- It can be only values
];

//Here you specify the export structure
const specification = {
  customer_name: { 
    displayName: 'Customer', 
    headerStyle: styles.headerDark, 
    cellStyle: function(value, row) { 
      return (row.status_id == 1) ? styles.cellGreen : {fill: {fgColor: {rgb: 'FFFF0000'}}}; 
    },
    width: 120 
  },
  status_id: {
    displayName: 'Status',
    headerStyle: styles.headerDark,
    cellFormat: function(value, row) { 
      return (value == 1) ? 'Active' : 'Inactive';
    },
    width: '10' 
  },
  note: {
    displayName: 'Description',
    headerStyle: styles.headerDark,
    cellStyle: styles.cellPink, 
    width: 220 
  }
}

// The data set should have the following shape (Array of Objects)
const dataset = [
  {customer_name: 'IBM', status_id: 1, note: 'some note', misc: 'not shown'},
  {customer_name: 'HP', status_id: 0, note: 'some note'},
  {customer_name: 'MS', status_id: 0, note: 'some note', misc: 'not shown'}
]

// Define an array of merges.
const merges = [
  { start: { row: 1, column: 1 }, end: { row: 1, column: 10 } },
  { start: { row: 2, column: 1 }, end: { row: 2, column: 5 } },
  { start: { row: 2, column: 6 }, end: { row: 2, column: 10 } }
]

// Create the excel report. This function will return a Buffer.
const report = excel.buildExport(
  [
    {
      name: 'Report',
      heading: heading,
      merges: merges,
      specification: specification,
      data: dataset
    }
  ]
);

// Save the buffer to a file
fs.writeFileSync('report.xlsx', report);
console.log('report.xlsx created successfully.');