Import from Excel
Added in Handsontable 19.0.0Load an Excel (.xlsx) workbook into your grid. The ImportFile plugin derives cell types from number formats and applies the layout the workbook carries. It uses the built-in XLSX engine unless you configure ExcelJS - see XLSX engines.
Demo
The grid shows the workbook’s data with headers, cell types, dropdown lists, merged cells, hidden columns, a formula, and a comment. The demo passes importStyles: true, so it also applies the workbook’s alignment, fills, and borders. Without it, styling is reported as cellStyles. A workbook with hidden rows or frozen panes gets those applied the same way.
Click Load sample to import a workbook the export plugin wrote, or Import XLSX to pick your own.
import { useRef, useState } from 'react';import { HotTable } from '@handsontable/react-wrapper';import { registerAllModules } from 'handsontable/registry';import { HyperFormula } from 'hyperformula';
registerAllModules();
const SAMPLE_URL = '/docs/scripts/xlsx/employees.xlsx';
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'],];
// The controls and their status live in their own component. A re-render of the component that// renders `HotTable` makes the wrapper send `data` and `colHeaders` to `updateSettings` again,// which replaces the imported sheet with the sample rows. A grid fed by a server prop must keep// its first value, for example with `const [firstRows] = useState(rows);`.const ImportControls = ({ hotRef }) => { const [status, setStatus] = useState('');
const importWorkbook = async (blob, name) => { const importPlugin = hotRef.current?.hotInstance?.getPlugin('importFile');
if (!importPlugin) { // No grid is mounted, for example while it sits in a hidden tab. setStatus('The grid is not ready.');
return; }
try { const result = await importPlugin.importFromBlob('xlsx', blob, { colHeaders: 'firstRow', importStyles: true, });
setStatus(`Imported ${name}`); console.log('Dropped features:', result.dropped); } catch (error) { // A refused file (an .xls, a password-protected or damaged workbook) rejects with a message // that says what to do; the grid keeps its data. setStatus(error.message); } };
const loadSample = async () => { const response = await fetch(SAMPLE_URL);
await importWorkbook(await response.blob(), 'employees.xlsx'); };
const importFile = async (event) => { const file = event.target.files?.[0];
if (file) { await importWorkbook(file, file.name); }
// Cleared on every path, so picking the same file again fires `change` again. No `finally`: // React Compiler does not compile a `try` with a `finally` clause. event.target.value = ''; };
return ( <div className="example-controls-container"> <div className="controls"> <button onClick={loadSample}>Load sample</button> <label htmlFor="import-file">Import XLSX</label> <input type="file" id="import-file" accept=".xlsx" onChange={importFile} /> <output role="status">{status}</output> </div> </div> );};
const ExampleComponent = () => { const hotRef = useRef(null);
return ( <> <ImportControls hotRef={hotRef} /> <HotTable // licenseKey="your-license-key" // get a key at https://handsontable.com/pricing/ ref={hotRef} 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} formulas={{ engine: HyperFormula }} comments={true} mergeCells={true} hiddenColumns={true} customBorders={true} height="auto" autoWrapRow={true} autoWrapCol={true} importFile={true} /> </> );};
export default ExampleComponent;import { RefObject, useRef, useState } from 'react';import { HotTable, HotTableRef } from '@handsontable/react-wrapper';import { registerAllModules } from 'handsontable/registry';import { HyperFormula } from 'hyperformula';
registerAllModules();
const SAMPLE_URL = '/docs/scripts/xlsx/employees.xlsx';
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'],];
// The controls and their status live in their own component. A re-render of the component that// renders `HotTable` makes the wrapper send `data` and `colHeaders` to `updateSettings` again,// which replaces the imported sheet with the sample rows. A grid fed by a server prop must keep// its first value, for example with `const [firstRows] = useState(rows);`.const ImportControls = ({ hotRef }: { hotRef: RefObject<HotTableRef | null> }) => { const [status, setStatus] = useState('');
const importWorkbook = async (blob: Blob, name: string) => { const importPlugin = hotRef.current?.hotInstance?.getPlugin('importFile');
if (!importPlugin) { // No grid is mounted, for example while it sits in a hidden tab. setStatus('The grid is not ready.');
return; }
try { const result = await importPlugin.importFromBlob('xlsx', blob, { colHeaders: 'firstRow', importStyles: true, });
setStatus(`Imported ${name}`); console.log('Dropped features:', result.dropped); } catch (error) { // A refused file (an .xls, a password-protected or damaged workbook) rejects with a message // that says what to do; the grid keeps its data. setStatus((error as Error).message); } };
const loadSample = async () => { const response = await fetch(SAMPLE_URL);
await importWorkbook(await response.blob(), 'employees.xlsx'); };
const importFile = async (event: React.ChangeEvent<HTMLInputElement>) => { const file = event.target.files?.[0];
if (file) { await importWorkbook(file, file.name); }
// Cleared on every path, so picking the same file again fires `change` again. No `finally`: // React Compiler does not compile a `try` with a `finally` clause. event.target.value = ''; };
return ( <div className="example-controls-container"> <div className="controls"> <button onClick={loadSample}>Load sample</button> <label htmlFor="import-file">Import XLSX</label> <input type="file" id="import-file" accept=".xlsx" onChange={importFile} /> <output role="status">{status}</output> </div> </div> );};
const ExampleComponent = () => { const hotRef = useRef<HotTableRef>(null);
return ( <> <ImportControls hotRef={hotRef} /> <HotTable // licenseKey="your-license-key" // get a key at https://handsontable.com/pricing/ ref={hotRef} 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} formulas={{ engine: HyperFormula }} comments={true} mergeCells={true} hiddenColumns={true} customBorders={true} height="auto" autoWrapRow={true} autoWrapCol={true} importFile={true} /> </> );};
export default ExampleComponent;Prerequisites
- The
ImportFileplugin is part ofregisterAllModules()and is on unless you setimportFile: false. No other library is needed. - When you register modules one by one, register the
ImportFileplugin itself (registerPlugin(ImportFile)), the plugins listed below, and the cell types the import derives:numeric,date,intl-datetime,time,checkbox, anddropdown. An unregistered type is applied astextand reported ascellType:<name>. A preview withapply: falsereturns the type the file asked for. - Enable the plugins for what you want applied:
CustomBorders,Comments, andFormulas.MergeCells,HiddenRows, andHiddenColumnsonly need to be registered, because the import turns them on. A missingCommentsplugin is reported ascomments, a missingCustomBordersplugin ascellStyles:borders(withimportStyles: true), and an unregisteredMergeCells,HiddenRows, orHiddenColumnsplugin under its own name. WithoutFormulas, formulas import as their cached values and are listed inresult.formulas, not inresult.dropped. A formula with no cached value imports empty and is reported asformula:noCachedValue. - To run the import in Jest or Vitest, set up the test environment as Testing describes.
Example
Turn the plugin on
<HotTable// licenseKey="your-license-key" // get a key at https://handsontable.com/pricing/importFile={true}/>Import a file the user picked
fileInput.addEventListener('change', async () => {const [file] = fileInput.files;try {const result = await hot.getPlugin('importFile').importFromBlob('xlsx', file, {colHeaders: 'firstRow',});console.log(result.dropped); // what the file carried and the grid did not take} catch (error) {status.textContent = error.message; // a refused file says what to do, and the grid keeps its data} finally {fileInput.value = ''; // lets the user pick the same file again}});Read what was dropped
result.droppedlists what the file carried and the grid did not take, and the plugin prints the same list in one console warning per import. An import that a newer import replaced while its file was being read, or that the plugin was disabled during, prints none. The Dropped features reference explains every key. A feature the file carries and your license does not include is reported aslicense:xlsx:<id>: see What your license unlocks.Preview before you apply
For a large file, or one from an untrusted source, read the result first and apply it only when it passes your checks.
const preview = await hot.getPlugin('importFile').importFromArrayBuffer('xlsx', buffer, { apply: false });if (preview.data.length <= 10000) {await hot.getPlugin('importFile').importFromArrayBuffer('xlsx', buffer);}
Security
The plugin treats every workbook as untrusted input:
- Promoted column headers are HTML-escaped, and cell values render as text.
- A text value that starts with
=stays text. Only a cell the file declares as a formula imports as one. - Files above the size, cell, and sheet limits are refused before they reach the grid. Macros are never run.
For a file from an untrusted source, import it with apply: false first or with the Formulas plugin off: a few bytes of formula can reference every cell of a sheet and crash the tab. The full rules, the limits, and the risks of each engine are in XLSX engines.
Formulas, arrays, and defined names
With the Formulas plugin on and importFormulas left at true, formulas import live:
- Dynamic arrays (
=SEQUENCE(3),=FILTER(...)) import live and spill over the cells below them. The cells the array covers import empty, so the spill does not collide with their cached values. When the array calls a function HyperFormula lacks, such asUNIQUEorSORT, it shows an error and does not spill, so the cells it covers keep their cached values. A formula that reads a spilled range with the spill operator (=SUM(B1#)), or uses implicit intersection (=@A1:A3), imports its cached value, is listed inresult.formulas, and is reported asformula:spillReference: HyperFormula supports neither. - Where HyperFormula spills differently from Excel, the import follows HyperFormula. Before it returns, it computes the array formulas in a temporary copy of the Formulas plugin’s engine. A cell the array does not reach there keeps its cached value. For example, a legacy array
{=A1*2}entered overB1:B3is one value to HyperFormula, soB2andB3keep the 2 the file holds. An array that would show#SPILL!imports its cached values when the file holds a value for it, is listed inresult.formulas, and is reported asformula:arraySpill. When the file holds an error for it, as Excel 365 does for a spill that is already blocked, the array stays live and shows#SPILL!, as in Excel. After you clear the cells in its way, enter the formula again to make it spill: HyperFormula does not spill it on its own. This happens withFILTER, which HyperFormula sizes by its input range. An array that shows an error there while the file holds a value is imported the same way: HyperFormula shows#VALUE!for an array that is only a range, such as=A1:A3or=Sheet1!A1:A3, a range name, orINDEX(A1:A3,0), where Excel spills the cells. The temporary copy holds only the imported sheet and the engine’s workbook-level names. An array formula that reads another sheet of the engine, or a name the engine defines for the grid’s sheet only, therefore keeps its cached values only when it would show#SPILL!. Otherwise it is imported live, as described above. An array that shows an error while the file holds no value for it, as in files written by openpyxl, is imported live too. - A plain formula that is one call to a function that returns an array, such as
=SEQUENCE(3)saved without the array flag, is one value in Excel, which adds an@to it. The import keeps that one value: when HyperFormula would spill it over more cells, the cell imports its cached value, is listed inresult.formulas, and is reported asformula:arraySpill. When the result is one cell, the formula stays live. - Legacy (CSE) arrays import as
=ARRAYFORMULA(...). The ExcelJS engine cannot tell the two kinds apart, so it imports every array this way. A dynamic array imports this way too when HyperFormula’suseArrayArithmeticis off, as in an engine you create withHyperFormula.buildEmpty(): without it, such an engine computes a formula like=A1:A3*2as one value. An export of that grid writes the array as a legacy array. - Defined names are added to the Formulas plugin’s engine as named expressions, and
result.namedExpressionslists them. A formula such as=SUM(Sales)stays live. A name HyperFormula cannot take, one that points at a sheet it does not hold, one that calls a function HyperFormula lacks (such asUNIQUE), one past the defined-name limits (a definition longer than 8,192 characters, or ranges that cover more than 100,000 cells), or one scoped to a sheet the workbook does not have is reported asdefinedNames:unsupported. The formulas over it import their cached values and are reported asformula:definedName. A name scoped to the imported sheet is added at workbook scope, ahead of a workbook name spelled the same, because the grid holds one sheet. A workbook name whose definition uses that spelling is then reported the same way, because in the file it reads the workbook name. If the engine fails to add a name while the result is applied, the import removes it again and reportsdefinedNames:unsupported. The formulas over that name stay in the grid and show#NAME?, because no cached value is available at that point. The ExcelJS engine adds no names, because ExcelJS loses their scope, so the formulas over every name import their cached values; see Known differences. The names live only in the engine: turning the Formulas plugin off and on again builds a new engine without them, so add them back fromresult.namedExpressions. - Errors that Excel stores as rich values, such as
#SPILL!and#CALC!, import as that error. Excel 365 and Excel for the web cache#VALUE!for them in the cell and keep the real error elsewhere in the file. The ExcelJS engine cannot read it, so with ExcelJS such a cell imports#VALUE!. With the Formulas plugin on, an error HyperFormula does not have, such as#CALC!or#FIELD!, imports as#VALUE!, soIFERRORandISERRORstill treat the cell as an error. A cell holding a linked data type, such as Stocks or Geography, imports#VALUE!and addscellValue:richValuetoresult.dropped. TRUEandFALSEwritten without parentheses becomeTRUE()andFALSE()in the live formula, because HyperFormula reads the bare words as names.result.formulaskeeps the text from the file.
When the engine already defines a name, at workbook scope or on the grid’s own sheet (which HyperFormula reads first there), the import keeps that definition if it matches the file’s. A name defined as a constant, such as addNamedExpression('TaxRate', 0.07), matches when the file’s definition has the same value. A text constant with a quotation mark, which Excel writes as "a""b", is added as the text a"b. If the definition differs, as in a second import of a changed file, the import does not replace it. The formulas over that name import their cached values, and the import reports formula:definedName and definedNames:unsupported. To take the file’s definition, remove the name first with HyperFormula’s removeNamedExpression().
Re-importing an export
A workbook you exported from Handsontable DataGrid carries its nested headers and row headers as sheet content. Import it with headerRows set to the header depth and rowHeaders: true, or the second header row lands in the data and the row numbers become a column.
await hot.getPlugin('importFile').importFromBlob('xlsx', file, { colHeaders: 'firstRow', headerRows: 2, rowHeaders: true,});A multi-sheet export carries its header rows on every sheet. When you import such a workbook sheet by sheet with colHeaders: 'firstRow' and rowHeaders: true, a defined name that points at another sheet keeps the file’s addresses, because the import knows only the headers of the sheet it reads. A formula over that name then reads the wrong cells. Import such a workbook without stripping the header rows, or add the names back yourself from result.namedExpressions after you correct them.
A grid with a columnSummary exports the computed total, and the import loads that total back as data, so a summary over the whole column then shows double. Set the summary’s ranges to stop above the summary row. Before an import with more rows, turn the summary off with updateSettings({ columnSummary: false }), and pass your endpoints again after the import. Otherwise the summary keeps writing to the row it computed for the previous data, over an imported value.
Imported headers are HTML-escaped, so R&D is stored as R&D. Set the grid’s textExtractor option to true to re-export them as the text the workbook held.
Keep the imported sheet through a re-render
HotTable calls updateSettings with its props on every render. A re-render of the component that renders HotTable sends its props again (columns, colWidths, and a few others are skipped when unchanged). Your original data and colHeaders replace the imported sheet, and props such as mergeCells={true} and hiddenColumns={true} clear its merges and hidden columns. React Compiler can hide this in one component and not in the next, so do not rely on it.
-
Keep import state, such as a status text, in a sibling component, as the demo’s
ImportControlsdoes. -
Render
HotTablein aReact.memocomponent with stable props, so a parent’s re-render does not reach it:const Grid = React.memo(({ rows, hotRef }) => (<HotTable// licenseKey="your-license-key" // get a key at https://handsontable.com/pricing/ref={hotRef}data={rows}importFile={true}/>)); -
A grid fed by a server prop must keep the first value, because each router response sends a new array:
const [firstRows] = useState(rows);and then<Grid rows={firstRows} hotRef={hotRef} />.
Unmounting the grid, or hiding it with <Activity mode="hidden">, destroys the instance and its imported sheet. Keep the file in state and import it again when the grid is back.
Import into a sheets bar workbook
With the sheets bar, an import changes the active sheet only, and the framework sections above don’t apply: a re-render doesn’t replace the imported layout.
Options
Pass these options as the third argument of importFromArrayBuffer(format, buffer, options) or importFromBlob(format, blob, options). importFromArrayBuffer() also accepts a view over a buffer, such as a Uint8Array.
| Option | Type | Default | Description |
|---|---|---|---|
sheet | string | number | 0 | Sheet to import, by name or by index. The index counts the sheets that are not very hidden and skips the _HotValidation sheet an export writes for its dropdown lists. |
colHeaders | boolean | 'firstRow' | false | 'firstRow' promotes the first row to column headers. true behaves like false: the grid keeps its current colHeaders. |
headerRows | number | 1 | How many rows the header band spans, with colHeaders: 'firstRow'. Above 1, those rows become nested headers and the data starts after them. Has to be an integer of at least 1. |
rowHeaders | boolean | false | Drop the first column and turn generated row headers on. |
range | number[] | whole sheet | [startRow, startColumn, endRow, endColumn] in 0-based sheet coordinates, each start at or before its end. An end past the sheet is clamped; a start past the sheet rejects the call. |
inferCellTypes | boolean | true | Derive numeric, date, intl-datetime, time, checkbox, and dropdown types from the cells’ number formats and values. The type most cells of a column share becomes the column’s type in result.columns; cells that differ get their own result.cellsMeta entry. false turns dropdowns off too, and dates import as raw serials. Details: number formats. |
importFormulas | boolean | true | Put formulas into the data when the Formulas plugin is on. The _xlfn., _xlws., and _xlpm. prefixes Excel writes are removed, and an export writes them back. A function HyperFormula lacks, such as SORT or LET, imports live and shows an error; set importFormulas: false to import the cached values instead. See Formulas, arrays, and defined names. |
importLayout | boolean | true | Apply merges, hidden rows and columns, frozen panes, column widths, and row heights. An import describes the whole sheet: each of these, and customBorders, that the grid has and the result does not carry is reset, including one you set yourself. Borders themselves come from importStyles, so with importStyles off an import clears the grid’s custom borders. The lists merge into an existing hiddenRows, hiddenColumns, or mergeCells options object, so options such as indicators survive. Independently of importLayout, a grid that already has a columns setting gets one empty column setting per imported column when the result carries no columns, so the previous file’s column types do not survive. |
importStyles | boolean | false | Apply alignment, font, fill, and borders from the workbook. See Styles. |
apply | boolean | true | Apply the result to the grid. With false, the result comes back and nothing changes, neither hook fires, and the dropped-features warning still prints. Its dropped leaves out the names that come from applying (cellType:<name>, mergeCells, hiddenRows, hiddenColumns, and layoutDirection), so a preview’s list can be shorter than the real import’s. |
engine | object | null | - | Engine for this call only. See Choose the engine. |
What your license unlocks
Formulas, styles, layout, comments, conditional formatting, data validation, and sheet protection read from the workbook are available as a bundle: the XLSX add-on. Every key issued before 19.0 includes all of it. Number formats always import, because the import reads them to infer cell types.
When your tier leaves out part of the add-on, the import skips that part of the file and does the rest. Without cell styles, for example, each cell imports without its font, fill, borders, and alignment. If the file carries a skipped feature and your options ask for it, the import adds a license:xlsx:<id> entry to result.dropped and prints one console warning that names what it skipped. When your tier does not include Excel import at all, importFromBlob() and importFromArrayBuffer() throw, and supportsImportFormat('xlsx') returns false, so check it before you offer an Excel upload.
Styles
By default, the plugin reads cell styling from the workbook and reports it as cellStyles in result.dropped instead of applying it. Set importStyles: true to apply it:
await hot.getPlugin('importFile').importFromBlob('xlsx', file, { colHeaders: 'firstRow', importStyles: true,});| Workbook style | Grid result |
|---|---|
| Horizontal and vertical alignment | A class name (htLeft, htCenter, htRight, htJustify, htTop, htMiddle, htBottom). |
| Bold, italic, underline, and font color | A generated class name, with its CSS declarations in result.styles. |
| Background fill | The same generated class name and declaration. |
| Borders | An entry in result.customBorders, in the shape the CustomBorders option takes. |
A class name goes on result.columns[].className when every cell of a column shares it, and on result.cellsMeta[].meta.className otherwise. The plugin installs result.styles in one <style> element owned by the grid, replaces it on the next import, and removes it when the grid is destroyed.
With importStyles: true, borders pass to the CustomBorders plugin through updateSettings, so an import replaces the borders the grid had, whatever importLayout is. When the result carries no borders - the workbook has none, or importStyles is off - importLayout clears the grid’s custom borders. Font size and font name are not imported. Conditional formatting is returned on result.conditionalFormatting, in the shape the export’s conditionalFormatting option takes, and never applied.
Methods
Each import method takes the format first ('xlsx'), the file second, and an options object third.
importFromBlob(format, blob, options)- imports aBlobor aFile, such as one from an<input type="file">. Returns aPromiseof the result.importFromArrayBuffer(format, buffer, options)- imports anArrayBuffer, or a view over one such as aUint8Array. Returns aPromiseof the result.supportsImportFormat(format)-truewhen the format can be read.supportsImportFormat('xlsx')also returnsfalsewhen your license key does not include Excel import.
To type data that doesn’t come from a workbook, such as CSV rows, pasted text, or API values, use inferColumnTypes(). See Infer column types.
Hooks
beforeImport(result, format)runs after mapping and before applying. Returnfalseto leave the grid untouched; the promise still resolves with the result. Mutateresultto change what is applied. The dropped-features warning is already printed by then.afterImport(result, format)runs after the grid was updated.- Neither hook runs for
apply: false, or for an import that a newer import replaced (see Concurrent imports).
Related articles
Related guides
Related API reference
Configuration options
Plugins
Hooks