I recently needed to implement a workflow where users download an Excel template, fill it in, and upload it back for processing. The key requirements were:

  • Template contents vary per project (member lists and file lists are fetched dynamically)
  • Dropdown cells should reference data in other sheets as their options
  • The Excel file should be generated server-side in a Next.js API route

ExcelJS handles all of this. This post covers how.

ExcelJS

ExcelJS is a library for reading and writing Excel files (.xlsx) in Node.js and the browser. It has 14,000+ GitHub stars and around 2 million weekly npm downloads.

npm install exceljs

It supports styling, formulas, images, and more, but this article focuses on data validation — specifically, dropdown lists.

Template Structure

The generated Excel has 4 sheets:

SheetRole
ActivityRecord of actions (user input)
EntityRecord of resources (user input)
MembersMember list (reference data)
FilesFile list (reference data)

The Activity sheet’s “Agent” column offers a dropdown populated from the Members sheet. The Entity sheet’s “Generated By” column references Activity IDs as a dropdown.

API Route Skeleton

Implemented as a Next.js App Router Route Handler:

// src/app/api/projects/[id]/template/route.ts
import { NextRequest, NextResponse } from "next/server";
import ExcelJS from "exceljs";

export async function GET(
  request: NextRequest,
  { params }: { params: Promise<{ id: string }> }
) {
  const { id: projectId } = await params;

  // Fetch project data from external API
  const members = await fetchMembers(projectId);

  const workbook = new ExcelJS.Workbook();
  // ... create sheets (see below)

  const buffer = await workbook.xlsx.writeBuffer();

  return new NextResponse(buffer, {
    status: 200,
    headers: {
      "Content-Type":
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      "Content-Disposition": `attachment; filename="template.xlsx"`,
    },
  });
}

writeBuffer() converts the workbook to binary, which is returned with the appropriate Content-Type.

Creating Reference Data Sheets

First, create the sheets that will serve as dropdown option sources.

const membersSheet = workbook.addWorksheet("Members");
membersSheet.columns = [
  { header: "Display Name", key: "displayName", width: 30 },
  { header: "ID", key: "userId", width: 15 },
  { header: "URI", key: "uri", width: 50 },
];

// Header styling
membersSheet.getRow(1).eachCell((cell) => {
  cell.font = { bold: true, color: { argb: "FFFFFFFF" } };
  cell.fill = {
    type: "pattern",
    pattern: "solid",
    fgColor: { argb: "FF4472C4" },
  };
});

for (const m of members) {
  membersSheet.addRow({
    displayName: `${m.name} (${m.id})`,
    userId: m.id,
    uri: m.uri,
  });
}

Setting Up Dropdowns

This is the core of the article. ExcelJS provides a dataValidation property on cells to configure input rules.

Fixed List Dropdown

When options are predetermined, pass a comma-separated string to formulae:

const ACTIVITY_TYPES = [
  "Problematizing",
  "Collecting",
  "Interpreting",
  "Narrating",
];

const actSheet = workbook.addWorksheet("Activity");
actSheet.columns = [
  { header: "ID", key: "id", width: 12 },
  { header: "Type", key: "type", width: 16 },
  { header: "Agent", key: "agent", width: 30 },
  { header: "Start Date", key: "startDate", width: 14 },
];

const inputRows = 100;

for (let row = 2; row <= inputRows + 1; row++) {
  actSheet.getCell(`B${row}`).dataValidation = {
    type: "list",
    allowBlank: true,
    formulae: [`"${ACTIVITY_TYPES.join(",")}"`],
    showErrorMessage: true,
    errorTitle: "Invalid value",
    error: "Please select a type from the list.",
  };
}

Note: the formulae value must be wrapped in double quotes inside the string — [`"${array.join(",")}"`].

Cross-Sheet Reference Dropdown

For dynamic data, reference a cell range in another sheet using standard Excel formula notation:

const memberCount = Math.max(members.length, 1);

for (let row = 2; row <= inputRows + 1; row++) {
  actSheet.getCell(`C${row}`).dataValidation = {
    type: "list",
    allowBlank: true,
    formulae: [`Members!$A$2:$A$${memberCount + 1}`],
  };
}

Members!$A$2:$A$5 is standard Excel notation. The $ signs denote absolute references, preventing the range from shifting on copy or move.

Cross-Reference Between Input Sheets

You can also reference one input sheet from another. Here, the Entity sheet references Activity IDs:

const entSheet = workbook.addWorksheet("Entity");
entSheet.columns = [
  { header: "ID", key: "id", width: 12 },
  { header: "Type", key: "type", width: 18 },
  { header: "Description", key: "desc", width: 60 },
  { header: "Generated By", key: "generatedBy", width: 16 },
];

for (let row = 2; row <= inputRows + 1; row++) {
  entSheet.getCell(`D${row}`).dataValidation = {
    type: "list",
    allowBlank: true,
    formulae: [`Activity!$A$2:$A$${inputRows + 1}`],
  };
}

As the user enters Act_0001, Act_0002, etc. in the Activity sheet, those values automatically appear in the Entity sheet’s “Generated By” dropdown. This cross-referencing between input sheets is particularly useful.

Date Formatting

Use numFmt to set the display format for date columns:

for (let row = 2; row <= inputRows + 1; row++) {
  actSheet.getCell(`D${row}`).numFmt = "yyyy-mm-dd";
}

Client-Side Download

On the frontend, fetch the API and trigger a download via a temporary anchor element:

const handleDownload = async () => {
  const response = await fetch(`/api/projects/${projectId}/template`);
  const blob = await response.blob();
  const url = window.URL.createObjectURL(blob);
  const a = document.createElement("a");
  a.href = url;
  a.download = `template_${new Date().toISOString().slice(0, 19)}.xlsx`;
  document.body.appendChild(a);
  a.click();
  document.body.removeChild(a);
  window.URL.revokeObjectURL(url);
};

Reading Uploaded Excel

The upload handler reads the filled-in Excel and processes each row:

export async function POST(request: NextRequest) {
  const formData = await request.formData();
  const file = formData.get("file") as File;

  const arrayBuffer = await file.arrayBuffer();
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.load(arrayBuffer);

  const actSheet = workbook.getWorksheet("Activity");
  if (actSheet) {
    actSheet.eachRow((row, rowNumber) => {
      if (rowNumber === 1) return; // skip header

      const id = row.getCell(1).text?.trim();
      const type = row.getCell(2).text?.trim();
      const agent = row.getCell(3).text?.trim();

      if (!id) return; // skip empty rows

      // Process the data (e.g., generate RDF, insert into DB)
    });
  }

  return NextResponse.json({ success: true });
}

row.getCell(n).text returns cell values as strings, including date cells, which simplifies type handling.

dataValidation Properties

For reference, here are the key properties:

PropertyTypeDescription
type"list" / "whole" / "decimal" / "date" / "custom"Validation type
formulaestring[]Formula or value list
allowBlankbooleanAllow blank cells
showErrorMessagebooleanShow error on invalid input
errorTitlestringError dialog title
errorstringError message body
showInputMessagebooleanShow hint on cell selection
promptTitlestringHint title
promptstringHint message

Summary

ExcelJS data validation supports three useful patterns:

  • Fixed list dropdown: formulae: ['"Item1,Item2"']
  • Cross-sheet reference dropdown: formulae: ['SheetName!$A$2:$A$10']
  • Cross-reference between input sheets: same formulae mechanism

Combined with Next.js API routes, this enables on-demand generation of Excel templates populated with dynamic data from external APIs — powering a “download → fill in → upload → process” workflow entirely from a web application.