Introduction

In many projects, there's a need to import and export Excel data in batches. This functionality can either be handled on the frontend by parsing data and generating files directly in the browser, or through backend services that process file streams and generate downloadable files.

Compared to server-side processing, frontend handling offers better responsiveness. Users can trigger exports instantly without waiting for backend processing. Data generation and validation can also be performed client-side, reducing server load and improving user experience.

The choice between frontend and backend implementation depends on business requirements. For small datasets requiring real-time operations, frontend solutions are preferred. However, large datasets with complex business logic or high securiyt needs may benefit more from backend processing.

Solution Overview

Historically, xlsx combined with xlsx-style was commonly used. While xlsx (also known as SheetJS) is popular, its community version lacks styling capabilities requiring the Pro version. Both libraries haven't been updated recently and are no longer recommended.

A better approach uses ExcelJS with FileSaver. ExcelJS is an open-source library supporting both import/export operations with extensive styling options. FileSaver handles saving generated files locally. This article demonstrates implementing Excel operations using these libraries.

About ExcelJS

ExcelJS is a powerful JavaScript library for creating, reading, and modifying Excel files in Node.js and browsers. Key features include:

• Creating and modifying workbooks with worksheets, rows, and cells
• Reading and parsing existing Excel files
• Exporting to multiple formats (XLSX, XLS, CSV)
• Supporting advanced faetures like formulas, charts, data validation, conditional formatting, and worksheet protection
• Customizing styles including fonts, colors, borders, alignment, and number/date formatting

Getting Started

Installation

npm install exceljs
npm install file-saver

Exporting Excel Files

Here's how to create and download an Excel file using ExcelJS and FileSaver:

<template>
  <button @click="generateSpreadsheet">Export Excel</button>
</template>

<script setup>
import ExcelJS from "exceljs";
import { saveAs } from "file-saver";

const generateSpreadsheet = () => {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Data Sheet");
  
  const sampleData = [
    {"Name": "John", "Age": 25, "Height": 180, "Weight": 75},
    {"Name": "Jane", "Age": 30, "Height": 165, "Weight": 60},
    {"Name": "Bob", "Age": 45, "Height": 175, "Weight": 85}
  ];
  
  const columnHeaders = Object.keys(sampleData[0]);
  worksheet.addRow(columnHeaders);
  
  sampleData.forEach(record => {
    worksheet.addRow(Object.values(record));
  });
  
  workbook.xlsx.writeBuffer().then(buffer => {
    const blob = new Blob([buffer], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"});
    saveAs(blob, "exported-data.xlsx");
  }).catch(err => console.error('Export failed:', err));
};
</script>

Importing Excel Files

To read Excel files, use FileReader to convert files to array buffers:

<template>
  <input type="file" accept=".xlsx,.xls" @change="processImport"/>
</template>

<script setup>
import ExcelJS from "exceljs";

const processImport = (event) => {
  const [file] = event.target.files;
  const reader = new FileReader();
  
  reader.onload = (e) => {
    const workbook = new ExcelJS.Workbook();
    
    workbook.xlsx.load(e.target.result).then(() => {
      const worksheet = workbook.worksheets[0];
      const headerRow = worksheet.getRow(1);
      
      const headers = [];
      headerRow.eachCell(cell => headers.push(cell.value));
      
      const results = [];
      for(let rowIndex = 2; rowIndex <= worksheet.rowCount; rowIndex++) {
        const rowObject = {};
        const currentRow = worksheet.getRow(rowIndex);
        
        currentRow.eachCell((cell, colIndex) => {
          rowObject[headers[colIndex-1]] = cell.value;
        });
        
        results.push(rowObject);
      }
      
      console.log(results);
    });
  };
  
  reader.readAsArrayBuffer(file);
};
</script>

Advanced Operations

Structured Data Addition

You can define columns with specific properties and add structured data:

const generateStructuredSpreadsheet = () => {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Structured Data");
  
  worksheet.columns = [
    {header: "Full Name", key: "fullName", width: 25},
    {header: "Years Old", key: "yearsOld", width: 12},
    {header: "Stature", key: "stature", width: 12},
    {header: "Mass", key: "mass", width: 12}
  ];
  
  worksheet.addRow({fullName: "Alice Smith", yearsOld: 28, stature: 170, mass: 65});
  worksheet.addRow({fullName: "Charlie Brown", yearsOld: 35, stature: 180, mass: 80});
  
  workbook.xlsx.writeBuffer().then(buffer => {
    const blob = new Blob([buffer], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"});
    saveAs(blob, "structured-export.xlsx");
  });
};

Data Reading Methods

Various methods exist for accessing spreadsheet data:

• getRow(rowNumber) - Retrieve specific row by index
• getColumn(identifier) - Access column by key, letter, or ID
• eachCell(callback) - Iterate through all cells in a row/column