none
How do you embed an Excel Workbook Into another Excel workbook using the Open XML Format SDK?

    Question

  • I would like to accomplish through code what is done through  Insert / Object.  the code snippet below comes close, but it always embeds the object as a .bin and never xlsx.  I'm not sure what I'm doing wrong.

    There's loads of articles that tell you a lot of things about worksheet manipulation and so on, but I haven't found one that shows me how to get this one thing done; 

            public static void EmbedFile(string container, string content)
            {
    
                using (SpreadsheetDocument xlDoc = SpreadsheetDocument.Open(container, true))
                {
    
                    WorkbookPart mainPart = xlDoc.WorkbookPart;
                    IEnumerable<Sheet> Sheets = mainPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>(); //.Where(s => s.Name == "items");
    
                    if (Sheets.Count() == 0)
                    {
    
                        return;
    
                    }
    
    
                    string relationshipId = Sheets.First().Id.Value;
                    WorksheetPart worksheetPart = (WorksheetPart)mainPart.GetPartById(relationshipId);
    
                    try
                    {
    
    
                        EmbeddedObjectPart obj = worksheetPart.AddEmbeddedObjectPart("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                        byte[] embeddedDocumentBytes;
    
                        using (FileStream fsEmbeddedDocument = File.OpenRead(content))
                        {
    
                            embeddedDocumentBytes = new byte[fsEmbeddedDocument.Length];
                            fsEmbeddedDocument.Read(embeddedDocumentBytes, 0, embeddedDocumentBytes.Length);
    
                        }
    
    
    
    
                        using (BinaryWriter writer = new BinaryWriter(obj.GetStream()))
                        {
    
                            writer.Write(embeddedDocumentBytes);
                            writer.Flush();
    
                        }
    
    
                    }
    
                    catch (Exception e)
                    {
    
                        Console.WriteLine(e.Message);
    
                        xlDoc.Close();
    
    
    
                        return;
    
                    }
    
    
    
                    xlDoc.Close();
    
                } //using
    
            } //EmbedFile    



    Saturday, February 09, 2013 3:27 PM

Answers

  • Hi TSRACT

    EmbeddedObjectPart is in the Packaging namespace, that is shared by all the Office applications. That means Excel should be able to use it, at least in theory... In any case, that should give you a starting point in the very long "Reflect" code.

    It might also help if you create a "test workbook" file that contains only one sheet with no data, plus the embedded workbook. That should narrow things down considerably?


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, February 11, 2013 1:57 PM
    Moderator

All replies

  • Hi TSRACT

    You mention Insert/Object, but not what you choose in that dialog box. Could you please provide more detail about what you get as the end result?

    And if you open that end result (by renaming to ZIP in the Windows Explorer), how has the new "thing" been incorporated into the existing workbook? What additional parts are there?

    Generally, if you "embed" something it will be a .bin file that's accessed in the application via object linking and embedding (OLE).

    Inserting something that integrates to become an integral part of the file is something else and is a much more complex task. In essence, you need to do what the Excel application does for you behind the scenes: copy each worksheet, plus all references the worksheets have to data, shared strings, graphics etc. and integrate those into the corresponding parts in the existing workbook. As far as I know, there are no "shortcuts".


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, February 10, 2013 7:44 AM
    Moderator
  • Wow, I did leave that out?  My copy/paste skills leave something to be desired :)

    Manual steps from Excel 2010
    Insert Tab / Object / Create From File Tab / Display as Icon (check)

    Once I follow the above steps, I end up with a .xlsx file in the "embeddings" folder as opposed to a .bin

    I just started looking at the c# generated by the "Reflect Code" option in the Open XML SDK 2.0 Productivity tool.

    While I'm sure everything is there, the problem is that EVERYTHING is there, and it's been quite a task weeding out all the code that has nothing to do with what I'm trying to accomplish.  It's a great tool, and I'm learning a lot, but there's a lot going on that I'm not clear about.

    I'm sure I'll find it sooner or later; Right now, this is proving to be the bottleneck in one of our projects

    Monday, February 11, 2013 1:24 AM
  • Hi TSRACT

    I don't have a lot of time to delve into this, at the moment, but...

    Have you come across this article:
    http://blogs.msdn.com/b/brian_jones/archive/2009/06/30/embedding-an-open-xml-file-in-another-open-xml-file.aspx

    It explains how the embedding works and the members of the OPen XML SDK involved. The "container" is Word, but I don't think that matters as far as the Open XML SDK Namespace that's used.

    This might help, as well
    http://stackoverflow.com/questions/9469696/editing-an-excel-object-embedded-in-a-word-document-in-excel

    And this
    http://msdn.microsoft.com/en-us/library/documentformat.openxml.packaging.embeddedobjectpart_members.aspx


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, February 11, 2013 8:44 AM
    Moderator
  • Thanks.  I've been across these links before.  No one seems to be able to locate an example where Excel is the container, as well as the content to be embedded (e.g. not just worksheets, but the entire workbook).

    I'll review the links though.  I could have missed a key piece.  

    Thanks again for your time on this.  I'll be sure to post the result once I've figured it out.

    Monday, February 11, 2013 1:33 PM
  • Hi TSRACT

    EmbeddedObjectPart is in the Packaging namespace, that is shared by all the Office applications. That means Excel should be able to use it, at least in theory... In any case, that should give you a starting point in the very long "Reflect" code.

    It might also help if you create a "test workbook" file that contains only one sheet with no data, plus the embedded workbook. That should narrow things down considerably?


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, February 11, 2013 1:57 PM
    Moderator
  • Did you ever solve the problem with the embedded xlsx file being named with a ".bin" extension?  I'm stuck on the same issue.  I've looked at the disassembled source code as well, still no luck.

    Thanks,

    Mike

    Thursday, May 23, 2013 4:49 PM