Infragistics(R) NetAdvantage(R) WPF
Adding a Shape to an Excel Worksheet
E-mail your feedback on this topic.

Glossary Item Box

Topic Overview

Purpose

This topic demonstrates how to add a predefined shape to an Excel® Worksheet using the Infragistics Excel Engine®.

In this topic

This topic contains the following sections:

  1. Topic Overview
    1. Purpose
    2. In this topic
    3. Required background
  2. Adding a Shape to an Excel Worksheet
    1. Introduction
    2. Preview
    3. Requirements
    4. Overview
    5. Steps
  3. Related Topics

Required background

You need to first read the following topics:

Introduction

With the Infragistics Excel Engine, you can manipulate a subset of predefined shapes in a Worksheet object.

Supported Shapes

From a Excel's user perspective, shapes are accessible from the Insert -> Shapes drop-down menu.

Unfortunatelly not all of these shapes are available through the Infragistics Excel Engine API.

All predefined shapes that are supported have a corresponding class defined for them in the Infragistics.Documents.Excel.PredefinedShapes Namespace. Each class derives (not necessary directly) from WorksheetShape Class. Those that are not directly derived from WorksheetShape Class are derived from WorksheetShapeWithText Class and have the ability to store formatted text in them.

Following is a list of the supported predefined shapes and the classes through which they are implemented.

Shape Looks like... Class Class of origin
Line LineShape Class WorksheetShape Class
Straight Connector StraightConnector1Shape Class WorksheetShape Class
Unknown Shape No visual representantion. Used to deserialize all not defined shapes. Read more at Importing Shapes from Excel UnknownShape Class WorksheetShapeWithText Class
Diamond DiamondShape Class WorksheetShapeWithText Class
Ellipse (Oval) EllipseShape Class WorksheetShapeWithText Class
Heart HeartShape Class WorksheetShapeWithText Class
Explosion 1 IrregularSeal1Shape Class WorksheetShapeWithText Class
Explosion 2 IrregularSeal2Shape Class WorksheetShapeWithText Class
Lightning Bolt LightningBoltShape Class WorksheetShapeWithText Class
Pentagon PentagonShape Class WorksheetShapeWithText Class
Rectangle RectangleShape Class WorksheetShapeWithText Class
Right Triangle RightTriangleShape Class WorksheetShapeWithText Class

Adding a Shape to an Excel Worsheet

Introduction

To add a shape to a worksheet initialize one of the predefined shapes, set its position, apply some customizations and add it to the worksheet. Detailed steps are provided below. In the procedure below, a blue-colored rectangle with a sample text in it is placed between C3 and K11 cells of an Excel grid. The rectangle’s side edges are positioned exactly on the middle of columns C and K, respectively.

Preview

Following is a preview of the final result displayed in Microsoft Excel 2010.

Requirements

To complete the procedure, you need the following:

Overview

Following is a conceptual overview of the process:

  1. Creating a shape instance
  2. Configuring the position and size
  3. Customizing the shape
  4. (Optional) Verifying the result

