wordpress hit counter
DrawingML implementation for Image/Picture in SpreadsheetML - OpenXML Developer - Blog - OpenXML Developer
Goodbye and Hello

OpenXmlDeveloper.org is Shutting Down

There is a time for all good things to come to an end, and the time has come to shut down OpenXmlDeveloper.org.

Screen-casts and blog posts: Content on OpenXmlDeveloper.org will be moving to EricWhite.com.

Forums: We are moving the forums to EricWhite.com and StackOverflow.com. Please do not post in the forums on OpenXmlDeveloper.org. Instead, please post in the forums at EricWhite.com or at StackOverflow.com.

Please see this blog post for more information about my plans moving forward.  Cheers, Eric

DrawingML implementation for Image/Picture in SpreadsheetML

DrawingML implementation for Image/Picture in SpreadsheetML

Rate This
  • Comments 9

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.

Page 1 of 1 (9 items)