SheetXL Studio Vanilla

https://sheetxl.com/docs
Admin
SheetXL Studio Vanilla provides a high-performance, Excel-compatible spreadsheet component that can...
Tokens:16,410
Snippets:260
Trust Score:5.4
Update:4 months ago
Show doc for...
Tokens:
Raw
### Install and Run SheetXL CLI

Source: https://sheetxl.com/docs/guides/getting-started/cli

Run the SheetXL CLI directly using npx to start an interactive REPL pre-loaded with the SheetXL SDK. Ensure Node.js v22+ is installed.

```bash
npx sheetxl  
```

--------------------------------

### Run Node.js SheetXL Example

Source: https://sheetxl.com/docs/guides/getting-started/node

Execute the 'example.js' file using Node.js to generate the workbook.

```bash
node example.js
```

--------------------------------

### Install SheetXL SDK and IO with npm

Source: https://sheetxl.com/docs/guides/getting-started/node

Add the SheetXL SDK and IO packages to an existing project using npm.

```bash
npm install @sheetxl/sdk @sheetxl/io
```

--------------------------------

### Install SheetXL with npm

Source: https://sheetxl.com/docs/guides/getting-started/react

Install the SheetXL Studio MUI package and its Material UI peer dependencies using npm.

```bash
npm install @sheetxl/studio-mui @mui/material @mui/styled-engine @mui/system @mui/utils @emotion/react @emotion/styled react react-dom
```

--------------------------------

### Install SheetXL with pnpm

Source: https://sheetxl.com/docs/guides/getting-started/react

Install the SheetXL Studio MUI package and its Material UI peer dependencies using pnpm.

```bash
pnpm add @sheetxl/studio-mui @mui/material @mui/styled-engine @mui/system @mui/utils @emotion/react @emotion/styled react react-dom
```

--------------------------------

### Install SheetXL SDK and IO with yarn

Source: https://sheetxl.com/docs/guides/getting-started/node

Add the SheetXL SDK and IO packages to an existing project using yarn.

```bash
yarn add @sheetxl/sdk @sheetxl/io
```

--------------------------------

### Install SheetXL with yarn

Source: https://sheetxl.com/docs/guides/getting-started/react

Install the SheetXL Studio MUI package and its Material UI peer dependencies using yarn.

```bash
yarn add @sheetxl/studio-mui mui/material @mui/styled-engine @mui/system @mui/utils @emotion/react @emotion/styled react react-dom
```

--------------------------------

### Install SheetXL SDK and IO with pnpm

Source: https://sheetxl.com/docs/guides/getting-started/node

Add the SheetXL SDK and IO packages to an existing project using pnpm.

```bash
pnpm add @sheetxl/sdk @sheetxl/io
```

--------------------------------

### Install SheetXL using npm

Source: https://sheetxl.com/docs/guides/getting-started/vanilla

Install the SheetXL package and its React dependencies into your project using npm. This is the first step for bundler integration.

```bash
npm install @sheetxl/studio-vanilla react react-dom
```

--------------------------------

### Basic React Studio Component

Source: https://sheetxl.com/docs/guides/getting-started/react

Integrate the SheetXL Studio component into your React application. This example shows how to render the Studio widget within a div.

```jsx
import { Studio } from '@sheetxl/studio-mui';  
  
/**  
 * This example is a complete React app that adds Studio to the  
 * the default App div.  
 *  
 * To see an example that runs as a standalone application and includes page level  
 * concerns. (light/dark Mode, tab title, pwa, etc)  
 * visit: https://github.com/sheetxl/sheetxl/tree/main/examples/studio-mui  
 *  
 */  
export default function App() {  
  /* Add the standalone widget to a div. The className is not required. */  
  return (  
    <div className="App">  
      <Studio />  
    </div>  
  );  
}
```

--------------------------------

### Basic SheetXL Workbook Creation and Export

Source: https://sheetxl.com/docs/guides/getting-started/node

