Exporting Excel with SheetJS for Dummies

tamnguyen — Fri, 23 Aug 2024 06:35:44 +0000

If you’ve ever worked with spreadsheets in a web application, you know how crucial it is to handle Excel files efficiently. Multiple powerful libraries let you read, write, and manipulate spreadsheet data with ease, and my weapon of choice is SheetJS. Whether you're building a data analytics tool, a reporting dashboard, or just need to manage spreadsheet data in your web app, SheetJS can be a game changer.

In this guide, I'll show you how to quickly create an Excel report to export using SheetJS with a real-world example. Let’s dive right in!

Setup

The example I'll be using is a simple React application displaying a sales data table with basic grouping in the header

Initialize the project using Vite with the command:

npm create vite sheetjs-demo

and follow the instructions to get the project up and running.

I'll use Ant Design for the UI components and SheetJS (or xlsx). Let's add it to our project:

npm install antd xlsx

The project setup is done. Now let's add some code in the App.tsx file to display our sales table:


import { Button, Table } from "antd";

const months = ["jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec"];
const items = ["Monitors", "Speakers", "Keyboards", "Mouses", "Laptops"];

function generateSalesData() {
  return months.reduce(
    (prev, month) => ({ ...prev, [month]: Math.floor(Math.random() * 100) }),
    {}
  );
}

function getDatasource() {
  return items.map((item) => ({ item, ...generateSalesData() }));
}

function capitalizeFirstLetter(string: string) {
  return string.charAt(0).toUpperCase() + string.slice(1);
}

function createExcel() {
  // WIP
}

function App() {
  const datasource = getDatasource();
  const columns = [
    {
      title: "Item",
      dataIndex: "item",
      key: "item",
    },
    {
      title: "1st Half 2024",
      children: months.slice(0, 6).map((month) => ({
        title: capitalizeFirstLetter(month),
        dataIndex: month,
        key: month,
      })),
    },
    {
      title: "2nd Half 2024",
      children: months.slice(6).map((month) => ({
        title: capitalizeFirstLetter(month),
        dataIndex: month,
        key: month,
      })),
    },
  ];

  return (
    <main>
      <Table size="small" columns={columns} dataSource={datasource} pagination={false} bordered />
      <Button
        onClick={() => createExcel()}
        style={{ float: "right", marginTop: "12px" }}
        type="primary"
      >
        Export to Excel
      </Button>
    </main>
  );
}

export default App;

Now you should have a working app showing the sales data of the items for each month

Create Excel File

Let's start with the basics first. Update the createExcel function to create the Excel file:

import * as XLSX from "xlsx";

function createExcel() {
  const data = getDatasource();

  // Convert data to worksheet
  const workSheet = XLSX.utils.json_to_sheet(data);

  // Create a new workbook and append the worksheet
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, workSheet, "2024 Sales");

  // Write the workbook to a file
  XLSX.writeFile(workbook, "Sales.xlsx");
}

The output:

Our first version doesn't have the grouping header for the 1st and 2nd Half 2024. Let's add that by first adding the header, then appending the data source right after the header:

// version 2
function createExcel() {
  const data = getDatasource();

  // Create the sheet with the grouping header row
  const workSheet = XLSX.utils.aoa_to_sheet([
    // Each array represents each row, and each item in the array represents each column
    // using "" as blank values
    ["item", "1st Half 2024", "", "", "", "", "", "2nd Half 2024"],
  ]);

  // Append data to the current worksheet at row 2 of the first column (A)
  XLSX.utils.sheet_add_json(workSheet, data, { origin: "A2" });

  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, workSheet, "2024 Sales");
  XLSX.writeFile(workbook, "Sales.xlsx");
}

Version 2 output

Now we have all the values in place, the next step is to beautify our table

// version 3
function createExcel() {
  const data = getDatasource();

  // Capitalize Item string
  const workSheet = XLSX.utils.aoa_to_sheet([
    ["Item", "1st Half 2024", "", "", "", "", "", "2nd Half 2024"],
  ]);

  // Separate the header from the data to capitalize
  const header = Object.keys(data[0]).map((key) => capitalizeFirstLetter(key));
  // Add the header to 2nd row with origin: A2
  XLSX.utils.sheet_add_aoa(workSheet, [header], { origin: "A2" });
  // Using skipHeader: true and move the origin below 1 row (to A3) to avoid writing the header again
  XLSX.utils.sheet_add_json(workSheet, data, { origin: "A3", skipHeader: true });

  // Merge the cells in header rows
  workSheet["!merges"] = [
    // s: start, e: end; r: row, c: column
    // merge the Item cell
    { s: { r: 0, c: 0 }, e: { r: 1, c: 0 } },
    // merge the 1st Half 2024 cell
    { s: { r: 0, c: 1 }, e: { r: 0, c: 6 } },
    // merge the 2nd Half 2024 cell
    { s: { r: 0, c: 7 }, e: { r: 0, c: 12 } },
  ];
  // Set the first column width to 10 characters, and the following 12 columns' width to 30 pixels
  workSheet["!cols"] = [{ wch: 10 }, ...Array(12).fill({ wpx: 30 })];

  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, workSheet, "2024 Sales");
  XLSX.writeFile(workbook, "Sales.xlsx");
}

The final output

Summary

In this example, I showed you some basic utility functions that xlsx provides. Here's the recap:

We first create a sheet with json_to_sheet or aoa_to_sheet depending on our data (json for objects and aoa for arrays).

We can append more data to the sheet we just created with functions: sheet_add_json and sheet_add_aoa by providing the sheet, data, and options (like origin to specify the location to append the new data)

We can change the sheet structure by merging the cells with ["!merges"] or setting the columns width with ["!cols"]

Next, we create a workbook with the function book_new and append the sheet we created so far with book_append_sheet

Finally, we export the workbook to a file by calling writeFile and providing a unique name.

You can check out the full demo here: https://github.com/tam-nguyen-vn/sheetjs-demo

Forem: tamnguyen

Exporting Excel with SheetJS for Dummies

Setup

Create Excel File

Summary