Work with Excel and CSV Files
Work with Excel and CSV Files
Section titled “Work with Excel and CSV Files”In this tutorial, you’ll load Excel or CSV files, convert them into structured data, and write data back to files.
This pattern is useful for importing supplier lists, exporting reports, or integrating with external systems that rely on spreadsheets.
What you’ll build
Section titled “What you’ll build”By the end of this tutorial, you will:
- Load an Excel or CSV file from the filesystem
- Parse rows into objects
- Work with the data in your application
- Write objects back to Excel or CSV files
Prerequisites
Section titled “Prerequisites”- Access to the
workingfilesystem area - A sample Excel (
.xlsx) or CSV file - A script or workflow step to run code
Step 1: Load the file
Section titled “Step 1: Load the file”Read the file from the filesystem as binary data:
const file = await this.fs.readFile( "working", "data/suppliers.xlsx", "binary");Step 2: Parse the file
Section titled “Step 2: Parse the file”Use the XLS utility to parse the file:
const xls = await SimpleXLS.fromBuffer(file.content, "xlsx");Convert the sheet into objects:
const rows = xls.parseToObjects();Each row becomes an object using the header row as keys.
Example output:
[ { "id": 1, "name": "Supplier A", "city": "Berlin" }, { "id": 2, "name": "Supplier B", "city": "Munich" }]Note
The first row of the spreadsheet is used as the header row.
Make sure your file contains column names in the first row.
Step 3: Work with the data
Section titled “Step 3: Work with the data”You can now use the parsed data in your application:
const supplierNames = rows.map(r => r.name);Typical use cases:
- Import data into your database
- Validate or transform records
- Feed data into workflows
Step 4: Create data to export
Section titled “Step 4: Create data to export”Prepare objects you want to write to a file:
const output = [ { id: 1, name: "Item A" }, { id: 2, name: "Item B" }];Step 5: Write to Excel or CSV
Section titled “Step 5: Write to Excel or CSV”Create a new workbook and fill it with data:
const workbook = new SimpleXLS();workbook.fillWithObjects(output);Convert to a buffer:
const buffer = await workbook.toBuffer("xlsx");Save to the filesystem:
await this.fs.writeFile( "working", "generated/output.xlsx", buffer, "binary");To write CSV instead:
const csvBuffer = await workbook.toBuffer("csv");Step 6: Advanced formatting (optional)
Section titled “Step 6: Advanced formatting (optional)”For more control (styles, formatting, templates), access the underlying workbook:
const sheet = workbook.getWorksheet(1);sheet.getRow(1).font = { bold: true };This allows you to:
- Format headers
- Apply styles
- Use templates
Result
Section titled “Result”You can now read and write Excel and CSV files in your application.
This enables:
- Data import workflows
- Report generation
- Integration with external systems
What’s happening
Section titled “What’s happening”The file is loaded as binary data, parsed into structured objects, and manipulated in memory.
When exporting, objects are converted back into spreadsheet format and written to the filesystem.
Next steps
Section titled “Next steps”- Import data into your database
- Generate reports as Excel files
- Combine with AI for data enrichment
- Use templates for formatted exports
Related guides
Section titled “Related guides”- Working with Files
- Document Generation
- Workflow Fundamentals