This example demonstrates creating a new workbook, setting a value in cell A1, exporting it to an XLSX buffer, and saving it to a file. Ensure you set your license key.

```javascript
import SheetXL from '@sheetxl/sdk';
import WorkbookIO from '@sheetxl/io';
import fs from 'node:fs';

// Set your license key or use CLI to install
SheetXL.LicenseManager.setLicenseKey('visit https://my.sheetxl.com to generate a license key.');

// Create a new workbook
const workbook = new SheetXL.Workbook();

workbook.getRange('A1').setValues([['Hello World!']]);

// Export the workbook to a buffer
const buffer = await WorkbookIO.write({
  workbook,
  type: 'xlsx',
});

// Save to disk
fs.writeFileSync('my-workbook.xlsx', buffer);

console.log('Workbook saved as my-workbook.xlsx');

```

--------------------------------

### Example Usage of Variadic ADD Function

Source: https://sheetxl.com/docs/guides/concepts/udf

Demonstrates how to call the variadic ADD function with multiple arguments. The first argument is 'a', and the rest are collected into the 'b' array.

```excel formula
=ADD(1, 2, 3, 4) ( a will be 1, b will be [2, 3, 4])  

=ADD(5) ( a will be 5, b will be [])  
```

--------------------------------

### Work with Non-Contiguous Ranges

Source: https://sheetxl.com/docs/guides/concepts/interactive-with-sheets

Handle multiple, non-contiguous cell ranges as a single unit using `ICellRanges`. This example shows retrieving statistics and the sum from specified ranges.

```typescript
const range:ICellRanges = workbook.getRanges('Sheet1!A1:A5000,Sheet1!C1:C5000').getStats().getSum();  
```

--------------------------------

### Asynchronous Function for Delayed Calculation

Source: https://sheetxl.com/docs/guides/concepts/udf

Implement long-running operations like network requests using async functions. This example simulates a delay before returning a calculated value.

```typescript
/**  
 * Simulates a delayed calculation.  
 *  
 * @summary Returns a number after a delay  
 * @param value The input value.  
 */  
export async function delayedCalculation(value: number): Promise<number> {  
  // Simulate a delay of 1 second  
  await new Promise(resolve => setTimeout(resolve, 1000));  
  return value * 2;  
}
```

--------------------------------

### Apply Cell Fill Styling

Source: https://sheetxl.com/docs/guides/concepts/interactive-with-sheets

Set the fill color for a specified range of cells using the `setFill()` method on the `IStyle` object. This example demonstrates applying 'accent1' color to cells A1 through D5000.

```typescript
// Set the fill to color accent 1.  
workbook.getRange('Sheet1!A1:D5000').getStyle().setFill('accent1');  
```

--------------------------------

### Control Workbook View: Hide Status Bar

Source: https://sheetxl.com/docs/guides/concepts/interactive-with-sheets

Adjust workbook-level display settings using `IWorkbookView`. This example demonstrates hiding the status bar for the entire workbook.

```typescript
// Hide the statusbar  
workbook.getView().setShowStatusBar(false);  
```

--------------------------------

### Embed SheetXL using HTML Script Tag

Source: https://sheetxl.com/docs/guides/getting-started/vanilla

Include SheetXL in traditional HTML pages using a script tag. This method requires no additional JavaScript or CSS setup.

```html
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8" />
    <title>SheetXL Demo</title>
</head>
<body>
    <div id="sheetxl" style="height: 600px; width: 100%;"></div>
    <script
      src="https://cdn.jsdelivr.net/npm/@sheetxl/studio-vanilla@latest/cdn/loader.js"
      data-target="#sheetxl"
      data-license-key="YOUR_LICENSE_KEY_HERE"
      defer>
    </script>
</body>
</html>
```

--------------------------------

### Get ICellRange via Workbook or Sheet

Source: https://sheetxl.com/docs/guides/concepts/interactive-with-sheets

Retrieve a rectangular selection of cells using either the workbook or a specific sheet. This is fundamental for reading, writing, and manipulating data within a defined region.

