Data Export: CSV and Excel Generation

Users need to export data for analysis, reporting, and integration with other tools. Here's how to generate CSV and Excel files efficiently.

CSV Generation#

import { stringify } from 'csv-stringify';
import { Readable } from 'stream';

// Simple CSV generation
function generateCsv(
  data: Record<string, any>[],
  columns: { key: string; header: string }[]
): Promise<string> {
  return new Promise((resolve, reject) => {
    const stringifier = stringify({
      header: true,
      columns: columns.map((c) => ({ key: c.key, header: c.header })),
    });

    const chunks: string[] = [];

    stringifier.on('readable', () => {
      let chunk;
      while ((chunk = stringifier.read())) {
        chunks.push(chunk);
      }
    });

    stringifier.on('error', reject);
    stringifier.on('finish', () => resolve(chunks.join('')));

    data.forEach((row) => stringifier.write(row));
    stringifier.end();
  });
}

// Usage
const csv = await generateCsv(orders, [
  { key: 'id', header: 'Order ID' },
  { key: 'customerName', header: 'Customer' },
  { key: 'total', header: 'Total' },
  { key: 'createdAt', header: 'Date' },
]);

// API endpoint
app.get('/export/orders', authenticate, async (req, res) => {
  const orders = await prisma.order.findMany({
    where: { userId: req.user.id },
    include: { customer: true },
  });

  const data = orders.map((o) => ({
    id: o.id,
    customerName: o.customer.name,
    total: o.total,
    createdAt: o.createdAt.toISOString(),
  }));

  const csv = await generateCsv(data, [
    { key: 'id', header: 'Order ID' },
    { key: 'customerName', header: 'Customer' },
    { key: 'total', header: 'Total ($)' },
    { key: 'createdAt', header: 'Date' },
  ]);

  res.setHeader('Content-Type', 'text/csv');
  res.setHeader('Content-Disposition', 'attachment; filename="orders.csv"');
  res.send(csv);
});

Streaming Large CSV#

import { stringify } from 'csv-stringify';
import { pipeline } from 'stream/promises';

// Stream CSV for large datasets
async function streamCsvExport(
  req: Request,
  res: Response,
  query: () => AsyncIterable<any>,
  columns: { key: string; header: string }[],
  filename: string
) {
  res.setHeader('Content-Type', 'text/csv');
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);

  const stringifier = stringify({
    header: true,
    columns: columns.map((c) => ({ key: c.key, header: c.header })),
  });

  // Stream data from database
  const dataStream = Readable.from(query());

  await pipeline(dataStream, stringifier, res);
}

// Prisma cursor-based streaming
async function* streamOrders(userId: string) {
  let cursor: string | undefined;
  const batchSize = 1000;

  while (true) {
    const orders = await prisma.order.findMany({
      where: { userId },
      take: batchSize,
      skip: cursor ? 1 : 0,
      cursor: cursor ? { id: cursor } : undefined,
      orderBy: { id: 'asc' },
    });

    if (orders.length === 0) break;

    for (const order of orders) {
      yield {
        id: order.id,
        total: order.total,
        status: order.status,
        createdAt: order.createdAt.toISOString(),
      };
    }

    cursor = orders[orders.length - 1].id;
  }
}

// Usage
app.get('/export/orders/stream', authenticate, async (req, res) => {
  await streamCsvExport(
    req,
    res,
    () => streamOrders(req.user.id),
    [
      { key: 'id', header: 'Order ID' },
      { key: 'total', header: 'Total' },
      { key: 'status', header: 'Status' },
      { key: 'createdAt', header: 'Date' },
    ],
    'orders.csv'
  );
});

Excel Generation#

import ExcelJS from 'exceljs';

async function generateExcel(
  data: Record<string, any>[],
  columns: { key: string; header: string; width?: number }[],
  sheetName = 'Sheet1'
): Promise<Buffer> {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet(sheetName);

  // Set columns
  worksheet.columns = columns.map((col) => ({
    header: col.header,
    key: col.key,
    width: col.width || 15,
  }));

  // Style header row
  worksheet.getRow(1).font = { bold: true };
  worksheet.getRow(1).fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFE0E0E0' },
  };

  // Add data
  worksheet.addRows(data);

  // Auto-filter
  worksheet.autoFilter = {
    from: 'A1',
    to: `${String.fromCharCode(64 + columns.length)}1`,
  };

  return workbook.xlsx.writeBuffer() as Promise<Buffer>;
}

