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();
}