```typescript
// return a range via a workbook  
const rangeWB:ICellRange = workbook.getRange('Sheet1!A1:E5000');  
  
// return the range via a sheet  
const rangeSheet:ICellRange = workbook.getSheet('Sheet1')?.getRange('A1:E5000');  
```

--------------------------------

### Create New SheetXL Project with npx

Source: https://sheetxl.com/docs/guides/getting-started/node

Use this command to initialize a new project with SheetXL pre-configured using npx.

```bash
npx create-sheetxl
```

--------------------------------

### Create New SheetXL Project with yarn

Source: https://sheetxl.com/docs/guides/getting-started/node

Use this command to initialize a new project with SheetXL pre-configured using yarn.

```bash
yarn create sheetxl
```

--------------------------------

### Create New SheetXL Project with pnpm

Source: https://sheetxl.com/docs/guides/getting-started/node

Use this command to initialize a new project with SheetXL pre-configured using pnpm.

```bash
pnpm create sheetxl
```

--------------------------------

### Create Workbook with Initial Data

Source: https://sheetxl.com/docs/guides/concepts/load-and-saving

Instantiate a Workbook and populate it with initial data using a 2D array of Scalars. This is useful for displaying a workbook with predefined content.

```typescript
const workbook:IWorkbook = new Workbook();  
workbook.getSelectedSheet().getRange('A1:C3').setValues([  
  [1,2,3],  
  [4,5,6],  
  [7,8,9]  
]);  
```

--------------------------------

### Create and Save Workbook in REPL

Source: https://sheetxl.com/docs/guides/getting-started/cli

Demonstrates creating a new Workbook, setting a value in cell A1, and saving it as 'myWorkbook.xlsx' within the SheetXL interactive REPL.

```javascript
wb = new Workbook();  
wb.getRange('a1').setValues([[1]]);  
save('myWorkbook.xlsx', wb);  
```

--------------------------------

### Create a New Workbook

Source: https://sheetxl.com/docs/guides/concepts/interactive-with-sheets

Instantiate a new workbook model by importing the `@sheetxl/sdk` package and creating a `Workbook` instance. This serves as the entry point for all spreadsheet operations.

```typescript
// create a default workbook  
const workbook:IWorkbook = new Workbook();  
```

--------------------------------

### Initialize SheetXL with JavaScript using CDN

Source: https://sheetxl.com/docs/guides/getting-started/vanilla

Programmatically create a SheetXL workbook instance using JavaScript. This method provides precise control over workbook creation and is useful for waiting for other events or passing complex props.

```javascript
try {
  // import the library from unpkg or your preferred CDN
  const module = await import('https://cdn.jsdelivr.net/npm/@sheetxl/studio-vanilla@latest/cdn/index.js');
  if (!module.SheetXL) {
    throw new Error('SheetXL module not found in the imported package');
  }

  // create a workbook element
  const workbookElement await module.SheetXL.attachStudio('#sheetxl', {
    licenseKey: 'visit https://my.sheetxl.com to generate a license key.',
    // pass any additional properties here
  });
  // you can interact with the workbookElement
} catch (err) {
  // only needed to catch cdn error. SheetXL will handle the rest
  document.getElementById('sheetxl-container').innerText = err.message ?? `Error loading SheetXL.`;
}
```

--------------------------------

### Full HTML File for JavaScript Initialization

Source: https://sheetxl.com/docs/guides/getting-started/vanilla

This is a complete HTML file structure that includes the necessary div container and the script tag for JavaScript-based SheetXL initialization.

```html
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8" />
  <title>SheetXL Demo</title>
</head>
<body>
  <div id="sheetxl-container" style="height: 500px; width: 800px;"></div>

  <script type="module">
    // The javascript code from above goes here
  </script>
</body>
</html>
```

--------------------------------

### Activate SheetXL License

Source: https://sheetxl.com/docs/guides/getting-started/cli

Activate your SheetXL license key using the 'activate' command. This command stores the key for future use.

