Article by Sheela E.N, Sonata Software Limited
This article explains the DrawingML implementation in Spreadsheet and how a drawing object is stored in SpreadsheetML using DrawingML. When an image/picture is inserted in a Spreadsheet document, DrawingML specifies the location and appearance of drawing elements in the package.
For this article we will use Microsoft Excel 2007 to insert a picture and and explain the DrawingML markup that is generated. We will essentially be using Excel as a time-saving IDE for creating the necessary DrawingML markup.
Open Microsoft Excel 2007 and insert a picture on the worksheet. The inserted picture has been sized to extend from cell B2 (top left) to cell G16 (lower right).
The height and width of the picture can be set by right clicking the picture and select the size and property.
To know the position of the picture on the worksheet, unzip the saved XLSX file and extract the corresponding drawing file (by default its name will be drawing1.xml) after resolving the relationship from the sheet relationship part.
The xml snippet of the drawing1.xml will be as follows –
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
<xdr:twoCellAnchor editAs="oneCell">
<xdr:from>
<xdr:col>2</xdr:col>
<xdr:colOff>9525</xdr:colOff>
<xdr:row>3</xdr:row>
<xdr:rowOff>28575</xdr:rowOff>
</xdr:from>
<xdr:to>
<xdr:col>4</xdr:col>
<xdr:colOff>57150</xdr:colOff>
<xdr:row>6</xdr:row>
<xdr:rowOff>95250</xdr:rowOff>
</xdr:to>
<xdr:pic>
<xdr:nvPicPr>
<xdr:cNvPr id="1025" name="Picture 1" descr="logo"/>
<xdr:cNvPicPr>
<a:picLocks noChangeAspect="1" noChangeArrowheads="1"/>
</xdr:cNvPicPr>
</xdr:nvPicPr>
<xdr:blipFill>
<a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:embed="rId1"/>
<a:srcRect/>
<a:stretch>
<a:fillRect/>
</a:stretch>
</xdr:blipFill>
<xdr:spPr bwMode="auto">
<a:xfrm>
<a:off x="1228725" y="514350"/>
<a:ext cx="1266825" cy="552450"/>
</a:xfrm>
<a:prstGeom prst="rect">
<a:avLst/>
</a:prstGeom>
<a:noFill/>
</xdr:spPr>
</xdr:pic>
<xdr:clientData/>
</xdr:twoCellAnchor>
</xdr:wsDr>
In the XML fragment of –
<xdr:wsDr xmlns:xdr=http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing…>
<xdr:twoCellAnchor editAs="oneCell">
The attribute “editAs” specifies how the drawing object is moved/resized when the rows and columns are resized. For the possible values and their details, refer to page 4765 of the ECMA Open XML specification document.
The details about the location (start and end coordinates) for the picture is specified by the <xdr:from> and <xdr:to> elements and are explained as below –
<xdr:from>
<xdr:col>2</xdr:col>
<xdr:colOff>9525</xdr:colOff>
<xdr:row>3</xdr:row>
<xdr:rowOff>28575</xdr:rowOff>
</xdr:from>
<xdr:from> element specifies the first anchor point for the drawing element. This will be used to anchor the top and left sides of the shape within the spreadsheet.
<xdr:col> and <xdr:row> elements mentions the starting x and y co-ordinates for the picture/image respectively.
<xdr:colOff> and <xdr:rowOff> elements determines the actual placement of the drawing within the spreadsheet along with row and column information.
Similarly the end anchor points for the drawing/image/picture is specified by <xdr:to> element.
<xdr:to>
<xdr:col>4</xdr:col>
<xdr:colOff>57150</xdr:colOff>
<xdr:row>6</xdr:row>
<xdr:rowOff>95250</xdr:rowOff>
</xdr:to>
The picture details are located under the <xdr:pic> element as –
Picture name and description properties are under the <xdr:nvPicPr> element.
<xdr:nvPicPr>
<xdr:cNvPr id="1025" name="Picture 1" descr="logo"/>
<xdr:cNvPicPr>
<a:picLocks noChangeAspect="1" noChangeArrowheads="1"/>
</xdr:cNvPicPr>
</xdr:nvPicPr>
Picture relationship ID and the reference from the physical location in the package as –
<xdr:blipFill>
<a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:embed="rId1"/>
<a:srcRect/>
<a:stretch>
<a:fillRect/>
</a:stretch>
</xdr:blipFill>
rId1 refers to the embedded picture placed on the worksheet.
If the picture is cropped, these details are stored in the <a:srcRect/> element –
<a:srcRect l="19775" t="1196" r="899" b="13156" /> within <xdr:blipFill> element, where l=left, t=top, r=right and b=bottom
The type of preset shape used for the picture is specified in the <xdr:spPr> element as –
<xdr:spPr bwMode="auto">
<a:xfrm>
<a:off x="1228725" y="514350"/>
<a:ext cx="1266825" cy="552450"/>
</a:xfrm>
<a:prstGeom prst="rect">
<a:avLst/>
</a:prstGeom>
<a:noFill/>
</xdr:spPr>
For more information about the possible values of the prst attribute of <a:prstGeom>, refer to page 3774 of the Ecma Open XML specification document.