Placing image tag – SpreadsheetML

[vkselvaaParticipant]

Is there any way to insert image using SpreadsheetML.

tblinvoicetemp += ‘<ss:Row ss:Height=”100″><ss:Cell><ss:Data ss:Type=”String” xmlns=”http://www.w3.org/TR/REC-html40″>FMF Racing</ss:Data></ss:Cell></ss:Row>’;

Must appreciate your help!!

• This topic was modified 7 years, 7 months ago by  vkselvaa.

[Jim SnyderParticipant]

There is. Vincent Tam wrote a book on OpenXML and this is a quote from the book. I have not done it so cannot help directly, but he has several chapters on working with images:

If you’ve done the trick on inserting an image into an Excel file, saving it as Open XML format, renaming to .zip, and unzipping that file, you might find this in the /xl/drawings/drawing1.xml file: -<xdr:twoCellAnchor editAs=”oneCell”> – <xdr:from> <xdr:col>0</xdr:col> <xdr:colOff>0</xdr:colOff> <xdr:row>0</xdr:row> <xdr:rowOff>0</xdr:rowOff> </xdr:from> – <xdr:to> <xdr:col>6</xdr:col> <xdr:colOff>248195</xdr:colOff> <xdr:row>7</xdr:row> <xdr:rowOff>152608</xdr:rowOff> </xdr:to>
This is for positioning the image. I’m using the AbsoluteAnchor instead of the TwoCellAnchor. After much hairtearing, I’ve decided the TwoCellAnchor class is too hard to use.

[Jim SnyderParticipant]

Trying that again with some post formatting:

If you’ve done the trick on inserting an image into an Excel file, saving it as Open XML format, renaming to .zip, and unzipping that file, you might find this in the /xl/drawings/drawing1.xml file:
-<xdr:twoCellAnchor editAs=”oneCell”>
– <xdr:from>
<xdr:col>0</xdr:col>
<xdr:colOff>0</xdr:colOff>
<xdr:row>0</xdr:row>
<xdr:rowOff>0</xdr:rowOff>
</xdr:from>
– <xdr:to>
<xdr:col>6</xdr:col>
<xdr:colOff>248195</xdr:colOff>
<xdr:row>7</xdr:row>
<xdr:rowOff>152608</xdr:rowOff>
</xdr:to>

This is for positioning the image. I’m using the AbsoluteAnchor instead of the TwoCellAnchor. After much hairtearing, I’ve decided the TwoCellAnchor class is too hard to use.

[vkselvaaParticipant]

Thanks Mate,
I am a SharePoint Developer and not much more knowledge in SpreadsheetML.

My scenario is to create multiple sheets excel file(Export to Excel) with company logo using JavaScript. So please assist where i put the above scripts?

var uri = ‘data:application/vnd.ms-excel;base64,’;
var strstate = ‘<?xml version=”1.0″?>’
//+'<ss:Workbook xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”>’
+ ‘<ss:Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet” xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet” xmlns:x=”urn:schemas-microsoft-com:office:excel” xmlns:o=”urn:schemas-microsoft-com:office:office” xmlns:html=”http://www.w3.org/TR/REC-html40″>’ + ‘<ss:Styles><Style ss:Name=”Normal” ss:ID=”Default”>’ + ‘<Alignment ss:Vertical=”Bottom” />’ + ‘<Borders />’ + ‘<Font />’ + ‘<Interior />’ + ‘<NumberFormat />’ + ‘<Protection />’ + ‘</Style>’ + ‘<ss:Style ss:ID=”1″><Interior ss:Color=”#FFFFFF” ss:Pattern=”Solid”></Interior></ss:Style>’ + ‘<ss:Style ss:ID=”2″><ss:Font ss:Bold=”1″ ss:Size=”11″ ss:Color=”black”/></ss:Style>’ + ‘<ss:Style ss:ID=”s53″><Interior ss:Color=”#FFFF00″ ss:Pattern=”Solid”></Interior></ss:Style>’ + ‘<ss:Style ss:ID=”n2″><ss:Font ss:Size=”11″ ss:Color=”black”/>’
// +'<Borders><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”2″ />’
// +'<Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”2″ />’
// +'<Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”2″ /></Borders>’
+ ‘<Interior ss:Color=”#FFFFFF” ss:Pattern=”Solid”></Interior> </ss:Style>’ + ‘<ss:Style ss:ID=”3″><ss:Font ss:Bold=”1″ ss:Size=”11″ ss:Color=”black”/></ss:Style>’ + ‘<ss:Style ss:ID=”4″><ss:Font ss:Bold=”1″ ss:Size=”11″ ss:Color=”red”/></ss:Style>’ + ‘<ss:Style ss:ID=”n4″><ss:Font ss:Size=”11″ ss:Color=”red”/></ss:Style>’ + ‘<ss:Style ss:ID=”5″><ss:Font ss:Size=”11″ ss:Color=”black”/></ss:Style>’ + ‘<ss:Style ss:ID=”b5″><ss:Font ss:Bold=”1″ ss:Size=”14″ ss:Color=”black”/></ss:Style>’