```bash
npx sheetxl activate YOUR_LICENSE_KEY_HERE  
```

--------------------------------

### Studio Component for Workbook Loading

Source: https://sheetxl.com/docs/guides/concepts/load-and-saving

Utilize the Studio React component to load workbooks from a source URL. Configure options like 'readonly' and listen for workbook changes via the onWorkbookChange callback.

```typescript
import { IWorkbook } from '@sheetxl/sdk';  
import { Studio } from '@sheetxl/mui-studio';  
  
// Instead of loading we can load from source URL  
// We need to wrap in a memo to ensure we don't rerender  
const workbookSource = useMemo(() => {  
  return {  
    // Lots of options available  
    source: 'https://www.sheetxl.com/docs/examples/financial-calculators.xlsx',  
    // Not required but if the viewer is meant to be readonly  
    readonly: true  
  }  
}, []);  
  
<Studio  
  workbook={workbookSource}  
  // Listen for when the results have be loaded  
  onWorkbookChange={(workbook: IWorkbook | null) => {  
    console.log('workbook changed', workbook);  
  }}  
/>  
```

--------------------------------

### Set License Key Programmatically

Source: https://sheetxl.com/docs/guides/getting-started/key

Use this method to set the license key by adding a line of code before using the API or rendering a component. Obtain your license key from my-sheetxl.

```javascript
LicenseManager.setLicenseKey(`visit https://my.sheetxl.com to generate a license key.`);
```

--------------------------------

### Insert and Delete Rows/Columns

Source: https://sheetxl.com/docs/guides/concepts/working-with-data

Use `ICellRange.insert` to insert new columns or rows and `ICellRange.delete` to remove existing ones.

```typescript
// insert column
workbook.getRange('Sheet1!B:E').insert();

// remove row
workbook.getRange('Sheet1!2:2').delete();
```

--------------------------------

### Read Workbook using WorkbookIO

Source: https://sheetxl.com/docs/guides/concepts/load-and-saving

Import data into a Workbook from various sources including files, base64 strings, URLs, and array buffers. Specify the format and name for the imported data.

```typescript
/**  
 * Read from local file system using available IO handlers.  
 * The input can be either a File, a Promise<File>, or a string.  
 * If a string is provided, if should be either an extension or a mimetype  
 * that will be passed to the accept attribute of an  
 * input: {@link https://developer.mozilla.org/en-US/docs/Web/HTML/Attributes/accept}.  
 *  
 * @param options The options ReadWorkbookOptions for loading the workbook.  
 * @returns A promise that resolves to an ImportResults object.  
 *  
 */  
import { WorkbookIO } from '@sheetxl/io';  
  
WorkbookIO.read(  
  options: ReadWorkbookOptions  
): Promise<ImportResults>;  
  
// Import from a file  
const result1 = await WorkbookIO.read({  
  source: myFile,  
  name: 'My Spreadsheet'  
});  
  
// Import from base64 (explicit disambiguation)  
const result2 = await WorkbookIO.read({  
  source: { base64: 'iVBORw0KGgo...' },  
  format: 'xlsx'  
});  
  
// Import from URL  
const result3 = await WorkbookIO.read({  
  source: 'https://www.sheetxl.com/docs/examples/financial-calculators.xlsx'  
});  
  
// Import from array buffer  
const result4 = await WorkbookIO.read({  
  source: myArrayBuffer,  
  format: 'csv',  
  name: 'data.csv'  
});  
```

--------------------------------

### Execute a JavaScript Script with SheetXL CLI

Source: https://sheetxl.com/docs/guides/getting-started/cli

Run a local JavaScript file using the SheetXL CLI for automation. The script executes within the SheetXL environment with SDK access.

```bash
npx sheetxl run path/to/your/script.js  
```

--------------------------------

### Import and Use SheetXL with a Bundler

Source: https://sheetxl.com/docs/guides/getting-started/vanilla

Import and initialize the SheetXL API in your main JavaScript or TypeScript file when using a modern bundler. No additional CSS is required as SheetXL handles it.

```javascript
import { SheetXL } from '@sheetxl/studio-vanilla';
// No CSS needed - SheetXL handles this.

