[]
DEMOS DOCS API PRICING
FREE TRIAL
(Showing Draft Content)

Shapes

DsExcel Java allows you to insert various types of shapes into worksheet cells, including standard shapes, connector shapes, picture shapes, and group shapes. You can manage and manipulate these shapes consistently using the properties and methods of the IShape and IShapes interfaces.

Add Shape

DsExcel Java provides the AddShape method of the IShapes interface, which supports multiple overloads. This allows you to add various types of shapes at specified positions or within specified cell ranges in a worksheet, as well as set their sizes. You can assign a custom name to each newly created shape, making it easy to directly access and modify the shape’s properties by name, without the need to iterate through the entire shapes collection.

The following example demonstrates how to add a Rectangle shape at a specified position and assign it a name. You can also refer to the AutoShapeType enumeration to add other types of shapes.

// Create a workbook.
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Add a shape with a custom name at a specific position.
IShape shape = worksheet.getShapes().addShape("Rectangle", AutoShapeType.Rectangle, 50, 50, 100, 150);

// Alternatively, add the shape to a specified range.
// IShape rangeShape = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, worksheet.Range["F5:I10"]);

// Assign a new name and retrieve the shape by its name.
shape.setName("NewRectangleName");
IShape balloon = worksheet.getShapes().get("NewRectangleName");
balloon.getTextFrame().getTextRange().setText("Rectangle Shape");
balloon.getTextFrame().getTextRange().setTextAlignment(TextAlignmentAnchor.Center);

// Save the Excel file.
workbook.Save("RectangleShape.xlsx");

The output is shown in the figure below:

image

Get Shape Type

Each shape object contains a Type property that indicates the specific type of the shape. This property corresponds to the ShapeType enumeration. By reading the Type property, you can quickly retrieve and identify the type of a shape. (e.g. Callout, Textbox, Line etc.)

The following example demonstrates how to iterate through all shapes in a worksheet and display a type label to the right of each shape. Click here to download the worksheet.ShapeType.xlsx

// Create a new workbook.
Workbook workbook = new Workbook();

// Import the workbook.
InputStream fileStream = this.getResourceStream("xlsx/ShapeType.xlsx");
workbook.open(fileStream);

IWorksheet sheet = workbook.getWorksheets().get(0);

int row = 2;
int column = 4;
for (int i = 0; i < sheet.getShapes().getCount(); i++) {
    IShape shape = sheet.getShapes().get(i);

    // Add a text on the right side of the shape to display the type of the shape.
    sheet.getRange(row, column).setValue("This is a " + shape.getType().toString() + ".");
    sheet.getRange(row, column).getFont().setBold(true);
    sheet.getRange(row, column).getFont().setSize(13);

    if ((i + 1) % 4 == 0) {
        row = 2;
        column += 6;
    } else {
        row += 10;    }
}

// Save to an excel file.
workbook.save("GetShapeType.xlsx");

The output is shown in the figure below:

image

The following example demonstrates how to iterate through all the shapes in a worksheet, filter out rectangles from the AutoShapes, and output the pixel-level position information (including top, left, height, and width) for each rectangle. Click here to download the worksheet.AutoShapeType.xlsx

// Create a new workbook.
Workbook workbook = new Workbook();

// Import the workbook.
workbook.open("AutoShapeType.xlsx");
IWorksheet sheet = workbook.getWorksheets().get(0);

// Iterate through all shape objects in the worksheet.
for (int i = 0; i < sheet.getShapes().getCount(); i++) {
    IShape shape = sheet.getShapes().get(i);
    if (shape.getType() == ShapeType.AutoShape && shape.getAutoShapeType() == AutoShapeType.Rectangle) {
        // Output the position and size of the rectangle.    
        System.out.println("Top: " + shape.getTop() +
                " , Left: " + shape.getLeft() +
                " , Height: " + shape.getHeight() +
                ", Width: " + shape.getWidth());
    }
}

Shape Adjustment

Apart from changing the size of a shape in DsExcel Java, you can also change the geometry of a shape and modify its appearance. This can be achieved by setting the adjustment values of shapes, such as AutoShapes or Connectors. It allows you to have more control over the shapes in order to create efficient flowcharts, dashboards and reports.

