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.typeand Base64image.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.cachedUidplaces a picture that a cell of the workbook already holds. A range’svaluesAsJsoncan thus be copied to another range as is. - An
IMAGEresult reads as anExcel.WebImageCellValue, with itsaddress. Writing aWebImageentry throws.
PictureValue:
addPicture command with inCell puts a picture in the active cell. The Picture button’s menu has Place in Cell and Place over Cells.
Pictures in formulas
The rules are Excel’s, each checked against Excel for Windows.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.
sizing, is Excel’s:
The errors are Excel’s too:
#BUSY!while the picture loads.#BLOCKED!for anhttpaddress, or when nothing can load pictures.#CONNECT!when the load fails.#VALUE!for an address that is nothttps, a file that is not a picture (SVG too), or a badsizing,heightorwidth.
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.
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.