Use the Excel Export API to Insert and Position a Picture in a Worksheet
- 4 minutes to read
This topic describes how to use the Excel Export Library to insert a picture into a worksheet. The IXlPicture interface contains properties and methods that allow you to load a picture from a file or stream, specify picture position, and add a hyperlink to the picture.
Follow the steps below to add a picture to a worksheet:
- Call the IXlSheet.CreatePicture method to create a new IXlPicture object.
- Call the IXlPicture.SetImage method to specify the image you wish to display in the worksheet. Use the Image.FromFile or Image.FromStream method to load the required image from a file or data stream.
To position the picture in the worksheet, use one of the following methods depending on the anchor type you wish to use:
Absolute Anchoring
Use the IXlPicture.SetAbsoluteAnchor method to specify the absolute picture position in the worksheet. The position is defined by offsets from the worksheet’s left and top edges to the picture’s top-left corner. The picture does not move or resize with the underlying cells.
One Cell Anchoring
Use the IXlPicture.SetOneCellAnchor method to anchor the picture’s top-left corner to a worksheet cell. The picture moves with the anchor cell, but the picture size remains the same.
Two Cell Anchoring
Use the IXlPicture.SetTwoCellAnchor method to anchor the picture to two cells in the worksheet. The first anchor cell specifies the position of the picture’s top-left corner, and the second anchor cell defines the position of the picture’s bottom-right corner. The method’s last parameter specifies how the picture behaves when the underlying columns and rows are resized and moved (IXlPicture.AnchorBehavior).
You can also use the IXlPicture.AnchorType, IXlPicture.TopLeft, and IXlPicture.BottomRight properties to specify or modify the anchor type and position of the picture’s anchors.
Note
When you finish working with the IXlPicture object, call the Dispose method to release all the resources used by the object. Otherwise, generated content is not written to the output file. You can also modify the IXlPicture object within the using statement (Using block in Visual Basic).
The example below uses the IXlPicture.SetTwoCellAnchor method to anchor a picture to two cells in a worksheet. The anchor for the picture’s top-left corner is located at the intersection of the column “B” and the second row, with no offsets. The anchor for the picture’s bottom-right corner is located at the intersection of the column “G” and the twelfth row, with offsets from both the column and row. The IXlPicture.AnchorBehavior is set to XlAnchorType.TwoCell.
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Insert a picture from a file and anchor it to two cells.
using (IXlPicture picture = sheet.CreatePicture()) {
picture.SetImage(Image.FromFile(Path.Combine(imagesPath, "image1.jpg")), ImageFormat.Jpeg);
picture.SetTwoCellAnchor(new XlAnchorPoint(1, 1, 0, 0), new XlAnchorPoint(6, 11, 2, 15), XlAnchorType.TwoCell);
}
}
The following image shows the result (the workbook is opened in Microsoft® Excel®):
Fit a Picture to a Cell
The IXlPicture interface contains the StretchToCell and FitToCell methods that allow you to resize a picture to fit a worksheet cell.
Use the StretchToCell method to stretch a picture so that it fills the entire cell. If cell size changes, the picture size changes accordingly.
// Insert a picture from a file and stretch the picture to fill the "B2" cell. using (IXlPicture picture = sheet.CreatePicture()) { picture.SetImage(Image.FromFile(Path.Combine(imagesPath, "image1.jpg")), ImageFormat.Jpeg); picture.StretchToCell(new XlCellPosition(1, 1)); }The following image shows the result (the workbook is opened in Microsoft® Excel®):
Use the FitToCell method to scale a picture proportionally to fit a cell of the specified size. The method’s last parameter specifies whether to center the picture in the cell.
// Insert a picture from a file into the "B2" cell. using (IXlPicture picture = sheet.CreatePicture()) { picture.SetImage(Image.FromFile(Path.Combine(imagesPath, "image1.jpg")), ImageFormat.Jpeg); picture.FitToCell(new XlCellPosition(1, 1), 300, 154, true); }The following image shows the result (the workbook is opened in Microsoft® Excel®):
