none
How to use OpenXML to get the embedded package part (an xlsx file) in a cell in .xlsx RRS feed

  • Question

  • I have a problem extracting embedded xlsx files in an xlsx file.

    I can get the embeddedpackageparts by worksheetpart.embeddedpackageparts. Howover, I dont have the row and col information of the embeddedpackageparts.

    So how do I extract the embeddedpackagepart if I know the sheet name and row and column numbers of the cell where the embeddedpackagepart resides in?

    I just want to know the position of each embeddedpackagepart in the sheet because I have multiple embedded xlsx files in the sheet. If I dont know the row and col number, I cant differentiate which embedded file is which because their names are just worksheet1, worksheet2, ....


    • Edited by qiezi1015 Saturday, August 31, 2013 2:25 AM
    Saturday, August 31, 2013 1:59 AM

Answers

  • Hi,

    I wrote a demo to find embedded object by the position.

    Here is code for your reference:

    using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace TestExternalExcel { class Program { static void Main(string[] args) { TestOpenExcel("E:\\Document\\embeddedxlsx.xlsx"); } static void TestOpenExcel(string filename) { using (SpreadsheetDocument myworkbook = SpreadsheetDocument.Open(filename, true)) { Worksheet ws = myworkbook.WorkbookPart.WorksheetParts.FirstOrDefault().Worksheet; OleObjects oleo = ws.GetFirstChild<OleObjects>(); OleObject o = getEmbeddedObject(oleo, "0,0,12,22"); } }

    static OleObject getEmbeddedObject(OleObjects parts, string condition) { foreach (AlternateContent oo in parts) { OleObject o = oo.GetFirstChild<AlternateContentChoice>().GetFirstChild<OleObject>(); string fromc = o.EmbeddedObjectProperties.ObjectAnchor.FromMarker.ColumnId.Text; string fromr = o.EmbeddedObjectProperties.ObjectAnchor.FromMarker.RowId.Text; string toc = o.EmbeddedObjectProperties.ObjectAnchor.ToMarker.ColumnId.Text; string tor = o.EmbeddedObjectProperties.ObjectAnchor.ToMarker.RowId.Text; if (condition == fromc + "," + fromr + "," + toc + "," + tor) { return o; } } return null; } } }

    Also we can get the position of an embedded object by name.

    Here is the code below:

    static string getEmbeddedObjectLocation(OleObjects parts, string sheetname)
            {
                foreach (AlternateContent oo in parts)
                {
                    OleObject o = oo.GetFirstChild<AlternateContentChoice>().GetFirstChild<OleObject>();
                    if (o.ProgId == sheetname)
                    {
                        string fromc = o.EmbeddedObjectProperties.ObjectAnchor.FromMarker.ColumnId.Text;
                        string fromr = o.EmbeddedObjectProperties.ObjectAnchor.FromMarker.RowId.Text;
                        string toc = o.EmbeddedObjectProperties.ObjectAnchor.ToMarker.ColumnId.Text;
                        string tor = o.EmbeddedObjectProperties.ObjectAnchor.ToMarker.RowId.Text;
                        return fromc + "," + fromr + "," + toc + "," + tor;
                    }
                }
                return "";
            }

    The parameter named sheetname is related to the formula of the object we insert into the Excel file.

    we can call this method like this:

    Worksheet ws = myworkbook.WorkbookPart.WorksheetParts.FirstOrDefault().Worksheet;
    OleObjects oleo = ws.GetFirstChild<OleObjects>();
    string location = getEmbeddedObjectLocation(oleo, "worksheet1");

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.


    Monday, September 2, 2013 5:05 PM
    Moderator