Steps

  1. Create a shape instance.
    There are two ways to do this:
    • Using the shape's constructor
    • Using the CreatePredefinedShape method

    Each of them is demonstrated in the code below

    1. Using the shape's constructor.

      In Visual Basic:

      Dim shape As New RectangleShape()
      
      

      In C#:

      RectangleShape shape = new RectangleShape();
      
      
    2. Using the CreatePredefinedShape method.

      In Visual Basic:

      Dim shape As RectangleShape =
          WorksheetShape.CreatePredefinedShape(PredefinedShapeType.Rectangle)
      
      

      In C#:

      RectangleShape shape =
          (RectangleShape)WorksheetShape.CreatePredefinedShape(PredefinedShapeType.Rectangle);
      
      
  2. Configure the position and size.
    There are two positioning methods to do this.
    • Using cells (preffered)
    • Using twips

    Using twips methods is created to provide an easier way to specify shape’s coordinates. Both alignment methods end with the same result – aligning the shape using anchor cells (as “Using cells” works).

    Each of them is demonstrated in code below. In both alignment methods you use the PositioningMode Property to specify the behavior of auto resizing the shape with the cells.

    1. Using cells.

      To position a shape in the Excel’s grid you need to specify top-left and bottom-right anchors (An anchor is a point, defined within a single cell. For example: The center of cell K9 or the top-right corner of the cell A0).

      The TopLeftCornerCell and TopLeftCornerPosition properties defines the top-left corner anchor and the BottomRightCornerCell and BottomRightCornerPosition properties defines the bottom-right corner anchor.

      The TopLeftCornerPosition property and BottomRightCornerPosition property contains a PointF structure, The X and Y coordinates are used relative (percentages) and not absolute values (pixels), For instance, PointF(50,50) will place the control's corner on the center of the selected cell. You should select an anchor cell by setting the TopLeftCornerCell and BottomRightCornerCell before setting the corresponding position.

      In Visual Basic:

      shape.TopLeftCornerCell = sheet.Rows(2).Cells(2)
      shape.TopLeftCornerPosition = New System.Drawing.PointF(50, 100)
      shape.BottomRightCornerCell = sheet.Rows(10).Cells(10)
      shape.BottomRightCornerPosition = New System.Drawing.PointF(50, 100)
      
      

      In C#:

      shape.TopLeftCornerCell = sheet.Rows[2].Cells[2];
      shape.TopLeftCornerPosition = new System.Drawing.PointF(50, 100);
      shape.BottomRightCornerCell = sheet.Rows[10].Cells[10];
      shape.BottomRightCornerPosition = new System.Drawing.PointF(50, 100);
      
      
    2. Using twips

      A twip is 1/20th of a point or 1/1440th of an inch. For example, in the 96 DPI resolution, one pixel has 15 twips in both width and height. Here, we use them to specify the top-left and right-bottom corner positions of the shape.

      In this method the top-left corner and the size of the shape is set by the SetBoundsInTwips method which accepts a Worksheet object and a Rectangle object containing the coordinates of the shapes in twips.

      The GetBoundsInTwips method can be used to retrieve the current position and size of a shape.

      By using SetBoundsInTwips method you are setting the anchor cells based on the current configuration of the worksheet.

      In Visual Basic:

      shape.SetBoundsInTwips(sheet, New Rectangle(150, 150, 3000, 3000))
      
      

      In C#:

      shape.SetBoundsInTwips(sheet, new Rectangle(150, 150, 3000, 3000));
      
      

      In this code above the shape is placed at 10, 10 at the top-left corner and 200, 200 at the bottom-right corner (in 96 DPI). This automatically sets the right cells and positions.

  3. Customize the shape
    Customize the shape as follows:
    • fill color - steel blue
    • outline color - royal blue
    • shape text - Infragistics.

    In Visual Basic:

    shape.Fill = ShapeFill.FromColor(System.Drawing.Color.SteelBlue)
    shape.Outline = ShapeOutline.FromColor(System.Drawing.Color.RoyalBlue)
    sheet.Shapes.Add(shape)
    Dim fs As New FormattedString("infragistics")
    shape.Text = fs
    fs.GetFont(0, 12).Bold = ExcelDefaultableBoolean.[True]
    fs.GetFont(0, 12).Color = Colors.Red
    fs.GetFont(0, 1).Height = 1000
    fs.GetFont(5, 1).Height = 1000
    
    

    In C#:

    shape.Fill = ShapeFill.FromColor(System.Drawing.Color.SteelBlue);
    shape.Outline = ShapeOutline.FromColor(System.Drawing.Color.RoyalBlue);
    sheet.Shapes.Add(shape);
    FormattedString fs = new FormattedString("infragistics");
    shape.Text = fs;
    fs.GetFont(0, 12).Bold = ExcelDefaultableBoolean.True;
    fs.GetFont(0, 12).Color = Colors.Red;
    fs.GetFont(0, 1).Height = 1000;
    fs.GetFont(5, 1).Height = 1000;
    
    

    For details, refer to the Customizing Excel Shapes topic.

    Note that the shape was added to the Worksheet before setting the Text property. You can apply formatted strings on a shapes only if the shape is added to a worksheet.

  4. (Optional)Verify the result.

    To verify the result, compile and run your application and then export the output of the procedure to an Excel file (for preview in Excel).

Related Topics