DsExcel Java provides the Adjustments method in the IShape interface to get a collection of adjustment values for the specified AutoShape or Connector.

The valid ranges of adjustent values for different adjustement types are described below:

Adjustment type

Valid values

Linear (horizontal or vertical)

Value 0.0 represents the left or top edge of the shape.

Value 1.0 represents the right or bottom edge of the shape.

For shapes such as connectors and callouts, the values 0.0 and 1.0 correspond to the rectangle defined by the starting and ending points of the connector or callout line.

Values lesser than 0.0 and greater than 1.0 are also valid.

The valid values for the adjustment correspond to the valid adjustments that can be made to shapes in Excel by extending the adjustment points.

For example, if you can only pull an adjustment point half way across the shape in Excel, the maximum value for the corresponding adjustment will be 0.5.

Radial

Value 1.0 represents the shape width. Hence, the maximum value for radial adjustment is 0.5, which is half way across the shape.

Angle

Value is expressed in degrees. If you specify the value outside the range of 180 degree, it will be normalized to be within that range.

In most cases, if a value exceeds the valid range, it is normalized to the closest valid value.

Refer to the following example code to adjust the dimensions of a shape in Excel:

private static void AdjustmentPointForShape() {
    // Initialize workbook
    Workbook workbook = new Workbook();
    // Fetch default worksheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    // Add a right arrow callout
    IShape shape = worksheet.getShapes().addShape(AutoShapeType.RightArrowCallout, 20, 20, 200, 100);

    // Set adjustment points for shapes
    IAdjustments adjustments = shape.getAdjustments();

    // To count adjustment points
    int c = adjustments.getCount();
    System.out.println("Count of Adjustment Values: " + c);

    adjustments.set(0, 0.5);// arrow neck width
    adjustments.set(1, 0.4);// arrow head width
    adjustments.set(2, 0.5);// arrow head height
    adjustments.set(3, 0.6);// text box width

    // Saving workbook to Xlsx
    workbook.save("17-AdjustmentPointForShape.xlsx", SaveFileFormat.Xlsx);

Control Position of Overlapping Shapes

The order of overlapping shapes in a worksheet is decided by their z-order positions. DsExcel Java allows its users to set the z-order of shapes so that their positions can be controlled while creating flow charts or business diagrams etc.

The zOrder method in DsExcel API can be used to move the specified shape in front of or behind the other shapes. It takes ZOrderType enum as a parameter to specify the position of a shape relative to the other shapes.

The getZOrderPosition method of the IShape interface can be used to retrieve the position of a specified shape in the z-order.

Note: If the z-order of a shape is changed, the index of the shape in Worksheet.Shapes collection is also changed.

Refer to the below example code to add various shapes, change their z-order and get their positions in z-order in a worksheet.

// Create a new workbook
Workbook workbook = new Workbook();
// Fetch default worksheet
IWorksheet worksheet = workbook.getActiveSheet();

IShapes shapes = worksheet.getShapes();

// Add shapes
IShape rectangle = shapes.addShape(AutoShapeType.Rectangle, 20, 20, 100, 100);
rectangle.getFill().getColor().setRGB(Color.GetBlue());

IShape oval = shapes.addShape(AutoShapeType.Oval, 50, 50, 100, 100);
oval.getFill().getColor().setRGB(Color.GetGreen());

IShape pentagon = shapes.addShape(AutoShapeType.Pentagon, 80, 80, 100, 100);
pentagon.getFill().getColor().setRGB(Color.GetRed());

IShape triangle = shapes.addShape(AutoShapeType.IsoscelesTriangle, 100, 100, 100, 100);
triangle.getFill().getColor().setRGB(Color.GetOrange());

// Set rectangle above oval
rectangle.zOrder(ZOrderType.BringForward);

// Get position of rectangle in z-order
System.out.println("Z-Order rectangle: " + rectangle.getZOrderPosition());

// Set triangle to bottom
triangle.zOrder(ZOrderType.SendToBack);

// Get position of triangle in z-order
System.out.println("Z-Order triangle: " + triangle.getZOrderPosition());

// Save to an excel file
workbook.save("SetShapeZOrder.xlsx");


See Also

Merge Cells