Skip to main content

Generate Excel Reports

Create dynamic Excel reports with data tables, charts, and formatting.

Overview​

This recipe covers:

  • Designing Excel report templates
  • Populating data tables dynamically
  • Handling formulas and calculations
  • Multi-sheet workbooks

Use Cases​

  • Sales reports - Regional performance, pipeline analysis
  • Financial statements - P&L, balance sheets, budgets
  • Operational reports - Inventory, timesheets, schedules
  • Analytics exports - Metrics, KPIs, dashboards

Basic Report Generation​

import { RenderDoc } from '@renderdoc/sdk';

const client = new RenderDoc({ apiKey: process.env.RENDERDOC_API_KEY });

async function generateSalesReport(month, year) {
  const salesData = await getSalesData(month, year);

  const result = await client.documents.generate({
    templateId: 'sales-report-template',
    format: 'xlsx',
    variables: {
      reportTitle: `Sales Report - ${month} ${year}`,
      generatedDate: new Date().toLocaleDateString(),
      generatedBy: 'Sales Analytics Team',

      // Summary metrics
      totalRevenue: salesData.totalRevenue,
      totalUnits: salesData.totalUnits,
      averageOrderValue: salesData.averageOrderValue,
      growthRate: salesData.growthRate,

      // Regional breakdown
      regions: salesData.regions.map(region => ({
        name: region.name,
        revenue: region.revenue,
        units: region.units,
        target: region.target,
        achievement: ((region.revenue / region.target) * 100).toFixed(1)
      })),

      // Top products
      topProducts: salesData.topProducts.slice(0, 10),

      // Daily trends
      dailyData: salesData.dailyData
    },
    filename: `sales-report-${month}-${year}`
  });

  return result.downloadUrl;
}

Template Structure​

Sheet 1: Summary Dashboard​

| A                    | B              | C        |
|---------------------|----------------|----------|
| {{reportTitle}}     |                |          |
| Generated: {{date}} |                |          |
|                     |                |          |
| KEY METRICS         |                |          |
| Total Revenue       | {{totalRevenue}}|         |
| Total Units         | {{totalUnits}} |          |
| Avg Order Value     | {{aov}}        |          |
| Growth Rate         | {{growthRate}} |          |

Sheet 2: Regional Data​