// Create the workbook instance
const studio = await SheetXL.attachStudio('#sheetxl', {
  licenseKey: 'visit https://my.sheetxl.com to generate a license key.',
});
```

--------------------------------

### Write Workbook using WorkbookIO

Source: https://sheetxl.com/docs/guides/concepts/load-and-saving

Export the current workbook state to the local file system. The export type can be specified to determine the handler used for writing the file.

```typescript
/**  
 * Exports to the local file system attempting to use the fileName provided. This will  
 * use the exportType to determine the export handler to use.  
 *  
 * @returns A Promise indicating success or failure.  
 */  
WorkbookIO.writeFile(  
  fileName: string | null,  
  workbook: IWorkbook,  
  exportType?: ExportType  
): Promise<boolean>;  
```

--------------------------------

### Load Workbook State from JSON

Source: https://sheetxl.com/docs/guides/concepts/load-and-saving

Initialize a new Workbook instance by loading its state from a JSON object. The JSON can be retrieved from local storage, a database, or another source.

```typescript
const json:IWorkbook.JSON = { /* Json loaded from db, local storage, or other location */ };  
const workbook:IWorkbook = new Workbook({ json });  
```

--------------------------------

### Wrap SheetXL with MUIThemeProvider

Source: https://sheetxl.com/docs/guides/concepts/ui-components

Wrap a SheetXL component within a MUIThemeProvider to apply application themes. This allows for customization of the UI's appearance using Material-UI's theming capabilities.

```jsx
/**
 * Wrapping a SheetXL component inside of a MUIThemeProvider.
 */
<ThemeProvider theme={currentTheme}>
  <Studio />
</ThemeProvider>
```

--------------------------------

### Attach SheetXL Studio to an Element

Source: https://sheetxl.com/docs

This snippet shows how to import and attach the SheetXL Studio to a specific HTML element. Ensure the target element exists on the page.

```javascript
import("https://cdn.jsdelivr.net/npm/@sheetxl/studio-vanilla@latest/cdn/index.js").then(({ SheetXL }) => SheetXL.attachStudio("#sheetxl"));
```

--------------------------------

### Update Column Headers and Row Headers

Source: https://sheetxl.com/docs/guides/concepts/headers-labels

Set the size for multiple columns and hide a range of rows. Ensure the workbook and range are correctly specified.

```javascript
// Set 4 columns  
workbook.getRange('Sheet1!B:E').getColumnHeaders().setSize(120);
// Hide some rows  
workbook.getRange('Sheet1!2:20').getRowHeaders().setHidden(true);
```

--------------------------------

### SheetXL Scripting Object Model

Source: https://sheetxl.com/docs/guides/concepts/scripting

Illustrates the hierarchy for accessing and executing scripts within a SheetXL workbook.

```plaintext
Workbook
  ↳ getScripting(): IScript
      ↳ getModules(): IModuleCollection
          ↳ getByName("BudgetAnalysis"): IModule
              ↳ execute("calculateGrowthRate")
              ↳ execute("generateReport")
          ↳ getFunctionNames(): string[]
```

--------------------------------

### Attach SheetXL Studio

Source: https://sheetxl.com/docs/guides/introduction/key-benefits

Integrate SheetXL Studio into your web application by attaching it to a specific DOM element. This snippet is used to quickly add spreadsheet functionality to your app.

```javascript
import("https://cdn.jsdelivr.net/npm/@sheetxl/studio-vanilla@latest/cdn/index.js").then(({ SheetXL }) => SheetXL.attachStudio("#sheetxl"));  
```

--------------------------------

### Add Listener to a Range using SDK

Source: https://sheetxl.com/docs/guides/concepts/listening-for-changes

Use the `addListener` method from the SDK to subscribe to changes in a specific range. Call the returned `removeListener` function to unsubscribe.

```typescript
import { IWorkbook } from '@sheetxl/sdk';

