Picture in Cell

DsExcel .NET allows you to embed images directly into worksheet cells, similar to MsExcel’s native functionality. Pictures in cells are anchored to the cell, and an image icon appears next to the cell to represent the data. The picture will move, resize, or be deleted in sync with the cell. The pictures in cells are supported when exporting to PDF, HTML, and image formats.

Add Picture in Cell

You can embed images into cells using the IRange.CellPicture property. To add a picture, create a CellPicture object containing the image data and optional alt text, and assign it to the cell.

The alt text serves as the display value for features that require text evaluation, such as filter and sort. If the alt text is empty, a localized default value will be used (“Picture” in English, “图片” in Chinese, “画像” in Japanese, “그림” in Korean, or “Bild” in German). You can use the CellPicture.AltText property to get or set the alt text.

The following example demonstrates how to add a picture with alt text to a cell.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.ActiveSheet;

// Read image data from a file.
byte[] imageData = File.ReadAllBytes("Picture1.png");

// Set picture in cell with alt text.
sheet.Range["A1"].CellPicture = new CellPicture(imageData, "Dress");

// Save to an Excel file.
workbook.Save("PictureinCell.xlsx");

The output is shown in the figure below:

Retrieve Picture from Cell

Use the IRange.CellPicture property to retrieve a picture from a cell. This property returns a CellPicture object if the cell contains a picture in cell; otherwise, it returns null. This property also supports reading valid pictures generated by the IMAGE() function.

The CellPicture.ImageData (byte[]) property returns a copy of the image’s binary data. Supported formats include PNG, JPG, GIF, BMP, and SVG. Modifying the returned array does not affect the image stored in the cell.

The following example demonstrates how to retrieve picture data from a cell and save it to a png file.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.ActiveSheet;

// Read image data from file.
byte[] imageData = File.ReadAllBytes("Picture1.png");

// Set picture in cell with alt text.
sheet.Range["A1"].CellPicture = new CellPicture(imageData, "Dress");

// Retrieve the picture from the cell.
CellPicture picture = sheet.Range["A1"].CellPicture;

if (picture != null)
{
    // Get the alt text.
    string altText = picture.AltText;
    Console.WriteLine($"Alt Text: {altText}");  // Output: Alt Text: Dress

    // Get the image data.
    byte[] retrievedData = picture.ImageData;

    // Save to a file.
    File.WriteAllBytes("retrieved_picture.png", retrievedData);

}

Modify Alt Text

The returned CellPicture object is a copy of the cell’s data. Modifying this object does not automatically update the cell. To change the picture content or alt text in the cell, create or modify a CellPicture and assign it back to the IRange.CellPicture property.

The following example demonstrates how to modify a picture in a cell.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.ActiveSheet;

// Read image data from file.
byte[] imageData = File.ReadAllBytes("Picture1.png");

// Set picture in cell with alt text.
sheet.Range["A1"].CellPicture = new CellPicture(imageData, "Dress");

// Get the existing picture.
CellPicture existingPicture = sheet.Range["A1"].CellPicture;

// Modify alt text and reassign.
existingPicture.AltText = "Pink dress with sparkles";
sheet.Range["A1"].CellPicture = existingPicture;

// Save to an Excel file.
workbook.Save("ModifyPictureinCell.xlsx");

The output is shown in the figure below:

Remove Picture from Cell

You can remove a picture from a cell using any of the following methods. When applied to a range containing multiple cells, the picture in each cell will be removed.

1. Use the IRange.Clear() or IRange.ClearContents() method. IRange.Clear() removes the cell’s formatting and picture, whereas IRange.ClearContents() removes only the picture, and preserves the cell’s formatting.

2. Set the IRange.CellPicture property to null.

3. Assign any value to the IRange.Value property.

The following example demonstrates how to remove a picture from a cell.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.ActiveSheet;

// Read image data from a file.
byte[] imageData = File.ReadAllBytes("Picture1.png");

// Set picture in cell with alt text.
sheet.Range["A1"].CellPicture = new CellPicture(imageData, "Dress");

// Method 1: Remove the picture using ClearContents().
sheet.Range["A1"].ClearContents();

// Method 2: Remove the picture by setting CellPicture to null.
sheet.Range["A1"].CellPicture = null;

// Method 3: Remove the picture by assigning a value to the cell.
sheet.Range["A1"].Value = "No picture";

// Save to an Excel file.
workbook.Save("RemovePicture.xlsx");