protected void ExcelDl_Click(object sender, EventArgs e) { Response.Clear(); Response.Buffer = true; Response.AddHeader("content-disposition", "attachment;filename=ViewHistoryChart.xlsx"); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.Charset = ""; StringWriter sw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(sw); Chart1.RenderControl(hw); using (MemoryStream stream = new MemoryStream()) { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)) { var fileName = HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\ChartImage.png"; WorkbookPart wbp = spreadsheetDocument.AddWorkbookPart(); WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>(); Workbook wb = new Workbook(); FileVersion fv = new FileVersion(); fv.ApplicationName = "Microsoft Office Excel"; Worksheet ws = new Worksheet(); SheetData sd = new SheetData(); // add contents List<string> values = new List<string>(); //values.Add(lblSectionFunctionSelected.Text); uint i = 1; foreach (var value in values) { UInt32Value rowIndex = UInt32Value.FromUInt32(i); var row = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet sd.Append(row); var cell = new Cell { CellValue = new CellValue(value), DataType = new EnumValue<CellValues>(CellValues.String) }; row.InsertAt(cell, 0); i++; } // add image DrawingsPart dp = wsp.AddNewPart<DrawingsPart>(); ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp)); using (FileStream fs = new FileStream(fileName, FileMode.Open)) { imgp.FeedData(fs); } DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties(); nvdp.Id = 1025; nvdp.Name = "Picture 1"; nvdp.Description = "Chart"; DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks(); picLocks.NoChangeAspect = true; picLocks.NoChangeArrowheads = true; DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties nvpdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties(); nvpdp.PictureLocks = picLocks; DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties nvpp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties(); nvpp.NonVisualDrawingProperties = nvdp; nvpp.NonVisualPictureDrawingProperties = nvpdp; DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch(); stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle(); DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill blipFill = new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill(); DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip(); blip.Embed = dp.GetIdOfPart(imgp); blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print; blipFill.Blip = blip; blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle(); blipFill.Append(stretch); DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D(); DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset(); offset.X = 0; offset.Y = 0; t2d.Offset = offset; Bitmap bm = new Bitmap(fileName); //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML //http://stackoverflow.com/questions/1341930/pixel-to-centimeter //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents(); extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution); extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution); bm.Dispose(); t2d.Extents = extents; DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties sp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties(); sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto; sp.Transform2D = t2d; DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry(); prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle; prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList(); sp.Append(prstGeom); sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill()); DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture(); picture.NonVisualPictureProperties = nvpp; picture.BlipFill = blipFill; picture.ShapeProperties = sp; DocumentFormat.OpenXml.Drawing.Spreadsheet.Position pos = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Position(); pos.X = 0; pos.Y = 10; Extent ext = new Extent(); ext.Cx = extents.Cx; ext.Cy = extents.Cy; AbsoluteAnchor anchor = new AbsoluteAnchor(); anchor.Position = pos; anchor.Extent = ext; anchor.Append(picture); anchor.Append(new ClientData()); WorksheetDrawing wsd = new WorksheetDrawing(); wsd.Append(anchor); Drawing drawing = new Drawing(); drawing.Id = dp.GetIdOfPart(imgp); wsd.Save(dp); ws.Append(sd); ws.Append(drawing); wsp.Worksheet = ws; wsp.Worksheet.Save(); Sheets sheets = new Sheets(); Sheet sheet = new Sheet(); sheet.Name = "history chart"; sheet.SheetId = 1; sheet.Id = wbp.GetIdOfPart(wsp); sheets.Append(sheet); wb.Append(fv); wb.Append(sheets); spreadsheetDocument.WorkbookPart.Workbook = wb; spreadsheetDocument.WorkbookPart.Workbook.Save(); spreadsheetDocument.Close(); } File.WriteAllBytes(HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\temp.xlsx", stream.ToArray()); } Response.WriteFile(HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\temp.xlsx"); Response.Flush(); Response.Close(); Response.End(); }
/// <summary> /// Add the logo of the system /// </summary> /// <param name="logoPath">Path of the logo</param> /// <param name="worksheetPart">Worksheet Part</param> /// <returns>Drawing</returns> private static Drawing AddLogo(string logoPath, WorksheetPart worksheetPart) { string sImagePath = logoPath; DrawingsPart dp = worksheetPart.AddNewPart<DrawingsPart>(); ImagePart imgp = dp.AddImagePart(ImagePartType.Png, worksheetPart.GetIdOfPart(dp)); using (FileStream fs = new FileStream(sImagePath, FileMode.Open, FileAccess.Read)) { imgp.FeedData(fs); } DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties(); nvdp.Id = 1025; nvdp.Name = "Picture 1"; nvdp.Description = "logo"; DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks(); picLocks.NoChangeAspect = true; picLocks.NoChangeArrowheads = true; DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties nvpdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties(); nvpdp.PictureLocks = picLocks; DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties nvpp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties(); nvpp.NonVisualDrawingProperties = nvdp; nvpp.NonVisualPictureDrawingProperties = nvpdp; DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch(); stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle(); DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill blipFill = new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill(); DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip(); blip.Embed = dp.GetIdOfPart(imgp); blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print; blipFill.Blip = blip; blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle(); blipFill.Append(stretch); DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D(); DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset(); offset.X = 0; offset.Y = 0; t2d.Offset = offset; Bitmap bm = new Bitmap(sImagePath); DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents(); extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution); extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution); bm.Dispose(); t2d.Extents = extents; DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties sp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties(); sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto; sp.Transform2D = t2d; DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry(); prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle; prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList(); sp.Append(prstGeom); sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill()); DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture(); picture.NonVisualPictureProperties = nvpp; picture.BlipFill = blipFill; picture.ShapeProperties = sp; DocumentFormat.OpenXml.Drawing.Spreadsheet.Position pos = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Position(); pos.X = 18 * 914400 / 72; pos.Y = 28 * 914400 / 72; Extent ext = new Extent(); ext.Cx = extents.Cx; ext.Cy = extents.Cy; AbsoluteAnchor anchor = new AbsoluteAnchor(); anchor.Position = pos; anchor.Extent = ext; anchor.Append(picture); anchor.Append(new ClientData()); WorksheetDrawing wsd = new WorksheetDrawing(); wsd.Append(anchor); Drawing drawing = new Drawing(); drawing.Id = dp.GetIdOfPart(imgp); wsd.Save(dp); return drawing; }
private 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 }
/// <summary> /// Insert a chart into a chartsheet. /// </summary> /// <param name="Chart">An SLChart object with the chart's properties already set.</param> /// <param name="ChartsheetName">The name should not be blank, nor exceed 31 characters. And it cannot contain these characters: \/?*[] It cannot be the same as an existing name (case-insensitive). But there's nothing stopping you from using 3 spaces as a name.</param> /// <returns>True if successful. False otherwise.</returns> public bool InsertChart(Charts.SLChart Chart, string ChartsheetName) { if (!SLTool.CheckSheetChartName(ChartsheetName)) { return false; } foreach (SLSheet sheet in slwb.Sheets) { if (sheet.Name.Equals(ChartsheetName, StringComparison.OrdinalIgnoreCase)) { return false; } } ChartsheetPart csp = wbp.AddNewPart<ChartsheetPart>(); #region the drawing part DrawingsPart dp = csp.AddNewPart<DrawingsPart>(); dp.WorksheetDrawing = new Xdr.WorksheetDrawing(); ChartPart chartp = dp.AddNewPart<ChartPart>(); chartp.ChartSpace = Chart.ToChartSpace(ref chartp); Xdr.AbsoluteAnchor absanchor = new Xdr.AbsoluteAnchor(); absanchor.Append(new Xdr.Position() { X = 0, Y = 0 }); // The paper size is involved. The default is Letter, which is 11 inch by 8.5 inch // with landscape orientation. // The page margin is also involved. In particular, the left and right margins for // the width, and top and bottom margins for the height. // We're using the "default" margin settings, which is // left=0.7, right=0.7, top=0.75, bottom=0.75 // Excel also has a buffer of 0.12 inches around the sides... // And so, // 8668512 = (11 - 0.7 - 0.7 - 0.12) * 914400 // 6291072 = (8.5 - 0.75 - 0.75 - 0.12) * 914400 absanchor.Append(new Xdr.Extent() { Cx = 8668512, Cy = 6291072 }); Xdr.GraphicFrame gf = new Xdr.GraphicFrame() { Macro = "" }; gf.NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties(); gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties() { Id = 2, Name = Chart.ChartName }; gf.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties() { GraphicFrameLocks = new A.GraphicFrameLocks() { NoGrouping = true } }; gf.Transform = new Xdr.Transform() { Offset = new A.Offset() { X = 0, Y = 0 }, 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) }); absanchor.Append(gf); absanchor.Append(new Xdr.ClientData()); dp.WorksheetDrawing.Append(absanchor); #endregion #region the chartsheet part csp.Chartsheet = new Chartsheet(); csp.Chartsheet.ChartSheetProperties = new ChartSheetProperties(); csp.Chartsheet.ChartSheetViews = new ChartSheetViews(); csp.Chartsheet.ChartSheetViews.Append(new ChartSheetView() { WorkbookViewId = 0 }); csp.Chartsheet.PageMargins = new PageMargins() { Left = SLConstants.NormalLeftMargin, Right = SLConstants.NormalRightMargin, Top = SLConstants.NormalTopMargin, Bottom = SLConstants.NormalBottomMargin, Header = SLConstants.NormalHeaderMargin, Footer = SLConstants.NormalFooterMargin }; csp.Chartsheet.Drawing = new DocumentFormat.OpenXml.Spreadsheet.Drawing() { Id = csp.GetIdOfPart(dp) }; #endregion ++giWorksheetIdCounter; slwb.Sheets.Add(new SLSheet(ChartsheetName, (uint)giWorksheetIdCounter, wbp.GetIdOfPart(csp), SLSheetType.Chartsheet)); return true; }
private static Drawing GetLogo(ExcelType excelType, WorksheetPart wsp, out uint startIndex) { var dp = wsp.AddNewPart<DrawingsPart>(); Drawing drawing; var sImagePath = GetLogoPath(excelType); if (string.IsNullOrEmpty(sImagePath)) { startIndex = 1; return null; } startIndex = 8; //sorry, I must do it in this way, MEmory stream oesn't work in FeedData. There are several discussions in the internet about it. ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp)); var bm = new Bitmap(Resource.CEEX_small); //var bm = new Bitmap(@"ExcelProvider\_img\CEEX_small.png"); using (var ms = new MemoryStream()) { bm.Save(ms,ImageFormat.Png); ms.Seek(0, 0); imgp.FeedData(ms); } var nvdp = new NonVisualDrawingProperties(); nvdp.Id = 1025; nvdp.Name = "logo"; nvdp.Description = "polymathlogo"; var picLocks = new PictureLocks(); picLocks.NoChangeAspect = true; picLocks.NoChangeArrowheads = true; var nvpdp = new NonVisualPictureDrawingProperties(); nvpdp.PictureLocks = picLocks; var nvpp = new NonVisualPictureProperties(); nvpp.NonVisualDrawingProperties = nvdp; nvpp.NonVisualPictureDrawingProperties = nvpdp; var stretch = new Stretch(); stretch.FillRectangle = new FillRectangle(); var blipFill = new BlipFill(); var blip = new Blip(); blip.Embed = dp.GetIdOfPart(imgp); blip.CompressionState = BlipCompressionValues.Print; blipFill.Blip = blip; blipFill.SourceRectangle = new SourceRectangle(); blipFill.Append(stretch); var t2d = new Transform2D(); var offset = new Offset(); offset.X = 0; offset.Y = 0; t2d.Offset = offset; //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML //http://stackoverflow.com/questions/1341930/pixel-to-centimeter //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c var extents = new Extents(); extents.Cx = bm.Width * (long)(914400 / bm.HorizontalResolution); extents.Cy = bm.Height * (long)(914400 / bm.VerticalResolution); bm.Dispose(); t2d.Extents = extents; var sp = new ShapeProperties(); sp.BlackWhiteMode = BlackWhiteModeValues.Auto; sp.Transform2D = t2d; var prstGeom = new PresetGeometry(); prstGeom.Preset = ShapeTypeValues.Rectangle; prstGeom.AdjustValueList = new AdjustValueList(); sp.Append(prstGeom); sp.Append(new NoFill()); var picture = new Picture(); picture.NonVisualPictureProperties = nvpp; picture.BlipFill = blipFill; picture.ShapeProperties = sp; var pos = new Position(); pos.X = 0; pos.Y = 0; var ext = new Extent(); ext.Cx = extents.Cx; ext.Cy = extents.Cy; var anchor = new AbsoluteAnchor(); anchor.Position = pos; anchor.Extent = ext; anchor.Append(picture); anchor.Append(new ClientData()); var wsd = new WorksheetDrawing(); wsd.Append(anchor); drawing = new Drawing(); drawing.Id = dp.GetIdOfPart(imgp); wsd.Save(dp); return drawing; }
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 }
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 }