How to Find Answers in Open XML
In this screen-cast, I am trying something a little different. I wanted to show the process of sifting through Open XML to see how to create the content you want. I see a lot of forum questions that are essentially, “How do I … in Open XML?” This is meant to show my process of answering any question like that. I have made only a couple of edits to this video, so you will see my process pretty much in real time answering the specific question, “How do I position my image at a particular cell in a spreadsheet?” Even though the question is specific, the techniques are not. I also chose this question because I did not know anything about images in a spreadsheet before I recorded the screen-cast.
This screen-cast does not contain any code to create the XML. I also skipped over the math for time’s sake. See below for some detailed explanation of the EMUs mentioned.
Here is the EMU math. The width of the image spans two columns plus part of a third. Each column is 80 pixels. The height spans 10 rows, plus a very small part of the next row. The full width and height of the image is 1531620 by 1841591. The offsets into the extra column and row are 312420 and 12791. If I subtract the offsets from the fill width and height, I get 1219200 by 1828800. For the width, I divide 1219200 by 2 and get 609600 per column. Divide that by 80 pixels and I get 7620 EMU per pixel. Doing the same for the rows also results in 7620 EMU per pixel.
Now I can detail how to set all of these values for your particular image. Let’s say I have an image that is 336 by 404 pixels. If I want it full size, I can multiply both by 7620 to get the EMUs for that image. If I want it smaller, I can multiply by a smaller number of EMUs to get a proportionally smaller image. My resized image was 1531620 by 1841591. Now I can set the xdr:from elements to position the top-left corner of the image. The xdr:to elements can be calculated by stepping through the column widths and heights from the starting point, multiplying the pixels by 7620 and then subtracting from the total width or height to find the last partial column or row. The remainder of the EMUs then becomes the offsets.
It looks like the a:xfrm element may not be necessary, but it can be calculated easily enough. The a:off values can be calculated by adding up the EMUs for the columns and rows that are to the left and above the starting point of the image. The a:ext is simply the total width and height of the image
This detailed math can be a bit tedious, but it is usually not that hard to work out once you get all the information.
Answering questions like these really can go quite quickly. I hope this screen-cast inspires you to dig in and find what you need.