Skip to content
Handsontable dev

Import from Excel

Load an Excel (.xlsx) workbook into your grid. The ImportFile plugin reads the file through an engine you provide, derives cell types from number formats, and applies the layout the workbook carries.

Prerequisites

  • Install ExcelJS, the engine that powers XLSX import. The supported version range is ^4.4.0.

    Terminal window
    npm install exceljs

Engines

XLSX import goes through an engine you inject, the same way the export does. ExcelJS is the only supported engine; the per-feature table in the export guide covers both directions.

Steps

  1. Pass the engine to the plugin.

    ::: only-for javascript

    import ExcelJS from 'exceljs';
    

    const hot = new Handsontable(container, {
      importFile: { engines: { xlsx: ExcelJS } },
      licenseKey: 'non-commercial-and-evaluation',
    });

    :::

    ::: only-for react

    import ExcelJS from 'exceljs';
    

    <HotTable
      importFile={{ engines: { xlsx: ExcelJS } }}
      licenseKey="non-commercial-and-evaluation"
    />

    :::

    ::: only-for angular

    import ExcelJS from 'exceljs';
    

    readonly hotSettings: GridSettings = {
      importFile: { engines: { xlsx: ExcelJS } },
      licenseKey: 'non-commercial-and-evaluation',
    };

    :::

    ::: only-for vue

    import ExcelJS from 'exceljs';
    

    const hotSettings = ref({
      importFile: { engines: { xlsx: ExcelJS } },
      licenseKey: 'non-commercial-and-evaluation',
    });

    :::

  2. Import a file picked by the user.

    fileInput.addEventListener('change', async () => {
      const [file] = fileInput.files;
      const result = await hot.getPlugin('importFile').importFromBlob('xlsx', file, {
        colHeaders: 'firstRow',
      });
    

      console.log(result.dropped); // features the engine could not recover, for example cellStyles
    });

  3. Inspect the result before applying it, for a large file or one from an untrusted source.

    const preview = await hot.getPlugin('importFile').importFromArrayBuffer('xlsx', buffer, { apply: false });
    

    if (preview.data.length <= 10000) {
      await hot.getPlugin('importFile').importFromArrayBuffer('xlsx', buffer);
    }

Result

The grid shows the workbook’s data with headers, types, dropdown sources, layout, formulas (when the Formulas plugin is enabled), and cell comments (when the Comments plugin is enabled).

Cell styling is applied only when you set importStyles: true. Without it, styling is read but not applied, and appears in result.dropped as cellStyles. Conditional formatting is never applied - the grid has no plugin for it - but it is no longer lost: it comes back on result.conditionalFormatting, in grid coordinates and in the shape the export’s conditionalFormatting option takes. Pass result.conditionalFormatting to exportFile’s conditionalFormatting option to carry the rules back into a file.

The plugin also reports every dropped feature in one console warning per import call, naming the engine. With importStyles off, a workbook that carries any cell styling reports it this way:

The "exceljs" xlsx engine dropped features it cannot write or read: cellStyles.

Expect cellStyles on almost any real workbook: Excel assigns a style to most cells you have used, even ones you never formatted yourself, so the plugin sees styling to report whether or not the sheet looks styled.

Three keys on the result carry features that need more than a cell value. Each row says whether the grid applies it:

KeyWhat it carries
nestedHeadersThe promoted header band when headerRows is above 1. It is applied - updateSettings both configures and enables the NestedHeaders plugin - and colHeaders is absent whenever it is present. A later import that promotes a single header row instead clears a previously applied nestedHeaders setting automatically, so the new colHeaders renders.
conditionalFormattingOne entry per rectangle the workbook’s rules cover, as { rows: [first, last], cols: [first, last], rules } in zero-based, inclusive grid coordinates. The rules are the engine’s own rule objects, passed through untouched. Nothing applies them.
layoutDirection'rtl' or 'ltr', the sheet’s own direction. Nothing applies it - see below.

These are the keys the import can report, beyond cellStyles:

