public static void CreateLineChart(DrawingsPart part, string formulaCat, string formulaVal, int row, bool newe, string title) { ChartPart chartPart1 = part.AddNewPart <ChartPart>(); GenerateChartPartContentLine(chartPart1, formulaCat, formulaVal, title); GeneratePartContentPie(part, chartPart1, row, newe); }
private void AppendSheetData(SheetData sheetData1, WorksheetPart worksheetPart, string drawingrID) { for (int rowIndex = 0; rowIndex < m_table.Rows.Count; rowIndex++) { Row row = new Row() { RowIndex = (UInt32Value)(rowIndex + 1U) }; DataRow tableRow = m_table.Rows[rowIndex]; for (int colIndex = 0; colIndex < tableRow.ItemArray.Length; colIndex++) { Cell cell = new Cell(); CellValue cellValue = new CellValue(); object data = tableRow.ItemArray[colIndex]; if (data is int || data is float || data is double) { cellValue.Text = data.ToString(); cell.Append(cellValue); } else if (data is string) { cell.DataType = CellValues.SharedString; string text = data.ToString(); if (!sharedStrings.Contains(text)) { sharedStrings.Add(text); } cellValue.Text = sharedStrings.IndexOf(text).ToString(); cell.Append(cellValue); } else if (data is Image) { DrawingsPart drawingsPart = null; Xdr.WorksheetDrawing worksheetDrawing = null; if (worksheetPart.DrawingsPart == null) { drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(drawingrID); worksheetDrawing = new Xdr.WorksheetDrawing(); worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); drawingsPart.WorksheetDrawing = worksheetDrawing; } else if (worksheetPart.DrawingsPart != null && worksheetPart.DrawingsPart.WorksheetDrawing != null) { drawingsPart = worksheetPart.DrawingsPart; worksheetDrawing = worksheetPart.DrawingsPart.WorksheetDrawing; } string imagerId = GetNextRelationShipId(); Xdr.TwoCellAnchor cellAnchor = AddTwoCellAnchor(rowIndex, 1, rowIndex, 1, imagerId); worksheetDrawing.Append(cellAnchor); ImagePart imagePart = drawingsPart.AddNewPart <ImagePart>("image/png", imagerId); GenerateImagePartContent(imagePart, data as Image); } row.Append(cell); } sheetData1.Append(row); } }
protected static ChartPart CreateChartPart(DrawingsPart drawingsPart) { ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") }); return(chartPart); }
public void WriteDrawings(WorksheetPart worksheetPart, OpenXmlWriter writer) { if (!_images.Any() || !_imageDetails.Any()) { return; } var drawingPartId = "drawingPart1"; DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(drawingPartId); int imgCounter = 0; foreach (var image in _images) { var imagePart1 = drawingsPart.AddNewPart <ImagePart>("image/png", "image" + imgCounter); imgCounter++; using (var ms = new MemoryStream()) { image.Save(ms, ImageFormat.Png); ms.Position = 0; imagePart1.FeedData(ms); } } using (var drawingsWriter = OpenXmlWriter.Create(drawingsPart)) { drawingsWriter.WriteStartElement(new Xdr.WorksheetDrawing(), new List <OpenXmlAttribute>(), new List <KeyValuePair <string, string> >() { new KeyValuePair <string, string>("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"), new KeyValuePair <string, string>("a", "http://schemas.openxmlformats.org/drawingml/2006/main") }); foreach (var imageDetails in _imageDetails) { var twoCellAnchor1 = createImageAnchor(imageDetails); drawingsWriter.WriteElement(twoCellAnchor1); } drawingsWriter.WriteEndElement(); } var idAtt = new OpenXmlAttribute("id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships", drawingPartId); var pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D }; // PageSetup pageSetup1 = new PageSetup() { PaperSize = (UInt32Value)9U, Orientation = OrientationValues.Portrait}; writer.WriteElement(pageMargins1); //writer.WriteElement(pageSetup1); writer.WriteStartElement(new Drawing(), new List <OpenXmlAttribute>() { idAtt }); writer.WriteEndElement(); }
public void exec(System.Data.DataTable dt) { System.IO.File.Copy(template, copie, true); SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(copie, true); WorkbookPart workbookPart = myWorkbook.WorkbookPart; WorksheetPart wsPart = XcelWin.getWorksheetPartByName(myWorkbook, "Feuil1"); //DocumentFormat.OpenXml.Spreadsheet.Columns columns = new Columns(); //for(int i=0;i<size.Length;i++) //{ // DocumentFormat.OpenXml.Spreadsheet.Column c = new Column(); // c.CustomWidth = true; // c.Min = (uint) i+1; // c.Max = (uint) i+1; // c.Width = size[i]; // columns.Append(c); //} //DocumentFormat.OpenXml.Spreadsheet.Columns cc = new Columns(); //cc.Append(new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = 1, Max = 3, CustomWidth = true, Width = 5 }); ////wsPart.Worksheet.Append(cc); //Worksheet ws = wsPart.Worksheet; //ws.Append(cc); DrawingsPart drawingsPart1 = wsPart.AddNewPart <DrawingsPart>("rId1"); XcelWin.GenerateDrawingsPart1Content(drawingsPart1); ChartPart chartPart1 = drawingsPart1.AddNewPart <ChartPart>("rId1"); XcelWin.GenerateChartPart1Content(chartPart1); Drawing drawing1 = new Drawing() { Id = "rId1" }; wsPart.Worksheet.Append(drawing1); workbookPart.Workbook.Save(); myWorkbook.Close(); }
public override C.Chart AddChart(DrawingsPart drawingsPart, EditingLanguage language) { var chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new C.ChartSpace() { EditingLanguage = new C.EditingLanguage() { Val = language.GetEnumMemberValue() } }; return(chartPart.ChartSpace.AppendChild(new C.Chart() { PlotArea = new C.PlotArea() })); }
public void Write(WorksheetPart part, Chart chart) { DrawingsPart drawingsPart1 = part.AddNewPart <DrawingsPart>("rId1"); GenerateDrawingsPart1Content(drawingsPart1, chart); ChartPart chartPart1 = drawingsPart1.AddNewPart <ChartPart>("rId1"); GenerateChartPart1Content(chartPart1, chart); var drawing = new Drawing() { Id = "rId1" }; part.Worksheet.Append(drawing); }
public static void InsertImage(WorksheetPart sheet1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, Stream imageStream) { //Inserting a drawing element in worksheet //Make sure that the relationship id is same for drawing element in worksheet and its relationship part int drawingPartId = GetNextRelationShipID(sheet1); Drawing drawing1 = new Drawing() { Id = "rId" + drawingPartId.ToString() }; //Check whether the WorksheetPart contains VmlDrawingParts (LegacyDrawing element) if (sheet1.VmlDrawingParts == null) { //if there is no VMLDrawing part (LegacyDrawing element) exists, just append the drawing part to the sheet sheet1.Worksheet.Append(drawing1); } else { //if VmlDrawingPart (LegacyDrawing element) exists, then find the index of legacy drawing in the sheet and inserts the new drawing element before VMLDrawing part int legacyDrawingIndex = GetIndexofLegacyDrawing(sheet1); if (legacyDrawingIndex != -1) { sheet1.Worksheet.InsertAt <OpenXmlElement>(drawing1, legacyDrawingIndex); } else { sheet1.Worksheet.Append(drawing1); } } //Adding the drawings.xml part DrawingsPart drawingsPart1 = sheet1.AddNewPart <DrawingsPart>("rId" + drawingPartId.ToString()); GenerateDrawingsPart1Content(drawingsPart1, startRowIndex, startColumnIndex, endRowIndex, endColumnIndex); //Adding the image ImagePart imagePart1 = drawingsPart1.AddNewPart <ImagePart>("image/jpeg", "rId1"); imagePart1.FeedData(imageStream); }
internal virtual void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location) { DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>(); writer.WriteStartElement(new Drawing() { Id = part.GetIdOfPart(drawingsPart) }); writer.WriteEndElement(); ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") }); Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart()); // Set chart title chartContainer.AppendChild(ChartPropertySetter.SetTitle(ChartPropertySetter.ChartProperties.Title)); chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted() { Val = false }); // Create a new clustered column chart. PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea()); uint chartSeriesCounter = 0; OpenXmlCompositeElement chart = ChartPropertySetter.CreateChart(plotArea); foreach (var chartDataSeriesGrouped in ChartData.GroupBy(x => x.Series)) { // Set chart and series depending on type. OpenXmlCompositeElement chartSeries = ChartPropertySetter.CreateChartSeries(chartDataSeriesGrouped.Key, chartSeriesCounter, chart); // Every method from chartPropertySetter can be overriden to customize chart export. ChartPropertySetter.SetChartShapeProperties(chartSeries); ChartPropertySetter.SetChartAxis(chartSeries, chartDataSeriesGrouped.ToList()); chartSeriesCounter++; } chart.Append(new AxisId() { Val = new UInt32Value(48650112u) }); chart.Append(new AxisId() { Val = new UInt32Value(48672768u) }); // Add the Category Axis (X axis). ChartPropertySetter.SetLineCategoryAxis(plotArea); // Add the Value Axis (Y axis). ChartPropertySetter.SetValueAxis(plotArea); ChartPropertySetter.SetLegend(chartContainer); ChartPropertySetter.SetChartLocation(drawingsPart, chartPart, location); }
/// <summary> /// draw the 2D bar chart /// index start from 1 /// </summary> /// <param name="startx">index start from 1 for row</param> /// <param name="starty">index start from 1 for column</param> /// <param name="columnCount"></param> /// <param name="rowCount"></param> public void InsertChartInSpreadsheet(WorksheetPart sheetpart, string sheetName, int startx, int starty, int columnCount, int rowCount, int chart_pointx, int chart_pointy) { WorksheetPart worksheetPart = CurrentWorksheetPart; #region SDK How to example code // Add a new drawing to the worksheet. DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(); worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) }); worksheetPart.Worksheet.Save(); // Add a new chart and set the chart language to English-US. ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") }); DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>( new DocumentFormat.OpenXml.Drawing.Charts.Chart()); // Create a new clustered column chart. PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea()); Layout layout = plotArea.AppendChild <Layout>(new Layout()); BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart(new BarDirection() { Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column) }, new BarGrouping() { Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered) })); #endregion string sheetName = GetCurrentSheetName(); string columnName = GetColumnName(starty - 1); string formulaString = string.Format("{0}!${1}${2}:${3}${4}", sheetName, columnName, startx + 1, columnName, startx + rowCount - 1); CategoryAxisData cad = new CategoryAxisData(); cad.StringReference = new StringReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString) }; uint i = 0; for (int sIndex = 1; sIndex < columnCount; sIndex++) { columnName = GetColumnName(starty + sIndex - 1); formulaString = string.Format("{0}!${1}${2}", sheetName, columnName, startx); SeriesText st = new SeriesText(); st.StringReference = new StringReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString) }; formulaString = string.Format("{0}!${1}${2}:${3}${4}", sheetName, columnName, startx + 1, columnName, startx + rowCount - 1); DocumentFormat.OpenXml.Drawing.Charts.Values v = new DocumentFormat.OpenXml.Drawing.Charts.Values(); v.NumberReference = new NumberReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString) }; BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index() { Val = new UInt32Value(i) }, new Order() { Val = new UInt32Value(i) }, st, v)); if (sIndex == 1) { barChartSeries.AppendChild(cad); } i++; } #region SDK how to example Code barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) }); barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) }); // Add the Category Axis. CategoryAxis catAx = plotArea.AppendChild <CategoryAxis>(new CategoryAxis(new AxisId() { Val = new UInt32Value(48650112u) }, new Scaling(new Orientation() { Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>( DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new AxisPosition() { Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom) }, new TickLabelPosition() { Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = new UInt32Value(48672768U) }, new Crosses() { Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero) }, new AutoLabeled() { Val = new BooleanValue(true) }, new LabelAlignment() { Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center) }, new LabelOffset() { Val = new UInt16Value((ushort)100) })); // Add the Value Axis. ValueAxis valAx = plotArea.AppendChild <ValueAxis>(new ValueAxis(new AxisId() { Val = new UInt32Value(48672768u) }, new Scaling(new Orientation() { Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>( DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new AxisPosition() { Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left) }, new MajorGridlines(), new DocumentFormat.OpenXml.Drawing.Charts.NumberFormat() { FormatCode = new StringValue("General"), SourceLinked = new BooleanValue(true) }, new TickLabelPosition() { Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = new UInt32Value(48650112U) }, new Crosses() { Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero) }, new CrossBetween() { Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between) })); // Add the chart Legend. Legend legend = chart.AppendChild <Legend>(new Legend(new LegendPosition() { Val = new EnumValue <LegendPositionValues>(LegendPositionValues.Right) }, new Layout())); chart.Append(new PlotVisibleOnly() { Val = new BooleanValue(true) }); // Save the chart part. chartPart.ChartSpace.Save(); // Position the chart on the worksheet using a TwoCellAnchor object. drawingsPart.WorksheetDrawing = new WorksheetDrawing(); TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild <TwoCellAnchor>(new TwoCellAnchor()); twoCellAnchor.Append(new FromMarker(new ColumnId("9"), new ColumnOffset("581025"), new RowId("17"), new RowOffset("114300"))); twoCellAnchor.Append(new ToMarker(new ColumnId("17"), new ColumnOffset("276225"), new RowId("32"), new RowOffset("0"))); // Append a GraphicFrame to the TwoCellAnchor object. DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame = twoCellAnchor.AppendChild <DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame>( new DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame()); graphicFrame.Macro = ""; graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties( new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties() { Id = new UInt32Value(2u), Name = "Chart 1" }, new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties())); graphicFrame.Append(new Transform(new Offset() { X = 0L, Y = 0L }, new Extents() { Cx = 0L, Cy = 0L })); graphicFrame.Append(new Graphic(new GraphicData(new ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) }) { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" })); twoCellAnchor.Append(new ClientData()); // Save the WorksheetDrawing object. drawingsPart.WorksheetDrawing.Save(); #endregion }
//gavdcodeend 10 //gavdcodebegin 11 private static void ExcelOpenXmlInsertChart() { Dictionary <string, int> chartData = new Dictionary <string, int>(); chartData.Add("abc", 1); chartData.Add("def", 2); chartData.Add("ghi", 1); using (SpreadsheetDocument myExcelDoc = SpreadsheetDocument.Open(@"C:\Temporary\ExcelDoc01.xlsx", true)) { // Use the name of the sheet IEnumerable <Sheet> mySheets = myExcelDoc.WorkbookPart.Workbook. Descendants <Sheet>().Where(s => s.Name == "NewSheet"); if (mySheets.Count() == 0) { return; } WorksheetPart myWorksheetPart = (WorksheetPart)myExcelDoc.WorkbookPart. GetPartById(mySheets.First().Id); // Add a new drawing to the worksheet DrawingsPart myDrawingsPart = myWorksheetPart.AddNewPart <DrawingsPart>(); myWorksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing() { Id = myWorksheetPart.GetIdOfPart(myDrawingsPart) }); myWorksheetPart.Worksheet.Save(); // Add a new chart and set the chart language to English-US ChartPart myChartPart = myDrawingsPart.AddNewPart <ChartPart>(); myChartPart.ChartSpace = new DrCh.ChartSpace(); myChartPart.ChartSpace.Append(new DrCh.EditingLanguage() { Val = new StringValue("en-US") }); DrCh.Chart myChart = myChartPart.ChartSpace.AppendChild <DrCh.Chart>(new DrCh.Chart()); // Create a new clustered column chart DrCh.PlotArea plotArea = myChart.AppendChild <DrCh.PlotArea>( new DrCh.PlotArea()); DrCh.Layout layout = plotArea.AppendChild <DrCh.Layout>(new DrCh.Layout()); DrCh.BarChart barChart = plotArea.AppendChild <DrCh.BarChart>( new DrCh.BarChart(new DrCh.BarDirection() { Val = new EnumValue <DrCh.BarDirectionValues> (DrCh.BarDirectionValues.Column) }, new DrCh.BarGrouping() { Val = new EnumValue <DrCh.BarGroupingValues> (DrCh.BarGroupingValues.Clustered) })); uint myIndex = 0; foreach (string oneKey in chartData.Keys) { DrCh.BarChartSeries barChartSeries = barChart.AppendChild <DrCh.BarChartSeries>(new DrCh.BarChartSeries(new DrCh.Index() { Val = new UInt32Value(myIndex) }, new DrCh.Order() { Val = new UInt32Value(myIndex) }, new DrCh.SeriesText(new DrCh.NumericValue() { Text = oneKey }))); DrCh.StringLiteral strLit = barChartSeries.AppendChild <DrCh. CategoryAxisData>(new DrCh.CategoryAxisData()). AppendChild <DrCh.StringLiteral>(new DrCh.StringLiteral()); strLit.Append(new DrCh.PointCount() { Val = new UInt32Value(1U) }); // Use the title for the graphic strLit.AppendChild <DrCh.StringPoint>(new DrCh.StringPoint() { Index = new UInt32Value(0U) }). Append(new DrCh.NumericValue("My New Graphic")); DrCh.NumberLiteral numLit = barChartSeries.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Values>( new DocumentFormat.OpenXml.Drawing.Charts.Values()). AppendChild <DrCh.NumberLiteral>(new DrCh.NumberLiteral()); numLit.Append(new DrCh.FormatCode("General")); numLit.Append(new DrCh.PointCount() { Val = new UInt32Value(1U) }); numLit.AppendChild <DrCh.NumericPoint>(new DrCh.NumericPoint() { Index = new UInt32Value(0u) }).Append (new DrCh.NumericValue(chartData[oneKey].ToString())); myIndex++; } barChart.Append(new DrCh.AxisId() { Val = new UInt32Value(48650112u) }); barChart.Append(new DrCh.AxisId() { Val = new UInt32Value(48672768u) }); // Add the Category Axis. DrCh.CategoryAxis catAx = plotArea.AppendChild <DrCh.CategoryAxis> (new DrCh.CategoryAxis(new DrCh.AxisId() { Val = new UInt32Value(48650112u) }, new DrCh.Scaling(new DrCh.Orientation() { Val = new EnumValue <DocumentFormat. OpenXml.Drawing.Charts.OrientationValues>( DrCh.OrientationValues.MinMax) }), new DrCh.AxisPosition() { Val = new EnumValue <DrCh.AxisPositionValues> (DrCh.AxisPositionValues.Bottom) }, new DrCh.TickLabelPosition() { Val = new EnumValue <DrCh.TickLabelPositionValues> (DrCh.TickLabelPositionValues.NextTo) }, new DrCh.CrossingAxis() { Val = new UInt32Value(48672768U) }, new DrCh.Crosses() { Val = new EnumValue <DrCh.CrossesValues>( DrCh.CrossesValues.AutoZero) }, new DrCh.AutoLabeled() { Val = new BooleanValue(true) }, new DrCh.LabelAlignment() { Val = new EnumValue <DrCh.LabelAlignmentValues>( DrCh.LabelAlignmentValues.Center) }, new DrCh.LabelOffset() { Val = new UInt16Value((ushort)100) })); // Add the Value Axis. DrCh.ValueAxis valAx = plotArea.AppendChild <DrCh.ValueAxis>( new DrCh.ValueAxis(new DrCh.AxisId() { Val = new UInt32Value(48672768u) }, new DrCh.Scaling(new DrCh.Orientation() { Val = new EnumValue <DrCh.OrientationValues>( DrCh.OrientationValues.MinMax) }), new DrCh.AxisPosition() { Val = new EnumValue <DrCh.AxisPositionValues>( DrCh.AxisPositionValues.Left) }, new DrCh.MajorGridlines(), new DrCh.NumberingFormat() { FormatCode = new StringValue("General"), SourceLinked = new BooleanValue(true) }, new DrCh.TickLabelPosition() { Val = new EnumValue <DrCh.TickLabelPositionValues> (DrCh.TickLabelPositionValues.NextTo) }, new DrCh.CrossingAxis() { Val = new UInt32Value(48650112U) }, new DrCh.Crosses() { Val = new EnumValue <DrCh.CrossesValues> (DrCh.CrossesValues.AutoZero) }, new DrCh.CrossBetween() { Val = new EnumValue <DrCh.CrossBetweenValues> (DrCh.CrossBetweenValues.Between) })); // Add the chart Legend. DrCh.Legend myLegend = myChart.AppendChild <DrCh.Legend>( new DrCh.Legend(new DrCh.LegendPosition() { Val = new EnumValue <DrCh.LegendPositionValues> (DrCh.LegendPositionValues.Right) }, new DrCh.Layout())); myChart.Append(new DrCh.PlotVisibleOnly() { Val = new BooleanValue(true) }); myChartPart.ChartSpace.Save(); // Position the chart on the worksheet using a TwoCellAnchor object. myDrawingsPart.WorksheetDrawing = new DrSp.WorksheetDrawing(); DrSp.TwoCellAnchor twoCellAnchor = myDrawingsPart.WorksheetDrawing. AppendChild <DrSp.TwoCellAnchor>( new DrSp.TwoCellAnchor()); twoCellAnchor.Append(new DrSp.FromMarker(new DrSp.ColumnId("9"), new DrSp.ColumnOffset("581025"), new DrSp.RowId("17"), new DrSp.RowOffset("114300"))); twoCellAnchor.Append(new DrSp.ToMarker(new DrSp.ColumnId("17"), new DrSp.ColumnOffset("276225"), new DrSp.RowId("32"), new DrSp.RowOffset("0"))); // Append a GraphicFrame to the TwoCellAnchor object. DrSp.GraphicFrame myGraphicFrame = twoCellAnchor.AppendChild <DrSp.GraphicFrame>(new DrSp.GraphicFrame()); myGraphicFrame.Macro = ""; myGraphicFrame.Append(new DrSp.NonVisualGraphicFrameProperties( new DrSp.NonVisualDrawingProperties() { Id = new UInt32Value(2u), Name = "Chart 1" }, new DrSp.NonVisualGraphicFrameDrawingProperties())); myGraphicFrame.Append(new DrSp.Transform( new DocumentFormat.OpenXml.Drawing.Offset() { X = 0L, Y = 0L }, new DocumentFormat.OpenXml.Drawing.Extents() { Cx = 0L, Cy = 0L })); myGraphicFrame.Append( new DocumentFormat.OpenXml.Drawing.Graphic( new DocumentFormat.OpenXml.Drawing.GraphicData( new DrCh.ChartReference() { Id = myDrawingsPart.GetIdOfPart(myChartPart) }) { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" })); twoCellAnchor.Append(new DrSp.ClientData()); myDrawingsPart.WorksheetDrawing.Save(); } }
public void CreateExcelDoc(string fileName) { List <Student> students = new List <Student>(); Initizalize(students); using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Students" }; SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); // Add drawing part to WorksheetPart DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(); worksheetPart.Worksheet.Append(new Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) }); worksheetPart.Worksheet.Save(); drawingsPart.WorksheetDrawing = new WorksheetDrawing(); sheets.Append(sheet); workbookPart.Workbook.Save(); // Add a new chart and set the chart language ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.AppendChild(new EditingLanguage() { Val = "en-US" }); Chart chart = chartPart.ChartSpace.AppendChild(new Chart()); chart.AppendChild(new AutoTitleDeleted() { Val = true }); // We don't want to show the chart title // Create a new Clustered Column Chart PlotArea plotArea = chart.AppendChild(new PlotArea()); Layout layout = plotArea.AppendChild(new Layout()); BarChart barChart = plotArea.AppendChild(new BarChart( new BarDirection() { Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column) }, new BarGrouping() { Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered) }, new VaryColors() { Val = false } )); // Constructing header Row row = new Row(); int rowIndex = 1; row.AppendChild(ConstructCell(string.Empty, CellValues.String)); foreach (var month in Months.Short) { row.AppendChild(ConstructCell(month, CellValues.String)); } // Insert the header row to the Sheet Data sheetData.AppendChild(row); rowIndex++; // Create chart series for (int i = 0; i < students.Count; i++) { BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries( new Index() { Val = (uint)i }, new Order() { Val = (uint)i }, new SeriesText(new NumericValue() { Text = students[i].Name }) )); // Adding category axis to the chart CategoryAxisData categoryAxisData = barChartSeries.AppendChild(new CategoryAxisData()); // Category // Constructing the chart category string formulaCat = "Students!$B$1:$G$1"; StringReference stringReference = categoryAxisData.AppendChild(new StringReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaCat } }); StringCache stringCache = stringReference.AppendChild(new StringCache()); stringCache.Append(new PointCount() { Val = (uint)Months.Short.Length }); for (int j = 0; j < Months.Short.Length; j++) { stringCache.AppendChild(new NumericPoint() { Index = (uint)j }).Append(new NumericValue(Months.Short[j])); } } var chartSeries = barChart.Elements <BarChartSeries>().GetEnumerator(); for (int i = 0; i < students.Count; i++) { row = new Row(); row.AppendChild(ConstructCell(students[i].Name, CellValues.String)); chartSeries.MoveNext(); string formulaVal = string.Format("Students!$B${0}:$G${0}", rowIndex); DocumentFormat.OpenXml.Drawing.Charts.Values values = chartSeries.Current.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values()); NumberReference numberReference = values.AppendChild(new NumberReference() { Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaVal } }); NumberingCache numberingCache = numberReference.AppendChild(new NumberingCache()); numberingCache.Append(new PointCount() { Val = (uint)Months.Short.Length }); for (uint j = 0; j < students[i].Values.Length; j++) { var value = students[i].Values[j]; row.AppendChild(ConstructCell(value.ToString(), CellValues.Number)); numberingCache.AppendChild(new NumericPoint() { Index = j }).Append(new NumericValue(value.ToString())); } sheetData.AppendChild(row); rowIndex++; } barChart.AppendChild(new DataLabels( new ShowLegendKey() { Val = false }, new ShowValue() { Val = false }, new ShowCategoryName() { Val = false }, new ShowSeriesName() { Val = false }, new ShowPercent() { Val = false }, new ShowBubbleSize() { Val = false } )); barChart.Append(new AxisId() { Val = 48650112u }); barChart.Append(new AxisId() { Val = 48672768u }); // Adding Category Axis plotArea.AppendChild( new CategoryAxis( new AxisId() { Val = 48650112u }, new Scaling(new Orientation() { Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new Delete() { Val = false }, new AxisPosition() { Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom) }, new TickLabelPosition() { Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = 48672768u }, new Crosses() { Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero) }, new AutoLabeled() { Val = true }, new LabelAlignment() { Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center) } )); // Adding Value Axis plotArea.AppendChild( new ValueAxis( new AxisId() { Val = 48672768u }, new Scaling(new Orientation() { Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new Delete() { Val = false }, new AxisPosition() { Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left) }, new MajorGridlines(), new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat() { FormatCode = "General", SourceLinked = true }, new TickLabelPosition() { Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = 48650112u }, new Crosses() { Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero) }, new CrossBetween() { Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between) } )); chart.Append( new PlotVisibleOnly() { Val = true }, new DisplayBlanksAs() { Val = new EnumValue <DisplayBlanksAsValues>(DisplayBlanksAsValues.Gap) }, new ShowDataLabelsOverMaximum() { Val = false } ); chartPart.ChartSpace.Save(); // Positioning the chart on the spreadsheet TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor()); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker( new ColumnId("0"), new ColumnOffset("0"), new RowId((rowIndex + 2).ToString()), new RowOffset("0") )); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker( new ColumnId("8"), new ColumnOffset("0"), new RowId((rowIndex + 12).ToString()), new RowOffset("0") )); // Append GraphicFrame to TwoCellAnchor GraphicFrame graphicFrame = twoCellAnchor.AppendChild(new GraphicFrame()); graphicFrame.Macro = string.Empty; graphicFrame.Append(new NonVisualGraphicFrameProperties( new NonVisualDrawingProperties() { Id = 2u, Name = "Sample Chart" }, new NonVisualGraphicFrameDrawingProperties() )); graphicFrame.Append(new Transform( new DocumentFormat.OpenXml.Drawing.Offset() { X = 0L, Y = 0L }, new DocumentFormat.OpenXml.Drawing.Extents() { Cx = 0L, Cy = 0L } )); graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Graphic( new DocumentFormat.OpenXml.Drawing.GraphicData( new ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) } ) { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" } )); twoCellAnchor.Append(new ClientData()); drawingsPart.WorksheetDrawing.Save(); worksheetPart.Worksheet.Save(); } }
private void CreateDrawingBySAX(DrawingsPart drawingsPart) { var data = _data as IReadOnlyList <Sheet3Data>; var dataDic = new Dictionary <C.Values, C.SeriesText>(); for (uint i = 1; i <= data.First().DataDic.Count; i++) { var columnName = OpenXMLExcels.GetColumnNameByIndex(i); dataDic[new C.Values() { NumberReference = new C.NumberReference() { Formula = new C.Formula($"{_sheetName}!${columnName}$2:${columnName}${data.Count + 2}") } }] = new C.SeriesText() { StringReference = new C.StringReference() { Formula = new C.Formula($"{_sheetName}!${columnName}$1") } }; } var chartPart = drawingsPart.AddNewPart <ChartPart>(); drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing(); var twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new Xdr.TwoCellAnchor() { FromMarker = new Xdr.FromMarker() { ColumnId = new Xdr.ColumnId("5"), ColumnOffset = new Xdr.ColumnOffset("581025"), RowId = new Xdr.RowId("4"), RowOffset = new Xdr.RowOffset("114300") }, ToMarker = new Xdr.ToMarker() { ColumnId = new Xdr.ColumnId("13"), ColumnOffset = new Xdr.ColumnOffset("276225"), RowId = new Xdr.RowId("19"), RowOffset = new Xdr.RowOffset("0") } }); twoCellAnchor.Append(new Xdr.GraphicFrame() { NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties() { NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties() { Id = 2, Name = "Chart 1", Title = "产品每月产量折线图" }, NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties() }, Transform = new Xdr.Transform() { Offset = new Offset() { X = 0, Y = 0 }, Extents = new Extents() { Cx = 0, Cy = 0 } }, Graphic = new Graphic(new GraphicData(new C.ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) }) { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart", }) }); twoCellAnchor.Append(new Xdr.ClientData()); using (var writer = OpenXmlWriter.Create(chartPart)) { //S: ChartSpace writer.WriteStartElement(new C.ChartSpace()); writer.WriteElement(new C.EditingLanguage() { Val = "zh-CN" }); //S: Chart writer.WriteStartElement(new C.Chart()); //S: PlotArea writer.WriteStartElement(new C.PlotArea()); writer.WriteElement(new C.Layout()); //S: LineChart writer.WriteStartElement(new C.LineChart()); writer.WriteElement(new C.Grouping() { Val = C.GroupingValues.Standard }); uint index = 0; foreach (var dataKvp in dataDic) { //S: LineChartSeries writer.WriteStartElement(new C.LineChartSeries()); writer.WriteElement(dataKvp.Key); writer.WriteElement(new C.Index() { Val = index }); writer.WriteElement(new C.Order() { Val = index }); writer.WriteElement(dataKvp.Value); if (index++ == 0) { var axisData = new C.CategoryAxisData() { StringReference = new C.StringReference() { Formula = new C.Formula($"{_sheetName}!$A$2:$A${data.Count + 2}") } }; writer.WriteElement(axisData); } //E: LineChartSeries writer.WriteEndElement(); } writer.WriteElement(new C.AxisId() { Val = 0 }); writer.WriteElement(new C.AxisId() { Val = 1 }); //E: LineChart writer.WriteEndElement(); writer.WriteElement(new C.CategoryAxis( new C.Crosses() { Val = C.CrossesValues.AutoZero }, new C.AutoLabeled() { Val = true }, new C.LabelAlignment() { Val = C.LabelAlignmentValues.Center }, new C.LabelOffset() { Val = 100 }) { AxisId = new C.AxisId() { Val = 0 }, Scaling = new C.Scaling(new C.Orientation() { Val = C.OrientationValues.MinMax }), AxisPosition = new C.AxisPosition() { Val = C.AxisPositionValues.Bottom }, TickLabelPosition = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo }, CrossingAxis = new C.CrossingAxis() { Val = 1 }, }); writer.WriteElement(new C.ValueAxis( new C.Crosses() { Val = C.CrossesValues.AutoZero }, new C.CrossBetween() { Val = C.CrossBetweenValues.Between }) { AxisId = new C.AxisId() { Val = 1 }, Scaling = new C.Scaling(new C.Orientation() { Val = C.OrientationValues.MinMax }), AxisPosition = new C.AxisPosition() { Val = C.AxisPositionValues.Left }, MajorGridlines = new C.MajorGridlines(), NumberingFormat = new C.NumberingFormat() { FormatCode = "General", SourceLinked = true }, TickLabelPosition = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo }, CrossingAxis = new C.CrossingAxis() { Val = 0 }, }); //E: PlotArea writer.WriteEndElement(); writer.WriteElement(new C.Legend( new C.LegendPosition() { Val = C.LegendPositionValues.Right }, new C.Layout() )); writer.WriteElement(new C.PlotVisibleOnly() { Val = true }); //E: Chart writer.WriteEndElement(); //E: ChartSpace writer.WriteEndElement(); writer.Close(); } }
public static ChartPart cloneChart(WorksheetPart worksheetPart, string chartTitle, int posLi, int posCol, int largeLi, int largeCol) { //Obtention de tous les ChartParts dans une enumeration DrawingsPart dp = worksheetPart.DrawingsPart; IEnumerable <ChartPart> cps = dp.ChartParts; //Recherche la ChartPart qui porte le titre correspondant ChartPart b = null; foreach (ChartPart cp in cps) { if (cp.ChartSpace.Descendants <Chart.Chart>().First().Descendants <Chart.Title>().First().Descendants <DocumentFormat.OpenXml.Drawing.Run>().First().Text.Text == chartTitle) { b = cp; } } DrawingsPart a = worksheetPart.DrawingsPart; //Ajout de la nouvelle partie et copie ChartPart x = a.AddNewPart <ChartPart>(); Stream stream = b.GetStream(); x.FeedData(stream); string id = a.GetIdOfPart(b); //Copie de l'ancre associée au graph original Draw.TwoCellAnchor newAnchor = null; DocumentFormat.OpenXml.Drawing.Spreadsheet.WorksheetDrawing wsd = a.WorksheetDrawing; foreach (Draw.TwoCellAnchor tca in wsd.Elements <Draw.TwoCellAnchor>()) { string tmp = tca.Descendants <Chart.ChartReference>().First().Id; if (tmp == id) { newAnchor = (Draw.TwoCellAnchor)tca.CloneNode(true); } } //positionnement de la nouvelle ancre int r = Convert.ToInt32(newAnchor.ToMarker.RowId.Text) - Convert.ToInt32(newAnchor.FromMarker.RowId.Text); int c = Convert.ToInt32(newAnchor.ToMarker.ColumnId.Text) - Convert.ToInt32(newAnchor.FromMarker.ColumnId.Text); newAnchor.FromMarker.ColumnId = new Draw.ColumnId() { Text = "" + largeCol * posCol }; newAnchor.ToMarker.ColumnId = new Draw.ColumnId() { Text = "" + (c + largeCol * posCol) }; newAnchor.FromMarker.RowId = new Draw.RowId() { Text = "" + largeLi * posLi }; newAnchor.ToMarker.RowId = new Draw.RowId() { Text = "" + (r + largeLi * posLi) }; newAnchor.Descendants <Chart.ChartReference>().First().Id = a.GetIdOfPart(x); wsd.Append(newAnchor); return(x); }
private static void InsertChartInSpreadsheet(string docName, string worksheetName, string title, Dictionary <string, int> data) { // Open the document for editing. using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true)) { IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>(). Where(s => s.Name == worksheetName); if (sheets.Count() == 0) { // The specified worksheet does not exist. return; } WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id); // Add a new drawing to the worksheet. DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>(); worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) }); worksheetPart.Worksheet.Save(); // Add a new chart and set the chart language to English-US. ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") }); DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>( new DocumentFormat.OpenXml.Drawing.Charts.Chart()); // Create a new clustered column chart. PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea()); Layout layout = plotArea.AppendChild <Layout>(new Layout()); BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart(new BarDirection() { Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column) }, new BarGrouping() { Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered) })); uint i = 0; // Iterate through each key in the Dictionary collection and add the key to the chart Series // and add the corresponding value to the chart Values. foreach (string key in data.Keys) { BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index() { Val = new UInt32Value(i) }, new Order() { Val = new UInt32Value(i) }, new SeriesText(new NumericValue() { Text = key }))); StringLiteral strLit = barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData()).AppendChild <StringLiteral>(new StringLiteral()); strLit.Append(new PointCount() { Val = new UInt32Value(1U) }); strLit.AppendChild <StringPoint>(new StringPoint() { Index = new UInt32Value(0U) }).Append(new NumericValue(key)); NumberLiteral numLit = barChartSeries.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Values>( new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild <NumberLiteral>(new NumberLiteral()); numLit.Append(new FormatCode("General")); numLit.Append(new PointCount() { Val = new UInt32Value(1U) }); numLit.AppendChild <NumericPoint>(new NumericPoint() { Index = new UInt32Value(0u) }) .Append(new NumericValue(data[key].ToString())); i++; } barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) }); barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) }); //// Add the Category Axis. CategoryAxis catAx = plotArea.AppendChild <CategoryAxis>(new CategoryAxis(new AxisId() { Val = new UInt32Value(48650112u) }, new Scaling(new Orientation() { Val = new EnumValue <DocumentFormat. OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new AxisPosition() { Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom) }, new TickLabelPosition() { Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = new UInt32Value(48672768U) }, new Crosses() { Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero) }, new AutoLabeled() { Val = new BooleanValue(true) }, new LabelAlignment() { Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center) }, new LabelOffset() { Val = new UInt16Value((ushort)100) })); // Add the Value Axis. ValueAxis valAx = plotArea.AppendChild <ValueAxis>(new ValueAxis(new AxisId() { Val = new UInt32Value(48672768u) }, new Scaling(new Orientation() { Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>( DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax) }), new AxisPosition() { Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left) }, new MajorGridlines(), new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat() { FormatCode = new StringValue("General"), SourceLinked = new BooleanValue(true) }, new TickLabelPosition() { Val = new EnumValue <TickLabelPositionValues> (TickLabelPositionValues.NextTo) }, new CrossingAxis() { Val = new UInt32Value(48650112U) }, new Crosses() { Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero) }, new CrossBetween() { Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between) })); // Add the chart Legend. Legend legend = chart.AppendChild <Legend>(new Legend(new LegendPosition() { Val = new EnumValue <LegendPositionValues>(LegendPositionValues.Right) }, new Layout())); chart.Append(new PlotVisibleOnly() { Val = new BooleanValue(true) }); // Save the chart part. chartPart.ChartSpace.Save(); // Position the chart on the worksheet using a TwoCellAnchor object. drawingsPart.WorksheetDrawing = new WorksheetDrawing(); TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild <TwoCellAnchor>(new TwoCellAnchor()); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("1"), new ColumnOffset("581025"), new RowId("1"), new RowOffset("114300"))); twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("10"), new ColumnOffset("276225"), new RowId("16"), new RowOffset("0"))); // Append a GraphicFrame to the TwoCellAnchor object. DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame = twoCellAnchor.AppendChild <DocumentFormat.OpenXml. Drawing.Spreadsheet.GraphicFrame>(new DocumentFormat.OpenXml.Drawing. Spreadsheet.GraphicFrame()); graphicFrame.Macro = ""; graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties( new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties() { Id = new UInt32Value(2u), Name = "Chart 1" }, new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties())); graphicFrame.Append(new Transform(new Offset() { X = 0L, Y = 0L }, new Extents() { Cx = 0L, Cy = 0L })); graphicFrame.Append(new Graphic(new GraphicData(new ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) }) { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" })); twoCellAnchor.Append(new ClientData()); // Save the WorksheetDrawing object. drawingsPart.WorksheetDrawing.Save(); } }
internal override void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location) { DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>(); writer.WriteStartElement(new Drawing() { Id = part.GetIdOfPart(drawingsPart) }); writer.WriteEndElement(); ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") }); Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart()); chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted() { Val = false }); // Create a new clustered column chart. PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea()); Layout layout1 = plotArea.AppendChild <Layout>(new Layout()); BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart()); barChart.Append(new BarDirection() { Val = BarDirectionValues.Bar }); barChart.Append(new BarGrouping() { Val = BarGroupingValues.Stacked }); barChart.Append(new GapWidth() { Val = (UInt16Value)75U }); barChart.Append(new Overlap() { Val = 100 }); GanttTypeChart ganttChart = new GanttTypeChart(UserSettings); var groupedData = GanttData .GroupBy(x => x.Name); List <GanttDataPairedSeries> ganttDataWithSeries = new List <GanttDataPairedSeries>(); for (int i = 0; i < groupedData.Max(x => x.Count()); i++) { // For each series create a hidden one for spacing. BarChartSeries barChartSeriesHidden = barChart.AppendChild <BarChartSeries>(new BarChartSeries( new Index() { Val = new UInt32Value((uint)(i * 2)) }, new Order() { Val = new UInt32Value((uint)(i * 2)) }, new SeriesText(new NumericValue() { Text = "Not Active" }))); BarChartSeries barChartSeriesValue = barChart.AppendChild <BarChartSeries>(new BarChartSeries( new Index() { Val = new UInt32Value((uint)(i * 2) + 1) }, new Order() { Val = new UInt32Value((uint)(i * 2) + 1) }, new SeriesText(new NumericValue() { Text = "Time Spent" }))); ganttChart.SetChartShapeProperties(barChartSeriesHidden, visible: false); ganttChart.SetChartShapeProperties(barChartSeriesValue, colorPoints: (uint)GanttData.Count); var ganttData = new List <GanttData>(); foreach (var data in groupedData.Where(x => x.Count() >= i + 1)) { ganttData.Add(data.ElementAt(i)); } ganttDataWithSeries.Add(new GanttDataPairedSeries() { BarChartSeriesHidden = barChartSeriesHidden, BarChartSeriesValue = barChartSeriesValue, Values = ganttData }); } ganttChart.SetChartAxis(ganttDataWithSeries, groupedData.ToList()); barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) }); barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) }); // Add the Category Axis (X axis). ganttChart.SetGanttCategoryAxis(plotArea); // Add the Value Axis (Y axis). ganttChart.SetGanttValueAxis(plotArea, GanttData.Min(x => x.Start), GanttData.Max(x => x.End)); chartContainer.Append(new PlotVisibleOnly() { Val = new BooleanValue(true) }); ganttChart.SetChartLocation(drawingsPart, chartPart, location); }
protected void AddImagePart(string id, string base64Image) { ImagePart = DrawingsPart.AddNewPart <ImagePart>("image/tiff", id); StreamImagePart(base64Image); }
/// <summary> /// Creates a chartsheet part from given data /// </summary> /// <param name="chartType">Type of chart to generate</param> /// <param name="values">Values to represent in the chart</param> /// <param name="headerReference">Columns to be used as series</param> /// <param name="categoryReference">Column to be used as category</param> /// <returns>Chartsheet part with contents related</returns> public static ChartsheetPart Create(SpreadsheetDocument parentDocument, ChartType chartType, List <string> values, List <string> headerReference, string categoryReference) { //Creates base content and associates it to a new chartsheet part WorkbookPart workbook = parentDocument.WorkbookPart; ChartsheetPart chartsheetPart = workbook.AddNewPart <ChartsheetPart>(); XDocument chartsheetDocument = CreateEmptyChartsheet(); chartsheetPart.PutXDocument(chartsheetDocument); //Creates a base drawings part and associates it to the chartsheet part DrawingsPart drawingsPart = chartsheetPart.AddNewPart <DrawingsPart>(); XDocument drawingDocument = CreateEmptyDrawing(); drawingsPart.PutXDocument(drawingDocument); //Adds content to chartsheet document to reference drawing document chartsheetDocument .Element(ns + "chartsheet") .Add( new XElement(ns + "drawing", new XAttribute(relationshipsns + "id", chartsheetPart.GetIdOfPart(drawingsPart)) ) ); //creates the chart part and associates it to the drawings part ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); XDocument chartDocument = CreateChart(chartType, values, headerReference, categoryReference); chartPart.PutXDocument(chartDocument); //Adds content to drawing document to reference chart document drawingDocument .Descendants(drawingns + "graphicData") .First() .Add( new XAttribute("uri", chartns), new XElement(chartns + "chart", new XAttribute(XNamespace.Xmlns + "c", chartns), new XAttribute(XNamespace.Xmlns + "r", relationshipsns), new XAttribute(relationshipsns + "id", drawingsPart.GetIdOfPart(chartPart)) ) ); //Associates the chartsheet part to the workbook part XDocument document = parentDocument.WorkbookPart.GetXDocument(); int sheetId = document.Root.Element(ns + "sheets").Elements(ns + "sheet").Count() + 1; int chartsheetCount = document.Root .Element(ns + "sheets") .Elements(ns + "sheet") .Where( t => t.Attribute("name").Value.StartsWith("chart") ) .Count() + 1; //Adds content to workbook document to reference chartsheet document document.Root .Element(ns + "sheets") .Add( new XElement(ns + "sheet", new XAttribute("name", string.Format("chart{0}", chartsheetCount)), new XAttribute("sheetId", sheetId), new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(chartsheetPart)) ) ); chartsheetPart.PutXDocument(); drawingsPart.PutXDocument(); parentDocument.WorkbookPart.PutXDocument(); return(chartsheetPart); }