// create a default workbook  
const workbook:IWorkbook = new Workbook();  
// get a range that can be iterators or allow for updates  
  
const removeListener:RemoveListener = workbook.getRange('Sheet1!A1:E5000').addListener((event: WorkbookRangeEvent): void => {  
  // do something with the range. We are justing going to log it.  
  console.log(`We received an event from ${event.getSource().toString()}`);  
});  
// call removeListener() to remove the listener when finished  

```

--------------------------------

### Optional Parameter UDF

Source: https://sheetxl.com/docs/guides/concepts/udf

A UDF demonstrating an optional parameter using the '?' syntax. The function handles the case where the parameter might be undefined.

```typescript
/**  
 * Return Hello message  
 * @param who Who is saying hello.  If omitted, defaults to "World".  
 */  
export function hello(who?: string): string {  
  return 'Hello ' + (who ?? 'World'); // Use nullish coalescing operator  
}
```

--------------------------------

### Copy Sparse Data Between Ranges Using Incremental Updates

Source: https://sheetxl.com/docs/guides/concepts/understanding-sparse-data

Efficiently copies data from a source range to a target range, processing only cells with values. Uses `IncrementalUpdater` for performance.

```typescript
// Assume we already have an `IWorkbook`.  
let sheet: ISheet = workbook.getSelectedSheet();  
  
// Step 1: Get the source range that contains the sparse data to be copied.  
//         Here, we assume we are copying data from range 'A1:B3'.  
const from: ICellRange = sheet.getRange('A1:B3');  
  
// Step 2: Define the target range where data will be copied.  
//         We reposition the target range by moving it to the right of the  
//         source range. In this case, we shift the target range to the  
//         right by the number of columns in the source range.  
const to: ICellRange = from.offsetBy(0, from.getColumnCount());  
  
// Step 3: Create an `IncrementalUpdater` for the target range.  
//         This updater will allow us to efficiently push updates to the  
//         target range as we iterate over the source range.  
//  
//         Here, we use `startIncrementalUpdates` to work with sparse data efficiently.  
const updates: ICellRange = to.startIncrementalUpdates();  
  
/**  
 * Step 4: Iterate over each cell in the source range and copy the data to the target range.  
 *  
 * - `forEach`: Iterates through each cell in the range.  
 * - `context.getCoords()`: Retrieves the coordinates (row, column) of the  
 *    current cell being iterated.  
 *  
 *   **Hint:** The coordinates retrieved by `context.getCoords()` are relative  
 *    to their respective ranges.  
 *    This means you can use them directly in the target range without needing  
 *    to apply any transformations.  
 *  
 * - `push`: Adds the data to the target range based on the context's coordinates.  
 */  
from.forEach((value, context): void => {  
  // Example transformation or action on the value (can be any logic or calculation).  
  let valueUpdate = value; // In this case, we're just copying the value without changes.  
  
  // Push the copied value into the target range at the corresponding coordinates.  
  updates.push(context.getCoords(), valueUpdate);  
});  
  
/**  
 * Step 5: Apply the updates to the target range.  
 *  
 * - `apply`: Once all updates are pushed, this method applies the changes to the sheet.  
 *    This ensures that all updates are executed efficiently and at once,  
 *    rather than cell by cell.  
 */  
updates.apply(); // Finalize and apply all the updates to the target range.  

```

--------------------------------

### Define an Autorun Macro Function

Source: https://sheetxl.com/docs/guides/concepts/scripting

This macro runs automatically once when a workbook is loaded. Use the `export default` syntax to designate the autorun function.

```typescript
/**
 * Exporting a function as default will cause it to be run on workbook load.
 */
