Part 16 of the series: "Extending bpmn-io Form-JS Beyond Its Limits"
A form designer opens the properties panel for a dropdown field. They need to add 200 options. The properties panel shows an Add button that creates one option at a time — a label field and a value field, one row per option.
They click Add. They type a label. They type a value. They click Add again. They type a label. They type a value.
Two hundred times.
This is not a hypothetical. It happened. Someone spent three hours populating a dropdown with regional office codes that already existed in a spreadsheet their team maintained. They typed every entry manually because there was no other way.
After that support ticket, I added Excel import and export to the properties panel's dropdown option manager. This article documents how it works, including the specific implementation decisions that make it robust when users don't follow the expected file format.
The Problem
The built-in static values manager in the properties panel is designed for small option lists. It works well for 5-10 options configured by the form designer. It breaks down at 50+ options and is genuinely unusable at 200+.
The data that populates large dropdowns almost always exists somewhere as a spreadsheet. Regional codes, product categories, department names, approval status codes — these are maintained by business teams in Excel. The form designer's job should be to configure the form, not to re-enter data that already exists.
The solution: let form designers import options from Excel and export the current options back to Excel for editing. Two buttons. One for each direction.
What I Tried First
My first attempt used the SheetJS library (xlsx package):
// ❌ SheetJS — has licensing complications
import * as XLSX from 'xlsx';
const workbook = XLSX.read(arrayBuffer, { type: 'array' });
const sheet = workbook.Sheets[workbook.SheetNames[0]];
const data = XLSX.utils.sheet_to_json(sheet, { header: 1 });
SheetJS works technically. The problem is licensing — the free version has restrictions for commercial use and the paid version requires a license per developer. For an internal tool at a company, this is manageable but adds procurement friction.
ExcelJS is MIT-licensed, browser-compatible, and has a clean API for both reading and writing. I switched to ExcelJS and haven't had a reason to go back.
The second issue with my first attempt was file input UX. I rendered a <input type="file"> directly in the properties panel UI. In the properties panel's narrow sidebar, a file input's default appearance — a "Choose File" button with "No file chosen" text — takes too much space and looks inconsistent with the rest of the panel.
The hidden file input pattern solves this: a styled button that the user sees triggers a click on a hidden file input. The file input does the work (opening the OS file picker, accepting the file) but is invisible.
The Implementation: Import
The Hidden File Input Pattern
The UI has two visible buttons (Import and Export) and one hidden file input:
// In the ListGroup component
jsxs("div", {
class: styles.listActions,
children: [
// ✅ Visible Add button
jsx("button", {
type: "button",
class: classnames(styles.button, styles.add),
onClick: add,
title: "Add new option",
children: "+"
}),
// ✅ Hidden file input — never visible to user
jsx("input", {
type: "file",
accept: ".xlsx,.xls",
style: { display: 'none' },
id: `${id}-file-input`, // ✅ ID used to trigger click
onChange: handleFileImport
}),
// ✅ Visible Import button — triggers hidden input
jsx("button", {
type: "button",
class: classnames(styles.button, styles.import),
onClick: () => document.getElementById(`${id}-file-input`).click(),
title: "Import from Excel",
children: "Import"
}),
// ✅ Visible Export button
jsx("button", {
type: "button",
class: classnames(styles.button, styles.export),
onClick: exportExcel,
title: "Export to Excel",
disabled: items.length === 0,
children: "Export"
})
]
})
When the user clicks Import, document.getElementById().click() programmatically triggers the hidden file input. The browser's native file picker opens. The user selects a file. The file input fires its onChange event. The handleFileImport function processes it.
Why use id to find the input rather than a ref? In Preact properties panel components (as discussed in Article 14), components are called as functions rather than mounted by a renderer — which means useRef doesn't work the same way in all entry factory contexts. Using id and document.getElementById is reliable in all contexts where the DOM exists.
Why not use a <label> element pointing to the file input? A <label for="..."> would also trigger the file input when clicked, without JavaScript. This works for simple cases but is harder to style consistently with the other buttons in the panel and requires careful HTML structure to avoid the label wrapping the button text.
Reading the File: readAsArrayBuffer
const handleFileImport = (event) => {
const file = event.target.files[0];
if (!file) return;
// ✅ Validate file type before reading
const fileName = file.name.toLowerCase();
if (!fileName.endsWith('.xlsx') && !fileName.endsWith('.xls')) {
notify.error('Please select a valid Excel file (.xlsx or .xls)', 4000);
event.target.value = ''; // Reset input
return;
}
const reader = new FileReader();
reader.onload = async (e) => {
try {
const workbook = new ExcelJS.Workbook();
// ✅ readAsArrayBuffer, not readAsDataURL
await workbook.xlsx.load(e.target.result);
const worksheet = workbook.worksheets[0];
if (!worksheet) {
throw new Error('No worksheets found in Excel file');
}
// Convert to array of arrays for processing
const data = [];
worksheet.eachRow((row, rowNumber) => {
const rowData = [];
row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
rowData.push(cell.value ? String(cell.value) : '');
});
data.push(rowData);
});
if (data.length === 0) {
throw new Error('Excel file appears to be empty');
}
processImportedData(data);
} catch (error) {
notify.error(`Error parsing Excel file: ${error.message}.`, 4000);
}
};
reader.onerror = () => {
notify.error('Error reading file. Please try again.', 4000);
};
// ✅ readAsArrayBuffer — what ExcelJS.load() expects
reader.readAsArrayBuffer(file);
// ✅ Reset the input so the same file can be imported again
event.target.value = '';
};
Why readAsArrayBuffer not readAsDataURL?
FileReader.readAsDataURL() returns a base64-encoded data URL string: data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,UEsDB.... ExcelJS's workbook.xlsx.load() expects an ArrayBuffer — raw binary data. Passing a data URL string to load() throws a cryptic error about invalid format.
FileReader.readAsArrayBuffer() returns an ArrayBuffer — exactly what ExcelJS expects. The e.target.result inside reader.onload is the ArrayBuffer ready to pass directly to workbook.xlsx.load().
The mistake of using readAsDataURL is easy to make because it's the more commonly documented FileReader method (used for image previews). For binary file parsing, always use readAsArrayBuffer.
Robust Header Detection
After reading the file, you need to find the "Label" and "Value" columns. The naive approach is to assume column A is Label and column B is Value:
// ❌ Positional detection — breaks when users add columns or reorder
const labelIndex = 0; // Column A
const valueIndex = 1; // Column B
This breaks immediately in real use. The business team's spreadsheet has columns in this order: Code, Description, Region, Active. They export their data, keep it in that format, and send it to the form designer. The form designer imports it. Positional detection reads "Code" as Label and "Description" as Value, which is wrong.
Robust header detection finds the columns by their header content:
function processImportedData(data) {
const headers = data[0] || [];
// ✅ Find columns by content, not position
const labelIndex = headers.findIndex(h =>
h && h.toString().toLowerCase().includes('label')
);
const valueIndex = headers.findIndex(h =>
h && h.toString().toLowerCase().includes('value')
);
if (labelIndex === -1 || valueIndex === -1) {
notify.error(
'Excel file must have "Label" and "Value" columns in the first row. ' +
`Found columns: ${headers.filter(Boolean).join(', ')}`,
6000
);
return;
}
importValues(data, labelIndex, valueIndex);
}
headers.findIndex(h => h.toString().toLowerCase().includes('label')) matches:
-
"Label"✅ -
"label"✅ -
"Option Label"✅ -
"Display Label"✅ -
"Label Text"✅
It does not match:
-
"Value"❌ (correct — not a label column) -
""❌ (correct — empty column) -
undefined❌ (correct — missing column)
The .includes() approach is intentionally permissive. The business team might name their column "Display Label" or "Option Label" and the import should still work. Exact string matching would require the form designer to rename the column before importing.
What if the same file has both "Label" and "Value" in a column name? For example, "Label Value"? findIndex returns the first match, so "Label Value" would be found by the label search. This is an edge case that hasn't occurred in practice — if it did, the form designer would need to rename the column.
Duplicate Detection and Statistics
function importValues(data, labelIndex, valueIndex) {
const importedValues = [];
const skippedRows = [];
// Skip header row (index 0), process data rows
for (let i = 1; i < data.length; i++) {
const row = data[i];
// Skip empty rows
if (!row || row.length === 0) continue;
const label = row[labelIndex] ? row[labelIndex].toString().trim() : '';
const value = row[valueIndex] ? row[valueIndex].toString().trim() : '';
// Skip rows with missing label or value
if (!label || !value) {
skippedRows.push(i + 1); // +1 for 1-based row numbering
continue;
}
// ✅ Duplicate detection — check within imported data only
// (not against existing options — import replaces, not appends)
const existingEntry = importedValues.find(p => p.value === value);
if (existingEntry) {
skippedRows.push(i + 1);
continue;
}
importedValues.push({ label, value });
}
if (importedValues.length === 0) {
notify.warning(
'No valid data found in Excel file. ' +
'Please ensure you have Label and Value columns with data.',
5000
);
return;
}
// ✅ Inform the user of what happened
if (skippedRows.length > 0) {
notify.info(
`Import completed! Imported: ${importedValues.length} options. ` +
`Skipped: ${skippedRows.length} rows (empty or duplicate values). ` +
`Skipped rows: ${skippedRows.join(', ')}`,
6000
);
} else {
notify.success(
`Successfully imported ${importedValues.length} options from Excel file.`,
4000
);
}
// ✅ Update the field's static values
editField(field, 'dropdown_config', {
...config,
static_values: importedValues
});
}
The skip/import statistics notification is important UX. Without it, form designers have no way to know if the import was complete or partial. If they imported 180 rows from a 200-row file, they'd see 180 options and might not know 20 were skipped. The notification shows exactly what happened and which row numbers were skipped so they can investigate.
Duplicate detection checks value, not label. Two options can have the same label (unusual but valid — "North" for two different regional codes). Two options with the same value are always a problem — the form would behave unpredictably when that value is selected.
Import replaces, it doesn't append. editField writes importedValues as the new static_values, replacing whatever was there before. The alternative (appending to existing) would be confusing — importing the same file twice would double the options. Replacement matches the user's mental model: "I'm replacing the options with what's in this file."
The Implementation: Export
Creating the Workbook
const exportExcel = async () => {
if (values.length === 0) {
notify.warning('No options to export', 3000);
return;
}
try {
// ✅ Create workbook and worksheet
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('Dropdown Options');
// ✅ Define columns — sets headers and column widths
worksheet.columns = [
{ header: 'Label', key: 'label', width: 30 },
{ header: 'Value', key: 'value', width: 30 }
];
// ✅ Style the header row
worksheet.getRow(1).font = { bold: true };
worksheet.getRow(1).fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FFE0E0E0' } // Light grey — ARGB format
};
// ✅ Add data rows
values.forEach(v => {
worksheet.addRow({
label: v.label || '',
value: v.value || ''
});
});
// ✅ Generate filename with timestamp
const timestamp = new Date()
.toISOString()
.slice(0, 19)
.replace(/[:.]/g, '-');
// "2024-01-15T10:30:00" → "2024-01-15T10-30-00"
const filename = `dropdown-options-${timestamp}.xlsx`;
// ✅ writeBuffer is async — ALWAYS await it
const buffer = await workbook.xlsx.writeBuffer();
// ✅ Create Blob and trigger download
const blob = new Blob([buffer], {
type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
});
const url = window.URL.createObjectURL(blob);
const link = document.createElement('a');
link.href = url;
link.download = filename;
link.click();
window.URL.revokeObjectURL(url);
notify.success(
`Successfully exported ${values.length} options to ${filename}`,
4000
);
} catch (error) {
notify.error(`Error exporting to Excel: ${error.message}`, 5000);
}
};
The writeBuffer Pitfall
The most common mistake when using ExcelJS for export is treating writeBuffer as synchronous:
// ❌ Missing await — buffer is a Promise, not an ArrayBuffer
const buffer = workbook.xlsx.writeBuffer();
const blob = new Blob([buffer], { type: '...' });
// blob contains a Promise object, not binary data
// download produces a corrupt file
// ✅ Correct — await the async operation
const buffer = await workbook.xlsx.writeBuffer();
const blob = new Blob([buffer], { type: '...' });
// blob contains the actual binary data
workbook.xlsx.writeBuffer() returns a Promise<ArrayBuffer>. Without await, you pass a Promise to Blob. The Blob constructor doesn't throw — it silently converts the Promise to a string representation and creates a Blob of that string. The downloaded file opens in Excel as a corrupt or empty workbook. No error, no indication of what went wrong.
The try/catch block catches the error if await does throw, but the missing await case doesn't throw — it silently corrupts. The only way to catch it is to await every time.
The Timestamp in the Filename
const timestamp = new Date()
.toISOString()
.slice(0, 19)
.replace(/[:.]/g, '-');
new Date().toISOString() returns "2024-01-15T10:30:00.000Z".
.slice(0, 19) takes "2024-01-15T10:30:00".
.replace(/[:.]/g, '-') replaces : and . (invalid filename characters on Windows) with -, giving "2024-01-15T10-30-00".
The final filename is "dropdown-options-2024-01-15T10-30-00.xlsx".
Why include a timestamp? When a form designer exports options, edits them in Excel, and imports them back, they might do this multiple times. Without a timestamp, every export creates dropdown-options.xlsx, overwriting the previous export. A form designer who accidentally imports the wrong version has no way to recover the previous one from their downloads folder. With timestamps, all exports are preserved.
The Styled Header Row
worksheet.getRow(1).font = { bold: true };
worksheet.getRow(1).fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FFE0E0E0' }
};
The grey fill and bold text serve a practical purpose: when the file opens in Excel, the header row is visually distinct from data rows. The form designer can immediately see what the columns are and where to add new options. Without styling, the header row looks identical to data rows — a common source of confusion where users accidentally add a third "header row" of data above the real headers.
The ARGB color format ('FFE0E0E0') is specific to ExcelJS — FF is the alpha channel (fully opaque), followed by E0E0E0 for light grey. Standard CSS hex colors (#E0E0E0) don't work here.
The Reusable Utility
After building the import/export for the dropdown properties panel, the grid field's dynamic import needed the same Excel-reading logic, and the dropdown options export was needed in a second context. I extracted the shared logic:
// src/formjs/shared/utils/excelUtils.js
import ExcelJS from 'exceljs';
/**
* Import label/value pairs from an Excel file.
* Finds Label and Value columns by content, not position.
*
* @param {File} file - The Excel file to import
* @returns {Promise<{
* values: Array<{label: string, value: string}>,
* skippedRows: number[],
* totalRows: number
* }>}
*/
export async function importLabelValueFromExcel(file) {
return new Promise(async (resolve, reject) => {
const reader = new FileReader();
reader.onload = async (e) => {
try {
const workbook = new ExcelJS.Workbook();
await workbook.xlsx.load(e.target.result);
const worksheet = workbook.worksheets[0];
if (!worksheet) {
reject(new Error('No worksheets found in Excel file'));
return;
}
// Build array of arrays from worksheet
const rawData = [];
worksheet.eachRow((row, rowNumber) => {
const rowData = [];
row.eachCell({ includeEmpty: true }, (cell) => {
rowData.push(cell.value !== null && cell.value !== undefined
? String(cell.value)
: ''
);
});
rawData.push(rowData);
});
if (rawData.length === 0) {
reject(new Error('Excel file appears to be empty'));
return;
}
// Find label and value columns by header content
const headers = rawData[0] || [];
const labelIndex = headers.findIndex(h =>
h && h.toString().toLowerCase().includes('label')
);
const valueIndex = headers.findIndex(h =>
h && h.toString().toLowerCase().includes('value')
);
if (labelIndex === -1 || valueIndex === -1) {
reject(new Error(
`Excel file must have "Label" and "Value" columns in the first row. ` +
`Found: ${headers.filter(Boolean).join(', ')}`
));
return;
}
// Process data rows
const values = [];
const skippedRows = [];
for (let i = 1; i < rawData.length; i++) {
const row = rawData[i];
if (!row || row.length === 0) {
skippedRows.push(i + 1);
continue;
}
const label = (row[labelIndex] || '').trim();
const value = (row[valueIndex] || '').trim();
if (!label || !value) {
skippedRows.push(i + 1);
continue;
}
// Duplicate value check
if (values.some(v => v.value === value)) {
skippedRows.push(i + 1);
continue;
}
values.push({ label, value });
}
resolve({
values,
skippedRows,
totalRows: rawData.length - 1 // Exclude header row
});
} catch (error) {
reject(error);
}
};
reader.onerror = () => reject(new Error('Error reading file'));
// ✅ Always readAsArrayBuffer for ExcelJS
reader.readAsArrayBuffer(file);
});
}
/**
* Export label/value pairs to an Excel file and trigger browser download.
*
* @param {Array<{label: string, value: string}>} values
* @param {string} filenamePrefix - e.g., 'dropdown-options'
* @returns {Promise<string>} The filename used for the download
*/
export async function exportLabelValueToExcel(values, filenamePrefix = 'options') {
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('Options');
// Define columns
worksheet.columns = [
{ header: 'Label', key: 'label', width: 30 },
{ header: 'Value', key: 'value', width: 30 }
];
// Style header row
const headerRow = worksheet.getRow(1);
headerRow.font = { bold: true };
headerRow.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FFE0E0E0' }
};
// Add data
values.forEach(v => {
worksheet.addRow({
label: v.label || '',
value: v.value || ''
});
});
// Generate timestamp filename
const timestamp = new Date()
.toISOString()
.slice(0, 19)
.replace(/[:.]/g, '-');
const filename = `${filenamePrefix}-${timestamp}.xlsx`;
// ✅ Always await writeBuffer
const buffer = await workbook.xlsx.writeBuffer();
const blob = new Blob([buffer], {
type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
});
const url = window.URL.createObjectURL(blob);
const link = document.createElement('a');
link.href = url;
link.download = filename;
link.click();
window.URL.revokeObjectURL(url);
return filename;
}
/**
* Validate file type for Excel import.
*
* @param {File} file
* @returns {{ valid: boolean, error?: string }}
*/
export function validateExcelFile(file) {
const name = file.name.toLowerCase();
if (!name.endsWith('.xlsx') && !name.endsWith('.xls')) {
return {
valid: false,
error: 'Please select a valid Excel file (.xlsx or .xls)'
};
}
return { valid: true };
}
How Providers Use the Utility
// DropdownPropertiesProvider.js
import {
importLabelValueFromExcel,
exportLabelValueToExcel,
validateExcelFile
} from '@/formjs/shared/utils/excelUtils';
const handleFileImport = (event) => {
const file = event.target.files[0];
if (!file) return;
// ✅ Validate first
const { valid, error } = validateExcelFile(file);
if (!valid) {
notify.error(error, 4000);
event.target.value = '';
return;
}
// ✅ Import using shared utility
importLabelValueFromExcel(file)
.then(({ values: importedValues, skippedRows, totalRows }) => {
if (importedValues.length === 0) {
notify.warning('No valid data found in Excel file.', 5000);
return;
}
// Show statistics
if (skippedRows.length > 0) {
notify.info(
`Imported: ${importedValues.length} options. ` +
`Skipped: ${skippedRows.length} rows (empty or duplicate). ` +
`Rows: ${skippedRows.join(', ')}`,
6000
);
} else {
notify.success(
`Successfully imported ${importedValues.length} options.`,
4000
);
}
// Update field
editField(field, 'dropdown_config', {
...config,
static_values: importedValues
});
})
.catch(err => {
notify.error(`Error importing: ${err.message}`, 4000);
});
event.target.value = ''; // Reset input
};
const exportExcel = () => {
if (values.length === 0) {
notify.warning('No options to export', 3000);
return;
}
// ✅ Export using shared utility
exportLabelValueToExcel(values, 'dropdown-options')
.then(filename => {
notify.success(`Exported ${values.length} options to ${filename}`, 4000);
})
.catch(err => {
notify.error(`Export failed: ${err.message}`, 5000);
});
};
Edge Cases Worth Handling
Rich text cells in Excel. ExcelJS returns cell values as either primitive types (string, number, boolean) or complex objects (rich text, formula results, hyperlinks). When converting to string with String(cell.value), a rich text object produces "[object Object]". The utility handles this by checking the cell value type:
// More robust cell value extraction
function getCellStringValue(cell) {
const v = cell.value;
if (v === null || v === undefined) return '';
if (typeof v !== 'object') return String(v);
// Rich text
if (v.richText) return v.richText.map(part => part.text).join('');
// Formula result
if (v.result !== undefined) return String(v.result);
// Hyperlink
if (v.text !== undefined) return String(v.text);
return '';
}
Numbers in the Value column. If a form designer exports options, the Value column might contain numeric values like 1, 2, 3. Excel stores these as numbers, not strings. When imported, String(cell.value) converts them to "1", "2", "3". This is usually correct — form option values should be strings. But if the original values were 1, 2, 3 (numbers) when configured in the properties panel, importing them as "1", "2", "3" (strings) changes their type. Keep this in mind if your forms store numeric option values.
The export button disabled state. The Export button is disabled when items.length === 0. This prevents the export handler from running with no data, which would create an Excel file with only the header row — valid but useless and potentially confusing.
jsx("button", {
type: "button",
class: classnames(styles.button, styles.export),
onClick: exportExcel,
disabled: items.length === 0, // ✅ Disabled when nothing to export
title: items.length === 0
? "Add options before exporting"
: "Export to Excel",
children: "Export"
})
Same file imported twice. After a successful import, event.target.value = '' resets the file input. Without this, the same file cannot be imported again in the same session — the browser's onChange event only fires when the selected file changes. If the form designer wants to re-import (perhaps after fixing an error in the file), resetting the input makes it possible.
The Tradeoffs
ExcelJS is not a small dependency. The ExcelJS bundle is approximately 1.5MB uncompressed. For a properties panel that runs only in the editor context, this is acceptable — form designers have better hardware than form users, and the editor isn't performance-critical in the same way as the runtime form. For runtime form renderers, you would not want ExcelJS — it's exclusively an editor/admin tool.
Import replaces all options. There is no merge import — if a form designer imports a file, all existing options are replaced. This is a deliberate simplicity choice. Merge semantics require deciding what to do with conflicts (same value, different label — which wins?). Replacement is unambiguous. The Export button lets form designers export current options, edit the file, and import back — effectively supporting an edit-with-merge workflow.
Header detection by includes() is permissive. A column named "Mislabeled Data" would match the label search. In practice, business spreadsheets don't have column headers that accidentally contain "label" or "value" as substrings, but it's a theoretical risk. Adding a preference for exact matches (check for exactly "Label" first, fall back to includes()) would be more robust.
No progress indicator for large imports. Importing 5000 rows is fast (ExcelJS handles it in under a second in the browser). If you're importing from a very large file, the UI freezes briefly while processing. A Web Worker-based implementation would prevent the freeze but adds significant complexity for a use case (5000+ dropdown options) that's genuinely unusual.
What Comes Next
The import/export pattern is complete for the properties panel. The same ExcelJS library and similar patterns appear in the grid field renderer — where users import data into the grid itself at runtime (not just configure options). The grid's Excel import has additional complexity: column validation, header matching, and row count limits.
Article 17 covers the grid field's Excel import pipeline — reading worksheet data into the grid's internal format, validating column headers against expected columns, handling zero values that look like empty cells, and the getCellValue function that handles ExcelJS's complex cell value types.
This is Part 16 of "Extending bpmn-io Form-JS Beyond Its Limits." The series covers the complete architecture for production-grade Form-JS extensions — the documentation that doesn't exist yet.
Tags: camunda bpmn formjs excel exceljs properties-panel import-export javascript devex
Top comments (0)