Charts and Graphics in Spreadsheet Documents
- 4 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
- Implement User Restrictions
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 the 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 a 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.
Implement End-User Restrictions
The SpreadsheetControl allows you to prevent end-users from modifying drawing objects embedded in a worksheet. Use the following properties to implement end-user restrictions.
SpreadsheetControl.Document.Options.DocumentCapabilities
Property Description WorkbookCapabilitiesOptions.Pictures Specifies whether end-users can load, insert, and export pictures. WorkbookCapabilitiesOptions.Charts Specifies whether end-users can load, insert, and export charts. WorkbookCapabilitiesOptions.Shapes Specifies whether end-users can load and export shapes. SpreadsheetControl.Options.Behavior.Drawing
Property Description SpreadsheetDrawingBehaviorOptions.Move Specifies whether end-users can change the position of drawing objects. SpreadsheetDrawingBehaviorOptions.Resize Specifies whether end-users can change the size of drawing objects. SpreadsheetDrawingBehaviorOptions.Rotate Specifies whether end-users can rotate drawing objects. SpreadsheetDrawingBehaviorOptions.ChangeZOrder Specifies whether end-users can change the order of drawing objects in a worksheet.
Set the desired property to DocumentCapability.Disabled or DocumentCapability.Hidden to make the operation unavailable, and disable or hide the corresponding commands in the SpreadsheetControl’s ribbon UI and popup menu.