diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index d73fbfd..3a8443f 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -81,6 +81,8 @@ + + diff --git a/ClosedXML/Excel/Drawings/XLPicture.cs b/ClosedXML/Excel/Drawings/XLPicture.cs index d591f8f..c87d54c 100644 --- a/ClosedXML/Excel/Drawings/XLPicture.cs +++ b/ClosedXML/Excel/Drawings/XLPicture.cs @@ -12,11 +12,45 @@ [DebuggerDisplay("{Name}")] internal class XLPicture : IXLPicture { + private static IDictionary FormatMap; private readonly IXLWorksheet _worksheet; - private Int32 height; private Int32 width; + static XLPicture() + { + var properties = typeof(ImageFormat).GetProperties(BindingFlags.Static | BindingFlags.Public); + FormatMap = Enum.GetValues(typeof(XLPictureFormat)) + .Cast() + .Where(pf => properties.Any(pi => pi.Name.Equals(pf.ToString(), StringComparison.OrdinalIgnoreCase))) + .ToDictionary( + pf => pf, + pf => properties.Single(pi => pi.Name.Equals(pf.ToString(), StringComparison.OrdinalIgnoreCase)).GetValue(null, null) as ImageFormat + ); + } + + internal XLPicture(IXLWorksheet worksheet, Stream stream) + : this(worksheet) + { + if (stream == null) throw new ArgumentNullException(nameof(stream)); + + this.ImageStream = new MemoryStream(); + { + stream.Position = 0; + stream.CopyTo(ImageStream); + ImageStream.Seek(0, SeekOrigin.Begin); + + using (var bitmap = new Bitmap(ImageStream)) + { + if (FormatMap.Values.Select(f => f.Guid).Contains(bitmap.RawFormat.Guid)) + this.Format = FormatMap.Single(f => f.Value.Guid.Equals(bitmap.RawFormat.Guid)).Key; + + DeduceDimensionsFromBitmap(bitmap); + } + ImageStream.Seek(0, SeekOrigin.Begin); + } + } + internal XLPicture(IXLWorksheet worksheet, Stream stream, XLPictureFormat format) : this(worksheet) { @@ -31,9 +65,11 @@ using (var bitmap = new Bitmap(ImageStream)) { - var expectedFormat = typeof(System.Drawing.Imaging.ImageFormat).GetProperty(this.Format.ToString()).GetValue(null, null) as System.Drawing.Imaging.ImageFormat; - if (expectedFormat.Guid != bitmap.RawFormat.Guid) - throw new ArgumentException("The picture format in the stream and the parameter don't match"); + if (FormatMap.ContainsKey(this.Format)) + { + if (FormatMap[this.Format].Guid != bitmap.RawFormat.Guid) + throw new ArgumentException("The picture format in the stream and the parameter don't match"); + } DeduceDimensionsFromBitmap(bitmap); } @@ -50,13 +86,11 @@ ImageStream.Seek(0, SeekOrigin.Begin); DeduceDimensionsFromBitmap(bitmap); - var formats = typeof(ImageFormat).GetProperties(BindingFlags.Static | BindingFlags.Public) - .Where(pi => (pi.GetValue(null, null) as ImageFormat).Guid.Equals(bitmap.RawFormat.Guid)); - + var formats = FormatMap.Where(f => f.Value.Guid.Equals(bitmap.RawFormat.Guid)); if (!formats.Any() || formats.Count() > 1) throw new ArgumentException("Unsupported or unknown image format in bitmap"); - this.Format = Enum.Parse(typeof(XLPictureFormat), formats.Single().Name, true).CastTo(); + this.Format = formats.Single().Key; } private XLPicture(IXLWorksheet worksheet) @@ -114,8 +148,11 @@ } public String Name { get; set; } + public Int32 OriginalHeight { get; private set; } + public Int32 OriginalWidth { get; private set; } + public XLPicturePlacement Placement { get; set; } public Int32 Top @@ -164,6 +201,8 @@ internal IDictionary Markers { get; private set; } + internal String RelId { get; set; } + public void Dispose() { this.ImageStream.Dispose(); @@ -256,6 +295,28 @@ return this; } + private static ImageFormat FromMimeType(string mimeType) + { + var guid = ImageCodecInfo.GetImageDecoders().FirstOrDefault(c => c.MimeType.Equals(mimeType, StringComparison.OrdinalIgnoreCase))?.FormatID; + if (!guid.HasValue) return null; + var property = typeof(System.Drawing.Imaging.ImageFormat).GetProperties(BindingFlags.Public | BindingFlags.Static) + .FirstOrDefault(pi => (pi.GetValue(null, null) as ImageFormat).Guid.Equals(guid.Value)); + + if (property == null) return null; + return (property.GetValue(null, null) as ImageFormat); + } + + private static string GetMimeType(Image i) + { + var imgguid = i.RawFormat.Guid; + foreach (ImageCodecInfo codec in ImageCodecInfo.GetImageDecoders()) + { + if (codec.FormatID == imgguid) + return codec.MimeType; + } + return "image/unknown"; + } + private void DeduceDimensionsFromBitmap(Bitmap bitmap) { this.OriginalWidth = bitmap.Width; @@ -265,4 +326,4 @@ this.height = bitmap.Height; } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/Excel/IXLWorksheet.cs index a4ce4e0..cee3feb 100644 --- a/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/Excel/IXLWorksheet.cs @@ -435,6 +435,10 @@ IList Pictures { get; } + Drawings.IXLPicture AddPicture(Stream stream); + + Drawings.IXLPicture AddPicture(Stream stream, String name); + Drawings.IXLPicture AddPicture(Stream stream, XLPictureFormat format); Drawings.IXLPicture AddPicture(Stream stream, XLPictureFormat format, String name); diff --git a/ClosedXML/Excel/XLWorkbook_ImageHandling.cs b/ClosedXML/Excel/XLWorkbook_ImageHandling.cs new file mode 100644 index 0000000..0533e1e --- /dev/null +++ b/ClosedXML/Excel/XLWorkbook_ImageHandling.cs @@ -0,0 +1,49 @@ +using DocumentFormat.OpenXml; +using DocumentFormat.OpenXml.Drawing.Spreadsheet; +using DocumentFormat.OpenXml.Packaging; +using System; +using System.Linq; + +using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet; + +namespace ClosedXML.Excel +{ + public partial class XLWorkbook + { + public static OpenXmlElement GetAnchorFromImageId(WorksheetPart worksheetPart, string relId) + { + var drawingsPart = worksheetPart.DrawingsPart; + var matchingAnchor = drawingsPart.WorksheetDrawing + .Where(wsdr => wsdr.Descendants() + .Any(x => x?.Blip?.Embed?.Value.Equals(relId) ?? false) + ); + + if (!matchingAnchor.Any()) + return null; + else + return matchingAnchor.First(); + } + + public static OpenXmlElement GetAnchorFromImageIndex(WorksheetPart worksheetPart, Int32 index) + { + var drawingsPart = worksheetPart.DrawingsPart; + var matchingAnchor = drawingsPart.WorksheetDrawing + .Where(wsdr => wsdr.Descendants() + .Any(x => x.Id.Value.Equals(Convert.ToUInt32(index + 1))) + ); + + if (!matchingAnchor.Any()) + return null; + else + return matchingAnchor.First(); + } + + public static NonVisualDrawingProperties GetPropertiesFromImageIndex(WorksheetPart worksheetPart, Int32 index) + { + var drawingsPart = worksheetPart.DrawingsPart; + return drawingsPart.WorksheetDrawing + .Descendants() + .FirstOrDefault(x => x.Id.Value.Equals(Convert.ToUInt32(index + 1))); + } + } +} diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index e3f3124..e74d134 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -14,6 +14,7 @@ using System.Xml.Linq; using Ap = DocumentFormat.OpenXml.ExtendedProperties; using Op = DocumentFormat.OpenXml.CustomProperties; +using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet; #endregion @@ -22,6 +23,7 @@ #region using Ap; + using Drawings; using Op; using System.Drawing; @@ -337,6 +339,8 @@ #endregion + LoadDrawings(wsPart, ws); + #region LoadComments if (wsPart.WorksheetCommentsPart != null) @@ -616,6 +620,72 @@ #endregion } + private void LoadDrawings(WorksheetPart wsPart, IXLWorksheet ws) + { + if (wsPart.DrawingsPart != null) + { + var drawingsPart = wsPart.DrawingsPart; + + var imageParts = drawingsPart.GetPartsOfType(); + for (int i = 0; i < imageParts.Count(); i++) + { + var imagePart = imageParts.ElementAt(i); + var imgId = drawingsPart.GetIdOfPart(imagePart); + using (var stream = imagePart.GetStream()) + { + var anchor = GetAnchorFromImageId(wsPart, imgId); + var vsdp = GetPropertiesFromImageIndex(wsPart, i); + + var picture = ws.AddPicture(stream, vsdp.Name) as XLPicture; + picture.RelId = imgId; + + Xdr.ShapeProperties spPr = anchor.Descendants().First(); + picture.Placement = XLPicturePlacement.FreeFloating; + picture.Width = ConvertFromEnglishMetricUnits(spPr.Transform2D.Extents.Cx, GraphicsUtils.Graphics.DpiX); + picture.Height = ConvertFromEnglishMetricUnits(spPr.Transform2D.Extents.Cy, GraphicsUtils.Graphics.DpiY); + + if (anchor is Xdr.OneCellAnchor) + { + var oneCellAnchor = anchor as Xdr.OneCellAnchor; + var from = LoadMarker(ws, oneCellAnchor.FromMarker); + picture.MoveTo(from.Address, from.Offset); + } + else if (anchor is Xdr.TwoCellAnchor) + { + var twoCellAnchor = anchor as Xdr.TwoCellAnchor; + var from = LoadMarker(ws, twoCellAnchor.FromMarker); + var to = LoadMarker(ws, twoCellAnchor.ToMarker); + picture.MoveTo(from.Address, from.Offset, to.Address, to.Offset); + } + else if (anchor is Xdr.AbsoluteAnchor) + { + var absoluteAnchor = anchor as Xdr.AbsoluteAnchor; + picture.MoveTo( + ConvertFromEnglishMetricUnits(absoluteAnchor.Position.X.Value, GraphicsUtils.Graphics.DpiX), + ConvertFromEnglishMetricUnits(absoluteAnchor.Position.Y.Value, GraphicsUtils.Graphics.DpiY) + ); + } + } + } + } + } + + private static Int32 ConvertFromEnglishMetricUnits(long emu, float resolution) + { + return Convert.ToInt32(emu * resolution / 914400); + } + + private static IXLMarker LoadMarker(IXLWorksheet ws, Xdr.MarkerType marker) + { + return new XLMarker( + ws.Cell(Convert.ToInt32(marker.RowId.InnerText) + 1, Convert.ToInt32(marker.ColumnId.InnerText) + 1).Address, + new Point( + ConvertFromEnglishMetricUnits(Convert.ToInt32(marker.ColumnOffset.InnerText), GraphicsUtils.Graphics.DpiX), + ConvertFromEnglishMetricUnits(Convert.ToInt32(marker.RowOffset.InnerText), GraphicsUtils.Graphics.DpiY) + ) + ); + } + #region Comment Helpers private XDocument GetCommentVmlFile(WorksheetPart wsPart) diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 457ecbc..886655c 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -1,3 +1,4 @@ +using ClosedXML.Extensions; using ClosedXML.Utils; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.CustomProperties; @@ -2509,9 +2510,9 @@ // http://polymathprogrammer.com/2009/10/22/english-metric-units-and-open-xml/ // http://archive.oreilly.com/pub/post/what_is_an_emu.html // https://en.wikipedia.org/wiki/Office_Open_XML_file_formats#DrawingML - private static long ConvertToEnglishMetricUnits(long pixels, float resolution) + private static Int64 ConvertToEnglishMetricUnits(Int32 pixels, Double resolution) { - return (long)(914400 * pixels / resolution); + return Convert.ToInt64(914400 * pixels / resolution); } private static void AddPictureAnchor(WorksheetPart worksheetPart, Drawings.IXLPicture picture, SaveContext context) @@ -2533,7 +2534,12 @@ worksheetDrawing.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); ///////// - var imagePart = drawingsPart.AddImagePart(pic.Format.ToOpenXml(), context.RelIdGenerator.GetNext(RelType.Workbook)); + // Overwrite actual image binary data + ImagePart imagePart; + if (drawingsPart.HasPartWithId(pic.RelId)) + imagePart = drawingsPart.GetPartById(pic.RelId) as ImagePart; + else + imagePart = drawingsPart.AddImagePart(pic.Format.ToOpenXml(), context.RelIdGenerator.GetNext(RelType.Workbook)); using (var stream = new MemoryStream()) { @@ -2541,14 +2547,17 @@ stream.Seek(0, SeekOrigin.Begin); imagePart.FeedData(stream); } + ///////// + + // Clear current anchors + var existingAnchor = GetAnchorFromImageId(worksheetPart, pic.RelId); + if (existingAnchor != null) + worksheetDrawing.RemoveChild(existingAnchor); var extentsCx = ConvertToEnglishMetricUnits(pic.Width, GraphicsUtils.Graphics.DpiX); var extentsCy = ConvertToEnglishMetricUnits(pic.Height, GraphicsUtils.Graphics.DpiY); - var nvps = worksheetDrawing.Descendants(); - var nvpId = nvps.Any() ? - (UInt32Value)worksheetDrawing.Descendants().Max(p => p.Id.Value) + 1 : - 1U; + var nvpId = Convert.ToUInt32(worksheetDrawing.DrawingsPart.ImageParts.ToList().IndexOf(imagePart) + 1); Xdr.FromMarker fMark; Xdr.ToMarker tMark; @@ -4751,7 +4760,7 @@ AddPictureAnchor(worksheetPart, pic, context); } - if (xlWorksheet.Pictures.Any()) + if (xlWorksheet.Pictures.Any() && !worksheetPart.Worksheet.OfType().Any()) { var worksheetDrawing = new Drawing { Id = worksheetPart.GetIdOfPart(worksheetPart.DrawingsPart) }; worksheetDrawing.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); @@ -4986,4 +4995,4 @@ #endregion GenerateWorksheetPartContent } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 9263993..ade653e 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -577,6 +577,30 @@ Internals.MergedRanges.ForEach( kp => targetSheet.Internals.MergedRanges.Add(targetSheet.Range(kp.RangeAddress.ToString()))); + foreach (var picture in Pictures) + { + var newPic = targetSheet.AddPicture(picture.ImageStream, picture.Format, picture.Name) + .WithPlacement(picture.Placement) + .WithSize(picture.Width, picture.Height); + + switch (picture.Placement) + { + case XLPicturePlacement.FreeFloating: + newPic.MoveTo(picture.Left, picture.Top); + break; + case XLPicturePlacement.Move: + var newAddress = new XLAddress(targetSheet, picture.TopLeftCellAddress.RowNumber, picture.TopLeftCellAddress.ColumnNumber, false, false); + newPic.MoveTo(newAddress, picture.GetOffset(XLMarkerPosition.TopLeft)); + break; + case XLPicturePlacement.MoveAndSize: + var newFromAddress = new XLAddress(targetSheet, picture.TopLeftCellAddress.RowNumber, picture.TopLeftCellAddress.ColumnNumber, false, false); + var newToAddress = new XLAddress(targetSheet, picture.BottomRightCellAddress.RowNumber, picture.BottomRightCellAddress.ColumnNumber, false, false); + + newPic.MoveTo(newFromAddress, picture.GetOffset(XLMarkerPosition.TopLeft), newToAddress, picture.GetOffset(XLMarkerPosition.BottomRight)); + break; + } + } + foreach (IXLNamedRange r in NamedRanges) { var ranges = new XLRanges(); @@ -1507,6 +1531,21 @@ return $"Picture {pictureNumber}"; } + public IXLPicture AddPicture(Stream stream) + { + var picture = new XLPicture(this, stream); + Pictures.Add(picture); + picture.Name = GetNextPictureName(); + return picture; + } + + public IXLPicture AddPicture(Stream stream, string name) + { + var picture = AddPicture(stream); + picture.Name = name; + return picture; + } + public Drawings.IXLPicture AddPicture(Stream stream, XLPictureFormat format) { var picture = new XLPicture(this, stream, format); @@ -1554,5 +1593,6 @@ picture.Name = name; return picture; } + } } diff --git a/ClosedXML/Extensions/OpenXmlPartContainerExtensions.cs b/ClosedXML/Extensions/OpenXmlPartContainerExtensions.cs new file mode 100644 index 0000000..db2424f --- /dev/null +++ b/ClosedXML/Extensions/OpenXmlPartContainerExtensions.cs @@ -0,0 +1,14 @@ +using DocumentFormat.OpenXml.Packaging; +using System; +using System.Linq; + +namespace ClosedXML.Extensions +{ + internal static class OpenXmlPartContainerExtensions + { + public static Boolean HasPartWithId(this OpenXmlPartContainer container, String relId) + { + return container.Parts.Any(p => p.RelationshipId.Equals(relId)); + } + } +} diff --git a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs index e0706a4..3750ca3 100644 --- a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs +++ b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs @@ -180,5 +180,19 @@ } } } + + [Test] + public void CanLoadFileWithImagesAndCopyImagesToNewSheet() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\ImageHandling\ImageAnchors.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + Assert.AreEqual(2, ws.Pictures.Count); + + var copy = ws.CopyTo("NewSheet"); + Assert.AreEqual(2, copy.Pictures.Count); + } + } } } diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 69966fb..5d53a55 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel; +using ClosedXML.Excel.Drawings; using NUnit.Framework; using System.Collections.Generic; using System.IO; @@ -118,5 +119,38 @@ } } } + + [Test] + public void CanLoadFileWithImagesWithCorrectAnchorTypes() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\ImageHandling\ImageAnchors.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + Assert.AreEqual(2, ws.Pictures.Count); + Assert.AreEqual(XLPicturePlacement.FreeFloating, ws.Pictures.First().Placement); + Assert.AreEqual(XLPicturePlacement.Move, ws.Pictures.Skip(1).First().Placement); + + var ws2 = wb.Worksheets.Skip(1).First(); + Assert.AreEqual(1, ws2.Pictures.Count); + Assert.AreEqual(XLPicturePlacement.MoveAndSize, ws2.Pictures.First().Placement); + } + } + + [Test] + public void CanLoadFileWithImagesWithCorrectImageType() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\ImageHandling\ImageFormats.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + Assert.AreEqual(1, ws.Pictures.Count); + Assert.AreEqual(XLPictureFormat.Jpeg, ws.Pictures.First().Format); + + var ws2 = wb.Worksheets.Skip(1).First(); + Assert.AreEqual(1, ws2.Pictures.Count); + Assert.AreEqual(XLPictureFormat.Png, ws2.Pictures.First().Format); + } + } } }