Next.js APIルートでExcelJSを使い、別シート参照ドロップダウン付きテンプレートを動的生成する

npm install exceljs

// 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;

  // 外部APIからデータを取得
  const members = await fetchMembers(projectId);

  const workbook = new ExcelJS.Workbook();
  // ... シート作成（後述）

  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"`,
    },
  });
}

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

// ヘッダースタイル
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,
  });
}

const ACTIVITY_TYPES = ["問題設定", "資料収集", "解釈", "叙述"];

const actSheet = workbook.addWorksheet("Activity");
actSheet.columns = [
  { header: "ID", key: "id", width: 12 },
  { header: "種類", key: "type", width: 16 },
  { header: "担当者", key: "agent", width: 30 },
  { header: "開始日", 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: "無効な値",
    error: "リストから種類を選択してください",
  };
}

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}`],
  };
}

const entSheet = workbook.addWorksheet("Entity");
entSheet.columns = [
  { header: "ID", key: "id", width: 12 },
  { header: "種類", key: "type", width: 18 },
  { header: "説明", key: "desc", width: 60 },
  { header: "生成元Activity", 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}`],
  };
}

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

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);
};

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; // ヘッダースキップ

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

      if (!id) return; // 空行スキップ

      // 変換処理（例: RDF生成、DB登録など）
    });
  }

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