1Excel::Writer::XLSX::ShUaspeer(3C)ontributed Perl DocumeEnxtcaetli:o:nWriter::XLSX::Shape(3)
2
6 Shape - A class for creating Excel Drawing shapes
7
9 To create a simple Excel file containing shapes using
10 Excel::Writer::XLSX:
11 #!/usr/bin/perl
13 use strict;
15 use warnings;
16 use Excel::Writer::XLSX;
17 my $workbook = Excel::Writer::XLSX->new( 'shape.xlsx' );
19 my $worksheet = $workbook->add_worksheet();
20 # Add a default rectangle shape.
22 my $rect = $workbook->add_shape();
23 # Add an ellipse with centered text.
25 my $ellipse = $workbook->add_shape(
26 type => 'ellipse',
27 text => "Hello\nWorld"
28 );
29 # Add a plus shape.
31 my $plus = $workbook->add_shape( type => 'plus');
32 # Insert the shapes in the worksheet.
34 $worksheet->insert_shape( 'B3', $rect );
35 $worksheet->insert_shape( 'C3', $ellipse );
36 $worksheet->insert_shape( 'D3', $plus );
37
39 The "Excel::Writer::XLSX::Shape" module is used to create Shape objects
40 for Excel::Writer::XLSX.
41 A Shape object is created via the Workbook "add_shape()" method:
43 my $shape_rect = $workbook->add_shape( type => 'rect' );
45 Once the object is created it can be inserted into a worksheet using
47 the "insert_shape()" method:
48 $worksheet->insert_shape('A1', $shape_rect);
50 A Shape can be inserted multiple times if required.
52 $worksheet->insert_shape('A1', $shape_rect);
54 $worksheet->insert_shape('B2', $shape_rect, 20, 30);
55
57 add_shape( %properties )
58 The "add_shape()" Workbook method specifies the properties of the Shape
59 in hash "property => value" format:
60 my $shape = $workbook->add_shape( %properties );
62 The available properties are shown below.
64 insert_shape( $row, $col, $shape, $x, $y, $scale_x, $scale_y )
66 The "insert_shape()" Worksheet method sets the location and scale of
67 the shape object within the worksheet.
68 # Insert the shape into the worksheet.
70 $worksheet->insert_shape( 'E2', $shape );
71 Using the cell location and the $x and $y cell offsets it is possible
73 to position a shape anywhere on the canvas of a worksheet.
74 A more detailed explanation of the "insert_shape()" method is given in
76 the main Excel::Writer::XLSX documentation.
77
79 Any shape property can be queried or modified by the corresponding
80 get/set method:
81 my $ellipse = $workbook->add_shape( %properties );
83 $ellipse->set_type( 'plus' ); # No longer an ellipse!
84 my $type = $ellipse->get_type(); # Find out what it really is.
85 Multiple shape properties may also be modified in one go by using the
87 "set_properties()" method:
88 $shape->set_properties( type => 'ellipse', text => 'Hello' );
90 The properties of a shape object that can be defined via "add_shape()"
92 are shown below.
93 name
95 Defines the name of the shape. This is an optional property and the
96 shape will be given a default name if not supplied. The name is
97 generally only used by Excel Macros to refer to the object.
98 type
100 Defines the type of the object such as "rect", "ellipse" or "triangle":
101 my $ellipse = $workbook->add_shape( type => 'ellipse' );
103 The default type is "rect".
105 The full list of available shapes is shown below.
107 See also the "shapes_all.pl" program in the "examples" directory of the
109 distro. It creates an example workbook with all supported shapes
110 labelled with their shape names.
111 • Basic Shapes
113 blockArc can chevron cube decagon
115 diamond dodecagon donut ellipse funnel
116 gear6 gear9 heart heptagon hexagon
117 homePlate lightningBolt line lineInv moon
118 nonIsoscelesTrapezoid noSmoking octagon parallelogram pentagon
119 pie pieWedge plaque rect round1Rect
120 round2DiagRect round2SameRect roundRect rtTriangle smileyFace
121 snip1Rect snip2DiagRect snip2SameRect snipRoundRect star10
122 star12 star16 star24 star32 star4
123 star5 star6 star7 star8 sun
124 teardrop trapezoid triangle
125 • Arrow Shapes
127 bentArrow bentUpArrow circularArrow curvedDownArrow
129 curvedLeftArrow curvedRightArrow curvedUpArrow downArrow
130 leftArrow leftCircularArrow leftRightArrow leftRightCircularArrow
131 leftRightUpArrow leftUpArrow notchedRightArrow quadArrow
132 rightArrow stripedRightArrow swooshArrow upArrow
133 upDownArrow uturnArrow
134 • Connector Shapes
136 bentConnector2 bentConnector3 bentConnector4
138 bentConnector5 curvedConnector2 curvedConnector3
139 curvedConnector4 curvedConnector5 straightConnector1
140 • Callout Shapes
142 accentBorderCallout1 accentBorderCallout2 accentBorderCallout3
144 accentCallout1 accentCallout2 accentCallout3
145 borderCallout1 borderCallout2 borderCallout3
146 callout1 callout2 callout3
147 cloudCallout downArrowCallout leftArrowCallout
148 leftRightArrowCallout quadArrowCallout rightArrowCallout
149 upArrowCallout upDownArrowCallout wedgeEllipseCallout
150 wedgeRectCallout wedgeRoundRectCallout
151 • Flow Chart Shapes
153 flowChartAlternateProcess flowChartCollate flowChartConnector
155 flowChartDecision flowChartDelay flowChartDisplay
156 flowChartDocument flowChartExtract flowChartInputOutput
157 flowChartInternalStorage flowChartMagneticDisk flowChartMagneticDrum
158 flowChartMagneticTape flowChartManualInput flowChartManualOperation
159 flowChartMerge flowChartMultidocument flowChartOfflineStorage
160 flowChartOffpageConnector flowChartOnlineStorage flowChartOr
161 flowChartPredefinedProcess flowChartPreparation flowChartProcess
162 flowChartPunchedCard flowChartPunchedTape flowChartSort
163 flowChartSummingJunction flowChartTerminator
164 • Action Shapes
166 actionButtonBackPrevious actionButtonBeginning actionButtonBlank
168 actionButtonDocument actionButtonEnd actionButtonForwardNext
169 actionButtonHelp actionButtonHome actionButtonInformation
170 actionButtonMovie actionButtonReturn actionButtonSound
171 • Chart Shapes
173 Not to be confused with Excel Charts.
175 chartPlus chartStar chartX
177 • Math Shapes
179 mathDivide mathEqual mathMinus mathMultiply mathNotEqual mathPlus
181 • Stars and Banners
183 arc bevel bracePair bracketPair chord
185 cloud corner diagStripe doubleWave ellipseRibbon
186 ellipseRibbon2 foldedCorner frame halfFrame horizontalScroll
187 irregularSeal1 irregularSeal2 leftBrace leftBracket leftRightRibbon
188 plus ribbon ribbon2 rightBrace rightBracket
189 verticalScroll wave
190 • Tab Shapes
192 cornerTabs plaqueTabs squareTabs
194 text
196 This property is used to make the shape act like a text box.
197 my $rect = $workbook->add_shape( type => 'rect', text => "Hello\nWorld" );
199 The text is super-imposed over the shape. The text can be wrapped using
201 the newline character "\n".
202 id
204 Identification number for internal identification. This number will be
205 auto-assigned, if not assigned, or if it is a duplicate.
206 format
208 Workbook format for decorating the shape text (font family, size, and
209 decoration).
210 start, start_index
212 Shape indices of the starting point for a connector and the index of
213 the connection. Index numbers are zero-based, start from the top dead
214 centre and are counted clockwise.
215 Indices are typically created for vertices and centre points of shapes.
217 They are the blue connection points that appear when connection shapes
218 are selected manually in Excel.
219 end, end_index
221 Same as above but for end points and end connections.
222 start_side, end_side
224 This is either the letter "b" or "r" for the bottom or right side of
225 the shape to be connected to and from.
226 If the "start", "start_index", and "start_side" parameters are defined
228 for a connection shape, the shape will be auto located and linked to
229 the starting and ending shapes respectively. This can be very useful
230 for flow and organisation charts.
231 flip_h, flip_v
233 Set this value to 1, to flip the shape horizontally and/or vertically.
234 rotation
236 Shape rotation, in degrees, from 0 to 360.
237 line, fill
239 Shape colour for the outline and fill. Colours may be specified as a
240 colour index, or in RGB format, i.e. "AA00FF".
241 See "COLOURS IN EXCEL" in the main documentation for more information.
243 line_type
245 Line type for shape outline. The default is solid. The list of possible
246 values is:
247 dash, sysDot, dashDot, lgDash, lgDashDot, lgDashDotDot, solid
249 valign, align
251 Text alignment within the shape.
252 Vertical alignment can be:
254 Setting Meaning
256 ======= =======
257 t Top
258 ctr Centre
259 b Bottom
260 Horizontal alignment can be:
262 Setting Meaning
264 ======= =======
265 l Left
266 r Right
267 ctr Centre
268 just Justified
269 The default is to centre both horizontally and vertically.
271 scale_x, scale_y
273 Scale factor in x and y dimension, for scaling the shape width and
274 height. The default value is 1.
275 Scaling may be set on the shape object or via "insert_shape()".
277 adjustments
279 Adjustment of shape vertices. Most shapes do not use this. For some
280 shapes, there is a single adjustment to modify the geometry. For
281 instance, the plus shape has one adjustment to control the width of the
282 spokes.
283 Connectors can have a number of adjustments to control the shape
285 routing. Typically, a connector will have 3 to 5 handles for routing
286 the shape. The adjustment is in percent of the distance from the
287 starting shape to the ending shape, alternating between the x and y
288 dimension. Adjustments may be negative, to route the shape away from
289 the endpoint.
290 stencil
292 Shapes work in stencil mode by default. That is, once a shape is
293 inserted, its connection is separated from its master. The master shape
294 may be modified after an instance is inserted, and only subsequent
295 insertions will show the modifications.
296 This is helpful for Org charts, where an employee shape may be created
298 once, and then the text of the shape is modified for each employee.
299 The "insert_shape()" method returns a reference to the inserted shape
301 (the child).
302 Stencil mode can be turned off, allowing for shape(s) to be modified
304 after insertion. In this case the "insert_shape()" method returns a
305 reference to the inserted shape (the master). This is not very useful
306 for inserting multiple shapes, since the x/y coordinates also gets
307 modified.
308
310 Use "$worksheet->hide_gridlines(2)" to prepare a blank canvas without
311 gridlines.
312 Shapes do not need to fit on one page. Excel will split a large drawing
314 into multiple pages if required. Use the page break preview to show
315 page boundaries superimposed on the drawing.
316 Connected shapes will auto-locate in Excel if you move either the
318 starting shape or the ending shape separately. However, if you select
319 both shapes (lasso or control-click), the connector will move with it,
320 and the shape adjustments will not re-calculate.
321
323 #!/usr/bin/perl
324 use strict;
326 use warnings;
327 use Excel::Writer::XLSX;
328 my $workbook = Excel::Writer::XLSX->new( 'shape.xlsx' );
330 my $worksheet = $workbook->add_worksheet();
331 # Add a default rectangle shape.
333 my $rect = $workbook->add_shape();
334 # Add an ellipse with centered text.
336 my $ellipse = $workbook->add_shape(
337 type => 'ellipse',
338 text => "Hello\nWorld"
339 );
340 # Add a plus shape.
342 my $plus = $workbook->add_shape( type => 'plus');
343 # Insert the shapes in the worksheet.
345 $worksheet->insert_shape( 'B3', $rect );
346 $worksheet->insert_shape( 'C3', $ellipse );
347 $worksheet->insert_shape( 'D3', $plus );
348 See also the "shapes_*.pl" program in the "examples" directory of the
350 distro.
351
353 • Add shapes which have custom geometries.
354 • Provide better integration of workbook formats for shapes.
356 • Add further validation of shape properties to prevent creation of
358 workbooks that will not open.
359 • Auto connect shapes that are not anchored to cell A1.
361 • Add automatic shape connection to shape vertices besides the object
363 centre.
364 • Improve automatic shape connection to shapes with concave sides
366 (e.g. chevron).
367
369 Dave Clarke dclarke@cpan.org
370
372 (c) MM-MMXX, John McNamara.
373 All Rights Reserved. This module is free software. It may be used,
375 redistributed and/or modified under the same terms as Perl itself.
376perl v5.32.1 2021-01-27 Excel::Writer::XLSX::Shape(3)