Export Excel

Module Extensions

`HTMLRevoGridElementEventMap` (Extended from `global`)

1
interface HTMLRevoGridElementEventMap {
2
  /**
3
       * Event for export excel
4
       *
5
       * @example
6
       * ```typescript
7
       * grid.addEventListener(EXCEL_EXPORT_EVENT, (event) => {
8
       *   console.log(event);
9
       * });
10
       */
11
      [EXCEL_EXPORT_EVENT]: ExportExcelEvent | undefined
12
}

`AdditionalData` (Extended from `@revolist/revogrid`)

1
interface AdditionalData {
2
  /**
3
       * Additional data property for export excel
4
       *
5
       * @example
6
       * ```typescript
7
       * const grid = document.createElement('revo-grid');
8
       * grid.additionalData = { excel: { allowDrag: false, parsingOptions: { raw: true, xlfn: true, dense: true, PRN: true } } };
9
       * ```
10
       */
11
      excel?: ExcelConfig
12
}

Plugin API

`ExportExcelPlugin`

The ExportExcelPlugin is a plugin for RevoGrid that enables seamless import and export of Excel files (.xlsx, .xls) to and from the grid, enhancing data interoperability with external systems.

Features:

Drag-and-drop interface for importing Excel files directly into the grid.
Event-driven architecture allowing hooks for customization via ‘excel-before-import’ and ‘excel-before-set’ events, giving plugins the ability to modify data before it’s processed by the grid.
Export current grid data to an Excel file, with customizable workbook and sheet names.
Configurable to allow or restrict drag file import based on user settings.

Usage:

Integrate ExportExcelPlugin as part of RevoGrid’s plugin array to enable Excel import/export functionality.
Listen to relevant events to extend or customize the data handling behavior.

Example

1
import { ExportExcelPlugin } from '@revolist/revogrid-pro'
2

3
const grid = document.createElement('revo-grid');
4
grid.plugins = [ExportExcelPlugin];

This plugin is essential for applications that require integration with Excel files, providing an efficient way to manage large datasets and maintain data consistency across different platforms.

1
class ExportExcelPlugin {
2
  /**
3
     * Imports an Excel file into the grid.
4
     *
5
     * @param file The File object to import
6
     *
7
     * This method reads the file using FileReader, parses it using xlsx library,
8
     * and then dispatches the 'excel-before-import' event to allow plugins to
9
     * modify the sheet before it is imported.
10
     *
11
     * After the sheet is imported, the method dispatches the 'excel-before-set'
12
     * event to allow plugins to modify the data before it is set as the new
13
     * source.
14
     *
15
     * If either event is prevented, the method will not set the new source.
16
     *
17
     * If the file is not an Excel file, the method will log an error.
18
     */
19
  async importFile(
20
    file: File,
21
    config: ExcelConfig = this.revogrid.additionalData?.excel ||;
22

23
  isExcel(file: File, allowedExtensions: string[] = ['.xlsx', '.xls']);
24

25
  export(config?: ExportExcelEvent);
26
}

`BookType`

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.

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

`Properties`

Basic File Properties

1
interface Properties {
2
  /** Summary tab "Title" */
3
    Title?: string;
4
  /** Summary tab "Subject" */
5
    Subject?: string;
6
  /** Summary tab "Author" */
7
    Author?: string;
8
  /** Summary tab "Manager" */
9
    Manager?: string;
10
  /** Summary tab "Company" */
11
    Company?: string;
12
  /** Summary tab "Category" */
13
    Category?: string;
14
  /** Summary tab "Keywords" */
15
    Keywords?: string;
16
  /** Summary tab "Comments" */
17
    Comments?: string;
18
  /** Statistics tab "Last saved by" */
19
    LastAuthor?: string;
20
  /** Statistics tab "Created" */
21
    CreatedDate?: Date
22
}

`ExportExcelEvent`

Example usage of the ExportExcelEvent type

Example:

1
* const exportConfig: ExportExcelEvent = {
2
 *   sheetName: 'Property Data', // Specify the name of the Excel sheet
3
 *   workbookName: 'Properties.xlsx', // Name of the workbook file
4
 *   writingOptions: {
5
 *     type: 'file', // Output type
6
 *     bookType: 'xlsx', // Workbook type
7
 *     compression: true, // Enable compression
8
 *     ignoreEC: true, // Ignore Excel compatibility
9
 *   },
10
 * };

1
/**
2
 * Example usage of the ExportExcelEvent type
3
 *
4
 * @example
5
 * const exportConfig: ExportExcelEvent =  {
6
 *   sheetName: 'Property Data', // Specify the name of the Excel sheet
7
 *   workbookName: 'Properties.xlsx', // Name of the workbook file
8
 *   writingOptions: {
9
 *     type: 'file', // Output type
10
 *     bookType: 'xlsx', // Workbook type
11
 *     compression: true, // Enable compression
12
 *     ignoreEC: true, // Ignore Excel compatibility
13
 *   },
14
 * };
15
 */
16
export type ExportExcelEvent = {
17
  sheetName?: string; // The name of the sheet within the Excel file
18
  workbookName?: string; // The name of the Excel workbook
19
  writingOptions?: {
20
    /** Output data encoding */
21
    type?: 'base64' | 'binary' | 'buffer' | 'file' | 'array' | 'string';
22

23
    /**
24
     * Generate Shared String Table
25
     * @default false
26
     */
27
    bookSST?: boolean;
28

29
    /**
30
     * File format of generated workbook
31
     * @default 'xlsx'
32
     */
33
    bookType?: BookType;
34

35
    /**
36
     * Use ZIP compression for ZIP-based formats
37
     * @default false
38
     */
39
    compression?: boolean;
40

41
    /**
42
     * Suppress "number stored as text" errors in generated files
43
     * @default true
44
     */
45
    ignoreEC?: boolean;
46

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

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

`ExcelConfig`

1
export type ExcelConfig =  {
2

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

6

7

8
  /** Excel parsing options */
9
  parsingOptions?: {
10
    /** If true, plaintext parsing will not parse values */
11
    raw?: boolean;
12
    /** If true, preserve _xlfn. prefixes in formula function names */
13
    xlfn?: boolean;
14
    dense?: boolean;
15
    PRN?: boolean;
16
  };
17

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

23
     /** Override worksheet range */
24
     range?: any;
25

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

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

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

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

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

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

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

Export Excel

Module Extensions

HTMLRevoGridElementEventMap (Extended from global)

AdditionalData (Extended from @revolist/revogrid)

Plugin API

ExportExcelPlugin

Example

BookType

Properties

ExportExcelEvent

ExcelConfig