none
How can I add any type of ole embedded object in excel using openxml RRS feed

  • Question

  • hi all,

    How can i add any type of ole embedded object in excel using OpenXml?

    I have add word and excel successfully as ole in the excel.But the requirement is add ole of any type. So I add a picture for test,but failed.

    My code is as following:



    class Program
        {

    public static string WordPID = "Document";
            public static string ExcelPID = "Worksheet";
            public static string PPTPID = "Presentation";
            public static string PicPID = "Packager Shell Object"; public static string attFile1 = "55.jpg"; public static string attFile2 = "123.docx"; public static string attFile3 = "3.xlsx"; public static int vmlID = 1025; static void Main(string[] args) { if(File.Exists(targetFile)) { File.Delete(targetFile); } File.Copy(sourceFile, targetFile, true); using (SpreadsheetDocument document = SpreadsheetDocument.Open(targetFile, true)) { WorkbookPart workbookpart = document.WorkbookPart; WorksheetPart worksheetPart1 = GetSheetByName(workbookpart, "sheet1"); worksheetPart1.Worksheet.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); worksheetPart1.Worksheet.Save(); VmlDrawingPart vmlDrawingPart1 = worksheetPart1.AddNewPart<VmlDrawingPart>("rId2"); GenerateVmlDrawingPart1Content(vmlDrawingPart1); ImagePart imagePart1 = vmlDrawingPart1.AddNewPart<ImagePart>("image/x-emf", "rIdV1"); GenerateImagePart1Content(imagePart1,iconFile1); ImagePart imagePart2 = vmlDrawingPart1.AddNewPart<ImagePart>("image/x-emf", "rIdV2"); GenerateImagePart1Content(imagePart2, iconFile2); ImagePart imagePart3 = vmlDrawingPart1.AddNewPart<ImagePart>("image/x-emf", "rIdV3"); GenerateImagePart1Content(imagePart3, iconFile3); Xdr.WorksheetDrawing worksheetDrawing1 = new Xdr.WorksheetDrawing(); worksheetDrawing1.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); worksheetDrawing1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); DrawingsPart drawingsPart1 = worksheetPart1.AddNewPart<DrawingsPart>("rId5"); AlternateContent alterContent1 = GenerateDrawingsPart1Content(); worksheetDrawing1.Append(alterContent1); drawingsPart1.WorksheetDrawing = worksheetDrawing1; worksheetPart1.AddPart(imagePart1, "rIdICON1"); worksheetPart1.AddPart(imagePart2, "rIdICON2"); worksheetPart1.AddPart(imagePart3, "rIdICON3"); EmbeddedObjectPart embeddedObjectPart1 = worksheetPart1.AddNewPart<EmbeddedObjectPart>("application/vnd.openxmlformats-officedocument.oleObject", "rIdOLE1"); EmbeddedPackagePart embeddedObjectPart2 = worksheetPart1.AddNewPart<EmbeddedPackagePart>("application/vnd.openxmlformats-officedocument.wordprocessingml.document", "rIdOLE2"); EmbeddedPackagePart embeddedObjectPart3 = worksheetPart1.AddNewPart<EmbeddedPackagePart>("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "rIdOLE3"); GenerateEmbeddedObjectPartContent(embeddedObjectPart1, attFile1); GenerateEmbeddedObjectPartContent(embeddedObjectPart2, attFile2); GenerateEmbeddedObjectPartContent(embeddedObjectPart3, attFile3); Drawing drawing1 = new Drawing() { Id = "rId5" }; LegacyDrawing legacyDrawing1 = new LegacyDrawing() { Id = "rId2" }; worksheetPart1.Worksheet.Append(drawing1); worksheetPart1.Worksheet.Append(legacyDrawing1); OleObjects oleObjects1 = new OleObjects(); oleObjects1.Append(TestExcel.Ole.CreateOle(cIndex1, rIndex1, "rIdOLE1", "rIdICON1",PicPID)); oleObjects1.Append(TestExcel.Ole.CreateOle(cIndex2, rIndex2, "rIdOLE2", "rIdICON2",WordPID)); oleObjects1.Append(TestExcel.Ole.CreateOle(cIndex3, rIndex3, "rIdOLE3", "rIdICON3",ExcelPID)); worksheetPart1.Worksheet.Append(oleObjects1); worksheetPart1.Worksheet.Save(); document.WorkbookPart.Workbook.Save(); } System.Diagnostics.Process.Start(@"Test.xlsx"); }


     public static AlternateContent CreateOle(int column, int row,string oleRId,string iconRId,string progID)
            {
                AlternateContent alternateContent2 = new AlternateContent();
                alternateContent2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                AlternateContentChoice alternateContentChoice2 = new AlternateContentChoice() { Requires = "x14" };
                OleObject oleObject1 = new OleObject() { ProgId = progID, DataOrViewAspect = DataViewAspectValues.DataViewAspectIcon, ShapeId = (UInt32Value)shapeID, Id = oleRId };
                EmbeddedObjectProperties embeddedObjectProperties1 = new EmbeddedObjectProperties() { DefaultSize = false, AutoPict = false, Id = iconRId };
    
                ObjectAnchor objectAnchor1 = new ObjectAnchor() { MoveWithCells = true };
    
                FromMarker fromMarker2 = new FromMarker();
                Xdr.ColumnId columnId3 = new Xdr.ColumnId();
                columnId3.Text = column.ToString();
                Xdr.ColumnOffset columnOffset3 = new Xdr.ColumnOffset();
                columnOffset3.Text = "47625";
                Xdr.RowId rowId3 = new Xdr.RowId();
                rowId3.Text = (row-2).ToString();
                Xdr.RowOffset rowOffset3 = new Xdr.RowOffset();
                rowOffset3.Text = "28575";
    
                fromMarker2.Append(columnId3);
                fromMarker2.Append(columnOffset3);
                fromMarker2.Append(rowId3);
                fromMarker2.Append(rowOffset3);
    
                ToMarker toMarker2 = new ToMarker();
                Xdr.ColumnId columnId4 = new Xdr.ColumnId();
                columnId4.Text = column.ToString();
                Xdr.ColumnOffset columnOffset4 = new Xdr.ColumnOffset();
                columnOffset4.Text = "419100";
                Xdr.RowId rowId4 = new Xdr.RowId();
                rowId4.Text = row.ToString();
                Xdr.RowOffset rowOffset4 = new Xdr.RowOffset();
                rowOffset4.Text = "19050";
    
                toMarker2.Append(columnId4);
                toMarker2.Append(columnOffset4);
                toMarker2.Append(rowId4);
                toMarker2.Append(rowOffset4);
    
                objectAnchor1.Append(fromMarker2);
                objectAnchor1.Append(toMarker2);
    
                embeddedObjectProperties1.Append(objectAnchor1);
    
                oleObject1.Append(embeddedObjectProperties1);
    
                alternateContentChoice2.Append(oleObject1);
    
                AlternateContentFallback alternateContentFallback2 = new AlternateContentFallback();
                OleObject oleObject2 = new OleObject() { ProgId = progID, DataOrViewAspect = DataViewAspectValues.DataViewAspectIcon, ShapeId = (UInt32Value)shapeID++, Id = oleRId };
    
                alternateContentFallback2.Append(oleObject2);
    
                alternateContent2.Append(alternateContentChoice2);
                alternateContent2.Append(alternateContentFallback2);
    
                return alternateContent2;
            }

    Because the fail of the generating ole of pic, I have to suspend my research and ask for help here.

    At the beginning, i just want to deal with the different kinds of ole using different kinds method, just enumerate the  extension and the program ID.But this method seemed hard for because the following 2 questions:

    1.There are so many kinds of ole and  how can i got the program id string corresponding? Is there any page contains the correspondences? What`s more, could the program could work correctly if i using the correct program id and embedded type.(Why i can't get the pic ole ?)

    2.how could i get the icon of the ole ? I have tried the Marshal method, but only small ugly icon generated without display name of the file.

    I have searched a lot on the net. it seemed lots of people have the same problem with me but not any satisfying answer. 

    There's an article prompting a workaround by using VBA API (wordDocument.InlineShapes.AddOLEObject ) to get the emf icon and the object type. It's a good solution but not fit my situation.  ( If VBA available why OpenXml needed....) My customer strongly demand no VBA used because the VBA caused so much com exception in the previous version.

    So is anyone can spread some light on me about how to add the ole in excel by using OpenXml? Or any suggestion on the above two questions?

    Any suggestion will be highly appreciated.

      


    Luther


    • Edited by Zhihua Lu Monday, June 11, 2012 8:44 AM change some code
    Friday, June 8, 2012 8:48 AM

All replies

  • Hi Luther,

    There has a quite different to embed different kind of object into workbook. However there will alway have a drawingspart in the worksheetpart. I think you need do some research in drawingspart.

    Have a good day,

    T.X.


    Monday, June 11, 2012 10:58 PM
  • hi T.X

    Thanks for your advice. I have done the research of drawings part .According to the code reflected,  it seems that the drawing part is only related to the display on the sheet( like a icon). As to the ole , the vml drawing part is used instead of drawing part .

    So could you show me some code how the drawing part used for ole so that i could do some research.


    Luther

    Tuesday, June 12, 2012 1:21 AM
  • Hi Luther,

    I think it's meaningless to discuss the drawing part. My main opinion of my last reply it tell you "There has a quite different to embed different kind of object into workbook." So we are hard to say "how to" if we don't know which kind of OLE object will be embedded.

    Have a good day,

    Tom

    Monday, June 18, 2012 6:01 AM