public static void AddImage(WorksheetPart worksheetPart, string imageFilePath, int rowNumber, int colNumber) { Bitmap bm = new Bitmap(imageFilePath); DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents(); var extentsCx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution); var extentsCy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution); bm.Dispose(); var colOffset = 0; var rowOffset = 0; //int colNumber = 5; //int rowNumber = 10; var drawingsPart = worksheetPart.DrawingsPart; var worksheetDrawing = drawingsPart.WorksheetDrawing; var imagePart = drawingsPart.AddImagePart(ImagePartType.Jpeg); using (var stream = new FileStream(imageFilePath, FileMode.Open)) { imagePart.FeedData(stream); } var nvps = worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>(); var nvpId = nvps.Count() > 0 ? (UInt32Value)worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1 : 1U; var oneCellAnchor = new Xdr.OneCellAnchor( new Xdr.FromMarker { ColumnId = new Xdr.ColumnId((colNumber - 1).ToString()), RowId = new Xdr.RowId((rowNumber - 1).ToString()), ColumnOffset = new Xdr.ColumnOffset(colOffset.ToString()), RowOffset = new Xdr.RowOffset(rowOffset.ToString()) }, new Xdr.Extent { Cx = extentsCx, Cy = extentsCy }, new Xdr.Picture( new Xdr.NonVisualPictureProperties( new Xdr.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imageFilePath }, new Xdr.NonVisualPictureDrawingProperties(new A.PictureLocks { NoChangeAspect = true }) ), 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 = extentsCx, Cy = extentsCy } ), new A.PresetGeometry { Preset = A.ShapeTypeValues.Rectangle } ) ), new Xdr.ClientData() ); worksheetDrawing.Append(oneCellAnchor); //foreach (ImagePart i in worksheetPart.DrawingsPart.ImageParts) //{ // var rId = worksheetPart.DrawingsPart.GetIdOfPart(i); // Console.WriteLine("The rId of this Image is '{0}'", rId); // //Stream stream = i.GetStream(); // //long length = stream.Length; // //byte[] byteStream = new byte[length]; // //stream.Read(byteStream, 0, (int)length); // //var imageAsString = Convert.ToBase64String(byteStream); // if (rId == "rId5") // { // using (var stream1 = new FileStream(logoFilePath, FileMode.Open)) // { // i.FeedData(stream1); // } // } // if (rId == "rId3") // { // using (var stream1 = new FileStream(imageFilePath, FileMode.Open)) // { // i.FeedData(stream1); // } // } //} }
private Xdr.OneCellAnchor createImageAnchor(ImageDetails details) { var image = _images[details.ImageIndex]; long imgWidth = convertPixelsToEMUs(image.Width, image.HorizontalResolution); long imgHeight = convertPixelsToEMUs(image.Height, image.VerticalResolution); // ReSharper disable once CompareOfFloatsByEqualityOperator if (details.ImageScaleFactor != 0) { imgWidth = (long)(imgWidth * details.ImageScaleFactor); imgHeight = (long)(imgHeight * details.ImageScaleFactor); } // there is still work to be done here since the images can get fuzzy and there is no indent // but, since you cannot have thousands of cell images(which we need) without performance penalty in the Excel application itselft // we can easily and quickly generate such excel files, but Excel has trouble reading and rendering them // so for now this stay as is Xdr.OneCellAnchor oneCellAnchor = new Xdr.OneCellAnchor(); Xdr.FromMarker fromMarker1 = new Xdr.FromMarker(); Xdr.ColumnId columnId1 = new Xdr.ColumnId(); columnId1.Text = (details.Column - 1).ToString(); Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset(); columnOffset1.Text = "0"; Xdr.RowId rowId1 = new Xdr.RowId(); rowId1.Text = (details.Row - 1).ToString(); Xdr.RowOffset rowOffset1 = new Xdr.RowOffset(); rowOffset1.Text = "0"; fromMarker1.Append(columnId1); fromMarker1.Append(columnOffset1); fromMarker1.Append(rowId1); fromMarker1.Append(rowOffset1); var anchorExtent = new Xdr.Extent() { Cx = imgWidth, Cy = imgWidth }; //Xdr.ToMarker toMarker1 = new Xdr.ToMarker(); //Xdr.ColumnId columnId2 = new Xdr.ColumnId(); //columnId2.Text = (details.Column-1).ToString(); //Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset(); //columnOffset2.Text = "203175"; //Xdr.RowId rowId2 = new Xdr.RowId(); //rowId2.Text = (details.Row -1).ToString(); ; //Xdr.RowOffset rowOffset2 = new Xdr.RowOffset(); //rowOffset2.Text = "12675"; //toMarker1.Append(columnId2); //toMarker1.Append(columnOffset2); //toMarker1.Append(rowId2); //toMarker1.Append(rowOffset2); Xdr.Picture picture1 = new Xdr.Picture(); Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties(); UInt32Value id = (UInt32Value)_pictureCounter; _pictureCounter++; Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = id, Name = "Picture " + id }; Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new Xdr.NonVisualPictureDrawingProperties(); A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true }; nonVisualPictureDrawingProperties1.Append(pictureLocks1); nonVisualPictureProperties1.Append(nonVisualDrawingProperties1); nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1); Xdr.BlipFill blipFill1 = new Xdr.BlipFill(); A.Blip blip1 = new A.Blip() { Embed = "image" + details.ImageIndex }; blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList(); A.BlipExtension blipExtension1 = new A.BlipExtension() { Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" }; A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false }; useLocalDpi1.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main"); blipExtension1.Append(useLocalDpi1); blipExtensionList1.Append(blipExtension1); blip1.Append(blipExtensionList1); A.Stretch stretch1 = new A.Stretch(); A.FillRectangle fillRectangle1 = new A.FillRectangle(); stretch1.Append(fillRectangle1); blipFill1.Append(blip1); blipFill1.Append(stretch1); Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties(); A.Transform2D transform2D1 = new A.Transform2D(); A.Offset offset1 = new A.Offset() { X = 0, Y = 0 }; A.Extents extents1 = new A.Extents() { Cx = imgWidth, Cy = imgHeight }; transform2D1.Append(offset1); transform2D1.Append(extents1); A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle }; A.AdjustValueList adjustValueList1 = new A.AdjustValueList(); presetGeometry1.Append(adjustValueList1); shapeProperties1.Append(transform2D1); shapeProperties1.Append(presetGeometry1); picture1.Append(nonVisualPictureProperties1); picture1.Append(blipFill1); picture1.Append(shapeProperties1); Xdr.ClientData clientData1 = new Xdr.ClientData(); oneCellAnchor.Append(fromMarker1); oneCellAnchor.Append(anchorExtent); //twoCellAnchor1.Append(toMarker1); oneCellAnchor.Append(picture1); oneCellAnchor.Append(clientData1); return(oneCellAnchor); }
public static void AddImage(WorksheetPart worksheetPart, Stream imageStream, string imgDesc, int colNumber, int rowNumber) { // We need the image stream more than once, thus we create a memory copy MemoryStream imageMemStream = new MemoryStream(); imageStream.Position = 0; imageStream.CopyTo(imageMemStream); imageStream.Position = 0; var drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(); if (!worksheetPart.Worksheet.ChildElements.OfType <Drawing>().Any()) { worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) }); } if (drawingsPart.WorksheetDrawing == null) { drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing(); } var worksheetDrawing = drawingsPart.WorksheetDrawing; Bitmap bm = new Bitmap(imageMemStream); var imagePart = drawingsPart.AddImagePart(GetImagePartTypeByBitmap(bm)); imagePart.FeedData(imageStream); A.Extents extents = new A.Extents(); var extentsCx = bm.Width * (long)(914400 / bm.HorizontalResolution); var extentsCy = bm.Height * (long)(914400 / bm.VerticalResolution); bm.Dispose(); var colOffset = 0; var rowOffset = 0; var nvps = worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>(); var nvpId = nvps.Count() > 0 ? (UInt32Value)worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1 : 1U; var oneCellAnchor = new Xdr.OneCellAnchor( new Xdr.FromMarker { ColumnId = new Xdr.ColumnId((colNumber - 1).ToString()), RowId = new Xdr.RowId((rowNumber - 1).ToString()), ColumnOffset = new Xdr.ColumnOffset(colOffset.ToString()), RowOffset = new Xdr.RowOffset(rowOffset.ToString()) }, new Xdr.Extent { Cx = extentsCx, Cy = extentsCy }, new Xdr.Picture( new Xdr.NonVisualPictureProperties( new Xdr.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imgDesc }, new Xdr.NonVisualPictureDrawingProperties(new A.PictureLocks { NoChangeAspect = true }) ), 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 = extentsCx, Cy = extentsCy } ), new A.PresetGeometry { Preset = A.ShapeTypeValues.Rectangle } ) ), new Xdr.ClientData() ); worksheetDrawing.Append(oneCellAnchor); }
public void CreatePackage(string sFile, string imageFileName) { // Create a spreadsheet document by supplying the filepath. SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument. Create(sFile, SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document. WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook. AppendChild <Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Mentor" }; sheets.Append(sheet); var drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(); if (!worksheetPart.Worksheet.ChildElements.OfType <Drawing>().Any()) { worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) }); } if (drawingsPart.WorksheetDrawing == null) { drawingsPart.WorksheetDrawing = new WorksheetDrawing(); } var worksheetDrawing = drawingsPart.WorksheetDrawing; var imagePart = drawingsPart.AddImagePart(ImagePartType.Png); using (var stream = new FileStream(imageFileName, FileMode.Open)) { imagePart.FeedData(stream); } Bitmap bm = new Bitmap(imageFileName); DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents(); var extentsCx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution); var extentsCy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution); bm.Dispose(); var colOffset = 0; var rowOffset = 0; int colNumber = 1; int rowNumber = 1; var nvps = worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>(); var nvpId = nvps.Count() > 0 ? (UInt32Value)worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1 : 1U; var oneCellAnchor = new Xdr.OneCellAnchor( new Xdr.FromMarker { ColumnId = new Xdr.ColumnId((colNumber - 1).ToString()), RowId = new Xdr.RowId((rowNumber - 1).ToString()), ColumnOffset = new Xdr.ColumnOffset(colOffset.ToString()), RowOffset = new Xdr.RowOffset(rowOffset.ToString()) }, new Xdr.Extent { Cx = extentsCx, Cy = extentsCy }, new Xdr.Picture( new Xdr.NonVisualPictureProperties( new Xdr.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imageFileName }, new Xdr.NonVisualPictureDrawingProperties(new A.PictureLocks { NoChangeAspect = true }) ), 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 = extentsCx, Cy = extentsCy } ), new A.PresetGeometry { Preset = A.ShapeTypeValues.Rectangle } ) ), new Xdr.ClientData() ); worksheetDrawing.Append(oneCellAnchor); workbookpart.Workbook.Save(); spreadsheetDocument.Close(); }
public static void AddImage(WorksheetPart worksheetPart, Stream imageStream, string imgDesc, int colNumber, int rowNumber, int width, int height) { // Necesitamos la transmisión de imágenes más de una vez, así creamos una copia de memoria MemoryStream imageMemStream = new MemoryStream(); imageStream.Position = 0; imageStream.CopyTo(imageMemStream); imageStream.Position = 0; var drawingsPart = worksheetPart.DrawingsPart; if (drawingsPart == null) { drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(); } if (!worksheetPart.Worksheet.ChildElements.OfType <Drawing>().Any()) { worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) }); } if (drawingsPart.WorksheetDrawing == null) { drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing(); } var worksheetDrawing = drawingsPart.WorksheetDrawing; // Bitmap bm = new Bitmap(imageMemStream); Bitmap bm = new Bitmap(ConvertImage(imageMemStream, ImageFormat.Jpeg)); bm.SetResolution(60, 60); var imagePart = drawingsPart.AddImagePart(GetImagePartTypeByBitmap(bm)); //imagePart.FeedData(imageStream); imagePart.FeedData(ConvertImage(imageStream, ImageFormat.Jpeg)); A.Extents extents = new A.Extents(); // var extentsCx = bm.Width * (long)(914400 / bm.HorizontalResolution); //var extentsCy = bm.Height * (long)(914400 / bm.VerticalResolution); //dividir las medidas hecnas en el paint por 1.35 esa era la medida para //el programa //var extentsCx = width * (long)(914400 / bm.HorizontalResolution); //var extentsCy = height * (long)(914400 / bm.VerticalResolution); var extentsCx = width * (long)(571500 / bm.HorizontalResolution); var extentsCy = height * (long)(571500 / bm.VerticalResolution); bm.Dispose(); var colOffset = 0; var rowOffset = 0; var nvps = worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>(); var nvpId = nvps.Count() > 0 ? (UInt32Value)worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1 : 1U; var oneCellAnchor = new Xdr.OneCellAnchor( new Xdr.FromMarker { ColumnId = new Xdr.ColumnId((colNumber - 1).ToString()), RowId = new Xdr.RowId((rowNumber - 1).ToString()), ColumnOffset = new Xdr.ColumnOffset(colOffset.ToString()), RowOffset = new Xdr.RowOffset(rowOffset.ToString()) }, new Xdr.Extent { Cx = extentsCx, Cy = extentsCy }, new Xdr.Picture( new Xdr.NonVisualPictureProperties( new Xdr.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imgDesc }, new Xdr.NonVisualPictureDrawingProperties(new A.PictureLocks { NoChangeAspect = true }) ), 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 = extentsCx, Cy = extentsCy } ), new A.PresetGeometry { Preset = A.ShapeTypeValues.Rectangle } ) ), new Xdr.ClientData() ); worksheetDrawing.Append(oneCellAnchor); }
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 void WriteImageParts(DrawingsPart dp) { ImagePart imgp; Xdr.WorksheetDrawing wsd = dp.WorksheetDrawing; SLRowProperties rp; SLColumnProperties cp; #region Charts if (slws.Charts.Count > 0) { int FromAnchorRowIndex = 0; long FromAnchorRowOffset = 0; int FromAnchorColumnIndex = 0; long FromAnchorColumnOffset = 0; int ToAnchorRowIndex = 4; long ToAnchorRowOffset = 0; int ToAnchorColumnIndex = 4; long ToAnchorColumnOffset = 0; double fTemp = 0; ChartPart chartp; Xdr.GraphicFrame gf; foreach (Charts.SLChart Chart in slws.Charts) { chartp = dp.AddNewPart<ChartPart>(); chartp.ChartSpace = Chart.ToChartSpace(ref chartp); gf = new Xdr.GraphicFrame(); gf.Macro = string.Empty; gf.NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties(); gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties(); gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Id = slws.NextWorksheetDrawingId; ++slws.NextWorksheetDrawingId; gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Name = Chart.ChartName; // alt text for charts //...gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Description = ""; gf.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties(); gf.Transform = new Xdr.Transform(); gf.Transform.Offset = new A.Offset() { X = 0, Y = 0 }; gf.Transform.Extents = new A.Extents() { Cx = 0, Cy = 0 }; gf.Graphic = new A.Graphic(); gf.Graphic.GraphicData = new A.GraphicData(); gf.Graphic.GraphicData.Uri = SLConstants.NamespaceC; gf.Graphic.GraphicData.Append(new C.ChartReference() { Id = dp.GetIdOfPart(chartp) }); FromAnchorRowIndex = (int)Math.Floor(Chart.TopPosition); fTemp = Chart.TopPosition - FromAnchorRowIndex; FromAnchorRowOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultRowHeightInEMU); if (slws.RowProperties.ContainsKey(FromAnchorRowIndex + 1)) { rp = slws.RowProperties[FromAnchorRowIndex + 1]; if (rp.HasHeight) FromAnchorRowOffset = (long)(fTemp * rp.HeightInEMU); } FromAnchorColumnIndex = (int)Math.Floor(Chart.LeftPosition); fTemp = Chart.LeftPosition - FromAnchorColumnIndex; FromAnchorColumnOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultColumnWidthInEMU); if (slws.ColumnProperties.ContainsKey(FromAnchorColumnIndex + 1)) { cp = slws.ColumnProperties[FromAnchorColumnIndex + 1]; if (cp.HasWidth) { FromAnchorColumnOffset = (long)(fTemp * cp.WidthInEMU); } } ToAnchorRowIndex = (int)Math.Floor(Chart.BottomPosition); fTemp = Chart.BottomPosition - ToAnchorRowIndex; ToAnchorRowOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultRowHeightInEMU); if (slws.RowProperties.ContainsKey(ToAnchorRowIndex + 1)) { rp = slws.RowProperties[ToAnchorRowIndex + 1]; if (rp.HasHeight) ToAnchorRowOffset = (long)(fTemp * rp.HeightInEMU); } ToAnchorColumnIndex = (int)Math.Floor(Chart.RightPosition); fTemp = Chart.RightPosition - ToAnchorColumnIndex; ToAnchorColumnOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultColumnWidthInEMU); if (slws.ColumnProperties.ContainsKey(ToAnchorColumnIndex + 1)) { cp = slws.ColumnProperties[ToAnchorColumnIndex + 1]; if (cp.HasWidth) { ToAnchorColumnOffset = (long)(fTemp * cp.WidthInEMU); } } Xdr.TwoCellAnchor tcanchor = new Xdr.TwoCellAnchor(); tcanchor.FromMarker = new Xdr.FromMarker(); tcanchor.FromMarker.RowId = new Xdr.RowId(FromAnchorRowIndex.ToString(CultureInfo.InvariantCulture)); tcanchor.FromMarker.RowOffset = new Xdr.RowOffset(FromAnchorRowOffset.ToString(CultureInfo.InvariantCulture)); tcanchor.FromMarker.ColumnId = new Xdr.ColumnId(FromAnchorColumnIndex.ToString(CultureInfo.InvariantCulture)); tcanchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset(FromAnchorColumnOffset.ToString(CultureInfo.InvariantCulture)); tcanchor.ToMarker = new Xdr.ToMarker(); tcanchor.ToMarker.RowId = new Xdr.RowId(ToAnchorRowIndex.ToString(CultureInfo.InvariantCulture)); tcanchor.ToMarker.RowOffset = new Xdr.RowOffset(ToAnchorRowOffset.ToString(CultureInfo.InvariantCulture)); tcanchor.ToMarker.ColumnId = new Xdr.ColumnId(ToAnchorColumnIndex.ToString(CultureInfo.InvariantCulture)); tcanchor.ToMarker.ColumnOffset = new Xdr.ColumnOffset(ToAnchorColumnOffset.ToString(CultureInfo.InvariantCulture)); tcanchor.Append(gf); tcanchor.Append(new Xdr.ClientData()); wsd.Append(tcanchor); wsd.Save(dp); } } #endregion #region Pictures if (slws.Pictures.Count > 0) { foreach (Drawing.SLPicture Picture in slws.Pictures) { imgp = dp.AddImagePart(Picture.PictureImagePartType); if (Picture.DataIsInFile) { using (FileStream fs = new FileStream(Picture.PictureFileName, FileMode.Open)) { imgp.FeedData(fs); } } else { using (MemoryStream ms = new MemoryStream(Picture.PictureByteData)) { imgp.FeedData(ms); } } Xdr.Picture pic = new Xdr.Picture(); pic.NonVisualPictureProperties = new Xdr.NonVisualPictureProperties(); pic.NonVisualPictureProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties(); pic.NonVisualPictureProperties.NonVisualDrawingProperties.Id = slws.NextWorksheetDrawingId; ++slws.NextWorksheetDrawingId; // recommendation is to set as the actual filename, but we'll follow Excel here... // Note: the name value can be used multiple times without Excel choking. // So for example, you can have two pictures with "Picture 1". pic.NonVisualPictureProperties.NonVisualDrawingProperties.Name = string.Format("Picture {0}", dp.ImageParts.Count()); pic.NonVisualPictureProperties.NonVisualDrawingProperties.Description = Picture.AlternativeText; // hlinkClick and hlinkHover as children if (Picture.HasUri) { HyperlinkRelationship hlinkrel = dp.AddHyperlinkRelationship(new System.Uri(Picture.HyperlinkUri, Picture.HyperlinkUriKind), Picture.IsHyperlinkExternal); pic.NonVisualPictureProperties.NonVisualDrawingProperties.HyperlinkOnClick = new A.HyperlinkOnClick() { Id = hlinkrel.Id }; } pic.NonVisualPictureProperties.NonVisualPictureDrawingProperties = new Xdr.NonVisualPictureDrawingProperties(); pic.BlipFill = new Xdr.BlipFill(); pic.BlipFill.Blip = new A.Blip(); pic.BlipFill.Blip.Embed = dp.GetIdOfPart(imgp); if (Picture.CompressionState != A.BlipCompressionValues.None) { pic.BlipFill.Blip.CompressionState = Picture.CompressionState; } if (Picture.Brightness != 0 || Picture.Contrast != 0) { A.LuminanceEffect lumeffect = new A.LuminanceEffect(); if (Picture.Brightness != 0) lumeffect.Brightness = Convert.ToInt32(Picture.Brightness * 1000); if (Picture.Contrast != 0) lumeffect.Contrast = Convert.ToInt32(Picture.Contrast * 1000); pic.BlipFill.Blip.Append(lumeffect); } pic.BlipFill.SourceRectangle = new A.SourceRectangle(); pic.BlipFill.Append(new A.Stretch() { FillRectangle = new A.FillRectangle() }); Picture.ShapeProperties.BlackWhiteMode = A.BlackWhiteModeValues.Auto; Picture.ShapeProperties.HasTransform2D = true; Picture.ShapeProperties.Transform2D.HasOffset = true; Picture.ShapeProperties.Transform2D.HasExtents = true; // not supporting yet because you need to change the positional offsets too. //if (Picture.RotationAngle != 0) //{ // pic.ShapeProperties.Transform2D.Rotation = Convert.ToInt32(Picture.RotationAngle * (decimal)SLConstants.DegreeToAngleRepresentation); //} // used when it's relative positioning // these are the actual values used, so it's 1 less than the given anchor indices. int iColumnId = 0, iRowId = 0; long lColumnOffset = 0, lRowOffset = 0; if (Picture.UseRelativePositioning) { iColumnId = Picture.AnchorColumnIndex - 1; iRowId = Picture.AnchorRowIndex - 1; long lOffset = 0; long lOffsetBuffer = 0; int i; for (i = 1; i <= iColumnId; ++i) { if (slws.ColumnProperties.ContainsKey(i)) { cp = slws.ColumnProperties[i]; if (cp.HasWidth) { lOffsetBuffer += cp.WidthInEMU; } else { lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } } else { // we use the current worksheet's column width lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } } lOffsetBuffer += Picture.OffsetX; lOffset = lOffsetBuffer; if (lOffset <= 0) { // in case the given offset is so negative, it pushes the sum to negative // We use "<= 0" here, so the else part assumes a positive offset iColumnId = 0; lColumnOffset = 0; } else { lOffsetBuffer = 0; i = 1; while (lOffset > lOffsetBuffer) { iColumnId = i - 1; lColumnOffset = lOffset - lOffsetBuffer; if (slws.ColumnProperties.ContainsKey(i)) { cp = slws.ColumnProperties[i]; if (cp.HasWidth) { lOffsetBuffer += cp.WidthInEMU; } else { lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } } else { // we use the current worksheet's column width lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } ++i; } } Picture.ShapeProperties.Transform2D.Offset.X = lColumnOffset; lOffsetBuffer = 0; for (i = 1; i <= iRowId; ++i) { if (slws.RowProperties.ContainsKey(i)) { rp = slws.RowProperties[i]; if (rp.HasHeight) { lOffsetBuffer += rp.HeightInEMU; } else { lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU; } } else { // we use the current worksheet's row height lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU; } } lOffsetBuffer += Picture.OffsetY; lOffset = lOffsetBuffer; if (lOffset <= 0) { // in case the given offset is so negative, it pushes the sum to negative // We use "<= 0" here, so the else part assumes a positive offset iRowId = 0; lRowOffset = 0; } else { lOffsetBuffer = 0; i = 1; while (lOffset > lOffsetBuffer) { iRowId = i - 1; lRowOffset = lOffset - lOffsetBuffer; if (slws.RowProperties.ContainsKey(i)) { rp = slws.RowProperties[i]; if (rp.HasHeight) { lOffsetBuffer += rp.HeightInEMU; } else { lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU; } } else { // we use the current worksheet's row height lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU; } ++i; } } Picture.ShapeProperties.Transform2D.Offset.Y = lRowOffset; } else { Picture.ShapeProperties.Transform2D.Offset.X = 0; Picture.ShapeProperties.Transform2D.Offset.Y = 0; } Picture.ShapeProperties.Transform2D.Extents.Cx = Picture.WidthInEMU; Picture.ShapeProperties.Transform2D.Extents.Cy = Picture.HeightInEMU; Picture.ShapeProperties.HasPresetGeometry = true; Picture.ShapeProperties.PresetGeometry = Picture.PictureShape; pic.ShapeProperties = Picture.ShapeProperties.ToXdrShapeProperties(); Xdr.ClientData clientdata = new Xdr.ClientData(); // the properties are true by default if (!Picture.LockWithSheet) clientdata.LockWithSheet = false; if (!Picture.PrintWithSheet) clientdata.PrintWithSheet = false; if (Picture.UseRelativePositioning) { Xdr.OneCellAnchor ocanchor = new Xdr.OneCellAnchor(); ocanchor.FromMarker = new Xdr.FromMarker(); // Subtract 1 because picture goes to bottom right corner // Subtracting 1 makes it more intuitive that (1,1) means top-left corner of (1,1) ocanchor.FromMarker.ColumnId = new Xdr.ColumnId() { Text = iColumnId.ToString(CultureInfo.InvariantCulture) }; ocanchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset() { Text = lColumnOffset.ToString(CultureInfo.InvariantCulture) }; ocanchor.FromMarker.RowId = new Xdr.RowId() { Text = iRowId.ToString(CultureInfo.InvariantCulture) }; ocanchor.FromMarker.RowOffset = new Xdr.RowOffset() { Text = lRowOffset.ToString(CultureInfo.InvariantCulture) }; ocanchor.Extent = new Xdr.Extent(); ocanchor.Extent.Cx = Picture.WidthInEMU; ocanchor.Extent.Cy = Picture.HeightInEMU; ocanchor.Append(pic); ocanchor.Append(clientdata); wsd.Append(ocanchor); } else { Xdr.AbsoluteAnchor absanchor = new Xdr.AbsoluteAnchor(); absanchor.Position = new Xdr.Position(); absanchor.Position.X = Picture.OffsetX; absanchor.Position.Y = Picture.OffsetY; absanchor.Extent = new Xdr.Extent(); absanchor.Extent.Cx = Picture.WidthInEMU; absanchor.Extent.Cy = Picture.HeightInEMU; absanchor.Append(pic); absanchor.Append(clientdata); wsd.Append(absanchor); } wsd.Save(dp); } } #endregion }
public async override Task ExecuteResultAsync(ActionContext context) { var response = context.HttpContext.Request; var enumerable = _data as System.Collections.IEnumerable; if (enumerable == null) { throw new ArgumentException("IEnumerable type required"); } byte[] FileContents = null; using (MemoryStream mem = new MemoryStream()) { using (var workbook = SpreadsheetDocument.Create(mem, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { var workbookPart = workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart> (); var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData(); sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData); DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets> (); string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); uint sheetId = 1; if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet> ().Count() > 0) { sheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet> ().Select(s => s.SheetId.Value).Max() + 1; } DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = "Sheet1" }; sheets.Append(sheet); //add logo string imgPath = "assets/logo.png"; var 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 WorksheetDrawing(); } var worksheetDrawing = drawingsPart.WorksheetDrawing; var imagePart = drawingsPart.AddImagePart(ImagePartType.Png); using (var stream = new FileStream(imgPath, FileMode.Open)) { imagePart.FeedData(stream); } Bitmap bm = new Bitmap(imgPath); DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents(); var extentsCx = (long)bm.Width * (long)((float)31440 / bm.HorizontalResolution); var extentsCy = (long)bm.Height * (long)((float)31440 / bm.VerticalResolution); bm.Dispose(); var colOffset = 2; var rowOffset = 0; int colNumber = 4; int rowNumber = 1; var nvps = worksheetDrawing.Descendants <DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties> (); var nvpId = nvps.Count() > 0 ? (UInt32Value)worksheetDrawing.Descendants <DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties> ().Max(p => p.Id.Value) + 1 : 1U; var oneCellAnchor = new DocumentFormat.OpenXml.Drawing.Spreadsheet.OneCellAnchor( new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker { ColumnId = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ColumnId((colNumber - 1).ToString()), RowId = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowId((rowNumber - 1).ToString()), ColumnOffset = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ColumnOffset(colOffset.ToString()), RowOffset = new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowOffset(rowOffset.ToString()) }, new DocumentFormat.OpenXml.Drawing.Spreadsheet.Extent { Cx = extentsCx, Cy = extentsCy }, new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture( new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties( new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imgPath }, new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties(new DocumentFormat.OpenXml.Drawing.PictureLocks { NoChangeAspect = true }) ), new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill( new DocumentFormat.OpenXml.Drawing.Blip { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print }, new DocumentFormat.OpenXml.Drawing.Stretch(new DocumentFormat.OpenXml.Drawing.FillRectangle()) ), new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties( new DocumentFormat.OpenXml.Drawing.Transform2D( new DocumentFormat.OpenXml.Drawing.Offset { X = 0, Y = 0 }, new DocumentFormat.OpenXml.Drawing.Extents { Cx = extentsCx, Cy = extentsCy } ), new DocumentFormat.OpenXml.Drawing.PresetGeometry { Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle } ) ), new DocumentFormat.OpenXml.Drawing.Spreadsheet.ClientData() ); worksheetDrawing.Append(oneCellAnchor); sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row()); sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row()); sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row()); sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row()); //company name DocumentFormat.OpenXml.Spreadsheet.Row company = new DocumentFormat.OpenXml.Spreadsheet.Row(); DocumentFormat.OpenXml.Spreadsheet.Cell cellCompany = new DocumentFormat.OpenXml.Spreadsheet.Cell() { StyleIndex = (UInt32Value)1U }; cellCompany.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cellCompany.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("FOOD, MEDICINE AND HEALTH CARE ADMINISTRATION AND CONTROL AUTHORITY OF ETHIOPIA"); company.AppendChild(cellCompany); sheetData.AppendChild(company); sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row()); //header DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (var column in _columns) { if (!(bool)column["IsVisible"]) { continue; } DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column["Title"].ToString()); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (var item in enumerable) { IDictionary <string, object> row = (IDictionary <string, object>)item; DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row(); foreach (var header in _columns) { if (!(bool)header["IsVisible"]) { continue; } DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell(); cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; var value = row[header["FieldName"].ToString()]; cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value?.ToString()); // newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } //geenrated by sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row()); DocumentFormat.OpenXml.Spreadsheet.Row footer = new DocumentFormat.OpenXml.Spreadsheet.Row(); DocumentFormat.OpenXml.Spreadsheet.Cell footerCell = new DocumentFormat.OpenXml.Spreadsheet.Cell(new DocumentFormat.OpenXml.Drawing.Spreadsheet.RowOffset("4")); footerCell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; footerCell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("Generated By eRIS"); footer.AppendChild(footerCell); sheetData.AppendChild(footer); sheetPart.Worksheet.Save(); workbook.WorkbookPart.Workbook.Save(); workbook.Close(); FileContents = mem.ToArray(); var contentResult = new FileContentResult(FileContents, this.ContentType); await contentResult.ExecuteResultAsync(context); //await response.Body.WriteAsync(FileContents, 0, FileContents.Length); } } }
private static void GeneratePartContentPie(DrawingsPart part, ChartPart chartPart1, int row, bool newe) { Xdr.WorksheetDrawing worksheetDrawing1 = new Xdr.WorksheetDrawing(); if (newe) { worksheetDrawing1.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); worksheetDrawing1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); worksheetDrawing1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheetDrawing1.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart"); worksheetDrawing1.AddNamespaceDeclaration("cx", "http://schemas.microsoft.com/office/drawing/2014/chartex"); worksheetDrawing1.AddNamespaceDeclaration("cx1", "http://schemas.microsoft.com/office/drawing/2015/9/8/chartex"); worksheetDrawing1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); worksheetDrawing1.AddNamespaceDeclaration("dgm", "http://schemas.openxmlformats.org/drawingml/2006/diagram"); } Xdr.OneCellAnchor oneCellAnchor1 = new Xdr.OneCellAnchor(); Xdr.FromMarker fromMarker1 = new Xdr.FromMarker(); Xdr.ColumnId columnId1 = new Xdr.ColumnId(); columnId1.Text = "0"; Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset(); columnOffset1.Text = "0"; Xdr.RowId rowId1 = new Xdr.RowId(); rowId1.Text = row.ToString(); Xdr.RowOffset rowOffset1 = new Xdr.RowOffset(); rowOffset1.Text = "0"; fromMarker1.Append(columnId1); fromMarker1.Append(columnOffset1); fromMarker1.Append(rowId1); fromMarker1.Append(rowOffset1); Xdr.Extent extent1 = new Xdr.Extent() { Cx = 5743575L, Cy = 2667000L }; Xdr.GraphicFrame graphicFrame1 = new Xdr.GraphicFrame(); Xdr.NonVisualGraphicFrameProperties nonVisualGraphicFrameProperties1 = new Xdr.NonVisualGraphicFrameProperties(); Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = (UInt32Value)(uint)row, Name = "Chart " + row.ToString() }; Xdr.NonVisualGraphicFrameDrawingProperties nonVisualGraphicFrameDrawingProperties1 = new Xdr.NonVisualGraphicFrameDrawingProperties(); nonVisualGraphicFrameProperties1.Append(nonVisualDrawingProperties1); nonVisualGraphicFrameProperties1.Append(nonVisualGraphicFrameDrawingProperties1); Xdr.Transform transform1 = new Xdr.Transform(); A.Offset offset1 = new A.Offset() { X = 0L, Y = 0L }; A.Extents extents1 = new A.Extents() { Cx = 0L, Cy = 0L }; transform1.Append(offset1); transform1.Append(extents1); A.Graphic graphic1 = new A.Graphic(); A.GraphicData graphicData1 = new A.GraphicData() { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" }; C.ChartReference chartReference1 = new C.ChartReference() { Id = part.GetIdOfPart(chartPart1) }; graphicData1.Append(chartReference1); graphic1.Append(graphicData1); graphicFrame1.Append(nonVisualGraphicFrameProperties1); graphicFrame1.Append(transform1); graphicFrame1.Append(graphic1); Xdr.ClientData clientData1 = new Xdr.ClientData() { LockWithSheet = false }; oneCellAnchor1.Append(fromMarker1); oneCellAnchor1.Append(extent1); oneCellAnchor1.Append(graphicFrame1); oneCellAnchor1.Append(clientData1); if (newe) { worksheetDrawing1.Append(oneCellAnchor1); part.WorksheetDrawing = worksheetDrawing1; } else { part.WorksheetDrawing.Append(oneCellAnchor1); } }
private void AddImage(WorksheetPart worksheetPart, string imageFileName, string imgDesc, int colNumber, int rowNumber) { var drawingsPart = worksheetPart.DrawingsPart ?? worksheetPart.AddNewPart <DrawingsPart>(); if (!worksheetPart.Worksheet.ChildElements.OfType <Drawing>().Any()) { worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) }); } if (drawingsPart.WorksheetDrawing == null) { drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing(); } var worksheetDrawing = drawingsPart.WorksheetDrawing; var imagePart = drawingsPart.AddImagePart(ImagePartType.Jpeg); using (var imageStream = new FileStream(imageFileName, FileMode.Open)) { imagePart.FeedData(imageStream); } int bmWidth = 0; int bmHeight = 0; float bmverticalResolution; float bmhorizontalResolution; using (Bitmap bm = new Bitmap(imageFileName)) { bmWidth = bm.Width; bmHeight = bm.Height; bmverticalResolution = bm.HorizontalResolution; bmhorizontalResolution = bm.VerticalResolution; } A.Extents extents = new A.Extents(); var extentsCx = bmWidth * (long)(914400 / bmverticalResolution); var extentsCy = bmHeight * (long)(914400 / bmhorizontalResolution); var colOffset = 0; var rowOffset = 0; var nvps = worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>(); var nvpId = nvps.Count() > 0 ? (UInt32Value)worksheetDrawing.Descendants <Xdr.NonVisualDrawingProperties>().Max(p => p.Id.Value) + 1 : 1U; var oneCellAnchor = new Xdr.OneCellAnchor( new Xdr.FromMarker { ColumnId = new Xdr.ColumnId((colNumber - 1).ToString()), RowId = new Xdr.RowId((rowNumber - 1).ToString()), ColumnOffset = new Xdr.ColumnOffset(colOffset.ToString()), RowOffset = new Xdr.RowOffset(rowOffset.ToString()) }, new Xdr.Extent { Cx = extentsCx, Cy = extentsCy }, new Xdr.Picture( new Xdr.NonVisualPictureProperties( new Xdr.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imgDesc }, new Xdr.NonVisualPictureDrawingProperties(new A.PictureLocks { NoChangeAspect = true }) ), 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 = extentsCx, Cy = extentsCy } ), new A.PresetGeometry { Preset = A.ShapeTypeValues.Rectangle } ) ), new Xdr.ClientData() ); worksheetDrawing.Append(oneCellAnchor); }
private void WriteImageParts(DrawingsPart dp) { ImagePart imgp; Xdr.WorksheetDrawing wsd = dp.WorksheetDrawing; SLRowProperties rp; SLColumnProperties cp; #region Charts if (slws.Charts.Count > 0) { int FromAnchorRowIndex = 0; long FromAnchorRowOffset = 0; int FromAnchorColumnIndex = 0; long FromAnchorColumnOffset = 0; int ToAnchorRowIndex = 4; long ToAnchorRowOffset = 0; int ToAnchorColumnIndex = 4; long ToAnchorColumnOffset = 0; double fTemp = 0; ChartPart chartp; Xdr.GraphicFrame gf; foreach (Charts.SLChart Chart in slws.Charts) { chartp = dp.AddNewPart<ChartPart>(); chartp.ChartSpace = Chart.ToChartSpace(ref chartp); gf = new Xdr.GraphicFrame(); gf.Macro = string.Empty; gf.NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties(); gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties(); gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Id = slws.NextWorksheetDrawingId; ++slws.NextWorksheetDrawingId; gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Name = Chart.ChartName; // alt text for charts //...gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Description = ""; gf.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties(); gf.Transform = new Xdr.Transform(); gf.Transform.Offset = new A.Offset() { X = 0, Y = 0 }; gf.Transform.Extents = new A.Extents() { Cx = 0, Cy = 0 }; gf.Graphic = new A.Graphic(); gf.Graphic.GraphicData = new A.GraphicData(); gf.Graphic.GraphicData.Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"; gf.Graphic.GraphicData.Append(new C.ChartReference() { Id = dp.GetIdOfPart(chartp) }); FromAnchorRowIndex = (int)Math.Floor(Chart.TopPosition); fTemp = Chart.TopPosition - FromAnchorRowIndex; FromAnchorRowOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultRowHeightInEMU); if (slws.RowProperties.ContainsKey(FromAnchorRowIndex + 1)) { rp = slws.RowProperties[FromAnchorRowIndex + 1]; if (rp.HasHeight) FromAnchorRowOffset = (long)(fTemp * rp.HeightInEMU); } FromAnchorColumnIndex = (int)Math.Floor(Chart.LeftPosition); fTemp = Chart.LeftPosition - FromAnchorColumnIndex; FromAnchorColumnOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultColumnWidthInEMU); if (slws.ColumnProperties.ContainsKey(FromAnchorColumnIndex + 1)) { cp = slws.ColumnProperties[FromAnchorColumnIndex + 1]; if (cp.HasWidth) FromAnchorColumnOffset = (long)(fTemp * cp.WidthInEMU); } ToAnchorRowIndex = (int)Math.Floor(Chart.BottomPosition); fTemp = Chart.BottomPosition - ToAnchorRowIndex; ToAnchorRowOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultRowHeightInEMU); if (slws.RowProperties.ContainsKey(ToAnchorRowIndex + 1)) { rp = slws.RowProperties[ToAnchorRowIndex + 1]; if (rp.HasHeight) ToAnchorRowOffset = (long)(fTemp * rp.HeightInEMU); } ToAnchorColumnIndex = (int)Math.Floor(Chart.RightPosition); fTemp = Chart.RightPosition - ToAnchorColumnIndex; ToAnchorColumnOffset = (long)(fTemp * slws.SheetFormatProperties.DefaultColumnWidthInEMU); if (slws.ColumnProperties.ContainsKey(ToAnchorColumnIndex + 1)) { cp = slws.ColumnProperties[ToAnchorColumnIndex + 1]; if (cp.HasWidth) ToAnchorColumnOffset = (long)(fTemp * cp.WidthInEMU); } Xdr.TwoCellAnchor tcanchor = new Xdr.TwoCellAnchor(); tcanchor.FromMarker = new Xdr.FromMarker(); tcanchor.FromMarker.RowId = new Xdr.RowId(FromAnchorRowIndex.ToString(CultureInfo.InvariantCulture)); tcanchor.FromMarker.RowOffset = new Xdr.RowOffset(FromAnchorRowOffset.ToString(CultureInfo.InvariantCulture)); tcanchor.FromMarker.ColumnId = new Xdr.ColumnId(FromAnchorColumnIndex.ToString(CultureInfo.InvariantCulture)); tcanchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset(FromAnchorColumnOffset.ToString(CultureInfo.InvariantCulture)); tcanchor.ToMarker = new Xdr.ToMarker(); tcanchor.ToMarker.RowId = new Xdr.RowId(ToAnchorRowIndex.ToString(CultureInfo.InvariantCulture)); tcanchor.ToMarker.RowOffset = new Xdr.RowOffset(ToAnchorRowOffset.ToString(CultureInfo.InvariantCulture)); tcanchor.ToMarker.ColumnId = new Xdr.ColumnId(ToAnchorColumnIndex.ToString(CultureInfo.InvariantCulture)); tcanchor.ToMarker.ColumnOffset = new Xdr.ColumnOffset(ToAnchorColumnOffset.ToString(CultureInfo.InvariantCulture)); tcanchor.Append(gf); tcanchor.Append(new Xdr.ClientData()); wsd.Append(tcanchor); wsd.Save(dp); } } #endregion #region Pictures if (slws.Pictures.Count > 0) { foreach (Drawing.SLPicture Picture in slws.Pictures) { imgp = dp.AddImagePart(Picture.PictureImagePartType); if (Picture.DataIsInFile) { using (FileStream fs = new FileStream(Picture.PictureFileName, FileMode.Open)) { imgp.FeedData(fs); } } else { using (MemoryStream ms = new MemoryStream(Picture.PictureByteData)) { imgp.FeedData(ms); } } Xdr.Picture pic = new Xdr.Picture(); pic.NonVisualPictureProperties = new Xdr.NonVisualPictureProperties(); pic.NonVisualPictureProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties(); pic.NonVisualPictureProperties.NonVisualDrawingProperties.Id = slws.NextWorksheetDrawingId; ++slws.NextWorksheetDrawingId; // recommendation is to set as the actual filename, but we'll follow Excel here... // Note: the name value can be used multiple times without Excel choking. // So for example, you can have two pictures with "Picture 1". pic.NonVisualPictureProperties.NonVisualDrawingProperties.Name = string.Format("Picture {0}", dp.ImageParts.Count()); pic.NonVisualPictureProperties.NonVisualDrawingProperties.Description = Picture.AlternativeText; // hlinkClick and hlinkHover as children if (Picture.HasUri) { HyperlinkRelationship hlinkrel = dp.AddHyperlinkRelationship(new System.Uri(Picture.HyperlinkUri, Picture.HyperlinkUriKind), Picture.IsHyperlinkExternal); pic.NonVisualPictureProperties.NonVisualDrawingProperties.HyperlinkOnClick = new A.HyperlinkOnClick() { Id = hlinkrel.Id }; } pic.NonVisualPictureProperties.NonVisualPictureDrawingProperties = new Xdr.NonVisualPictureDrawingProperties(); pic.BlipFill = new Xdr.BlipFill(); pic.BlipFill.Blip = new A.Blip(); pic.BlipFill.Blip.Embed = dp.GetIdOfPart(imgp); if (Picture.CompressionState != A.BlipCompressionValues.None) { pic.BlipFill.Blip.CompressionState = Picture.CompressionState; } if (Picture.Brightness != 0 || Picture.Contrast != 0) { A.LuminanceEffect lumeffect = new A.LuminanceEffect(); if (Picture.Brightness != 0) lumeffect.Brightness = Convert.ToInt32(Picture.Brightness * 1000); if (Picture.Contrast != 0) lumeffect.Contrast = Convert.ToInt32(Picture.Contrast * 1000); pic.BlipFill.Blip.Append(lumeffect); } pic.BlipFill.SourceRectangle = new A.SourceRectangle(); pic.BlipFill.Append(new A.Stretch() { FillRectangle = new A.FillRectangle() }); pic.ShapeProperties = new Xdr.ShapeProperties(); pic.ShapeProperties.BlackWhiteMode = A.BlackWhiteModeValues.Auto; pic.ShapeProperties.Transform2D = new A.Transform2D(); // not supporting yet because you need to change the positional offsets too. //if (Picture.RotationAngle != 0) //{ // pic.ShapeProperties.Transform2D.Rotation = Convert.ToInt32(Picture.RotationAngle * (decimal)SLConstants.DegreeToAngleRepresentation); //} pic.ShapeProperties.Transform2D.Offset = new A.Offset(); // used when it's relative positioning // these are the actual values used, so it's 1 less than the given anchor indices. int iColumnId = 0, iRowId = 0; long lColumnOffset = 0, lRowOffset = 0; if (Picture.UseRelativePositioning) { iColumnId = Picture.AnchorColumnIndex - 1; iRowId = Picture.AnchorRowIndex - 1; long lOffset = 0; long lOffsetBuffer = 0; int i; if (iColumnId > 0) { for (i = 1; i <= iColumnId; ++i) { if (slws.ColumnProperties.ContainsKey(i)) { cp = slws.ColumnProperties[i]; if (cp.HasWidth) { lOffsetBuffer += cp.WidthInEMU; } else { lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } } else { // we use the current worksheet's column width lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } } } lOffsetBuffer += Picture.OffsetX; lOffset = lOffsetBuffer; if (lOffset <= 0) { // in case the given offset is so negative, it pushes the sum to negative // We use "<= 0" here, so the else part assumes a positive offset iColumnId = 0; lColumnOffset = 0; } else { lOffsetBuffer = 0; i = 1; while (lOffset > lOffsetBuffer) { iColumnId = i - 1; lColumnOffset = lOffset - lOffsetBuffer; if (slws.ColumnProperties.ContainsKey(i)) { cp = slws.ColumnProperties[i]; if (cp.HasWidth) { lOffsetBuffer += cp.WidthInEMU; } else { lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } } else { // we use the current worksheet's column width lOffsetBuffer += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } ++i; } } pic.ShapeProperties.Transform2D.Offset.X = lColumnOffset; lOffsetBuffer = 0; if (iRowId > 0) { for (i = 1; i <= iRowId; ++i) { if (slws.RowProperties.ContainsKey(i)) { rp = slws.RowProperties[i]; if (rp.HasHeight) { lOffsetBuffer += rp.HeightInEMU; } else { lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU; } } else { // we use the current worksheet's row height lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU; } } } lOffsetBuffer += Picture.OffsetY; lOffset = lOffsetBuffer; if (lOffset <= 0) { // in case the given offset is so negative, it pushes the sum to negative // We use "<= 0" here, so the else part assumes a positive offset iRowId = 0; lRowOffset = 0; } else { lOffsetBuffer = 0; i = 1; while (lOffset > lOffsetBuffer) { iRowId = i - 1; lRowOffset = lOffset - lOffsetBuffer; if (slws.RowProperties.ContainsKey(i)) { rp = slws.RowProperties[i]; if (rp.HasHeight) { lOffsetBuffer += rp.HeightInEMU; } else { lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU; } } else { // we use the current worksheet's row height lOffsetBuffer += slws.SheetFormatProperties.DefaultRowHeightInEMU; } ++i; } } pic.ShapeProperties.Transform2D.Offset.Y = lRowOffset; } else { pic.ShapeProperties.Transform2D.Offset.X = 0; pic.ShapeProperties.Transform2D.Offset.Y = 0; } pic.ShapeProperties.Transform2D.Extents = new A.Extents(); pic.ShapeProperties.Transform2D.Extents.Cx = Picture.WidthInEMU; pic.ShapeProperties.Transform2D.Extents.Cy = Picture.HeightInEMU; pic.ShapeProperties.Append(new A.PresetGeometry() { Preset = Picture.PictureShape, AdjustValueList = new A.AdjustValueList() }); switch (Picture.FillType) { case Drawing.SLPicture.SLPictureFillType.None: pic.ShapeProperties.Append(new A.NoFill()); break; case Drawing.SLPicture.SLPictureFillType.Solid: A.SolidFill solidfill = new A.SolidFill(); solidfill.InnerXml = Picture.FillClassInnerXml; pic.ShapeProperties.Append(solidfill); break; default: pic.ShapeProperties.Append(new A.NoFill()); break; } if (Picture.HasOutline) { A.Outline outline = new A.Outline(); if (Picture.PictureOutline.Width != null) { outline.Width = Picture.PictureOutline.Width.Value; } if (Picture.PictureOutline.CompoundLineType != null) { outline.CompoundLineType = Picture.PictureOutline.CompoundLineType.Value; } if (Picture.PictureOutline.CapType != null) { outline.CapType = Picture.PictureOutline.CapType.Value; } outline.Append((A.SolidFill)Picture.PictureOutlineFill.CloneNode(true)); foreach (var child in Picture.PictureOutlineFill.ChildElements) { if (child is A.PresetDash) { outline.Append((A.PresetDash)child.CloneNode(true)); } else if (child is A.Round) { outline.Append((A.Round)child.CloneNode(true)); } else if (child is A.Bevel) { outline.Append((A.Bevel)child.CloneNode(true)); } else if (child is A.Miter) { outline.Append((A.Miter)child.CloneNode(true)); } } pic.ShapeProperties.Append(outline); } if (Picture.HasEffectList) { A.EffectList effectlist = new A.EffectList(); if (Picture.HasGlow) { A.Glow glow = new A.Glow(); glow.Radius = Picture.GlowRadius; glow.InnerXml = Picture.GlowColorInnerXml; effectlist.Append(glow); } if (Picture.HasInnerShadow) { effectlist.Append((A.InnerShadow)Picture.PictureInnerShadow.CloneNode(true)); } if (Picture.HasOuterShadow) { effectlist.Append((A.OuterShadow)Picture.PictureOuterShadow.CloneNode(true)); } if (Picture.HasReflection) { A.Reflection refl = new A.Reflection(); if (Picture.ReflectionBlurRadius != 0) refl.BlurRadius = Picture.ReflectionBlurRadius; if (Picture.ReflectionStartOpacity != 100000) refl.StartOpacity = Picture.ReflectionStartOpacity; if (Picture.ReflectionStartPosition != 0) refl.StartPosition = Picture.ReflectionStartPosition; if (Picture.ReflectionEndAlpha != 0) refl.EndAlpha = Picture.ReflectionEndAlpha; if (Picture.ReflectionEndPosition != 100000) refl.EndPosition = Picture.ReflectionEndPosition; if (Picture.ReflectionDistance != 0) refl.Distance = Picture.ReflectionDistance; if (Picture.ReflectionDirection != 0) refl.Direction = Picture.ReflectionDirection; if (Picture.ReflectionFadeDirection != 5400000) refl.FadeDirection = Picture.ReflectionFadeDirection; if (Picture.ReflectionHorizontalRatio != 100000) refl.HorizontalRatio = Picture.ReflectionHorizontalRatio; if (Picture.ReflectionVerticalRatio != 100000) refl.VerticalRatio = Picture.ReflectionVerticalRatio; if (Picture.ReflectionHorizontalSkew != 0) refl.HorizontalSkew = Picture.ReflectionHorizontalSkew; if (Picture.ReflectionVerticalSkew != 0) refl.VerticalSkew = Picture.ReflectionVerticalSkew; if (Picture.ReflectionAlignment != A.RectangleAlignmentValues.Bottom) refl.Alignment = Picture.ReflectionAlignment; if (!Picture.ReflectionRotateWithShape) refl.RotateWithShape = false; effectlist.Append(refl); } if (Picture.HasSoftEdge) { A.SoftEdge softedge = new A.SoftEdge(); softedge.Radius = Picture.SoftEdgeRadius; effectlist.Append(softedge); } pic.ShapeProperties.Append(effectlist); } if (Picture.HasScene3D) { A.Scene3DType scene3d = new A.Scene3DType(); scene3d.Camera = new A.Camera(); scene3d.Camera.Preset = Picture.CameraPreset; scene3d.Camera.FieldOfView = Picture.CameraFieldOfView; // default is 100% if (Picture.CameraZoom != 100000) { scene3d.Camera.Zoom = Picture.CameraZoom; } if (Picture.CameraLatitude != 0 || Picture.CameraLongitude != 0 || Picture.CameraRevolution != 0) { scene3d.Camera.Rotation = new A.Rotation(); scene3d.Camera.Rotation.Latitude = Picture.CameraLatitude; scene3d.Camera.Rotation.Longitude = Picture.CameraLongitude; scene3d.Camera.Rotation.Revolution = Picture.CameraRevolution; } scene3d.LightRig = new A.LightRig(); scene3d.LightRig.Rig = Picture.LightRigType; scene3d.LightRig.Direction = Picture.LightRigDirection; if (Picture.LightRigLatitude != 0 || Picture.LightRigLongitude != 0 || Picture.LightRigRevolution != 0) { scene3d.LightRig.Rotation = new A.Rotation(); scene3d.LightRig.Rotation.Latitude = Picture.LightRigLatitude; scene3d.LightRig.Rotation.Longitude = Picture.LightRigLongitude; scene3d.LightRig.Rotation.Revolution = Picture.LightRigRevolution; } pic.ShapeProperties.Append(scene3d); } if (Picture.HasShape3D) { A.Shape3DType shape3d = new A.Shape3DType(); if (Picture.HasBevelTop) { shape3d.BevelTop = new A.BevelTop(); shape3d.BevelTop.Preset = Picture.BevelTopPreset; shape3d.BevelTop.Width = Picture.BevelTopWidth; shape3d.BevelTop.Height = Picture.BevelTopHeight; } if (Picture.HasBevelBottom) { shape3d.BevelBottom = new A.BevelBottom(); shape3d.BevelBottom.Preset = Picture.BevelBottomPreset; shape3d.BevelBottom.Width = Picture.BevelBottomWidth; shape3d.BevelBottom.Height = Picture.BevelBottomHeight; } if (Picture.HasExtrusion) { A.ExtrusionColor extcolor = new A.ExtrusionColor(); extcolor.InnerXml = Picture.ExtrusionColorInnerXml; shape3d.ExtrusionColor = extcolor; shape3d.ExtrusionHeight = Picture.ExtrusionHeight; } if (Picture.HasContour) { A.ContourColor contourcolor = new A.ContourColor(); contourcolor.InnerXml = Picture.ContourColorInnerXml; shape3d.ContourColor = contourcolor; shape3d.ContourWidth = Picture.ContourWidth; } if (Picture.HasMaterialType) { shape3d.PresetMaterial = Picture.MaterialType; } if (Picture.HasZDistance) { shape3d.Z = Picture.ZDistance; } pic.ShapeProperties.Append(shape3d); } Xdr.ClientData clientdata = new Xdr.ClientData(); // the properties are true by default if (!Picture.LockWithSheet) clientdata.LockWithSheet = false; if (!Picture.PrintWithSheet) clientdata.PrintWithSheet = false; if (Picture.UseRelativePositioning) { Xdr.OneCellAnchor ocanchor = new Xdr.OneCellAnchor(); ocanchor.FromMarker = new Xdr.FromMarker(); // Subtract 1 because picture goes to bottom right corner // Subtracting 1 makes it more intuitive that (1,1) means top-left corner of (1,1) ocanchor.FromMarker.ColumnId = new Xdr.ColumnId() { Text = iColumnId.ToString(CultureInfo.InvariantCulture) }; ocanchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset() { Text = lColumnOffset.ToString(CultureInfo.InvariantCulture) }; ocanchor.FromMarker.RowId = new Xdr.RowId() { Text = iRowId.ToString(CultureInfo.InvariantCulture) }; ocanchor.FromMarker.RowOffset = new Xdr.RowOffset() { Text = lRowOffset.ToString(CultureInfo.InvariantCulture) }; ocanchor.Extent = new Xdr.Extent(); ocanchor.Extent.Cx = Picture.WidthInEMU; ocanchor.Extent.Cy = Picture.HeightInEMU; ocanchor.Append(pic); ocanchor.Append(clientdata); wsd.Append(ocanchor); } else { Xdr.AbsoluteAnchor absanchor = new Xdr.AbsoluteAnchor(); absanchor.Position = new Xdr.Position(); absanchor.Position.X = Picture.OffsetX; absanchor.Position.Y = Picture.OffsetY; absanchor.Extent = new Xdr.Extent(); absanchor.Extent.Cx = Picture.WidthInEMU; absanchor.Extent.Cy = Picture.HeightInEMU; absanchor.Append(pic); absanchor.Append(clientdata); wsd.Append(absanchor); } wsd.Save(dp); } } #endregion }
// Generates content of drawingsPart2. private void GenerateDrawingsPart2Content(DrawingsPart drawingsPart2) { Xdr.WorksheetDrawing worksheetDrawing2 = new Xdr.WorksheetDrawing(); worksheetDrawing2.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); worksheetDrawing2.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); Xdr.TwoCellAnchor twoCellAnchor5 = new Xdr.TwoCellAnchor(); Xdr.FromMarker fromMarker5 = new Xdr.FromMarker(); Xdr.ColumnId columnId9 = new Xdr.ColumnId(); columnId9.Text = "6"; Xdr.ColumnOffset columnOffset9 = new Xdr.ColumnOffset(); columnOffset9.Text = "571500"; Xdr.RowId rowId9 = new Xdr.RowId(); rowId9.Text = "5"; Xdr.RowOffset rowOffset9 = new Xdr.RowOffset(); rowOffset9.Text = "147637"; fromMarker5.Append(columnId9); fromMarker5.Append(columnOffset9); fromMarker5.Append(rowId9); fromMarker5.Append(rowOffset9); Xdr.ToMarker toMarker5 = new Xdr.ToMarker(); Xdr.ColumnId columnId10 = new Xdr.ColumnId(); columnId10.Text = "14"; Xdr.ColumnOffset columnOffset10 = new Xdr.ColumnOffset(); columnOffset10.Text = "266700"; Xdr.RowId rowId10 = new Xdr.RowId(); rowId10.Text = "20"; Xdr.RowOffset rowOffset10 = new Xdr.RowOffset(); rowOffset10.Text = "33337"; toMarker5.Append(columnId10); toMarker5.Append(columnOffset10); toMarker5.Append(rowId10); toMarker5.Append(rowOffset10); Xdr.GraphicFrame graphicFrame2 = new Xdr.GraphicFrame() { Macro = "" }; Xdr.NonVisualGraphicFrameProperties nonVisualGraphicFrameProperties2 = new Xdr.NonVisualGraphicFrameProperties(); Xdr.NonVisualDrawingProperties nonVisualDrawingProperties11 = new Xdr.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = "Chart 1" }; Xdr.NonVisualGraphicFrameDrawingProperties nonVisualGraphicFrameDrawingProperties2 = new Xdr.NonVisualGraphicFrameDrawingProperties(); nonVisualGraphicFrameProperties2.Append(nonVisualDrawingProperties11); nonVisualGraphicFrameProperties2.Append(nonVisualGraphicFrameDrawingProperties2); Xdr.Transform transform2 = new Xdr.Transform(); A.Offset offset15 = new A.Offset() { X = 0L, Y = 0L }; A.Extents extents15 = new A.Extents() { Cx = 0L, Cy = 0L }; transform2.Append(offset15); transform2.Append(extents15); A.Graphic graphic2 = new A.Graphic(); A.GraphicData graphicData2 = new A.GraphicData() { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" }; C.ChartReference chartReference1 = new C.ChartReference() { Id = "rId1" }; chartReference1.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart"); chartReference1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); graphicData2.Append(chartReference1); graphic2.Append(graphicData2); graphicFrame2.Append(nonVisualGraphicFrameProperties2); graphicFrame2.Append(transform2); graphicFrame2.Append(graphic2); Xdr.ClientData clientData5 = new Xdr.ClientData(); twoCellAnchor5.Append(fromMarker5); twoCellAnchor5.Append(toMarker5); twoCellAnchor5.Append(graphicFrame2); twoCellAnchor5.Append(clientData5); Xdr.OneCellAnchor oneCellAnchor1 = new Xdr.OneCellAnchor(); Xdr.FromMarker fromMarker6 = new Xdr.FromMarker(); Xdr.ColumnId columnId11 = new Xdr.ColumnId(); columnId11.Text = "17"; Xdr.ColumnOffset columnOffset11 = new Xdr.ColumnOffset(); columnOffset11.Text = "186742"; Xdr.RowId rowId11 = new Xdr.RowId(); rowId11.Text = "11"; Xdr.RowOffset rowOffset11 = new Xdr.RowOffset(); rowOffset11.Text = "174123"; fromMarker6.Append(columnId11); fromMarker6.Append(columnOffset11); fromMarker6.Append(rowId11); fromMarker6.Append(rowOffset11); Xdr.Extent extent1 = new Xdr.Extent() { Cx = 3627019L, Cy = 937629L }; Xdr.Shape shape11 = new Xdr.Shape() { Macro = "", TextLink = "" }; Xdr.NonVisualShapeProperties nonVisualShapeProperties3 = new Xdr.NonVisualShapeProperties(); Xdr.NonVisualDrawingProperties nonVisualDrawingProperties12 = new Xdr.NonVisualDrawingProperties() { Id = (UInt32Value)3U, Name = "Rectangle 2" }; Xdr.NonVisualShapeDrawingProperties nonVisualShapeDrawingProperties3 = new Xdr.NonVisualShapeDrawingProperties(); nonVisualShapeProperties3.Append(nonVisualDrawingProperties12); nonVisualShapeProperties3.Append(nonVisualShapeDrawingProperties3); Xdr.ShapeProperties shapeProperties35 = new Xdr.ShapeProperties(); A.Transform2D transform2D14 = new A.Transform2D() { Rotation = 1966164 }; A.Offset offset16 = new A.Offset() { X = 10549942L, Y = 3793623L }; A.Extents extents16 = new A.Extents() { Cx = 3627019L, Cy = 937629L }; transform2D14.Append(offset16); transform2D14.Append(extents16); A.PresetGeometry presetGeometry9 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle }; A.AdjustValueList adjustValueList9 = new A.AdjustValueList(); presetGeometry9.Append(adjustValueList9); A.NoFill noFill1 = new A.NoFill(); shapeProperties35.Append(transform2D14); shapeProperties35.Append(presetGeometry9); shapeProperties35.Append(noFill1); Xdr.TextBody textBody24 = new Xdr.TextBody(); A.BodyProperties bodyProperties24 = new A.BodyProperties() { Wrap = A.TextWrappingValues.None, LeftInset = 91440, TopInset = 45720, RightInset = 91440, BottomInset = 45720 }; A.ShapeAutoFit shapeAutoFit1 = new A.ShapeAutoFit(); bodyProperties24.Append(shapeAutoFit1); A.ListStyle listStyle24 = new A.ListStyle(); A.Paragraph paragraph24 = new A.Paragraph(); A.ParagraphProperties paragraphProperties7 = new A.ParagraphProperties() { Alignment = A.TextAlignmentTypeValues.Center }; A.Run run12 = new A.Run(); A.RunProperties runProperties12 = new A.RunProperties() { Language = "en-US", FontSize = 5400, Bold = true, Capital = A.TextCapsValues.None, Spacing = 0 }; A.Outline outline10 = new A.Outline() { Width = 22225 }; A.SolidFill solidFill18 = new A.SolidFill(); A.SchemeColor schemeColor191 = new A.SchemeColor() { Val = A.SchemeColorValues.Accent2 }; solidFill18.Append(schemeColor191); A.PresetDash presetDash9 = new A.PresetDash() { Val = A.PresetLineDashValues.Solid }; outline10.Append(solidFill18); outline10.Append(presetDash9); A.SolidFill solidFill19 = new A.SolidFill(); A.SchemeColor schemeColor192 = new A.SchemeColor() { Val = A.SchemeColorValues.Accent2 }; A.LuminanceModulation luminanceModulation1 = new A.LuminanceModulation() { Val = 40000 }; A.LuminanceOffset luminanceOffset11 = new A.LuminanceOffset() { Val = 60000 }; schemeColor192.Append(luminanceModulation1); schemeColor192.Append(luminanceOffset11); solidFill19.Append(schemeColor192); A.EffectList effectList9 = new A.EffectList(); runProperties12.Append(outline10); runProperties12.Append(solidFill19); runProperties12.Append(effectList9); A.Text text12 = new A.Text(); text12.Text = "Hello"; run12.Append(runProperties12); run12.Append(text12); A.Run run13 = new A.Run(); A.RunProperties runProperties13 = new A.RunProperties() { Language = "en-US", FontSize = 5400, Bold = true, Capital = A.TextCapsValues.None, Spacing = 0, Baseline = 0 }; A.Outline outline11 = new A.Outline() { Width = 22225 }; A.SolidFill solidFill20 = new A.SolidFill(); A.SchemeColor schemeColor193 = new A.SchemeColor() { Val = A.SchemeColorValues.Accent2 }; solidFill20.Append(schemeColor193); A.PresetDash presetDash10 = new A.PresetDash() { Val = A.PresetLineDashValues.Solid }; outline11.Append(solidFill20); outline11.Append(presetDash10); A.SolidFill solidFill21 = new A.SolidFill(); A.SchemeColor schemeColor194 = new A.SchemeColor() { Val = A.SchemeColorValues.Accent2 }; A.LuminanceModulation luminanceModulation2 = new A.LuminanceModulation() { Val = 40000 }; A.LuminanceOffset luminanceOffset12 = new A.LuminanceOffset() { Val = 60000 }; schemeColor194.Append(luminanceModulation2); schemeColor194.Append(luminanceOffset12); solidFill21.Append(schemeColor194); A.EffectList effectList10 = new A.EffectList(); runProperties13.Append(outline11); runProperties13.Append(solidFill21); runProperties13.Append(effectList10); A.Text text13 = new A.Text(); text13.Text = " World"; run13.Append(runProperties13); run13.Append(text13); A.EndParagraphRunProperties endParagraphRunProperties13 = new A.EndParagraphRunProperties() { Language = "en-US", FontSize = 5400, Bold = true, Capital = A.TextCapsValues.None, Spacing = 0 }; A.Outline outline12 = new A.Outline() { Width = 22225 }; A.SolidFill solidFill22 = new A.SolidFill(); A.SchemeColor schemeColor195 = new A.SchemeColor() { Val = A.SchemeColorValues.Accent2 }; solidFill22.Append(schemeColor195); A.PresetDash presetDash11 = new A.PresetDash() { Val = A.PresetLineDashValues.Solid }; outline12.Append(solidFill22); outline12.Append(presetDash11); A.SolidFill solidFill23 = new A.SolidFill(); A.SchemeColor schemeColor196 = new A.SchemeColor() { Val = A.SchemeColorValues.Accent2 }; A.LuminanceModulation luminanceModulation3 = new A.LuminanceModulation() { Val = 40000 }; A.LuminanceOffset luminanceOffset13 = new A.LuminanceOffset() { Val = 60000 }; schemeColor196.Append(luminanceModulation3); schemeColor196.Append(luminanceOffset13); solidFill23.Append(schemeColor196); A.EffectList effectList11 = new A.EffectList(); endParagraphRunProperties13.Append(outline12); endParagraphRunProperties13.Append(solidFill23); endParagraphRunProperties13.Append(effectList11); paragraph24.Append(paragraphProperties7); paragraph24.Append(run12); paragraph24.Append(run13); paragraph24.Append(endParagraphRunProperties13); textBody24.Append(bodyProperties24); textBody24.Append(listStyle24); textBody24.Append(paragraph24); shape11.Append(nonVisualShapeProperties3); shape11.Append(shapeProperties35); shape11.Append(textBody24); Xdr.ClientData clientData6 = new Xdr.ClientData(); oneCellAnchor1.Append(fromMarker6); oneCellAnchor1.Append(extent1); oneCellAnchor1.Append(shape11); oneCellAnchor1.Append(clientData6); worksheetDrawing2.Append(twoCellAnchor5); worksheetDrawing2.Append(oneCellAnchor1); drawingsPart2.WorksheetDrawing = worksheetDrawing2; }
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 }
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(); } }