Shape - A class for creating Excel Drawing shapes
To create a simple Excel file containing shapes using Excel::Writer::XLSX:
#!/usr/bin/perl use strict; use warnings; use Excel::Writer::XLSX; my $workbook = Excel::Writer::XLSX->new( 'shape.xlsx' ); my $worksheet = $workbook->add_worksheet(); # Add a default rectangle shape. my $rect = $workbook->add_shape(); # Add an ellipse with centered text. my $ellipse = $workbook->add_shape( type => 'ellipse', text => "Hello\nWorld" ); # Add a cross shape. my $cross = $workbook->add_shape( type => 'cross'); # Insert the shapes in the worksheet. $worksheet->insert_shape( 'A1', $rect ); $worksheet->insert_shape( 'B2', $ellipse ); $worksheet->insert_shape( 'C3', $cross );
Excel::Writer::XLSX::Shape module is used to create Shape objects for Excel::Writer::XLSX.
A Shape object is created via the Workbook
my $shape_rect = $workbook->add_shape( type => 'rect' );
Once the object is created it can be inserted into a worksheet using the
A Shape can be inserted multiple times if required.
$worksheet->insert_shape('A1', $shape_rect); $worksheet->insert_shape('B2', $shape_rect, 20, 30);
add_shape() Workbook method specifies the properties of the Shape in hash
property => value format:
my $shape = $workbook->add_shape( %properties );
The available properties are shown below.
insert_shape() Worksheet method sets the location and scale of the shape object within the worksheet.
# Insert the shape into the a worksheet. $worksheet->insert_shape( 'E2', $shape );
Using the cell location and the
$y cell offsets it is possible to position a shape anywhere on the canvas of a worksheet.
A more detailed explanation of the
insert_shape() method is given in the main Excel::Writer::XLSX documentation.
Any shape property can be queried or modified by the corresponding get/set method:
my $ellipse = $workbook->add_shape( %properties ); $ellipse->set_type( 'cross' ); # No longer an ellipse! my $type = $ellipse->get_type(); # Find out what it really is.
Multiple shape properties may also be modified in one go by using the
$shape->set_properties( type => 'ellipse', text => 'Hello' );
The properties of a shape object that can be defined via
add_shape() are shown below.
Defines the name of the shape. This is an optional property and the shape will be given a default name if not supplied. The name is generally only used by Excel Macros to refer to the object.
Defines the type of the object such as
my $ellipse = $workbook->add_shape( type => 'ellipse' );
The default type is
The full list of available shapes is shown below.
See also the
shapes_all.pl program in the
examples directory of the distro. It creates an example workbook with all supported shapes labelled with their shape names.
blockArc can chevron cube decagon diamond dodecagon donut ellipse funnel gear6 gear9 heart heptagon hexagon homePlate lightningBolt line lineInv moon nonIsoscelesTrapezoid noSmoking octagon parallelogram pentagon pie pieWedge plaque rect round1Rect round2DiagRect round2SameRect roundRect rtTriangle smileyFace snip1Rect snip2DiagRect snip2SameRect snipRoundRect star10 star12 star16 star24 star32 star4 star5 star6 star7 star8 sun teardrop trapezoid triangle
bentArrow bentUpArrow circularArrow curvedDownArrow curvedLeftArrow curvedRightArrow curvedUpArrow downArrow leftArrow leftCircularArrow leftRightArrow leftRightCircularArrow leftRightUpArrow leftUpArrow notchedRightArrow quadArrow rightArrow stripedRightArrow swooshArrow upArrow upDownArrow uturnArrow
bentConnector2 bentConnector3 bentConnector4 bentConnector5 curvedConnector2 curvedConnector3 curvedConnector4 curvedConnector5 straightConnector1
accentBorderCallout1 accentBorderCallout2 accentBorderCallout3 accentCallout1 accentCallout2 accentCallout3 borderCallout1 borderCallout2 borderCallout3 callout1 callout2 callout3 cloudCallout downArrowCallout leftArrowCallout leftRightArrowCallout quadArrowCallout rightArrowCallout upArrowCallout upDownArrowCallout wedgeEllipseCallout wedgeRectCallout wedgeRoundRectCallout
flowChartAlternateProcess flowChartCollate flowChartConnector flowChartDecision flowChartDelay flowChartDisplay flowChartDocument flowChartExtract flowChartInputOutput flowChartInternalStorage flowChartMagneticDisk flowChartMagneticDrum flowChartMagneticTape flowChartManualInput flowChartManualOperation flowChartMerge flowChartMultidocument flowChartOfflineStorage flowChartOffpageConnector flowChartOnlineStorage flowChartOr flowChartPredefinedProcess flowChartPreparation flowChartProcess flowChartPunchedCard flowChartPunchedTape flowChartSort flowChartSummingJunction flowChartTerminator
actionButtonBackPrevious actionButtonBeginning actionButtonBlank actionButtonDocument actionButtonEnd actionButtonForwardNext actionButtonHelp actionButtonHome actionButtonInformation actionButtonMovie actionButtonReturn actionButtonSound
Not to be confused with Excel Charts.
chartPlus chartStar chartX
mathDivide mathEqual mathMinus mathMultiply mathNotEqual mathPlus
arc bevel bracePair bracketPair chord cloud corner diagStripe doubleWave ellipseRibbon ellipseRibbon2 foldedCorner frame halfFrame horizontalScroll irregularSeal1 irregularSeal2 leftBrace leftBracket leftRightRibbon plus ribbon ribbon2 rightBrace rightBracket verticalScroll wave
cornerTabs plaqueTabs squareTabs
This property is used to make the shape act like a text box.
my $rect = $workbook->add_shape( type => 'rect', text => "Hello\nWorld" );
The text is super-imposed over the shape. The text can be wrapped using the newline character
Identification number for internal identification. This number will be auto-assigned, if not assigned, or if it is a duplicate.
Workbook format for decorating the shape text (font family, size, and decoration).
Shape indices of the starting point for a connector and the index of the connection. Index numbers are zero-based, start from the top dead centre and are counted clockwise.
Indices are are typically created for vertices and centre points of shapes. They are the blue connection points that appear when connection shapes are selected manually in Excel.
Same as above but for end points and end connections.
This is either the letter
r for the bottom or right side of the shape to be connected to and from.
start_side parameters are defined for a connection shape, the shape will be auto located and linked to the starting and ending shapes respectively. This can be very useful for flow and organisation charts.
Set this value to 1, to flip the shape horizontally and/or vertically.
Shape rotation, in degrees, from 0 to 360.
Shape color for the outline and fill. Colours may be specified as a color index, or in RGB format, i.e.
COLOURS IN EXCEL in the main documentation for more information.
Line type for shape outline. The default is solid. The list of possible values is:
dash, sysDot, dashDot, lgDash, lgDashDot, lgDashDotDot, solid
Text alignment within the shape.
Vertical alignment can be:
Setting Meaning ======= ======= t Top ctr Centre b Bottom
Horizontal alignment can be:
Setting Meaning ======= ======= l Left r Right ctr Centre just Justified
The default is to centre both horizontally and vertically.
Scale factor in x and y dimension, for scaling the shape width and height. The default value is 1.
Scaling may be set on the shape object or via
Adjustment of shape vertices. Most shapes do not use this. For some shapes, there is a single adjustment to modify the geometry. For instance, the plus shape has one adjustment to control the width of the spokes.
Connectors can have a number of adjustments to control the shape routing. Typically, a connector will have 3 to 5 handles for routing the shape. The adjustment is in percent of the distance from the starting shape to the ending shape, alternating between the x and y dimension. Adjustments may be negative, to route the shape away from the endpoint.
Shapes work in stencil mode by default. That is, once a shape is inserted, its connection is separated from its master. The master shape may be modified after an instance is inserted, and only subsequent insertions will show the modifications.
This is helpful for Org charts, where an employee shape may be created once, and then the text of the shape is modified for each employee.
insert_shape() method returns a reference to the inserted shape (the child).
Stencil mode can be turned off, allowing for shape(s) to be modified after insertion. In this case the
insert_shape() method returns a reference to the inserted shape (the master). This is not very useful for inserting multiple shapes, since the x/y coordinates also gets modified.
worksheet->hide_gridlines(2) to prepare a blank canvas without gridlines.
Shapes do not need to fit on one page. Excel will split a large drawing into multiple pages if required. Use the page break preview to show page boundaries superimposed on the drawing.
Connected shapes will auto-locate in Excel if you move either the starting shape or the ending shape separately. However, if you select both shapes (lasso or control-click), the connector will move with it, and the shape adjustments will not re-calculate.
#!/usr/bin/perl use strict; use warnings; use Excel::Writer::XLSX; my $workbook = Excel::Writer::XLSX->new( 'shape.xlsx' ); my $worksheet = $workbook->add_worksheet(); # Add a default rectangle shape. my $rect = $workbook->add_shape(); # Add an ellipse with centered text. my $ellipse = $workbook->add_shape( type => 'ellipse', text => "Hello\nWorld" ); # Add a cross with a user-defined id. my $cross = $workbook->add_shape( type => 'cross', id => 33 ); # Insert the shapes in the worksheet. $worksheet->insert_shape( 'A1', $rect ); $worksheet->insert_shape( 'B2', $ellipse ); $worksheet->insert_shape( 'C3', $cross );
See also the
shapes_*.pl program in the
examples directory of the distro.
Dave Clarke email@example.com
© MM-MMXII, John McNamara.
All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.