KeyWhat it means
imagesThe sheet carries embedded images. The grid has no cell-level home for them.
tablesThe sheet defines one or more Excel tables. Their cell values are imported; the table definition is not.
autoFilterThe sheet carries an auto-filter range. Configure the Filters plugin on the target grid instead.
hyperlinkA cell is a hyperlink. Its display text is imported; the target URL is not.
richTextA cell mixes formatting inside one string. The text is imported; the per-run formatting is not.
sheetProtection:passwordThe sheet is protected with a password. The file carries only a salted hash, so no password can be recovered - readOnly cells are still derived from the protection.
merge:overlapTwo merged ranges overlap. The later one is skipped.
cellStyles:bordersThe workbook carries borders and the CustomBorders plugin is not enabled on the target grid.
commentsThe workbook carries cell comments and the Comments plugin is not enabled on the target grid. Set comments: true on the grid to import them.
layoutDirectionThe sheet’s layout direction disagrees with the grid’s. Handsontable resolves layoutDirection at initialization and ignores it afterwards, so construct the grid with layoutDirection: 'rtl' to follow a right-to-left workbook. The direction is on result.layoutDirection either way.
dataValidation:<type>A non-list data validation, which has no grid equivalent.
conditionalFormatting:unparsedRefA conditional formatting range the plugin could not parse. The rule’s other ranges still apply.
dataValidation:unresolvedListA list validation whose source cannot be read - a formula such as INDIRECT(), or a range on a sheet the workbook does not contain. An inline list and a range on the same or another sheet become a dropdown column.
numFmt:<pattern>A number format with no Intl.NumberFormat equivalent - scientific notation, a fraction, or more than the 100 fraction digits Intl.NumberFormat accepts.
formula:outOfRangeA formula referencing a cell outside the imported window. Its cached value is imported instead. A reference to another sheet, such as Rates!A1, is kept as written.

Click Import XLSX and pick a .xlsx file to load it into the grid below.

Vue
<script setup lang="ts">
import { ref, useTemplateRef } from 'vue';
import { HotTable } from '@handsontable/vue3';
import { registerAllModules } from 'handsontable/registry';
import ExcelJS from 'exceljs';
import type { GridSettings } from 'handsontable/settings';


registerAllModules();


const hotRef = useTemplateRef<InstanceType<typeof HotTable>>('hotRef');


const hotData = [
  ['Ana García', 'Engineering', 'Senior Engineer', 98000, true, '2022-03-14'],
  ['James Okafor', 'Marketing', 'Marketing Manager', 87500, true, '2021-07-01'],
  ['Li Wei', 'Engineering', 'Product Manager', 104000, false, '2020-11-23'],
  ['Priya Nair', 'Sales', 'Account Executive', 76200, true, '2023-01-09'],
  ['Tom Bakker', 'Support', 'Support Specialist', 58900, true, '2019-05-30'],
];


const hotSettings = ref<GridSettings>({
  data: hotData,
  colHeaders: ['Name', 'Department', 'Job title', 'Salary ($)', 'Active', 'Hire date'],
  columns: [
    { type: 'text' },
    { type: 'dropdown', source: ['Engineering', 'Marketing', 'Sales', 'Support'] },
    { type: 'text' },
    { type: 'numeric', numericFormat: { style: 'currency', currency: 'USD', minimumFractionDigits: 2 } },
    { type: 'checkbox' },
    { type: 'date', dateFormat: { year: 'numeric', month: '2-digit', day: '2-digit' } },
  ],
  rowHeaders: true,
  height: 'auto',
  autoWrapRow: true,
  autoWrapCol: true,
  importFile: { engines: { xlsx: ExcelJS } },
  licenseKey: 'non-commercial-and-evaluation',
});


async function importFile(event: Event): Promise<void> {
  const input = event.target as HTMLInputElement;
  const file = input.files?.[0];


  if (!file) {
    return;
  }


  const importPlugin = hotRef.value?.hotInstance?.getPlugin('importFile');
  const result = await importPlugin?.importFromBlob('xlsx', file, {
    colHeaders: 'firstRow',
  });


  console.log('Dropped features:', result?.dropped);


  input.value = '';
}
</script>


<template>
  <div id="example1">
    <div class="example-controls-container">
      <div class="controls">
        <label for="import-file">Import XLSX</label>
        <input type="file" id="import-file" accept=".xlsx" @change="importFile">
      </div>
    </div>
    <HotTable ref="hotRef" :settings="hotSettings" />
  </div>
</template>

Options

Pass these options as the third argument to importFromArrayBuffer(format, buffer, options) or importFromBlob(format, blob, options).

