XLSX Export

import {
  conditionalFormatting, createDataGrid, filtering, selection, sorting
} from '@sv5ui/datagrid';
import { buildGridXlsx, downloadGridXlsx } from '@sv5ui/datagrid/xlsx';

const grid = createDataGrid<Line>({
  columns,
  data: lines,
  getRowId: (line) => String(line.id),
  features: [
    sorting(),
    filtering(),
    selection(),
    conditionalFormatting({ rules: [{ kind: 'colorScale', column: 'price' }] })
  ]
});

// The visible columns, in their current order, filtered and sorted the way
// the reader left them.
await downloadGridXlsx(grid, { filename: 'orders.xlsx', sheetName: 'Orders' });

buildGridXlsx(grid, { sheetName: 'Orders', conditionalFormatting: true });
// Uint8Array, 6016 bytes over these six rows

// Narrow it:
await downloadGridXlsx(grid, {
  columnIds: ['sku', 'quantity', 'total'],   // and in this order
  selectedOnly: true,                        // only the ticked rows
  freezeHeader: true,                        // the header stays put on scroll
  autoFilter: true                           // Excel's own filter dropdowns
});

// Or take the bytes and do something else with them:
const bytes = buildGridXlsx(grid);           // Uint8Array
await fetch('/api/reports', { method: 'POST', body: bytes });

const bytes = await buildGridXlsxAsync(grid, {
  batchSize: 50,           // the demo above, so onProgress reports ten times
  onProgress: (loaded, total) => {
    done = loaded;
    expected = total;
  }
});

// Same 500 rows, both builders. The demo above measures this in your own
// browser rather than trusting the numbers written here:
buildGridXlsx(grid).length;             // 111_360, stored
(await buildGridXlsxAsync(grid)).length; //  16_331, deflated - about a seventh

await downloadGridXlsx(grid, { loadedOnly: true });

// The async builder yields between batches and deflates its entries, which
// is only worth doing off the main thread.

import { buildGridXlsx, buildGridXlsxAsync, downloadGridXlsx } from '@sv5ui/datagrid/xlsx';

// The grid holds a hundred of five thousand rows, so "export the grid" is
// two different requests and the builders do not guess which one you meant.

// Refuses. Nothing in memory is the whole set, and writing a hundred rows
// into a file called everything.xlsx is the wrong kind of success.
buildGridXlsx(grid);
// Error: xlsx: this grid uses rowModel: "server", so only the rows currently
// held in memory are available synchronously. Use await downloadGridXlsx(grid),
// which pages the data source for the whole set, or pass { loadedOnly: true }
// to export just what is on screen.

// What is on screen, said out loud.
buildGridXlsx(grid, { loadedOnly: true });

// Or the whole set: this pages the data source itself, in batches, and
// reports as it goes.
await buildGridXlsxAsync(grid, {
  batchSize: 1000,
  onProgress: (loaded, total) => (progress = `${loaded} of ${total}`)
});

await downloadGridXlsx(grid, { fileName: 'people.xlsx', batchSize: 1000 });

// A selection is exempt too: selectedOnly means the rows you picked, which
// are by definition ones you have.

import { createWorkbook, XLSX_MIME } from '@sv5ui/datagrid/xlsx';

// No grid involved. This is a workbook writer that a grid happens to use.
const bytes = createWorkbook({
  sheetName: 'Q1',
  columns: [{ header: 'SKU', width: 14 }, { header: 'Units' }, { header: 'Revenue' }],
  rows: [
    ['DG-100', 3, 121.5],
    ['DG-210', 7, 539]
  ],
  freezeHeader: true,
  autoFilter: true
});

// Several sheets: pass an array.
createWorkbook([
  { sheetName: 'Q1', columns, rows: q1 },
  { sheetName: 'Q2', columns, rows: q2 }
]);

// A sheet name Excel will not take is sanitized rather than rejected: the
// characters / \\ : * ? [ ] each become a space, and the name is clipped to
// Excel's 31.

new Blob([bytes], { type: XLSX_MIME });

import { cellRef, columnLetter, toSerialDate } from '@sv5ui/datagrid/xlsx';

columnLetter(0);    // 'A'
columnLetter(26);   // 'AA'
cellRef(0, 0);      // 'A1'   row is zero-based, the reference is not
cellRef(9, 2);      // 'C10'

// Excel counts days from 1899-12-30, and the time of day rides along as the
// fraction. Read from local parts, like every other date in the grid.
toSerialDate(new Date(1900, 0, 1));      // 2
toSerialDate(new Date(2026, 0, 1));      // 46023
toSerialDate(new Date(2026, 0, 1, 12));  // 46023.5

// A formula cell, rather than a value:
const rows = [['DG-100', 3, 40.5, { formula: 'B2*C2' }]];