Appending images

There's some black magic happening within excel. There's also only one way which the Engine currently supports.

I've stumbled upon some nagging on OpenXML forum about using drawings instead of embedding, but the way which I developed this in the beginning was by actually appending the images into excel first, reverse-engineering it later.

This way is Excel 2013 approved ^TM

Essential keywords:

• a drawing is a XML file containing all images & drawing shapes within the respective sheet, therefore
• there's one drawing per one sheet
• media folder contains actual pictures referenced from the drawings
• therefore the coupling is as follows: sheet -> drawing -> actual image file
• all coupling is done by the engine automatically. You can recheck using _rels files.
• positions are written in a terms of:
  • xdr:from, which contains row & column number
  • xdr:from, which also contains some weird offset number
  • xdr:to - essentially identical to xdr:from except that it point to where the picture ends
  • xdr:spPr which contains x and y position of the image within the sheet

The Engine API

First, forget about drawing.xml file. Picture generation itself is quite complex matter in OpenXML, therefore the API expects following nodes within images parameter:

<image>
    <path>blank_image</path>
    <ext>jpg</ext>
    <mime>image/jpeg</mime>
    <usedIn>
        <sheet nr="1" />
    </usedIn>
    <xdr:from>
      <!-- something -->
    </xdr:from>
    <xdr:to>
      <!-- something -->
    </xdr:to>
    <xdr:spPr>
      <!-- something -->
    </xdr:spPr>
</image>

Let's walk through the <image> node:

• path is name of the file without extension
• ext is extension of the file
• usedIn contains <sheet nr="X">, where this particular image is used. Again, transliteration is done in the background.
  • the @nr is integer position() of respective <worksheet> tag from sheetContents parameter. Therefore, it starts with 1 and works it's way up for multi-sheet excels.
• xdr:from, xdr:to, xdr:spPr nodes should be copypasted from source drawing.xml file

xdr: & a: namespace dtd is:

xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"

Extension is required as separate element, because excel does not know mime/types. The API on does the extraction of mime types & respective extension on the background.

Now, suppose you work with one image - then it's fairly simple. You just open the drawing.xml file from the template excel & copy it's parameters into the <image> tag.

However, if you are preparing grid of images ( hint: stickers ), the best practice for us was following:

1. Create file "x.xls", where you populate one row of the images - effectively having only the x axis values changed.
2. do the same with "y.xls" file, where you populate just one column - again, effectively having only one axis values changed
3. count the increments and based on the position(), modulos and some advanced math do the final calculation.

Do not get scared by huge numbers in offsets and X/Y positions. Nobody knows which dimensions we're working with. Gandalf approved.

Ultimately, it's all about the individual case and the individual developer. All we tried to simplify is appending images into drawing files, positioning them within the excel file & having it all just clear within the main file.

If you're wondering how the actual binary image gets it's way to final excel, look under the hood

Issues to watch for

• Always check that xdr:from is lower than xdr:to parameters.
• If you don't see pictures in the generated sheet, check the media folder that they're there.
• If they're there, check the dimensions
  1. create new worksheet, append one image and steal the xdr:from, xdr:to and xdr:sPr values from there.
  2. put those values into your XSL file
  3. generate excel. If images show up, you have a problem with dimensions & offset.
  4. therefore, recheck generated drawingX.xml against the drawingX.xml from the original template xlsx file. You still haven't deleted it, right?

Navigation

1. Preparing excel files
2. Preparing the first output
3. Putting plain data into excel sheet
4. Appending images
5. Creating multiple worksheets
6. Printer settings
7. Macro-enabled workbooks
8. Under the hood
9. Engine parameters documentation
10. Best practice for writing Excel XML
11. Authors, license, contributing