| Region | Revenue | Units | Target | Achievement |
|--------|---------|-------|--------|-------------|
| {{#each regions}}                               |
| {{name}}|{{revenue}}|{{units}}|{{target}}|{{achievement}}%|
| {{/each}}                                       |
| TOTAL  | =SUM()  |=SUM() |=SUM()  |             |

Sheet 3: Product Performance​

| Rank | Product | Category | Units | Revenue |
|------|---------|----------|-------|---------|
| {{#each topProducts}}                        |
| {{@index+1}}|{{name}}|{{category}}|{{units}}|{{revenue}}|
| {{/each}}                                    |

Financial Report Example​

async function generateFinancialReport(quarter, year) {
  const financials = await getFinancialData(quarter, year);

  const result = await client.documents.generate({
    templateId: 'financial-report',
    format: 'xlsx',
    variables: {
      reportTitle: `Q${quarter} ${year} Financial Report`,
      companyName: 'Acme Corporation',
      preparedDate: new Date().toLocaleDateString(),

      // Income Statement
      revenue: {
        productSales: financials.productSales,
        serviceSales: financials.serviceSales,
        otherIncome: financials.otherIncome,
        totalRevenue: financials.totalRevenue
      },

      expenses: {
        costOfGoodsSold: financials.cogs,
        salaries: financials.salaries,
        marketing: financials.marketing,
        operations: financials.operations,
        depreciation: financials.depreciation,
        totalExpenses: financials.totalExpenses
      },

      netIncome: financials.netIncome,
      profitMargin: ((financials.netIncome / financials.totalRevenue) * 100).toFixed(2),

      // Balance Sheet
      assets: financials.assets,
      liabilities: financials.liabilities,
      equity: financials.equity,

      // Cash Flow
      operatingCashFlow: financials.operatingCashFlow,
      investingCashFlow: financials.investingCashFlow,
      financingCashFlow: financials.financingCashFlow,
      netCashFlow: financials.netCashFlow,

      // Comparison to previous quarter
      previousQuarter: financials.previousQuarter,
      changePercent: financials.changePercent
    },
    filename: `financial-report-Q${quarter}-${year}`
  });

  return result.downloadUrl;
}

Inventory Report Example​

async function generateInventoryReport(warehouseId) {
  const inventory = await getInventoryData(warehouseId);

  const result = await client.documents.generate({
    templateId: 'inventory-report',
    format: 'xlsx',
    variables: {
      warehouseId,
      warehouseName: inventory.warehouseName,
      reportDate: new Date().toISOString(),

      // Summary
      totalSKUs: inventory.totalSKUs,
      totalUnits: inventory.totalUnits,
      totalValue: inventory.totalValue,
      lowStockCount: inventory.lowStockItems.length,
      outOfStockCount: inventory.outOfStockItems.length,

      // All items
      items: inventory.items.map(item => ({
        sku: item.sku,
        name: item.name,
        category: item.category,
        quantity: item.quantity,
        reorderPoint: item.reorderPoint,
        unitCost: item.unitCost,
        totalValue: item.quantity * item.unitCost,
        status: item.quantity === 0 ? 'Out of Stock' :
                item.quantity < item.reorderPoint ? 'Low Stock' : 'In Stock'
      })),

      // Alerts
      lowStockItems: inventory.lowStockItems,
      outOfStockItems: inventory.outOfStockItems
    },
    filename: `inventory-${warehouseId}-${Date.now()}`
  });

  return result.downloadUrl;
}

Multi-Sheet Workbook​

For complex reports with multiple sheets:

variables: {
  // Sheet 1: Executive Summary
  summary: {
    title: 'Executive Summary',
    keyMetrics: [...],
    highlights: [...]
  },

  // Sheet 2: Detailed Data
  details: {
    title: 'Transaction Details',
    transactions: [...]
  },

  // Sheet 3: Regional Breakdown
  regional: {
    title: 'Regional Analysis',
    regions: [...]
  },

  // Sheet 4: Appendix
  appendix: {
    title: 'Appendix',
    methodology: '...',
    definitions: [...]
  }
}

Batch Report Generation​

Generate reports for all departments:

async function generateDepartmentReports(month, year) {
  const departments = await getAllDepartments();

  const documents = await Promise.all(
    departments.map(async (dept) => {
      const data = await getDepartmentData(dept.id, month, year);

      return {
        variables: {
          departmentName: dept.name,
          departmentHead: dept.manager,
          reportPeriod: `${month} ${year}`,
          metrics: data.metrics,
          expenses: data.expenses,
          headcount: data.headcount,
          projects: data.projects
        },
        filename: `${dept.code}-report-${month}${year}`,
        metadata: {
          departmentId: dept.id,
          type: 'monthly-report'
        }
      };
    })
  );

  const batch = await client.documents.generateBatch({
    templateId: 'department-report',
    format: 'xlsx',
    documents
  });

  return batch;
}

Scheduled Reports​

Daily Report (Cron Job)​

// Daily at 6 AM
cron.schedule('0 6 * * *', async () => {
  const yesterday = getYesterday();

  const report = await generateDailySalesReport(yesterday);

  // Store report URL
  await saveReport('daily-sales', yesterday, report.downloadUrl);

  // Notify stakeholders
  await notifyStakeholders('Daily Sales Report', report.downloadUrl);
});

Weekly Report​

// Every Monday at 8 AM
cron.schedule('0 8 * * 1', async () => {
  const lastWeek = getLastWeek();

  const report = await generateWeeklySummary(lastWeek);

  await distributeReport(report);
});

Monthly Report​

// 1st of each month at 9 AM
cron.schedule('0 9 1 * *', async () => {
  const lastMonth = getLastMonth();

  const reports = await generateMonthlyReports(lastMonth);

  await archiveReports(reports);
  await sendToLeadership(reports);
});

Best Practices​

1. Pre-calculate Aggregates​

// Calculate in code, not in Excel
const totalRevenue = data.reduce((sum, row) => sum + row.revenue, 0);
const averageRevenue = totalRevenue / data.length;

variables: {
  data,
  totalRevenue,
  averageRevenue
}

2. Format Numbers Consistently​

const formatNumber = (n) => n.toLocaleString('en-US');
const formatCurrency = (n) => `$${n.toLocaleString('en-US', { minimumFractionDigits: 2 })}`;
const formatPercent = (n) => `${(n * 100).toFixed(1)}%`;

3. Handle Missing Data​

variables: {
  revenue: data.revenue || 0,
  growth: data.growth !== undefined ? formatPercent(data.growth) : 'N/A',
  notes: data.notes || ''
}

4. Sort Data Before Sending​

const sortedProducts = products.sort((a, b) => b.revenue - a.revenue);
const top10Products = sortedProducts.slice(0, 10);

Next Steps​