+'<ss:Style ss:ID=”6″><ss:Font ss:Bold=”1″ ss:Size=”11″ ss:Color=”blue”/></ss:Style>’ + ‘</ss:Styles>’;

var tblinvoicetemp = ‘<ss:Worksheet ss:Name=”Invoice”>’;

//tblinvoicetemp += ‘<ss:Table><ss:Row><ss:Cell><ss:Data ss:Type=”String”>First Name</ss:Data></ss:Cell><ss:Cell><ss:Data ss:Type=”String”>Last Name</ss:Data>’;
//tblinvoicetemp += ‘</ss:Cell><ss:Cell><ss:Data ss:Type=”String”>Phone Number</ss:Data></ss:Cell></ss:Row></ss:Table>’;

tblinvoicetemp += ‘<ss:Table ss:StyleID=”1″><ss:Column ss:Width=”10″/><ss:Column ss:Width=”300″/><ss:Column ss:Width=”300″/><ss:Column ss:Width=”300″/>’;

[Jim SnyderParticipant]

The key is in the first paragraph. Manually make a spreadsheet with the images where you want OpenXML to place them. Then the following steps enable you to see the XML Excel used to put them there:

If you’ve done the trick on inserting an image into an Excel file, saving it as Open XML format, renaming to .zip, and unzipping that file, you might find this in the /xl/drawings/drawing1.xml file:

When you write the script for OpenXML, the main thing that threw me for a while was the realization that order is important, matching the XML order of tags to what you are building or editing.

[Jim SnyderParticipant]

Also, I use C# to write the OpenXML and cannot help much with the JavaScript. The main thing to understand is that you are working your way inward from the SpreadsheetDocument object. It contains a Workbookpart object with at least two classes embedded. They are the Workbook which contains Sheet objects and the Worksheetpart that ties Worksheet objects to SheetData objects. Another Vincent Tam excerpt:

The “behind-the-scenes” explanation is that you need at least 2 classes, Worksheet class and Workbook class.

The Worksheet class requires at least the SheetData class as a child (even if empty).

The Workbook class requires at least the Sheets class as a child, which in turn requires at least one Sheet class as a child (thus grandchild of Workbook).

If you explore the resulting spreadsheet file, you’ll find the corresponding worksheet.xml and workbook.xml files. They are tied together with the relationship ID of the WorksheetPart class (assigned to the Sheet.Id property).

Just understand that underneath all this, you’re really just working with XML files and XML tags. The Open XML SDK just abstracts that away for you.

If you have downloaded the OpenXML SDK 2.5 toolkit (https://www.microsoft.com/en-us/download/details.aspx?id=30425), you can read the documentation within to help you specifically with the XML.

Also, “Open XML The markup explained” by Wouter van Vugt explains things at the SpreadsheetML level which may be helpful.

[Eric WhiteKeymaster]

Also, take a look at this screen-cast:

Precisely Placing Images in an Open XML Spreadsheet

[vkselvaaParticipant]

Thanks Eric and Jim.

Client required to export html tables to multiple sheets excel file(like Export to Excel), In which i want to insert company logo while exporting. Is this possible or not?

As per my understanding, Your solution read the excel file and place the image on it.

[Jim SnyderParticipant]

That is correct. However, you can do things just in XML if you understand what is going on in detail. I would consider that painful to do. Another pair of solutions without OpenXML or XML hacking would be either VBA or Interop.

In VBA, you could make an external XLSM that would open the file and insert the image. In Interop, you would read in the file and insert the image.

My company network does not allow this, but ADO.NET can treat the spreadsheet like a database and insert the image without reading the Excel. I would consider this as the cleanest approach for what you are trying to do.

[vkselvaaParticipant]

Thanks Jim.

[Jim SnyderParticipant]

Thank you!

[Author]

Posts