# Excel and ODS

Use the `@rowsncolumns/toolkit` package to read, write and download Excel files from `SheetData` object

## Supported formats

| Format | Export | Import |
| ------ | ------ | ------ |
| ODS    | Yes    | Yes    |
| XLSM   | No     | Yes    |
| XLSX   | Yes    | Yes    |
| CSV    | Yes    | Yes    |
| TSV    | No     | Yes    |

## Importing excel files

You can use `importExcelFile` to import excel file using a web worker.

```tsx
import { useSpreadsheetState } from "@rowsncolumns/spreadsheet-state";
import { useLoadingIndicator } from "@rowsncolumns/spreadsheet"
 
const App = () => {
    const { importExcelFile } = useSpreadsheetState({ ... });
    const [showLoader, hideLoader] = useLoadingIndicator();
    return (
        <input
            type="file"
            onChange={async (e) => {
              const file = e.target.files?.[0];
              if (file) {
                showLoader("Parsing excel file");
                
                await importExcelFile(file)

                hideLoader();
              }
            }}
          />
    )
}
```

## Read Excel in Node.js

The browser worker (`libs/toolkit/excel-parser/worker.ts`) demonstrates the low-level primitives that parse `.xlsx` files. You can reuse the same helpers on the server to read workbooks, stream rows, and fetch metadata such as conditional formatting, named ranges, fonts, drawings, charts, and data validations.

```ts
import { readFile } from "node:fs/promises";
import {
  WorkBook,
  readExcelFile,
  getSheets,
  processSheetData,
  getConditionalFormatting,
  getDrawings,
  getCharts,
  getDataValidations,
} from "@rowsncolumns/toolkit";

const workbook = new WorkBook();

export async function readExcelOnServer(filePath: string) {
  const nodeBuffer = await readFile(filePath);

  // Convert Node's Buffer into the ArrayBuffer expected by the parser
  const arrayBuffer = nodeBuffer.buffer.slice(
    nodeBuffer.byteOffset,
    nodeBuffer.byteOffset + nodeBuffer.byteLength
  );

  const buffer = await readExcelFile(arrayBuffer);
  await workbook.load(buffer);

  const [sheets, tables] = await getSheets(workbook);
  const conditionalFormatting = await getConditionalFormatting(workbook);
  const dataValidations = await getDataValidations(workbook);
  const drawings = await getDrawings(workbook);
  const charts = await getCharts(workbook);

  for await (const chunk of processSheetData(workbook, 500, true)) {
    console.log(
      `Sheet ${chunk.sheetId} (${chunk.title}) -> ${chunk.rows.length} rows`,
      "cellXfs registry size",
      chunk.cellXfs.size
    );
  }

  workbook.dispose();

  return {
    sheets,
    tables,
    conditionalFormatting,
    dataValidations,
    drawings,
    charts,
  };
}
```

Key steps:

* read the file with Node APIs (e.g. `fs.readFile`, S3 SDK) to obtain a `Buffer`
* convert the `Buffer` into an `ArrayBuffer` before calling `readExcelFile`
* reuse the worker helpers (`WorkBook`, `getSheets`, `getConditionalFormatting`, `getDrawings`, `getCharts`, `getDataValidations`, `processSheetData`) to assemble everything you need server-side

### Streaming rows with `processSheetData`

Signature:

```ts
processSheetData(workbook, chunkSize = 100, enableCellXfsRegistry = false);
```

* `workbook`: the shared `WorkBook` instance with `load()` already called.
* `chunkSize`: how many logical rows you want in each yielded chunk (mirrors the worker's paging behavior).
* `enableCellXfsRegistry`: set to `true` if you need the `cellXfs` map populated so you can reuse deduped cell formats outside the worker.

Result type (from `libs/toolkit/excel-parser/helpers.ts`):

```ts
type ProcessSheetDataResult = {
  sheetId: number;
  title: string;
  currentSheetIndex: number;
  totalSheets: number;
  chunkIndex: number;
  rows: RowDataWithValue<CellData | null | undefined>[];
  rowIndices: number[];
  startRowIndex: number;
  endRowIndex: number;
  totalRows: number;
  isLastChunk: boolean;
  cellXfs: WorkBook["cellXfs"];
};
```

`cellXfs` is the same memoized format registry the worker uses (a map keyed by the serialized cell format) so you can reconcile styles outside the worker.

Example: accumulate totals per sheet while keeping the formatting registry alive for later use.

```ts
const totals = new Map<number, number>();

for await (const chunk of processSheetData(workbook, 250, true)) {
  const sum = chunk.rows.reduce((acc, row) => {
    const numericValues = row.values.filter(
      (value): value is CellData => !!value && typeof value.effectiveValue === "number"
    );
    return acc + numericValues.reduce(
      (inner, cell) => inner + (cell.effectiveValue ?? 0),
      0
    );
  }, 0);

  totals.set(chunk.sheetId, (totals.get(chunk.sheetId) ?? 0) + sum);

  // The cellXfs map can be sent elsewhere once you're done streaming
  console.log("Formats registry entries", chunk.cellXfs.size);
}
```

## Export to Excel

Toolkit provides `createExcelFile` and `exportToExcel` functions to either create a excel ArrayBuffer or create a downloadable excel file.

```tsx
// Export toolkit
import {
  createExcelFile,
  exportToExcel,
} from "@rowsncolumns/toolkit";

import React, { useState } from "react"
import { useSpreadsheetState, SheeData } from "@rowsncolumns/spreadsheet-state"
import { functionDescriptions, functions } from "@rowsncolumns/functions";
import { 
  SpreadsheetProvider,
  CanvasGrid, 
  CellData, 
  Sheet, 
  EmbeddedChart, 
  EmbeddedObject, 
  TableView 
} from "@rowsncolumns/spreadsheet"

const MySpreadSheet = () => {
  const [sheets, onChangeSheets] = useState<Sheet[]>([]);
  const [sheetData, onChangeSheetData] = useState<SheetData<CellData>>({});
  const [scale, onChangeScale] = useState(1);
  const [charts, onChangeCharts] = useState<EmbeddedChart[]>([]);
  const [embeds, onChangeEmbeds] = useState<EmbeddedObject[]>([]);
  const [tables, onChangeTables] = useState<TableView[]>([]);
  
  return (
    <button onClick={async () => {
      // Download excel file
      await exportToExcel({
        filename: "my_excel_file",
        sheets,
        sheetData,
        tables
      })
      
      // OR Create a Array buffer
      const blob = await createExcelFile({ sheets, sheetData, tables })
      
    }}>Download excel</button>
  )
}
```

## Export to ODS

Toolkit provides `exportToODS` to download an ODS file.

```tsx
import { exportToODS } from "@rowsncolumns/toolkit";

export const ExportOdsButton = ({
  sheets,
  sheetData,
  tables,
  conditionalFormats,
  cellXfs,
  namedRanges,
  theme,
  charts,
  dataValidations,
  embeds,
}) => (
  <button
    onClick={() =>
      exportToODS({
        filename: "my_ods_file",
        sheets,
        sheetData,
        tables,
        conditionalFormats,
        cellXfs,
        namedRanges,
        theme,
        charts,
        dataValidations,
        embeds,
      })
    }
  >
    Export to ODS
  </button>
);
```