Skip to content
RevoGrid [Pro]

Exporting/Importing Excel

Source code
src/components/export-excel/ExportExcel.ts
import { defineCustomElements } from '@revolist/revogrid/loader';
defineCustomElements();


import { currentTheme, useRandomData } from '../composables/useRandomData';


const { createRandomData } = useRandomData();
const { isDark } = currentTheme();


import { ExportExcelPlugin, type ExportExcelEvent } from '@revolist/revogrid-pro';
export function load(parentSelector: string) {
  const grid = document.createElement('revo-grid');


  grid.source = createRandomData(100);


  grid.source = createRandomData(100);
  grid.columns = [
    {
      name: '🆔 ID',
      prop: 'id',
    },
    {
      name: '🍎 Fruit',
      prop: 'name',
    },
    {
      name: '💰 Price',
      prop: 'price',
    },
  ];
  // Define plugin
  grid.plugins = [ExportExcelPlugin];


  const exportConfig: ExportExcelEvent = { sheetName: 'my-file.xlsx' };
  document.querySelector('.export')?.addEventListener('click', () => {
    grid.getPlugins().then(plugins => {
      const plugin = plugins.find(p => p instanceof ExportExcelPlugin) as ExportExcelPlugin;
      plugin?.export(exportConfig);
    });


    // or dispatch event
    // dispatch(grid, 'export-excel', exportConfig);
  });


  grid.theme = isDark() ? 'darkCompact' : 'compact';
  grid.hideAttribution = true;
  document.querySelector(parentSelector)?.appendChild(grid);
}

Initialize the Grid and Plugin

Once your columns and data are defined, initialize the RevoGrid component and attach the ExportExcelPlugin:

...
  import { ExportExcelPlugin } from '@revolist/revogrid-pro';
  grid.plugins = [ExportExcelPlugin];
...

To trigger the export, you’ll need to set up a button that dispatches the export event:

Then you have 2 options to trigger the export.

Use dispatch to trigger the export event:

...
    dispatch(grid, 'export-excel', exportConfig)
...

Or you can use the plugin’s export method:

grid.getPlugins().then(plugins => {
    plugins.find(p => p instanceof ExportExcelPlugin)?.export(exportConfig)
})

Export Options

The ExportExcelEvent type allows you to customize various aspects of the Excel export:

export-excel/export-excel-event.type.ts
/**
 * This file defines the `ExportExcelEvent` type, which is used to configure the export
 * of data from RevoGrid to an Excel file. It includes options for the Excel sheet and
 * workbook names, as well as detailed configuration for writing options such as file
 * type, compression, and workbook properties.
 *
 *  **Features**:
 * - Support for a variety of Excel file types through the `BookType` union.
 * - Customizable properties for the exported Excel file such as title, author, and
 *   creation date encapsulated in the `Properties` interface.
 * - Flexible writing options allowing control over data encoding, compression, and error
 *   suppression.
 *
 * This type is essential for applications needing to export grid data to Excel, providing
 * a structured way to specify export settings and ensure compatibility with various Excel
 * file formats.
 */
export type BookType = 'xlsx' | 'xlsm' | 'xlsb' | 'xls' | 'xla' | 'biff8' | 'biff5' | 'biff2' | 'xlml' | 'ods' | 'fods' | 'csv' | 'txt' | 'sylk' | 'slk' | 'html' | 'dif' | 'rtf' | 'prn' | 'eth' | 'dbf';


/** Basic File Properties */
export interface Properties {
  /** Summary tab "Title" */
  Title?: string;
  /** Summary tab "Subject" */
  Subject?: string;
  /** Summary tab "Author" */
  Author?: string;
  /** Summary tab "Manager" */
  Manager?: string;
  /** Summary tab "Company" */
  Company?: string;
  /** Summary tab "Category" */
  Category?: string;
  /** Summary tab "Keywords" */
  Keywords?: string;
  /** Summary tab "Comments" */
  Comments?: string;
  /** Statistics tab "Last saved by" */
  LastAuthor?: string;
  /** Statistics tab "Created" */
  CreatedDate?: Date;
}


/**
 * Example usage of the ExportExcelEvent type
 *
 * @example
 * const exportConfig: ExportExcelEvent = {
 *   sheetName: 'Property Data', // Specify the name of the Excel sheet
 *   workbookName: 'Properties.xlsx', // Name of the workbook file
 *   writingOptions: {
 *     type: 'file', // Output type
 *     bookType: 'xlsx', // Workbook type
 *     compression: true, // Enable compression
 *     ignoreEC: true, // Ignore Excel compatibility
 *   },
 * };
 */
export type ExportExcelEvent = {
  sheetName?: string; // The name of the sheet within the Excel file
  workbookName?: string; // The name of the Excel workbook
  writingOptions?: {
    /** Output data encoding */
    type?: 'base64' | 'binary' | 'buffer' | 'file' | 'array' | 'string';


    /**
     * Generate Shared String Table
     * @default false
     */
    bookSST?: boolean;


    /**
     * File format of generated workbook
     * @default 'xlsx'
     */
    bookType?: BookType;


    /**
     * Use ZIP compression for ZIP-based formats
     * @default false
     */
    compression?: boolean;


    /**
     * Suppress "number stored as text" errors in generated files
     * @default true
     */
    ignoreEC?: boolean;


    /** Override workbook properties on save */
    Props?: Properties;


    /** Base64 encoding of NUMBERS base for exports */
    numbers?: string;
  }; // Options for how the file is written
};

