none
Create an Excel Spreadsheet with an image on the First Header with Open Xml RRS feed

  • Question

  • Hello,

    I have been trying to make a supposedly simple task of adding an image to the left of the First Header in an Excel Document, but i can't seem to accomplish it.

    I have seen a few examples of inserting images but all of them are to insert an image to cells, i also tried to use Code Reflector from the OpenXML SDK Tools, but no clue in making it work. I have been able to insert some text on the header but i have no clue on how to do the same with an image. What i'm looking to acomplish is to have an image on the left side of the First Header and a Title on the right side of the header.

    Can someone please shed some light on this with an actual working example?

    This is the code i have working to create the excel Document with data from a DataTable

    private MemoryStream CreateSummaryExcelDoc(DataTable dataTable)
            {
                MemoryStream s = new MemoryStream();
    
                //Create excel document
                using (SpreadsheetDocument excelDocument =
                    SpreadsheetDocument.Create(s, SpreadsheetDocumentType.Workbook, true))
                {
    
                    var workbookPart = excelDocument.AddWorkbookPart();
                    excelDocument.WorkbookPart.Workbook = new Workbook();
                    excelDocument.WorkbookPart.Workbook.Sheets = new Sheets();
    
                    var sheetPart = excelDocument.WorkbookPart.AddNewPart<WorksheetPart>();
    
                    var sheetData = new SheetData();
                    sheetPart.Worksheet = new Worksheet(sheetData);
                    Worksheet ws = sheetPart.Worksheet;
    
                    // Header Text to Insert
                    var textToInsert = "&R&B&18My Header Text"; // &R-Right Section f the Header &B- Bold &18-Font Size
                    // Insert text to FirstHeader
                    InsertHeaderFooter(ws, HeaderType.FirstHeader, textToInsert);
    
                    Sheets sheets = excelDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();
    
                    string relationshipId = excelDocument.WorkbookPart.GetIdOfPart(sheetPart);
                    // Create My Sheet
                    Sheet sheet = new Sheet() { Id = relationshipId, SheetId = 1, Name = "My Sheet" };
                    sheets.Append(sheet);
    
                    // Table Header Row
                    Row headerRow = new Row();
                    // Insert the columns 
                    List<String> columns = new List<string>();
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        columns.Add(column.ColumnName);
    
                        Cell cell = new Cell();
                        cell.DataType = CellValues.String; 
                        cell.CellValue = new CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }
    
                    sheetData.AppendChild(headerRow);
    
                    // Insert data
                    foreach (DataRow dsrow in dataTable.Rows)
                    {
                        Row newRow = new Row();
                        foreach (String col in columns)
                        {
                            Cell cell = new Cell();
                            cell.DataType = CellValues.String; 
                            cell.CellValue = new CellValue(dsrow[col].ToString()); 
                            newRow.AppendChild(cell);
                        }
    
                        sheetData.AppendChild(newRow);
                    }
                }
    
                return s;
            }

    And this is function to add text to an Header.

    private static void InsertHeaderFooter(Worksheet ws, HeaderType type, String textToInsert)
            {           
                HeaderFooter hf = ws.Descendants<HeaderFooter>().FirstOrDefault();
                if (hf == null)
                {
                    hf = new HeaderFooter();
                    ws.AppendChild<HeaderFooter>(hf);
                }
    
                // The HeaderFooter node should be there, at this point!
                if (hf != null)
                {
                    // You've found the node. Now add the header or footer.
                    // Deal with the attributes first:
                    switch (type)
                    {
                        case HeaderType.EvenHeader:
                        case HeaderType.EvenFooter:
                        case HeaderType.OddHeader:
                        case HeaderType.OddFooter:
                            // Even or odd only? Add a differentOddEven attribute and set 
                            // it to "1".
                            hf.DifferentOddEven = true;
                            break;
    
                        case HeaderType.FirstFooter:
                        case HeaderType.FirstHeader:
                            hf.DifferentFirst = true;
                            break;
                    }
    
                    switch (type)
                    {
                        // This code creates new header elements, even if they
                        // already exist. Either way, you end up with a 
                        // "fresh" element.
                        case HeaderType.AllHeader:
                            hf.EvenHeader = new EvenHeader();
                            hf.EvenHeader.Text = textToInsert;
    
                            hf.OddHeader = new OddHeader();
                            hf.OddHeader.Text = textToInsert;
                            break;
    
                        case HeaderType.AllFooter:
                            hf.EvenFooter = new EvenFooter();
                            hf.EvenFooter.Text = textToInsert;
    
                            hf.OddFooter = new OddFooter();
                            hf.OddFooter.Text = textToInsert;
                            break;
    
                        case HeaderType.EvenFooter:
                            hf.EvenFooter = new EvenFooter();
                            hf.EvenFooter.Text = textToInsert;
                            break;
    
                        case HeaderType.EvenHeader:
                            hf.EvenHeader = new EvenHeader();
                            hf.EvenHeader.Text = textToInsert;
                            break;
    
                        case HeaderType.OddFooter:
                            hf.OddFooter = new OddFooter();
                            hf.OddFooter.Text = textToInsert;
                            break;
    
                        case HeaderType.OddHeader:
                            hf.OddHeader = new OddHeader();
                            hf.OddHeader.Text = textToInsert;
                            break;
    
                        case HeaderType.FirstHeader:
                            hf.FirstHeader = new FirstHeader();
                            hf.FirstHeader.Text = textToInsert;
                            break;
    
                        case HeaderType.FirstFooter:
                            hf.FirstFooter = new FirstFooter();
                            hf.FirstFooter.Text = textToInsert;
                            break;
                    }
                }
                ws.Save();
            }

    I appreciate any kind of help in this matter. Thanks!

    Wednesday, July 29, 2015 4:27 PM

