> ## Documentation Index
> Fetch the complete documentation index at: https://docs.reducto.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Pictures in cells

A picture can be the value of a cell, as Insert > Pictures > Place in Cell does in Microsoft Excel. The picture is not a drawing over the grid: it sorts, filters and moves with its row, and a formula can return it.

Press **Run**. Then change C2 to another status: the lookup in D2 returns that status's badge.

```ts theme={null}
// `workbook` is the open workbook. Each run is one undo step in the grid.
import type { ExcelWorkbook } from "@reductoai/xlsx/officejs";

declare const workbook: ExcelWorkbook;

/** A PNG as Base64. Any PNG, JPEG or GIF will do. */
function badge(color: string, label: string): string {
  const canvas = document.createElement("canvas");
  canvas.width = 200;
  canvas.height = 64;
  const ctx = canvas.getContext("2d")!;
  ctx.fillStyle = color;
  ctx.beginPath();
  ctx.roundRect(0, 0, 200, 64, 32);
  ctx.fill();
  ctx.fillStyle = "#FFFFFF";
  ctx.font = "600 30px system-ui, sans-serif";
  ctx.textAlign = "center";
  ctx.textBaseline = "middle";
  ctx.fillText(label, 100, 34);
  return canvas.toDataURL("image/png").split(",")[1]!;
}

const STATUSES = [
  ["Open", "#2E7D32"],
  ["Blocked", "#C62828"],
  ["Review", "#EF6C00"],
] as const;

const sheet = workbook.worksheets.getActiveWorksheet();
sheet.getRange("A1:B1").values = [["Status", "Badge"]];
sheet.getRange("A2:A4").values = STATUSES.map(([name]) => [name]);

// A picture is a cell value: write it with valuesAsJson.
sheet.getRange("B2:B4").valuesAsJson = STATUSES.map(([name, color]) => [
  {
    type: "LocalImage",
    image: { type: "PNG", data: badge(color, name) },
    altText: `${name} badge`,
  },
]);

// A formula can return a picture. Change C2 and D2 follows.
sheet.getRange("C1:D1").values = [["Pick a status", "Its badge"]];
sheet.getRange("C2").values = [["Blocked"]];
sheet.getRange("C2").dataValidation.rule = {
  list: { inCellDropDown: true, source: "Open,Blocked,Review" },
};
sheet.getRange("D2").formulas = [["=XLOOKUP(C2,A2:A4,B2:B4)"]];

// A picture is not text, a number or an error.
sheet.getRange("A6:B8").formulas = [
  ["=VALUETOTEXT(B2)", "its alternative text"],
  ["=TYPE(B2)", "128, as in Excel"],
  ["=COUNTIF(B2:B4,D2)", "cells that hold D2's picture"],
];

sheet.getRange("A1:D1").format.font.bold = true;
sheet.getRange("2:4").format.rowHeight = 30;
sheet.getRange("A:D").format.columnWidth = 72;
sheet.getRange("A2:D4").format.verticalAlignment = "Center";
sheet.getRange("B2:D4").format.horizontalAlignment = "Center";

console.log(sheet.getRange("A6:A8").values);
```

## Where the picture sits

The picture is scaled to fit inside its cell and keeps its proportions. A small picture is scaled up. The cell's alignment places it: the default is left and bottom, as in Excel. A merged cell gives the picture its whole area. The cell's fill shows behind it.

## Read and write

`valuesAsJson` reads and writes a picture as an `Excel.LocalImageCellValue`. In the Office JavaScript API this type is still in preview (ExcelApi BETA); the behaviour here is what Excel for Mac 16.115 does.

* To place a new image, give `image.type` and Base64 `image.data`. The bytes decide the format: a PNG, JPEG or GIF.
* Reading gives `image.cachedUid`, an id for the image, as Excel does. The same image has the same id in every cell.
* Writing `image.cachedUid` places a picture that a cell of the workbook already holds. A range's `valuesAsJson` can thus be copied to another range as is.
* An `IMAGE` result reads as an `Excel.WebImageCellValue`, with its `address`. Writing a `WebImage` entry throws.

```ts theme={null}
import { ExcelWorkbook } from "@reductoai/xlsx/officejs";

const book = ExcelWorkbook.fromBytes(bytes);
const cell = book.worksheets.getActiveWorksheet().getRange("B2");

const [[value]] = cell.valuesAsJson;
if (value.type === "LocalImage") {
  console.log(value.image.cachedUid, value.altText); // "{…}" "Open badge"
  console.log(cell.values, cell.text); // [["#VALUE!"]] [["Open badge"]], as in Excel
}

cell.valuesAsJson = [[{ type: "LocalImage", image: { type: "PNG", data: base64 } }]];
cell.getOffsetRange(0, 1).valuesAsJson = cell.valuesAsJson;
```

Below the facade, a picture is a `PictureValue`:

```ts theme={null}
import { PictureValue, Workbook } from "@reductoai/xlsx";

const wb = Workbook.create();
const ws = wb.sheet(0);
ws.setValue(0, 0, new PictureValue(pngBytes, "Company logo"));

const value = ws.value(0, 0);
if (value instanceof PictureValue) console.log(value.bytes.length, value.alt);
```