export default function main(workbook: SheetXL.IWorkbook): void {
  // code that will only be executed once on load
}
```

--------------------------------

### Save Workbook State to JSON

Source: https://sheetxl.com/docs/guides/concepts/load-and-saving

Serialize the current workbook state to a JSON object. This JSON can then be saved to local storage or a database.

```typescript
const asJSON:IWorkbook.JSON = workbook.toJSON();  
// save asJSON to local storage or db  
```

--------------------------------

### Simple Addition UDF

Source: https://sheetxl.com/docs/guides/concepts/udf

A basic UDF that adds two numbers. It demonstrates a simple function signature and return type.

```typescript
/**  
 * Add 2 numbers.  
 * @summary Add two numbers  
 */  
export function add(a: number, b: number): number {  
  return a + b;  
}
```

--------------------------------

### Listen for Sheet Changes using useModelListener Hook

Source: https://sheetxl.com/docs/guides/concepts/listening-for-changes

The `useModelListener` hook from `@sheetxl/mui-studio` allows React components to listen for various sheet events, such as `onBeforeSave` and `onSave`. The `fireOnModelChange` option can be used to trigger listeners immediately.

```typescript
import { IWorkbook } from '@sheetxl/sdk';  
import { Studio, useModelListener } from '@sheetxl/mui-studio';  
  
/* Listen for changes to sheet */  
useModelListener<ISheet, ISheet.IListeners>(sheet, {  
  // See ISheet.IListeners for all events  
  onBeforeSave?(source: ISheet): Promise<void> | void {  
    // throw an exception to prevent save.  
  },  
  onSave(source: ISheet, json: ISheet.JSON): void {  
    // do something with the json  
  },  
}, {  
  // Optional: Fire listeners immediately on mount with current state  
  fireOnModelChange: true  
});  
  

```

--------------------------------

### Define a Macro Function to Set Cell Fill Color

Source: https://sheetxl.com/docs/guides/concepts/scripting

Use this to automate tasks like changing cell styles. Macro functions can be triggered manually or via UI elements and accept spreadsheet objects as input.

```typescript
/**
 * Prompts the user for a color.
 * @summary Set Fill Color
 */
export function setFillColor(range: ICellRange, fillColor: IColor): void {
  range.getStyle().setFill(fillColor);
}
```

--------------------------------

### Read and Write Cell Values

Source: https://sheetxl.com/docs/guides/concepts/working-with-data

Use `setValues` to write a 2D array of data to a range and `getValues` to retrieve data from a range.

```typescript
workbook.getRange('Sheet1!A1:B2').setValues([[100, 200], [300, 400]]);
workbook.getRange('Sheet1!A1:C3').getValues();
```

--------------------------------

### Reading Cell Styles

Source: https://sheetxl.com/docs/guides/concepts/styling

Access cell styles using the ICell interface. For multiple cells, use the forEach iterator for better performance.

```typescript
// Note getCell is not recommended if accessing many values  
const cell:ICell = workbook.getRange('Sheet1!A1').getCell();  
const style:IStyle = cell.getStyle();  
  
// If accessing many values use the forEach iterator.  
workbook.getRange('Sheet1!A1:E5').forEach((value: Scalar, context: ICell.IteratorContext) => {  
  // only cells with a value will be visited. A complex ICell can be accessed using the context.  
  const cell:ICell = context.getCell();  
  const style:IStyle = cell.getStyle();  
}, options);
```

--------------------------------

### Define a Custom Formula Function

Source: https://sheetxl.com/docs/guides/concepts/scripting

Use this to create reusable UDFs for custom calculations within spreadsheet formulas. Ensure the function returns a value usable in formulas.

```typescript
/**
 * @summary Add 2 numbers.
 */
export function add(a: number, b: number): number {
  return a + b;
}
```

--------------------------------

### Updating Cell Styles

Source: https://sheetxl.com/docs/guides/concepts/styling

Update cell styles using the IStyle.update method with a JSON object. Partial updates are supported, and string values can be parsed like CSS.

```typescript
// update style to several cells.  
const updateExample1:IStyle.Update = {  
  font: '14pt Arial',  
  fill: IColor.Named.AliceBlue,  
};  
workbook.getRange('Sheet1!A1:E5').getStyle().update(updateExample1);  
  