All replies

  • Hi Johny,

    I use OpenXML SDK tools to compare two excel files which is no header and another contains image header. I found that it will generate “xl/drawings/_rels/vmlDrawing1.vml.rels”, “/xl/drawings/vmlDrawing1.vml” and they are connected with “new LegacyDrawingHeaderFooter(){ Id = "rId2" };”. The code below shows a simple demo to insert the image to the first header.

    using System.Linq;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    using System.Drawing;
    using System.IO;
    using System.Drawing.Imaging;
    using System;
    
    
    namespace WindowsFormsApplication2
    {
        public class GeneratedClass
        {
            private static System.Collections.Generic.IDictionary<System.String, OpenXmlPart> UriPartDictionary = new System.Collections.Generic.Dictionary<System.String, OpenXmlPart>();
            private static System.Collections.Generic.IDictionary<System.String, DataPart> UriNewDataPartDictionary = new System.Collections.Generic.Dictionary<System.String, DataPart>();
            private static SpreadsheetDocument document;
    
            public static void ChangePackage(string filePath)
            {
                using (document = SpreadsheetDocument.Open(filePath, true))
                {
                    ChangeParts();
                }
            }
    
            private static void ChangeParts()
            {
                //Stores the referrences to all the parts in a dictionary.
                BuildUriPartDictionary();
                //Adds new parts or new relationships.
                AddParts();
                //Changes the contents of the specified parts.
                ChangeCoreFilePropertiesPart1(((CoreFilePropertiesPart)UriPartDictionary["/docProps/core.xml"]));
                ChangeWorksheetPart1(((WorksheetPart)UriPartDictionary["/xl/worksheets/sheet1.xml"]));
            }
    
            /// <summary>
            /// Stores the references to all the parts in the package.
            /// They could be retrieved by their URIs later.
            /// </summary>
            private static void BuildUriPartDictionary()
            {
                System.Collections.Generic.Queue<OpenXmlPartContainer> queue = new System.Collections.Generic.Queue<OpenXmlPartContainer>();
                queue.Enqueue(document);
                while (queue.Count > 0)
                {
                    foreach (var part in queue.Dequeue().Parts)
                    {
                        if (!UriPartDictionary.Keys.Contains(part.OpenXmlPart.Uri.ToString()))
                        {
                            UriPartDictionary.Add(part.OpenXmlPart.Uri.ToString(), part.OpenXmlPart);
                            queue.Enqueue(part.OpenXmlPart);
                        }
                    }
                }
            }
    
            /// <summary>
            /// Adds new parts or new relationship between parts.
            /// </summary>
            private static void AddParts()
            {
                //Generate new parts.
                VmlDrawingPart vmlDrawingPart1 = UriPartDictionary["/xl/worksheets/sheet1.xml"].AddNewPart<VmlDrawingPart>("rId2");
                GenerateVmlDrawingPart1Content(vmlDrawingPart1);
    
                ImagePart imagePart1 = vmlDrawingPart1.AddNewPart<ImagePart>("image/png", "rId1");
                GenerateImagePart1Content(imagePart1);
    
            }
    
            private static void GenerateVmlDrawingPart1Content(VmlDrawingPart vmlDrawingPart1)
            {
                System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(vmlDrawingPart1.GetStream(System.IO.FileMode.Create), System.Text.Encoding.UTF8);
                writer.WriteRaw("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\"\r\n xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n <o:shapelayout v:ext=\"edit\">\r\n  <o:idmap v:ext=\"edit\" data=\"1\"/>\r\n </o:shapelayout><v:shapetype id=\"_x0000_t75\" coordsize=\"21600,21600\" o:spt=\"75\"\r\n  o:preferrelative=\"t\" path=\"m@4@5l@4@11@9@11@9@5xe\" filled=\"f\" stroked=\"f\">\r\n  <v:stroke joinstyle=\"miter\"/>\r\n  <v:formulas>\r\n   <v:f eqn=\"if lineDrawn pixelLineWidth 0\"/>\r\n   <v:f eqn=\"sum @0 1 0\"/>\r\n   <v:f eqn=\"sum 0 0 @1\"/>\r\n   <v:f eqn=\"prod @2 1 2\"/>\r\n   <v:f eqn=\"prod @3 21600 pixelWidth\"/>\r\n   <v:f eqn=\"prod @3 21600 pixelHeight\"/>\r\n   <v:f eqn=\"sum @0 0 1\"/>\r\n   <v:f eqn=\"prod @6 1 2\"/>\r\n   <v:f eqn=\"prod @7 21600 pixelWidth\"/>\r\n   <v:f eqn=\"sum @8 21600 0\"/>\r\n   <v:f eqn=\"prod @7 21600 pixelHeight\"/>\r\n   <v:f eqn=\"sum @10 21600 0\"/>\r\n  </v:formulas>\r\n  <v:path o:extrusionok=\"f\" gradientshapeok=\"t\" o:connecttype=\"rect\"/>\r\n  <o:lock v:ext=\"edit\" aspectratio=\"t\"/>\r\n </v:shapetype><v:shape id=\"LH\" o:spid=\"_x0000_s1025\" type=\"#_x0000_t75\"\r\n  style=\';margin-left:0;margin-top:0;width:207pt;height:156pt;\r\n  z-index:1\'>\r\n  <v:imagedata o:relid=\"rId1\" o:title=\"WOPI\"/>\r\n  <o:lock v:ext=\"edit\" rotation=\"t\"/>\r\n </v:shape></xml>");
                writer.Flush();
                writer.Close();
            }
    
            private static void GenerateImagePart1Content(ImagePart imagePart1)
            {
                Image image = Image.FromFile(@"C:\Users\Administrator\Desktop\Capture.PNG");
                using (MemoryStream stream = new MemoryStream())
                {
                    // Save image to stream.
                    image.Save(stream, ImageFormat.Png);
                    string imagePart1Data = Convert.ToBase64String(stream.ToArray());
                    System.IO.Stream data = GetBinaryDataStream(imagePart1Data);
                    imagePart1.FeedData(data);
                    data.Close();
                }    
    
            }
    
            private static void ChangeCoreFilePropertiesPart1(CoreFilePropertiesPart coreFilePropertiesPart1)
            {
                var package = coreFilePropertiesPart1.OpenXmlPackage;
                package.PackageProperties.Modified = System.Xml.XmlConvert.ToDateTime("2015-07-30T03:03:22Z", System.Xml.XmlDateTimeSerializationMode.RoundtripKind);
            }
    
            private static void ChangeWorksheetPart1(WorksheetPart worksheetPart1)
            {
                Worksheet worksheet1 = worksheetPart1.Worksheet;
    
                HeaderFooter headerFooter1 = new HeaderFooter();
                OddHeader oddHeader1 = new OddHeader();
                oddHeader1.Text = "&L&G";
    
                headerFooter1.Append(oddHeader1);
                worksheet1.Append(headerFooter1);
    
                LegacyDrawingHeaderFooter legacyDrawingHeaderFooter1 = new LegacyDrawingHeaderFooter() { Id = "rId2" };
                worksheet1.Append(legacyDrawingHeaderFooter1);
            }
    
                 private static System.IO.Stream GetBinaryDataStream(string base64String)
            {
                return new System.IO.MemoryStream(System.Convert.FromBase64String(base64String));
            }
    
            
    
        }
    }

    It will insert the image(Image image = Image.FromFile(@"C:\Users\Administrator\Desktop\Capture.PNG");) to the header of the excel. You need to pay attention to the size of the image, if it is much bigger, it will show in the cells.

    You could modify the code to your own requirement.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.





    Thursday, July 30, 2015 5:39 AM
  • Thanks for you feedback Edward.

    I've attempted to implement some of your code ideas but couldn't make it work for me.

    So, to the code i first presented i replaced this line of code:

    InsertHeaderFooter(ws, HeaderType.FirstHeader, textToInsert)

    with this block:

    //Generate header image parts.
                    VmlDrawingPart vmlDrawingPart1 = sheetPart.AddNewPart<VmlDrawingPart>("rId2");
                    GenerateVmlDrawingPart1Content(vmlDrawingPart1);
    
                    ImagePart imagePart1 = vmlDrawingPart1.AddNewPart<ImagePart>("image/png", "rId1");
                    GenerateImagePart1Content(imagePart1);
    
                    ChangeWorksheetPart1((WorksheetPart)sheetPart);
    using the same functions you presented, just changing the image path in GenerateImagePart1Content().

    I'm getting the excel with all the information from the DataTable correctly, but no image on the header. 

    When i use the OpenXML SDK tools to inspect the generated .xlsx i get the following schema for the picture (i would post a print, but i'm not allowed yet):

    []/xl/media/image.bin as an ImagePart with relationshipID "rID1" inside a VMLDrawingPart []/xl/drawings/vmldrawing.vml

    Is that image supposed to be .bin if i'm using a .png?



    Thanks for your time.

    Thursday, July 30, 2015 2:52 PM
  • Hi Johny,

    Based on your code, it seems that you just used the AddParts() code, have you add the code below to your own method? If not, I test at my side, it will not show up the image.

    ChangeCoreFilePropertiesPart1(((CoreFilePropertiesPart)UriPartDictionary["/docProps/core.xml"]));
    ChangeWorksheetPart1(((WorksheetPart)UriPartDictionary["/xl/worksheets/sheet1.xml"]));

    First, I suggest you call “ChangePackage()” in your code to check whether it would work. Second, what do you mean by get all the information from the DataTable, what is the image? Do mean that you store the path of image in the DataTable or image itself?

    >> Is that image supposed to be .bin if i'm using a .png?

    Yes, you are right, I check the file at my side, and the file in the path is .bin.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, July 31, 2015 5:25 AM
  • Hello Edward,

    Answering the first part of your reply, i've used ChangeWorksheetPart1((WorksheetPart)sheetPart); but not ChangeCoreFilePropertiesPart1 because i didn't understand that UriPartDictionary.

    The image i pass it FromFile(@"C:\Users\Administrator\Desktop\pic.png"), just like you did, but a different path. The DataTable is just data i get from a database and that i insert it in the spreadsheet, it's not related to the image.

    Best Regards,
    Johny

    Friday, July 31, 2015 9:48 AM
  • Hi Johny,

    Yes, ChangeCoreFilePropertiesPart1 is not needed.

    Have your issue been resolved? If you have, it would be appreciated if you could share us your solution.
    If not, could you share us your code, and then we could test at our side?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, August 3, 2015 6:54 AM
  • Hello Edward

    Couldn't solve the problem.

    Here's the full code.

    http://pastie.org/10327063

    Thanks. Best Regards.
    Monday, August 3, 2015 8:24 AM
  • Hi Johny,

    I have looked into your class, and it seems that you are developing an asp.net mvc project, am I right? I am not very familiar with asp.net, I am not sure whether it could get your image file from your desktop if you debug your web project. I suggest you do as below:

    1. Test the code with your Console or winform application
    2. Create a excel file with header image manually or the code I provided
    3. User Open XML SDK Productivity Tool to compare the difference of the file and the generated file created with your code, and then according the difference to modify your code.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, August 4, 2015 5:07 AM
  • Thanks! Your code was so useful! I've been searching for a way to put picture in Excel Header for so long but with your example I succeeded!
    Wednesday, April 8, 2020 10:08 PM