In the editor, the `addPicture` command with `inCell` puts a picture in the active cell. The Picture button's menu has **Place in Cell** and **Place over Cells**.

```ts theme={null}
import { mountXlsxEditor } from "@reductoai/xlsx-editor";

const editor = mountXlsxEditor(document.getElementById("app")!, { workbook: bytes });
editor.model.run({ type: "addPicture", bytes: pngBytes, inCell: true });
```

## Pictures in formulas

The rules are Excel's, each checked against Excel for Windows.

| Formula | Result |
| - | - |
| `=B2`, `=IF(TRUE,B2)`, `=INDEX(B2:B4,2)`, `=XLOOKUP(…)`, `=VLOOKUP(…)`, `=FILTER(…)`, `=IFERROR(B2,"")` | The picture |
| `=SORT(B2:B4)`, `=UNIQUE(B2:B4)`, `=VSTACK(B2,B3)`, `=TRANSPOSE(B2:B4)` | Pictures, spilled |
| `=B2=B3`, `=MATCH(B2,B2:B4,0)`, `=COUNTIF(B2:B4,B2)` | Two pictures are equal when the image and the alternative text are the same |
| `=B2&"x"`, `=B2+1`, `=LEN(B2)`, `=SUM(B2:B4)`, `=B2<B3` | `#VALUE!` |
| `=ISERROR(B2)`, `=ISTEXT(B2)`, `=ISNUMBER(B2)`, `=ISBLANK(B2)` | `FALSE` |
| `=TYPE(B2)` | `128` |
| `=COUNTA(B2:B4)` | `3` |
| `=VALUETOTEXT(B2)` | The alternative text, or `Picture` |

## In the file

The picture is saved as Excel writes it: a `_localImage` rich value, with the image in `xl/media`. The same image in many cells is stored once. The cell also holds `#VALUE!`, which is what an application without pictures in cells shows.

## The IMAGE function

`=IMAGE(source, [alt_text], [sizing], [height], [width])` puts the picture at an `https` address in its cell. Press **Run**: the cells show `#BUSY!` until each picture loads.

```ts theme={null}
import type { ExcelWorkbook } from "@reductoai/xlsx/officejs";

declare const workbook: ExcelWorkbook;

const PNG =
  "https://upload.wikimedia.org/wikipedia/commons/4/47/PNG_transparency_demonstration_1.png";
const PUPPY = "https://picsum.photos/id/237";

const sheet = workbook.worksheets.getActiveWorksheet();
sheet.getRange("A1:B1").values = [["Picture", "Formula"]];
const rows = [
  `=IMAGE("${PNG}","Dice")`,
  `=IMAGE("${PNG}","Dice",1)`,
  `=IMAGE("${PUPPY}/40/30","Puppy",2)`,
  `=IMAGE("${PNG}","Dice",3,30,40)`,
  `=IMAGE("${PUPPY}/400/300","Puppy")`,
  `=IMAGE("http://example.com/a.png")`,
  `=IMAGE("https://example.com/missing.png")`,
];
sheet.getRange("A2:A8").formulas = rows.map((f) => [f]);
// A leading apostrophe keeps a formula as text.
sheet.getRange("B2:B8").values = rows.map((f) => [`'${f}`]);
sheet.getRange("A10:B11").formulas = [
  ["=VALUETOTEXT(A2)", "its alternative text"],
  ["=A2=A3", "TRUE: the sizing does not count"],
];

sheet.getRange("A1:B1").format.font.bold = true;
sheet.getRange("2:8").format.rowHeight = 40;
sheet.getRange("A:A").format.columnWidth = 90;
```

The fourth argument, `sizing`, is Excel's:

| `sizing` | The picture |
| - | - |
| `0` (default) | Fits inside the cell and keeps its proportions |
| `1` | Fills the cell |
| `2` | Keeps its own size; the cell clips it |
| `3` | Takes `height` and `width` in pixels; one of them alone keeps the proportions |

The errors are Excel's too:

* `#BUSY!` while the picture loads.
* `#BLOCKED!` for an `http` address, or when nothing can load pictures.
* `#CONNECT!` when the load fails.
* `#VALUE!` for an address that is not `https`, a file that is not a picture (SVG too), or a bad `sizing`, `height` or `width`.

A workbook loads pictures with the function you give `setImageLoader`. The editor uses `fetchImage` by default, so the site must allow your page's origin (CORS). Set `images: false` to load nothing. With no loader, `IMAGE` uses the picture the file saved for its address, so an open file shows Excel's pictures without a load.

```ts theme={null}
import { fetchImage, Workbook } from "@reductoai/xlsx";

const wb = Workbook.open(bytes);
wb.setImageLoader(fetchImage);
await wb.functionsSettled();
```

Each address loads once. `wb.refreshFunctions("IMAGE")` loads them again. A WebP, BMP or other format the browser can read is saved as PNG, as Excel does. On Node, import `@reductoai/xlsx/node` and install `skia-canvas` to do the same; without them, only PNG, JPEG and GIF load, and other formats show `#VALUE!`. The result is saved as Excel's `_webimage` rich value, with the address and the picture.
