// Adds child parts and generates content of the specified part. public static void CreateTable(WorkbookPart workbookPart, WorksheetPart worksheetPart, string[] columns, int topLeftColumn, int topLeftRow, int width, int height) { List<WorksheetPart> worksheets = workbookPart.GetPartsOfType<WorksheetPart>().ToList(); uint maxTableId = worksheets.Select(ws => ws.TableDefinitionParts.ToList()).SelectMany(tableDefinitions => tableDefinitions).Aggregate<TableDefinitionPart, uint>(0, (current, tableDef) => Math.Max(tableDef.Table.Id, current)); uint tableId = maxTableId + 1; var tables = new TableParts { Count = 1U }; worksheetPart.Worksheet.Append((IEnumerable<OpenXmlElement>)tables); var newTableDefnPart = worksheetPart.AddNewPart<TableDefinitionPart>(); string relationshipId = worksheetPart.GetIdOfPart(newTableDefnPart); string cellReference = string.Format("{0}{1}:{2}{3}", GetColumnIdentifier(topLeftColumn), topLeftRow, GetColumnIdentifier(topLeftColumn + width - 1), topLeftRow + height); var table1 = new Table { Id = tableId, Name = "Table" + relationshipId, DisplayName = "Table" + relationshipId, Reference = cellReference, TotalsRowShown = false }; var autoFilter1 = new AutoFilter { Reference = cellReference }; var tableColumns1 = new TableColumns { Count = (uint)columns.Length }; for (int iColumn = 0; iColumn < columns.Length; iColumn++) { var tableColumn = new TableColumn { Id = (UInt32Value)(uint)iColumn + 1, Name = columns[iColumn] }; tableColumns1.Append((IEnumerable<OpenXmlElement>)tableColumn); } var tableStyleInfo1 = new TableStyleInfo { Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false }; table1.Append((IEnumerable<OpenXmlElement>)autoFilter1); table1.Append((IEnumerable<OpenXmlElement>)tableColumns1); table1.Append((IEnumerable<OpenXmlElement>)tableStyleInfo1); newTableDefnPart.Table = table1; var table = new TablePart { Id = relationshipId }; tables.Append((IEnumerable<OpenXmlElement>)table); //TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" }; //worksheetPart.Worksheet.Append(tableStyles1); }
private void DeleteComments(WorksheetPart worksheetPart, XLWorksheet worksheet, SaveContext context) { // We have the comments so we can delete the comments part worksheetPart.DeletePart(worksheetPart.WorksheetCommentsPart); var vmlDrawingPart = worksheetPart.VmlDrawingParts.FirstOrDefault(); // Only delete the VmlDrawingParts for comments. if (vmlDrawingPart != null) { var xdoc = XDocumentExtensions.Load(vmlDrawingPart.GetStream(FileMode.Open)); //xdoc.Root.Elements().Where(e => e.Name.LocalName == "shapelayout").Remove(); xdoc.Root.Elements().Where( e => e.Name.LocalName == "shapetype" && (string)e.Attribute("id") == @"_x0000_t202").Remove(); xdoc.Root.Elements().Where( e => e.Name.LocalName == "shape" && (string)e.Attribute("type") == @"#_x0000_t202").Remove(); var imageParts = vmlDrawingPart.ImageParts.ToList(); var legacyParts = vmlDrawingPart.LegacyDiagramTextParts.ToList(); var rId = worksheetPart.GetIdOfPart(vmlDrawingPart); worksheet.LegacyDrawingId = rId; worksheetPart.ChangeIdOfPart(vmlDrawingPart, "xxRRxx"); // Anything will do for the new relationship id // we just want it alive enough to create the copy var hasShapes = xdoc.Root.Elements().Any(e => e.Name.LocalName == "shape" || e.Name.LocalName == "group"); VmlDrawingPart vmlDrawingPartNew = null; var hasNewPart = (imageParts.Count > 0 || legacyParts.Count > 0 || hasShapes); if (hasNewPart) { vmlDrawingPartNew = worksheetPart.AddNewPart<VmlDrawingPart>(rId); using (var writer = new XmlTextWriter(vmlDrawingPartNew.GetStream(FileMode.Create), Encoding.UTF8)) { writer.WriteRaw(xdoc.ToString()); } imageParts.ForEach(p => vmlDrawingPartNew.AddPart(p, vmlDrawingPart.GetIdOfPart(p))); legacyParts.ForEach(p => vmlDrawingPartNew.AddPart(p, vmlDrawingPart.GetIdOfPart(p))); } worksheetPart.DeletePart(vmlDrawingPart); if (hasNewPart && rId != worksheetPart.GetIdOfPart(vmlDrawingPartNew)) worksheetPart.ChangeIdOfPart(vmlDrawingPartNew, rId); } }
/// <summary> /// Add the logo of the system /// </summary> /// <param name="logoPath">Path of the logo</param> /// <param name="worksheetPart">Worksheet Part</param> /// <returns>Drawing</returns> private static Drawing AddLogo(string logoPath, WorksheetPart worksheetPart) { string sImagePath = logoPath; DrawingsPart dp = worksheetPart.AddNewPart<DrawingsPart>(); ImagePart imgp = dp.AddImagePart(ImagePartType.Png, worksheetPart.GetIdOfPart(dp)); using (FileStream fs = new FileStream(sImagePath, FileMode.Open, FileAccess.Read)) { imgp.FeedData(fs); } DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties(); nvdp.Id = 1025; nvdp.Name = "Picture 1"; nvdp.Description = "logo"; DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks(); picLocks.NoChangeAspect = true; picLocks.NoChangeArrowheads = true; DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties nvpdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties(); nvpdp.PictureLocks = picLocks; DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties nvpp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties(); nvpp.NonVisualDrawingProperties = nvdp; nvpp.NonVisualPictureDrawingProperties = nvpdp; DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch(); stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle(); DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill blipFill = new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill(); DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip(); blip.Embed = dp.GetIdOfPart(imgp); blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print; blipFill.Blip = blip; blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle(); blipFill.Append(stretch); DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D(); DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset(); offset.X = 0; offset.Y = 0; t2d.Offset = offset; Bitmap bm = new Bitmap(sImagePath); DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents(); extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution); extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution); bm.Dispose(); t2d.Extents = extents; DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties sp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties(); sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto; sp.Transform2D = t2d; DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry(); prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle; prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList(); sp.Append(prstGeom); sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill()); DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture(); picture.NonVisualPictureProperties = nvpp; picture.BlipFill = blipFill; picture.ShapeProperties = sp; DocumentFormat.OpenXml.Drawing.Spreadsheet.Position pos = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Position(); pos.X = 18 * 914400 / 72; pos.Y = 28 * 914400 / 72; Extent ext = new Extent(); ext.Cx = extents.Cx; ext.Cy = extents.Cy; AbsoluteAnchor anchor = new AbsoluteAnchor(); anchor.Position = pos; anchor.Extent = ext; anchor.Append(picture); anchor.Append(new ClientData()); WorksheetDrawing wsd = new WorksheetDrawing(); wsd.Append(anchor); Drawing drawing = new Drawing(); drawing.Id = dp.GetIdOfPart(imgp); wsd.Save(dp); return drawing; }
private static Drawing GetLogo(ExcelType excelType, WorksheetPart wsp, out uint startIndex) { var dp = wsp.AddNewPart<DrawingsPart>(); Drawing drawing; var sImagePath = GetLogoPath(excelType); if (string.IsNullOrEmpty(sImagePath)) { startIndex = 1; return null; } startIndex = 8; //sorry, I must do it in this way, MEmory stream oesn't work in FeedData. There are several discussions in the internet about it. ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp)); var bm = new Bitmap(Resource.CEEX_small); //var bm = new Bitmap(@"ExcelProvider\_img\CEEX_small.png"); using (var ms = new MemoryStream()) { bm.Save(ms,ImageFormat.Png); ms.Seek(0, 0); imgp.FeedData(ms); } var nvdp = new NonVisualDrawingProperties(); nvdp.Id = 1025; nvdp.Name = "logo"; nvdp.Description = "polymathlogo"; var picLocks = new PictureLocks(); picLocks.NoChangeAspect = true; picLocks.NoChangeArrowheads = true; var nvpdp = new NonVisualPictureDrawingProperties(); nvpdp.PictureLocks = picLocks; var nvpp = new NonVisualPictureProperties(); nvpp.NonVisualDrawingProperties = nvdp; nvpp.NonVisualPictureDrawingProperties = nvpdp; var stretch = new Stretch(); stretch.FillRectangle = new FillRectangle(); var blipFill = new BlipFill(); var blip = new Blip(); blip.Embed = dp.GetIdOfPart(imgp); blip.CompressionState = BlipCompressionValues.Print; blipFill.Blip = blip; blipFill.SourceRectangle = new SourceRectangle(); blipFill.Append(stretch); var t2d = new Transform2D(); var offset = new Offset(); offset.X = 0; offset.Y = 0; t2d.Offset = offset; //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML //http://stackoverflow.com/questions/1341930/pixel-to-centimeter //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c var extents = new Extents(); extents.Cx = bm.Width * (long)(914400 / bm.HorizontalResolution); extents.Cy = bm.Height * (long)(914400 / bm.VerticalResolution); bm.Dispose(); t2d.Extents = extents; var sp = new ShapeProperties(); sp.BlackWhiteMode = BlackWhiteModeValues.Auto; sp.Transform2D = t2d; var prstGeom = new PresetGeometry(); prstGeom.Preset = ShapeTypeValues.Rectangle; prstGeom.AdjustValueList = new AdjustValueList(); sp.Append(prstGeom); sp.Append(new NoFill()); var picture = new Picture(); picture.NonVisualPictureProperties = nvpp; picture.BlipFill = blipFill; picture.ShapeProperties = sp; var pos = new Position(); pos.X = 0; pos.Y = 0; var ext = new Extent(); ext.Cx = extents.Cx; ext.Cy = extents.Cy; var anchor = new AbsoluteAnchor(); anchor.Position = pos; anchor.Extent = ext; anchor.Append(picture); anchor.Append(new ClientData()); var wsd = new WorksheetDrawing(); wsd.Append(anchor); drawing = new Drawing(); drawing.Id = dp.GetIdOfPart(imgp); wsd.Save(dp); return drawing; }
static void InsertImageTest(Sheet sheet, WorksheetPart sheetPart) { var filepath = System.IO.Path.GetFullPath(@"..\..\test.jpg"); int widthPx = 100, heightPx = 100; int rowIndex = 2, colomnIndex = 3; int rowOffsetPx = 5, columnOffsetPx = 10; var imageType = ImagePartType.Jpeg; var noChangeAspect = true; var noCrop = false; var noMove = false; var noResize = false; var noRotation = false; var noSelection = false; float imageResX, imageResY; using (var bmp = Image.FromFile(filepath) as Bitmap) { imageResX = bmp.HorizontalResolution; imageResY = bmp.VerticalResolution; } var widthEmu = CalcEmuScale(widthPx, imageResX); var heightEmu = CalcEmuScale(heightPx, imageResY); var columnOffsetEmu = CalcEmuScale(columnOffsetPx, imageResY); var rowOffsetEmu = CalcEmuScale(rowOffsetPx, imageResX); var drawingsPart = sheetPart.DrawingsPart ?? sheetPart.AddNewPart<DrawingsPart>(); if (!sheetPart.Worksheet.ChildElements.OfType<Drawing>().Any()) { sheetPart.Worksheet.Append(new Drawing() { Id = sheetPart.GetIdOfPart(drawingsPart) }); } if (drawingsPart.WorksheetDrawing == null) { drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing(); } var sheetDrawing = drawingsPart.WorksheetDrawing; var imagePart = drawingsPart.AddImagePart(imageType); using (var stream = new FileStream(filepath, FileMode.Open)) { imagePart.FeedData(stream); } var nvps = sheetDrawing.Descendants<Xdr.NonVisualDrawingProperties>(); var nvpId = (nvps.Count() > 0) ? (UInt32Value)nvps.Max(prop => prop.Id.Value) + 1 : 1U; var pictureLocks = new A.PictureLocks() { NoChangeAspect = noChangeAspect, NoCrop = noCrop, NoMove = noMove, NoResize = noResize, NoRotation = noRotation, NoSelection = noSelection, }; var oneCellAnchor = new Xdr.OneCellAnchor( new Xdr.FromMarker() { ColumnId = new Xdr.ColumnId((colomnIndex - 1).ToString()), RowId = new Xdr.RowId((rowIndex - 1).ToString()), ColumnOffset = new Xdr.ColumnOffset(columnOffsetEmu.ToString()), RowOffset = new Xdr.RowOffset(rowOffsetEmu.ToString()), }, new Xdr.Extent() { Cx = widthEmu, Cy = heightEmu, }, new Xdr.Picture( new Xdr.NonVisualPictureProperties( new Xdr.NonVisualDrawingProperties() { Id = nvpId, Name = Path.GetFileName(filepath), Description = filepath, }, new Xdr.NonVisualPictureDrawingProperties(pictureLocks) ), new Xdr.BlipFill( new A.Blip() { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = A.BlipCompressionValues.Print }, new A.Stretch(new A.FillRectangle()) ), new Xdr.ShapeProperties( new A.Transform2D( new A.Offset() { X = 0, Y = 0, }, new A.Extents() { Cx = widthEmu, Cy = heightEmu } ), new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle } ) ), new Xdr.ClientData()); sheetDrawing.Append(oneCellAnchor); var errors = new DocumentFormat.OpenXml.Validation.OpenXmlValidator().Validate(sheetPart); foreach (var err in errors) { Console.WriteLine(err.Description); } if(errors.Count() > 0) { Console.Read(); } }
private static void GeneratePicture(WorksheetPart worksheetPart, IXLPicture picture) { if (!File.Exists(picture.FilePath)) { throw new ArgumentException("image file not found: " + picture.FilePath); } var drawingsPart = worksheetPart.DrawingsPart ?? worksheetPart.AddNewPart<DrawingsPart>(); var worksheet = worksheetPart.Worksheet; if (!worksheet.ChildElements.OfType<Drawing>().Any()) { var drawing = new Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart), }; worksheet.InsertBefore(drawing, worksheet.Last()); } if (drawingsPart.WorksheetDrawing == null) { drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing(); } var sheetDrawing = drawingsPart.WorksheetDrawing; var imagePart = drawingsPart.AddImagePart(XlPictureTypeConverter.Convert(picture.Type)); int imageWidth, imageHeight; float imageResX, imageResY; using (var stream = new FileStream(picture.FilePath, FileMode.Open)) { using (var bitmap = new System.Drawing.Bitmap(stream)) { imageWidth = bitmap.Width; imageHeight = bitmap.Height; imageResX = bitmap.HorizontalResolution; imageResY = bitmap.VerticalResolution; } stream.Seek(0, SeekOrigin.Begin); imagePart.FeedData(stream); } if (picture.WidthPx == default(int)) { picture.WidthPx = imageWidth; } if (picture.HeightPx == default(int)) { picture.HeightPx = imageHeight; } var nvps = sheetDrawing.Descendants<Xdr.NonVisualDrawingProperties>(); var nvpId = (nvps.Count() > 0) ? (UInt32Value)nvps.Max(prop => prop.Id.Value) + 1 : 1U; var pictureLocks = new A.PictureLocks() { NoChangeAspect = !picture.CanUserChangeAspect, NoCrop = !picture.CanUserCrop, NoMove = !picture.CanUserMove, NoResize = !picture.CanUserResize, NoRotation = !picture.CanUserRotate, NoSelection = !picture.CanUserSelect, }; OpenXmlCompositeElement cellAnchor; { var markers = picture.GetMarkers(); var markerCount = markers.Count(); if (markerCount == 0) { cellAnchor = new Xdr.AbsoluteAnchor(new Xdr.Position() { X = 0, Y = 0 }); } else if (markerCount == 1) { cellAnchor = new Xdr.OneCellAnchor( XLMarkerConverter.Convert<Xdr.FromMarker>( markers.First(), (x) => CalcEmuScale(x, imageResX), (y) => CalcEmuScale(y, imageResY))); } else if (markerCount == 2) { var from = markers.First(); var to = markers.Last(); if ( from.RowIndex > to.RowIndex || from.ColumnIndex > to.ColumnIndex || (from.RowIndex == to.RowIndex && from.RowOffsetPx > to.RowOffsetPx) || (from.ColumnIndex == to.ColumnIndex && from.ColumnOffsetPx > to.ColumnOffsetPx) ) { throw new ArgumentException("positions of second marker must be larger than those of first marker"); } cellAnchor = new Xdr.TwoCellAnchor( XLMarkerConverter.Convert<Xdr.FromMarker>( from, (x) => CalcEmuScale(x, imageResX), (y) => CalcEmuScale(y, imageResY)), XLMarkerConverter.Convert<Xdr.ToMarker>( to, (x) => CalcEmuScale(x, imageResX), (y) => CalcEmuScale(y, imageResY))); } else { throw new ArgumentException("invalid markers count: " + markers.Count()); } } var widthEmu = CalcEmuScale(picture.WidthPx, imageResX); var heightEmu = CalcEmuScale(picture.HeightPx, imageResY); cellAnchor.Append( new Xdr.Extent() { Cx = widthEmu, Cy = heightEmu, }, new Xdr.Picture( new Xdr.NonVisualPictureProperties( new Xdr.NonVisualDrawingProperties() { Id = nvpId, Name = (!string.IsNullOrEmpty(picture.Name)) ? picture.Name : nvpId.ToString() + "_" + Path.GetFileNameWithoutExtension(picture.FilePath), Description = (!string.IsNullOrEmpty(picture.Description)) ? picture.Description : nvpId.ToString() + "_" + Path.GetFileNameWithoutExtension(picture.FilePath), }, new Xdr.NonVisualPictureDrawingProperties(pictureLocks) ), new Xdr.BlipFill( new A.Blip() { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = A.BlipCompressionValues.Print }, new A.Stretch(new A.FillRectangle()) ), new Xdr.ShapeProperties( new A.Transform2D( new A.Offset() { X = 0, Y = 0, }, new A.Extents() { Cx = widthEmu, Cy = heightEmu } ), new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle } ) ), new Xdr.ClientData() ); sheetDrawing.Append(cellAnchor); #if false var errors = new DocumentFormat.OpenXml.Validation.OpenXmlValidator().Validate(worksheetPart); foreach (var error in errors) { Console.WriteLine(error.Description); } if (errors.Count() > 0) Console.Read(); #endif }