Charts and Graphics in Spreadsheet Documents
- 3 minutes to read
This document provides information about embedded drawing objects - shapes, pictures and charts.
Actions covered here:
- Create a Drawing Object
- Move and Position a Drawing Object
- Rotate and Resize a Drawing Object
- Associate a Drawing Object with a Hyperlink
- Delete A Drawing Object
Create a Drawing Object
The Shape interface is a base interface for all drawing objects. These objects are stored in the ShapeCollection collection, accessible using the Worksheet.Shapes property.
Note
Pictures and charts are contained in two collections: ShapeCollection and PictureCollection or ChartCollection, respectively.
Use the Shape.ShapeType property to determine a drawing object’s type in ShapeCollection.
The table below lists the supported drawing object types and methods used to create them.
| Drawing Object | Method |
|---|---|
| Shape | ShapeCollection.AddShape |
| Shape Group | ShapeCollection.GroupShapes |
| Text Box | ShapeCollection.AddTextBox |
| Shape Connector | ShapeCollection.AddConnector |
| Picture | PictureCollection.AddPicture |
| Chart | ChartCollection.Add |
Move and Position a Drawing Object
Use an API from the table below to define a drawing object’s location.
| Property | Description |
|---|---|
| FloatingObject.Move | Moves a floating object by a specified offset. |
| FloatingObject.Left | Gets or sets the distance from the left edge of the worksheet to the top left corner of the drawing object. |
| FloatingObject.OffsetX | Specifies a distance between the top left corner of the floating object and the left edge of the cell where the top left corner of the floating object is located. |
| FloatingObject.Top | Defines the distance between the top edge of the worksheet and the top left corner of the floating object. |
| FloatingObject.OffsetY | Gets a distance between the top left corner of the floating object to the top edge of the cell where the top left corner of the floating object is located. |
| FloatingObject.TopLeftCell | Specifies a cell where the top left corner of a floating object is located. |
| FloatingObject.BottomRightCell | Gets or sets a cell where the bottom right corner of a floating object is located. |
| Shape.ZOrderPosition | Retrieves the position of the current drawing object in the Z-order. The default shapes’ Z-order value is equal to their numerical order in the ShapeCollection. |
| ShapeCollection.NormalizeZOrder | Resets the shapes’ Z-order. |
The image below illustrates the distances this API specifies.
Resize and Rotate a Drawing Object
You can specify the drawing object’s size and rotation angle using the following properties.
- FloatingObject.Height - defines the object’s height;
- FloatingObject.Width - defines the object’s width;
- FloatingObject.LockAspectRatio - specifies whether to keep the drawing object’s proportions;
- Shape.Rotation - gets or sets the drawing object’s rotation angle;
- Shape.IncrementRotation - rotates the drawing object by the given number of degrees.
End-users can resize or rotate a drawing object with special handles, which are visible for selected objects.
The FloatingObject.Placement property determines whether the drawing object moves and/or resizes together with underlying cells. For example, set the Placement property to Placement.MoveAndSize to make the drawing object move and resize with cells, as shown below.
Associate the Drawing Object with a Hyperlink
You can provide a hyperlink to the drawing object. When an end-user clicks the drawing object, the link navigates to a certain location on the Internet, or to a file, to a place in a workbook, or it sends an email. Use the Shape.InsertHyperlink method to specify a hyperlink for a drawing object, and use the Shape.RemoveHyperlink method to delete a hyperlink. Refer to the How to: Add a Hyperlink to a Picture topic for a code example.
Delete a Drawing Object
The following methods allow you to remove a drawing object.
-
ShapeCollection.RemoveAt - removes the element at the specified index.
- Shape.Delete - deletes a drawing object.