Here’s a breakdown of some of the key options available in the WritingOptions interface from SheetJS:

  • type: Specifies the output data encoding (e.g., ‘base64’, ‘binary’, ‘buffer’, etc.).
  • bookSST: Generates a Shared String Table, reducing file size for large text-based datasets.
  • bookType: Sets the file format of the generated workbook. Supported types: 'xlsx' | 'xlsm' | 'xlsb' | 'xls' | 'xla' | 'biff8' | 'biff5' | 'biff2' | 'xlml' | 'ods' | 'fods' | 'csv' | 'txt' | 'sylk' | 'slk' | 'html' | 'dif' | 'rtf' | 'prn' | 'eth' | 'dbf'.
  • compression: Enables ZIP compression for supported formats, helping to reduce file size.
  • ignoreEC: Suppresses “number stored as text” errors, ensuring smoother data exports.

Import Excel Files

By default RevoGrid support importing Excel files using drag and drop. Simply drop the file into the grid and it will be imported. Don’t forget to provide columns for mapping the data.


excel-before-import event allows you to modify the sheet before it is imported. You can choose which sheet to import.

After the sheet is imported, the method dispatches the excel-before-set event to allows to modify the data before it is set as the new source.

Plugin provides the following configuration options:

...
  const excelConfig: ExcelConfig = {
    allowDrag: true,
  };
  revogrid.additionalData = {
    excel: excelConfig
  }
...
export-excel.types.ts
/**
 * The `ExcelConfig` type defines configuration options for handling Excel file imports
 * in RevoGrid, allowing developers to tailor how Excel files are parsed and integrated
 * into the grid environment.
 *
 *  **Features**:
 * - Drag-and-drop support for Excel files, which can be enabled or disabled via `allowDrag`.
 * - Detailed Excel parsing options, including raw data handling and formula prefix preservation
 *   through the `parsingOptions` object.
 * - Customizable JSON format options for converting Excel sheets to grid-compatible data,
 *   including header row definition, range overrides, and handling of blank rows and raw numbers.
 * - Control over column generation with `skipColumnGeneration`, allowing the grid to maintain
 *   existing columns or generate new ones based on the Excel file's first row.
 * - Specification of permissible file extensions for imports to ensure compatibility and security.
 *
 * This type is crucial for applications that require flexible and controlled integration
 * of Excel data into RevoGrid, supporting a wide range of data handling scenarios.
 */
import type { Sheet2JSONOpts } from 'xlsx';
import { EXCEL_EXPORT_EVENT } from '../events';
import { ExportExcelEvent } from './export-excel-event.type';


export type ExcelConfig = {


  /** Allow drag and drop files to the grid area, default: true */
  allowDrag?: boolean;






  /** Excel parsing options */
  parsingOptions?: {
    /** If true, plaintext parsing will not parse values */
    raw?: boolean;
    /** If true, preserve _xlfn. prefixes in formula function names */
    xlfn?: boolean;
    dense?: boolean;
    PRN?: boolean;
  };


  /** Extra options to parse the sheet to the grid format. default: { header: 1 } - define the first row as the header */
  jsonFormat?: {
     /** Output format */
     header?: "A"|number|string[];


     /** Override worksheet range */
     range?: any;


     /** Include or omit blank lines in the output */
     blankrows?: boolean;


     /** Default value for null/undefined values */
     defval?: any;


     /** if true, return raw data; if false, return formatted text */
     raw?: boolean;


     /** if true, skip hidden rows and columns */
     skipHidden?: boolean;


     /** if true, return raw numbers; if false, return formatted numbers */
     rawNumbers?: boolean;
  };


  /** Skip column generation based on the first row - keep existing columns defined in the grid. default: false */
  skipColumnGeneration?: boolean;


  /** Allowed file extensions. default: ['.xlsx', '.xls'] */
  allowedExtensions?: string[];
};




declare global {
  interface HTMLRevoGridElementEventMap {
    /**
     * Event for export excel
     *
     * @example
     * ```typescript
     * grid.addEventListener(EXCEL_EXPORT_EVENT, (event) => {
     *   console.log(event);
     * });
     */
    [EXCEL_EXPORT_EVENT]: ExportExcelEvent | undefined;
  }
}


declare module '@revolist/revogrid' {
  export interface AdditionalData {
    /**
     * Additional data property for export excel
     *
     * @example
     * ```typescript
     * const grid = document.createElement('revo-grid');
     * grid.additionalData = { excel: { allowDrag: false, parsingOptions: { raw: true, xlfn: true, dense: true, PRN: true } } };
     * ```
     */
    excel?: ExcelConfig;
  }
}

Importing Excel Files using the importFile Method

You can also import Excel files using the plugin method importFile:

    // Handle drop event
    revogrid.addEventListener('drop', async (event: DragEvent) => {
      event.preventDefault();


      if (event.dataTransfer && event.dataTransfer.files.length > 0) {
        const file = event.dataTransfer.files[0];
        revogrid.getPlugins().then(plugins => {
            plugins.find(p => p instanceof ExportExcelPlugin)?.importFile(file);
        });
      }
    });

This method reads the file using FileReader, parses it using xlsx library, and then dispatches the ‘excel-before-import’ event to allow plugins to modify the sheet before it is imported.

After the sheet is imported, the method dispatches the ‘excel-before-set’ event to allow plugins to modify the data before it is set as the new source.

If either event is prevented, the method will not set the new source. If the file is not an Excel file, the method will log an error.