// Multi-sheet workbook
async function generateReportWorkbook(report: Report): Promise<Buffer> {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = 'Your App';
  workbook.created = new Date();

  // Summary sheet
  const summarySheet = workbook.addWorksheet('Summary');
  summarySheet.addRow(['Report Period', report.period]);
  summarySheet.addRow(['Generated', new Date().toISOString()]);
  summarySheet.addRow([]);
  summarySheet.addRow(['Total Orders', report.totalOrders]);
  summarySheet.addRow(['Total Revenue', report.totalRevenue]);

  // Orders sheet
  const ordersSheet = workbook.addWorksheet('Orders');
  ordersSheet.columns = [
    { header: 'Order ID', key: 'id', width: 20 },
    { header: 'Customer', key: 'customer', width: 25 },
    { header: 'Amount', key: 'amount', width: 15 },
    { header: 'Date', key: 'date', width: 20 },
  ];
  ordersSheet.addRows(report.orders);

  // Format currency column
  ordersSheet.getColumn('amount').numFmt = '$#,##0.00';

  // Products sheet
  const productsSheet = workbook.addWorksheet('Products');
  productsSheet.columns = [
    { header: 'Product', key: 'name', width: 30 },
    { header: 'Units Sold', key: 'units', width: 15 },
    { header: 'Revenue', key: 'revenue', width: 15 },
  ];
  productsSheet.addRows(report.products);

  return workbook.xlsx.writeBuffer() as Promise<Buffer>;
}

// API endpoint
app.get('/export/report', authenticate, async (req, res) => {
  const report = await generateReport(req.user.id);
  const buffer = await generateReportWorkbook(report);

  res.setHeader(
    'Content-Type',
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
  );
  res.setHeader('Content-Disposition', 'attachment; filename="report.xlsx"');
  res.send(buffer);
});

Streaming Excel#

// Stream large Excel files
async function streamExcelExport(
  res: Response,
  query: () => AsyncIterable<any>,
  columns: { key: string; header: string }[],
  filename: string
) {
  const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
    stream: res,
    useStyles: true,
  });

  res.setHeader(
    'Content-Type',
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
  );
  res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);

  const worksheet = workbook.addWorksheet('Data');

  // Set columns
  worksheet.columns = columns.map((col) => ({
    header: col.header,
    key: col.key,
    width: 20,
  }));

  // Stream rows
  for await (const row of query()) {
    worksheet.addRow(row).commit();
  }

  await worksheet.commit();
  await workbook.commit();
}

Background Export Jobs#

// Queue large exports
async function queueExport(
  userId: string,
  type: string,
  filters: Record<string, any>
): Promise<string> {
  const exportJob = await prisma.exportJob.create({
    data: {
      userId,
      type,
      filters,
      status: 'pending',
    },
  });

  await exportQueue.add('generate-export', {
    jobId: exportJob.id,
  });

  return exportJob.id;
}

// Worker
const exportWorker = new Worker(
  'exports',
  async (job) => {
    const { jobId } = job.data;

    const exportJob = await prisma.exportJob.findUnique({
      where: { id: jobId },
    });

    try {
      await prisma.exportJob.update({
        where: { id: jobId },
        data: { status: 'processing' },
      });

      // Generate export
      const buffer = await generateExport(exportJob.type, exportJob.filters);

      // Upload to S3
      const key = `exports/${exportJob.userId}/${jobId}.xlsx`;
      await s3.putObject({
        Bucket: process.env.S3_BUCKET,
        Key: key,
        Body: buffer,
      });

      // Update job
      await prisma.exportJob.update({
        where: { id: jobId },
        data: {
          status: 'completed',
          fileKey: key,
          completedAt: new Date(),
        },
      });

      // Notify user
      await sendExportReadyEmail(exportJob.userId, jobId);
    } catch (error) {
      await prisma.exportJob.update({
        where: { id: jobId },
        data: {
          status: 'failed',
          error: error.message,
        },
      });
    }
  },
  { connection: redis }
);

// Check status
app.get('/exports/:id', authenticate, async (req, res) => {
  const exportJob = await prisma.exportJob.findFirst({
    where: {
      id: req.params.id,
      userId: req.user.id,
    },
  });

  if (!exportJob) {
    return res.status(404).json({ error: 'Not found' });
  }

  if (exportJob.status === 'completed') {
    const downloadUrl = await getSignedUrl(exportJob.fileKey);
    return res.json({ status: exportJob.status, downloadUrl });
  }

  res.json({ status: exportJob.status });
});

Best Practices#

Performance:
✓ Stream large datasets
✓ Use cursor pagination
✓ Queue heavy exports
✓ Set memory limits

User Experience:
✓ Show progress for large exports
✓ Email when complete
✓ Provide format options
✓ Include metadata (date, filters)

Data:
✓ Format dates consistently
✓ Handle null values
✓ Escape special characters
✓ Include headers

Conclusion#

Data export is essential for user productivity. Stream large datasets, queue heavy exports, and provide format options. Always test exports with real data volumes to ensure performance.