Show / Hide Table of Contents

Ole Embed Workbook as Icon with OpenXML SDK

The example shows how to embed an Excel Workbook as OLE Object and display it as icon with OpenXML SDK.

Output: OleEmbedIcon.xlsx

public void Run()
{
    using (var spreadsheetDocument = SpreadsheetDocument.Create("OleEmbedIcon.xlsx", SpreadsheetDocumentType.Workbook))
    {
        string toEmbedFilePath = "ToEmbed.xlsx";
        string imagePath = "OleIcon.emf";
        WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        Workbook workbook = new Workbook();

        Worksheet worksheet = new Worksheet();
        worksheetPart.Worksheet = worksheet;

        VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>();
        DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();

        var embeddedPart =  worksheetPart.AddEmbeddedPackagePart(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        using (FileStream stream = new FileStream(toEmbedFilePath, FileMode.Open))
        {
            embeddedPart.FeedData(stream);
        }

        var imagePart = worksheetPart.AddImagePart(
                    "image/x-emf");
        using (FileStream stream = new FileStream(imagePath, FileMode.Open))
        {
            imagePart.FeedData(stream);
        }

        worksheetPart.Worksheet.Append(new SheetData());
        Spreadsheet.Drawing drawing = new Spreadsheet.Drawing();
        drawing.Id = worksheetPart.GetIdOfPart(drawingsPart);
        worksheetPart.Worksheet.Append(drawing);
        LegacyDrawing legacyDrawing = new LegacyDrawing() { Id = worksheetPart.GetIdOfPart(vmlDrawingPart) };
        worksheetPart.Worksheet.Append(legacyDrawing);

                
        this.BuildDrawingPart(drawingsPart);
        this.BuildVmlDrawingPart(vmlDrawingPart);

        var oleObjects = worksheet.AppendChild(new OleObjects());

        var oleObject = new Spreadsheet.OleObject();
        oleObject.Id = worksheetPart.GetIdOfPart(embeddedPart);
        oleObject.ShapeId = 1025;
        oleObject.EmbeddedObjectProperties = new EmbeddedObjectProperties();
        oleObject.EmbeddedObjectProperties.DefaultSize = false;
        oleObject.EmbeddedObjectProperties.Id = worksheetPart.GetIdOfPart(imagePart);
        oleObject.EmbeddedObjectProperties.ObjectAnchor = new ObjectAnchor();
        oleObject.EmbeddedObjectProperties.ObjectAnchor.FromMarker = new Spreadsheet.FromMarker();
        oleObject.EmbeddedObjectProperties.ObjectAnchor.FromMarker.ColumnId = new ColumnId("0");
        oleObject.EmbeddedObjectProperties.ObjectAnchor.FromMarker.ColumnOffset = new ColumnOffset("0");
        oleObject.EmbeddedObjectProperties.ObjectAnchor.FromMarker.RowId = new RowId("0");
        oleObject.EmbeddedObjectProperties.ObjectAnchor.FromMarker.RowOffset = new RowOffset("0");
        oleObject.EmbeddedObjectProperties.ObjectAnchor.ToMarker = new Spreadsheet.ToMarker();
        oleObject.EmbeddedObjectProperties.ObjectAnchor.ToMarker.ColumnId = new ColumnId("1");
        oleObject.EmbeddedObjectProperties.ObjectAnchor.ToMarker.ColumnOffset = new ColumnOffset("304800");
        oleObject.EmbeddedObjectProperties.ObjectAnchor.ToMarker.RowId = new RowId("1");
        oleObject.EmbeddedObjectProperties.ObjectAnchor.ToMarker.RowOffset = new RowOffset("12699");

        var alternateContent =  oleObjects.AppendChild(new AlternateContent());
        alternateContent.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
        var choice = alternateContent.AppendNewAlternateContentChoice();
        choice.Requires = "x14";
        choice.Append(oleObject);
        var fallback = alternateContent.AppendNewAlternateContentFallback();
        fallback.Append(new Spreadsheet.OleObject(){Id = worksheetPart.GetIdOfPart(embeddedPart) ,ShapeId = 1025});
                
        worksheetPart.Worksheet.Save();
        Sheets sheets = new Sheets();
        Sheet sheet = new Sheet();
        sheet.Name = "Sheet1";
        sheet.SheetId = 1;
        sheet.Id = workbookPart.GetIdOfPart(worksheetPart);
        sheets.Append(sheet);
        workbook.Append(sheets);

        spreadsheetDocument.WorkbookPart.Workbook = workbook;
        spreadsheetDocument.WorkbookPart.Workbook.Save();
        spreadsheetDocument.Close();
    }
}

internal void BuildDrawingPart(DrawingsPart drawingsPart)
{
    var wsDr = new WorksheetDrawing();
    var twoCellAnchor = new TwoCellAnchor();
    twoCellAnchor.EditAs = new EnumValue<DocumentFormat.OpenXml.Drawing.Spreadsheet.EditAsValues>(DocumentFormat.OpenXml.Drawing.Spreadsheet.EditAsValues.OneCell);
    twoCellAnchor.FromMarker = new Drawing.Spreadsheet.FromMarker();
    twoCellAnchor.FromMarker.ColumnId = new ColumnId("0");
    twoCellAnchor.FromMarker.ColumnOffset = new ColumnOffset("0");
    twoCellAnchor.FromMarker.RowId = new RowId("0");
    twoCellAnchor.FromMarker.RowOffset = new RowOffset("0");
    twoCellAnchor.ToMarker = new Drawing.Spreadsheet.ToMarker();
    twoCellAnchor.ToMarker.ColumnId = new ColumnId("1");
    twoCellAnchor.ToMarker.ColumnOffset = new ColumnOffset("304800");
    twoCellAnchor.ToMarker.RowId = new RowId("1");
    twoCellAnchor.ToMarker.RowOffset = new RowOffset("12699");

    var sp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Shape();
    sp.Macro = string.Empty;
    sp.TextLink = string.Empty;
    sp.NonVisualShapeProperties = new Drawing.Spreadsheet.NonVisualShapeProperties();


    sp.NonVisualShapeProperties.NonVisualDrawingProperties = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties();
    sp.NonVisualShapeProperties.NonVisualDrawingProperties.Id = 1025;
    sp.NonVisualShapeProperties.NonVisualDrawingProperties.Name = "Object 1";
    sp.NonVisualShapeProperties.NonVisualDrawingProperties.Hidden = true;
    sp.NonVisualShapeProperties.NonVisualDrawingProperties.NonVisualDrawingPropertiesExtensionList =
                new NonVisualDrawingPropertiesExtensionList();
    sp.NonVisualShapeProperties.NonVisualDrawingProperties.NonVisualDrawingPropertiesExtensionList.Append(
        new DocumentFormat.OpenXml.Drawing.Extension(new CompatExtension {ShapeId = "_x0000_s1025"})
                    {Uri = "{63B3BB69-23CF-44E3-9099-C40C66FF867C}"});
    sp.NonVisualShapeProperties.NonVisualShapeDrawingProperties = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualShapeDrawingProperties();
            
    sp.ShapeProperties = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties();
    sp.ShapeProperties.BlackWhiteMode = new EnumValue<BlackWhiteModeValues>(BlackWhiteModeValues.Auto);
    sp.ShapeProperties.Transform2D = new DocumentFormat.OpenXml.Drawing.Transform2D();
    sp.ShapeProperties.Transform2D.Offset = new Offset() { X = 0, Y = 0 };
    sp.ShapeProperties.Transform2D.Extents = new Extents();
    sp.ShapeProperties.Transform2D.Extents.Cx = 0;
    sp.ShapeProperties.Transform2D.Extents.Cy = 0;
            

    var prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
    prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
    prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
    sp.ShapeProperties.Append(prstGeom);
    var solidFill = sp.ShapeProperties.AppendChild(new DocumentFormat.OpenXml.Drawing.SolidFill());
    solidFill.RgbColorModelHex = new RgbColorModelHex(){Val = "FFFFFF", LegacySpreadsheetColorIndex = 65};

    var ln = sp.ShapeProperties.AppendChild(new DocumentFormat.OpenXml.Drawing.Outline {Width = 9525});
    ln.Append(new SolidFill(new RgbColorModelHex {Val = "000000", LegacySpreadsheetColorIndex = 64}));
    ln.Append(new PresetDash {Val = new EnumValue<PresetLineDashValues>(PresetLineDashValues.Solid)});
    ln.Append(new Miter {Limit = 800000});
    ln.Append(new HeadEnd());
    ln.Append(new TailEnd
    {
        Length = new EnumValue<LineEndLengthValues>(LineEndLengthValues.Medium),
        Type = new EnumValue<LineEndValues>(LineEndValues.None),
        Width = new EnumValue<LineEndWidthValues>(LineEndWidthValues.Medium)
    });
    sp.ShapeProperties.Append(new EffectList());
    twoCellAnchor.Append(sp);
    twoCellAnchor.Append(new Drawing.Spreadsheet.ClientData() { LockWithSheet = false });

    var alternateContent = wsDr.AppendChild(new AlternateContent());
            
    var choice = alternateContent.AppendNewAlternateContentChoice();
    choice.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");
    alternateContent.AppendNewAlternateContentFallback();
    choice.Requires = "a14";
    choice.Append(twoCellAnchor);

            
    wsDr.Save(drawingsPart);
}

internal void BuildVmlDrawingPart(VmlDrawingPart vmlDrawingPart)
{
    var writer = new XmlTextWriter(vmlDrawingPart.GetStream(FileMode.Create), Encoding.UTF8);
    writer.WriteStartElement("xml");

    var shapeType = new DocumentFormat.OpenXml.Vml.Shapetype();
    shapeType.Id = "_x0000_s1025";
    shapeType.CoordinateSize = "21600,21600";
    shapeType.Filled = false;
    shapeType.Stroked = false;
    shapeType.OptionalNumber = 75;
    shapeType.PreferRelative = true;
    shapeType.EdgePath = "m@4@5l@4@11@9@11@9@5xe";

    var stroke = new DocumentFormat.OpenXml.Vml.Stroke();
    stroke.JoinStyle = new EnumValue<StrokeJoinStyleValues>(StrokeJoinStyleValues.Miter);
    shapeType.Append(stroke);

    var formulas = new DocumentFormat.OpenXml.Vml.Formulas();
    formulas.Append(new Vml.Formula(){Equation = "if lineDrawn pixelLineWidth 0" });
    formulas.Append(new Vml.Formula() { Equation = "sum @0 1 0" });
    formulas.Append(new Vml.Formula() { Equation = "sum 0 0 @1" });
    formulas.Append(new Vml.Formula() { Equation = "prod @2 1 2" });
    formulas.Append(new Vml.Formula() { Equation = "prod @3 21600 pixelWidth" });
    formulas.Append(new Vml.Formula() { Equation = "prod @3 21600 pixelHeight" });
    formulas.Append(new Vml.Formula() { Equation = "sum @0 0 1" });
    formulas.Append(new Vml.Formula() { Equation = "prod @6 1 2" });
    formulas.Append(new Vml.Formula() { Equation = "prod @7 21600 pixelWidth" });
    formulas.Append(new Vml.Formula() { Equation = "sum @8 21600 0" });
    formulas.Append(new Vml.Formula() { Equation = "prod @7 21600 pixelHeight" });
    formulas.Append(new Vml.Formula() { Equation = "sum @10 21600 0" });
    shapeType.Append(formulas);

    var path = new DocumentFormat.OpenXml.Vml.Path();
    path.AllowGradientShape = TrueFalseValue.FromBoolean(true);
    path.ConnectionPointType = new EnumValue<ConnectValues>(ConnectValues.Rectangle);
    path.AllowExtrusion = false;

    shapeType.Append(path);

    var shape = new Vml.Shape();
    shape.Id = "_x0000_s1025";
    shape.Style =
                "margin-left:0pt;margin-top:0pt;width:72pt;height:54pt;z-index:1;position:absolute";
    shape.InsetMode = new EnumValue<InsetMarginValues>(InsetMarginValues.Auto);
    shape.Filled = true;
    shape.FillColor = "window [65]";
    shape.Stroked = true;
    shape.StrokeColor = "windowText[64]";
    shape.Type = "#_x0000_t75";

    shape.Append(new Vml.Fill() { Color2 = "window [65]" });
            
    var clientData = new Vml.Spreadsheet.ClientData();
    clientData.ObjectType = new EnumValue<ObjectValues>(ObjectValues.Picture);
    clientData.Append(new ResizeWithCells("True"));
    clientData.Append(new DocumentFormat.OpenXml.Vml.Spreadsheet.Anchor("0, 0, 0, 0, 1, 32, 1, 1"));
    clientData.Append(new PrintObject());
    clientData.Append(new AutoSizePicture());
    clientData.Append(new ClipboardFormat("Pict"));

    shape.Append(clientData);

    shapeType.WriteTo(writer);
    shape.WriteTo(writer);

    writer.WriteEndElement();
    writer.Flush();
    writer.Close();
}
Back to top Generated by DocFX