OptionTypeDefaultDescription
sheetstring | number0Sheet to import, by index among the sheets that are not very hidden - a hidden sheet still counts - or by name.
colHeadersboolean | 'firstRow'false'firstRow' promotes the first row to column headers.
headerRowsnumber1How many rows the header band spans, with colHeaders: 'firstRow'. Above 1, those rows become nested headers instead of colHeaders, and the data starts after them. Has to be an integer of at least 1 - an invalid value is rejected even when colHeaders is not 'firstRow'. Otherwise ignored unless colHeaders is 'firstRow'.
rowHeadersbooleanfalseDrop the first column and enable generated row headers.
rangenumber[]whole sheet[startRow, startColumn, endRow, endColumn] in sheet coordinates.
inferCellTypesbooleantrueDerive numeric, date, time, checkbox, and dropdown types. A numeric type carries Intl.NumberFormat options, and a date or time type carries Intl.DateTimeFormatOptions, both derived from the cell’s number format. The type most cells of a column share becomes the column’s type in columns; the cells that differ get their own entry in cellsMeta. Passing columns to the grid replaces any columns setting it had and fixes its column count, so the plugin omits it when no column carries a type, a readOnly flag, or a class name. When the grid already has a columns setting in that case, the plugin sends one empty column setting per imported column instead, so the previous file’s types do not survive.
importFormulasbooleantruePut formulas into the data when the Formulas plugin is enabled.
importLayoutbooleantrueMerges, hidden rows and columns, frozen panes, widths, and heights. An import describes the whole sheet: merges, hidden rows and columns, frozen panes, custom borders, column widths, and row heights that a previous import left on the grid and this workbook does not carry are reset. The imported lists are merged into an existing hiddenRows, hiddenColumns, or mergeCells options object, so options such as indicators survive.
applybooleantrueApply the result to the grid.
engineobject-Per-call engine override.
importStylesbooleanfalseApply alignment, font, fill, and borders from the workbook.

Styles

By default, the plugin reads cell styling from the workbook but doesn’t apply it - it reports cellStyles in result.dropped instead. Set importStyles: true to apply alignment, font, fill, and border styling to the imported cells.

await hot.getPlugin('importFile').importFromBlob('xlsx', file, {
  colHeaders: 'firstRow',
  importStyles: true,
});

Styling maps to the grid this way:

Workbook styleGrid result
Horizontal and vertical alignmentA class name (htLeft, htCenter, htRight, htJustify, htTop, htMiddle, htBottom) on result.cellsMeta[].meta.className
Bold, italic, underline, and font colorA generated class name on result.cellsMeta[].meta.className, with the CSS declarations in result.styles
Background fillThe same generated class name and declaration, added to result.styles
BordersAn entry in result.customBorders, in the shape the CustomBorders plugin’s customBorders option takes

The plugin installs result.styles for you: it writes one <style> element, owned by the grid instance, that holds a rule per generated class name. Destroying the grid removes this element, and an import that carries no styling removes it too, so one import never leaves the previous one’s rules behind. Colors that the workbook doesn’t write as plain hex are skipped rather than turned into a declaration.

result.customBorders passes straight to updateSettings, so enable the CustomBorders plugin on the target grid - otherwise borders are dropped and reported as cellStyles:borders. Two consequences follow from that passthrough:

  • An import that carries any border replaces the target grid’s existing borders. updateSettings restarts the CustomBorders plugin with the new setting, so borders you configured before the import are gone.
  • A workbook with no borders at all leaves the target grid’s borders untouched, because the result then carries no customBorders key for updateSettings to apply.

Font size and font name aren’t imported: the model doesn’t carry them today. Conditional formatting is read into result.conditionalFormatting and never applied.

Security

The workbook is untrusted input, and the plugin treats it that way:

  • Column headers are escaped. Handsontable renders colHeaders as HTML, so a promoted header row is markup unless something stops it. Every header the plugin promotes is HTML-escaped, which also keeps text such as 5 < 10 whole. If you replace the headers with your own unescaped markup after the import, you own that decision - configure the sanitizer option to police it.
  • Cell values are rendered as text. They are not projected through any HTML path, so they need no escaping and keep whatever the file wrote.
  • Colors are validated before they reach a stylesheet. A color that is not a plain hex value is skipped rather than turned into a CSS declaration.
  • Oversized input is refused. A file above 128 MiB is rejected before the engine parses it. After the parse, a sheet declaring more than 1,048,576 rows, 16,384 columns, or 5,000,000 cells is refused before the plugin allocates anything for it, and a workbook whose sheets together declare more than 10,000,000 cells is refused too. A file can declare a size it does not hold, and reading it at face value exhausts the tab. The parse itself runs inside the engine you inject, so only the byte cap bounds it. The caps are security bounds, not a promise of comfort: a sheet near the 5,000,000-cell cap takes tens of seconds to read and map and several gigabytes of memory, which is more than a browser tab is usually given. Keep imports well below the cap, or split the workbook.
  • References are bounded. A list validation or conditional formatting range that reaches past the sheet limits is not read, and one that spans a whole column or row is clamped to the cells the sheet holds.

Hooks