Function buildResultsWorkbook

buildResultsWorkbook(ctx: ExportContext): Promise<Blob>

Builds the two-sheet .xlsx workbook (Summary + Results) for a set of results and returns it as a downloadable Blob. The Results sheet freezes the header row and first column, colors the header light blue and the first column light grey, enables AutoFilter, hides columns not visible in the table, and renders variants as collapsible outline-level-1 subrows beneath their parent.

Parameters

ctx: ExportContext
The export context: rows, scope, query, filters, visibility.

Returns Promise<Blob>

A promise resolving to the .xlsx file as a Blob.

Example

const blob = await buildResultsWorkbook({
  scope: "filtered",
  createdAt: Date.now(),
  query: "acetone",
  appVersion: "1.3.0",
  activeFilters: [{ label: "Supplier", value: "Loudwolf" }],
  groups: [{ parent: product, variants: [] }],
  columnVisibility: { description: false },
});
// blob.type === XLSX_MIME_TYPE; opens with a Summary and Results sheet.

Source

export async function buildResultsWorkbook(ctx: ExportContext): Promise<Blob> {
  // Lazy-loaded so the ~1 MB ExcelJS bundle is code-split out of the main chunk
  // and only fetched when the user actually exports.
  const { default: ExcelJS } = await import('exceljs');
  const workbook = new ExcelJS.Workbook();
  workbook.creator = 'ChemPal';
  workbook.created = new Date(ctx.createdAt);

  writeSummarySheet(workbook.addWorksheet(i18n('export_sheet_summary')), ctx);

  const sheet = workbook.addWorksheet(i18n('export_sheet_results'), {
    views: [{ state: 'frozen', xSplit: 1, ySplit: 1 }],
    properties: { outlineLevelRow: 1 },
  });
  // Parent rows act as the group header above their variant subrows.
  sheet.properties.outlineProperties = { summaryBelow: false, summaryRight: false };

  // Defining `header` here creates row 1; the price header carries the currency.
  sheet.columns = EXPORT_COLUMNS.map((column, index) => ({
    header:
      index === PRICE_COLUMN_INDEX
        ? i18n('column_price_currency', [ctx.userSettings?.currency ?? 'USD'])
        : column.header,
    key: column.id,
    width: column.width,
    hidden: ctx.columnVisibility[column.id] === false,
  }));

  for (const group of ctx.groups) {
    writeDataRow(sheet, group.parent, ctx, false);
    for (const variant of group.variants) {
      // Variants inherit the parent's product-level context (supplier, country,
      // documents) while overriding their own quantity / price / etc.
      const merged: Product = { ...group.parent, ...variant };
      writeDataRow(sheet, merged, ctx, true);
    }
  }

  styleHeaderRow(sheet);

  sheet.autoFilter = {
    from: { row: 1, column: 1 },
    to: { row: 1, column: EXPORT_COLUMNS.length },
  };

  const buffer = await workbook.xlsx.writeBuffer();
  return new Blob([buffer], { type: XLSX_MIME_TYPE });
}