// revert the fill to the normal style but leave the font change intact.  
workbook.getRange('Sheet1!A1:E5').getStyle().update({  
  fill: null,  
});
```

--------------------------------

### Retrieve ISheet or ISheetCollection

Source: https://sheetxl.com/docs/guides/concepts/interactive-with-sheets

Access a specific sheet by its name or retrieve all sheets within a workbook. This is essential for navigating and managing the different worksheets in a spreadsheet.

```typescript
// return a sheet via a workbook  
const sheet:ISheet = workbook.getSheet('Sheet1');  
  
// return all sheets via a workbook  
const sheets:ISheetCollection = workbook.getSheets();  
```

--------------------------------

### Streaming Function for Countdown Timer

Source: https://sheetxl.com/docs/guides/concepts/udf

Use Observables to return continuously updating values, suitable for real-time data or timers. The function emits values at specified intervals.

```typescript
/**  
 * Simulates a countdown clock  
 *  
 * @param start The start amount  
 * @param increment The amount to tick down by  
 * @param delay The time in milliseconds to delay.  
 */  
export function countDown(start: number, increment: number=1, delay: number=1000): Observable<number> {  
  return new Observable((subscriber: Subscriber<number>) => {  
    let current = start;  
    let timeoutId: any; // store to support clear.  
  
    function tick() {  
      if (subscriber.closed) {  
        return; // Stop emitting values  
      }  
  
      subscriber.next(current);  
      current -= increment;  
  
      if (current <= 0) {  
        subscriber.complete();  
      } else {  
        timeoutId = setTimeout(tick, delay); // Store the timeout ID to close  
      }  
    }  
  
    tick(); // start  
  
    // Return a teardown function. - Not required.  
    return () => {  
      clearTimeout(timeoutId); // Clear the timeout  
    };  
  });  
}
```

--------------------------------

### Sum Numbers on Even Rows in a Sparse Range

Source: https://sheetxl.com/docs/guides/concepts/understanding-sparse-data

Uses cell context to iterate only over cells with values and sum numbers specifically on even rows. Ignores non-numeric types and odd rows.

```typescript
let total: number = 0;  
const range = sheet.getRange('A1:Z300');  
// This will only visit cells that have values.  
for (const entry of range.entries()) {  
  const value:Scalar = entry.value;  
  // ignore types that are not a number. and any value not on an even row.  
  if (typeof value === 'number' && entry.context.getCoords().rowIndex % 2) {  
    total += value;  
  }  
}  
console.log('The Total for event row is: ' + total);  

```

--------------------------------

### Handle Division by Zero Error in SheetXL

Source: https://sheetxl.com/docs/guides/concepts/udf

Implement error handling for division by zero by throwing a SheetXL.FormulaError.DIV0() error. This ensures the spreadsheet displays the correct error code.

```typescript
/**
 * Divides two numbers
 * @summary Returns divided number
 */
export function divide(a: number, b: number): number {
  if (b === 0) throw new SheetXL.FormulaError.DIV0();
  return a/b;
}

```

--------------------------------

### Sum Numbers in a Sparse Range

Source: https://sheetxl.com/docs/guides/concepts/understanding-sparse-data

Iterates only over cells with values to sum all numbers within a specified range. Ignores non-numeric types.

```typescript
let total: number = 0;  
// This will only visit cells that have values.  
for (const value of sheet.getRange('A1:Z300')) {  
  // ignore types that are not a number.  
  if (typeof value === 'number') {  
    total += value;  
  }  
}  
console.log('The Total is: ' + total);  

```

--------------------------------

### Clear and Ignore Cell Values

Source: https://sheetxl.com/docs/guides/concepts/working-with-data

Use `null` to clear a cell's content and `undefined` to ignore a cell, leaving its content unchanged. This is useful for partial updates.

```typescript
range.setValues([[100, 200], [undefined, null]]);
```
Tokens:
Raw