How to: Insert and Position a Picture in a Worksheet
- 5 minutes to read
The Excel Export library provides you with the capability to add images to a generated document. A picture is represented by the IXlPicture object that provides a set of properties and methods allowing you to insert the required image, adjust the picture placement and add a hyperlink to a picture, if required.
To add a picture to a worksheet, do the following.
- Create a new IXlPicture object by calling the IXlSheet.CreatePicture method.
- Set the IXlPicture.Image property to the Image object that specifies the image to be displayed in the worksheet. Use the FromFile or FromStream method of the Image object to load the required image from a file or data stream.
To specify the picture’s position in the worksheet, use one of the following methods depending on the anchoring type you wish to use.
Absolute Anchoring
Use the IXlPicture.SetAbsoluteAnchor method to specify the absolute position of the picture in the worksheet based on absolute coordinates that represent offsets from the left and top sides of the worksheet to the picture’s top-left corner and set the desired width and height of the picture. The picture will not move or resize with the underlying cells.
One Cell Anchoring
Use the IXlPicture.SetOneCellAnchor method to anchor the top-left corner of the picture to a cell in the worksheet and set the picture’s width and height. The picture will move with the anchor cell, but its size will remain the same.
Two Cell Anchoring
Use the IXlPicture.SetTwoCellAnchor method to anchor the picture to two specific cells in the worksheet. The first anchor cell defines the position of the top-left corner of the picture, while the second one specifies where picture’s bottom-right corner is located. The last parameter of this method allows you to specify how the picture should behave when the underlying columns and rows are resized and moved.
Note that you can also use the IXlPicture.AnchorType, IXlPicture.TopLeft and IXlPicture.BottomRight properties to set or change the anchoring type and position of the picture’s anchors in a worksheet. To specify or modify the anchoring behavior of a picture anchored to two cells in a worksheet, use the IXlPicture.AnchorBehavior property.
The example below demonstrates how to use the IXlPicture.SetTwoCellAnchor method to anchor a picture to two cells in a worksheet. The starting anchor for a picture is located at the intersection of the second column (“B”) and the second row, with no offsets. The final anchor is located at the intersection of the seventh column (“G”) and the twelfth row, with offsets from both the column and row. The IXlPicture.AnchorBehavior is set to XlAnchorType.TwoCell, so that the picture moves and resizes with its underlying cells.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/xl-export-api-examples-t253492
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
// Insert a picture from a file and anchor it to cells.
using (IXlPicture picture = sheet.CreatePicture())
{
picture.Image = Image.FromFile(Path.Combine(imagesPath, "image1.jpg"));
picture.SetTwoCellAnchor(new XlAnchorPoint(1, 1, 0, 0), new XlAnchorPoint(6, 11, 2, 15), XlAnchorType.TwoCell);
}
}
The following image shows the result of executing the code above (the workbook is opened in Microsoft® Excel®).
Fit a Picture Into a Cell
The IXlPicture object provides the IXlPicture.StretchToCell and IXlPicture.FitToCell methods that allow you to resize a picture to fit a specific cell in a worksheet.
Use the IXlPicture.StretchToCell method to stretch a picture in a desired cell so that it fills the entire space of this cell. If the cell size changes, the picture size will be adjusted accordingly.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/xl-export-api-examples-t253492
// Insert a picture from a file and stretch it to fill the cell B2. using (IXlPicture picture = sheet.CreatePicture()) { picture.Image = Image.FromFile(Path.Combine(imagesPath, "image1.jpg")); picture.StretchToCell(new XlCellPosition(1, 1)); } }The following image shows the result of executing the code above (the workbook is opened in Microsoft® Excel®).
Use the IXlPicture.FitToCell method to rescale a picture to fit a cell of a specified size retaining the picture’s proportions. The last parameter of this method allows you to specify whether the picture should be aligned at the center of the cell.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/xl-export-api-examples-t253492
// Insert a picture from a file to fit in the cell B2. using (IXlPicture picture = sheet.CreatePicture()) { picture.Image = Image.FromFile(Path.Combine(imagesPath, "image1.jpg")); picture.FitToCell(new XlCellPosition(1, 1), 300, 154, true); } }The following image shows the result of executing the code above (the workbook is opened in Microsoft® Excel®).
