/// <summary>
/// Design settings for X axis.
/// </summary>
public CategoryAxis SetGanttCategoryAxis(PlotArea plotArea, bool hide = false)
{
return(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 Delete()
{
Val = hide
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
},
new MajorTickMark()
{
Val = TickMarkValues.None
},
new MinorTickMark()
{
Val = TickMarkValues.Outside
},
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)
})));
}
//https://github.com/OfficeDev/office-content/blob/master/en-us/OpenXMLCon/articles/281776d0-be75-46eb-8fdc-a1f656291175.md
//Here be dragons
internal override void AddData(Statistics stat)
{
// 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.
var chartPart = CreateChartPart(drawingsPart);
DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace
.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Chart());
// Create a new clustered column chart.
PlotArea plotArea = chart.AppendChild(new PlotArea());
plotArea.AppendChild(new Layout());
CreateHistogram(plotArea, stat, 0, 48650112U, 48672768U);
CreateCumulative(plotArea, stat, 1, 438381208U, 438380816U);
// Add the chart Legend.
chart.AppendChild(
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();
AppendGraphicFrame(drawingsPart, chartPart);
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
}
public override OpenXmlCompositeElement CreateChart(PlotArea plotArea)
{
var chart = plotArea.AppendChild <LineChart>(new LineChart());
chart.Append(new Grouping()
{
Val = GroupingValues.Standard
});
chart.Append(new VaryColors()
{
Val = false
});
return(chart);
}
public override OpenXmlCompositeElement CreateChart(PlotArea plotArea)
{
var chart = plotArea.AppendChild <PieChart>(new PieChart());
chart.Append(new VaryColors()
{
Val = true
});
chart.Append(new FirstSliceAngle()
{
Val = (UInt16Value)0U
});
return(chart);
}
protected void AppendCategoryAxis(PlotArea plotArea, uint id, string name, uint crossingAxisId, bool show = true)
{
List <OpenXmlElement> elements = GetAxisElements(id, name, crossingAxisId, show);
elements.Add(new AxisPosition {
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
});
elements.Add(new AutoLabeled {
Val = new BooleanValue(true)
});
elements.Add(new LabelAlignment {
Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center)
});
elements.Add(new LabelOffset {
Val = new UInt16Value((ushort)100)
});
plotArea.AppendChild(new CategoryAxis(elements));
}
public override OpenXmlCompositeElement CreateChart(PlotArea plotArea)
{
var chart = plotArea.AppendChild <BarChart>(new BarChart());
chart.Append(new BarDirection()
{
Val = BarDirectionValues.Column
});
chart.Append(new BarGrouping()
{
Val = BarGroupingValues.Clustered
});
chart.Append(new VaryColors()
{
Val = false
});
return(chart);
}
public IPieChart InsertPieChart()
{
ChartSpace chartSpace = this.chartPart.ChartSpace;
Chart chart = chartSpace.GetFirstChild <Chart>() ?? chartSpace.AppendChild(new Chart());
PlotArea plotArea = chart.PlotArea ?? (chart.PlotArea = new PlotArea());
return(new OpenXmlPieChart(
this,
plotArea.AppendChild(
new DoughnutChart()
.AppendChildFluent(new PieChartSeries()
{
Index = new Index()
{
Val = 0
}
})
)
));
}
public IBarChart InsertBarChart(CartesianAxes axes)
{
ChartSpace chartSpace = this.chartPart.ChartSpace;
Chart chart = chartSpace.GetFirstChild <Chart>() ?? chartSpace.AppendChild(new Chart());
PlotArea plotArea = chart.PlotArea ?? (chart.PlotArea = new PlotArea());
return(new OpenXmlBarChart(
this,
plotArea.AppendChild(
new BarChart()
.AppendChildFluent(new AxisId()
{
Val = axes.CategoryAxis.Id
})
.AppendChildFluent(new AxisId()
{
Val = axes.ValueAxis.Id
})
)
));
}
protected void AppendValueAxis(PlotArea plotArea, uint id, string name, uint crossingAxisId, AxisPositionValues axisPos = AxisPositionValues.Left, TickLabelPositionValues tickPos = TickLabelPositionValues.NextTo, bool showMajorGridlines = true)
{
List <OpenXmlElement> elements = GetAxisElements(id, name, crossingAxisId, tickPosition: tickPos);
elements.Add(new AxisPosition {
Val = new EnumValue <AxisPositionValues>(axisPos)
});
if (showMajorGridlines)
{
elements.Add(new MajorGridlines());
}
elements.Add(new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = new StringValue("General"),
SourceLinked = new BooleanValue(true)
});
elements.Add(new CrossBetween {
Val = new EnumValue <CrossBetweenValues>(CrossBetweenValues.Between)
});
plotArea.AppendChild(new ValueAxis(elements));
}
public ILineChart InsertLineChart(CartesianAxes axes)
{
ChartSpace chartSpace = this.chartPart.ChartSpace;
Chart chart = chartSpace.GetFirstChild <Chart>() ?? chartSpace.AppendChild(new Chart());
PlotArea plotArea = chart.PlotArea ?? (chart.PlotArea = new PlotArea());
Marker marker = new Marker();
marker.SetAttribute(new DocumentFormat.OpenXml.OpenXmlAttribute("Val", marker.NamespaceUri, "1"));
return(new OpenXmlLineChart(
this,
plotArea.AppendChild(
new LineChart()
.AppendChildFluent(new Grouping()
{
Val = GroupingValues.Standard
})
.AppendChildFluent(new VaryColors()
{
Val = true
})
.AppendChildFluent(new Marker())
.AppendChildFluent(new Smooth()
{
Val = false
})
.AppendChildFluent(new AxisId()
{
Val = axes.CategoryAxis.Id
})
.AppendChildFluent(new AxisId()
{
Val = axes.ValueAxis.Id
})
)
));
}
/// <summary>
/// Design settings for Y axis.
/// </summary>
public ValueAxis SetGanttValueAxis(PlotArea plotArea, TimeSpan minSpan, TimeSpan maxSpan)
{
MajorGridlines majorGridlines1 = new MajorGridlines();
ChartShapeProperties chartShapeProperties2 = new ChartShapeProperties();
Outline outline2 = new Outline();
SolidFill solidFill2 = new SolidFill();
SchemeColor schemeColor2 = new SchemeColor()
{
Val = SchemeColorValues.Accent1
};
Alpha alpha1 = new Alpha()
{
Val = 10000
};
schemeColor2.Append(alpha1);
solidFill2.Append(schemeColor2);
outline2.Append(solidFill2);
chartShapeProperties2.Append(outline2);
majorGridlines1.Append(chartShapeProperties2);
return(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 MinAxisValue()
{
Val = 0
}, new MaxAxisValue()
{
Val = 0.99
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left)
},
majorGridlines1,
new MajorTickMark()
{
Val = TickMarkValues.None
},
new MinorTickMark()
{
Val = TickMarkValues.None
},
new MajorUnit()
{
Val = 4.1666666666666713E-2D
},
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = "h:mm;@", SourceLinked = false
},
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)
})));
}
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);
}
//Here be dragons
internal override void AddData(Statistics stat)
{
// 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.
var chartPart = CreateChartPart(drawingsPart);
DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Chart());
// Create a new clustered column chart.
PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea());
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)
},
new VaryColors()
{
Val = false
}
));
// 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.
BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index()
{
Val = new UInt32Value((uint)0)
},
new Order()
{
Val = new UInt32Value((uint)0)
},
new SeriesText(new NumericValue()
{
Text = "Timeline"
})));
StringLiteral strLit =
barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData())
.AppendChild <StringLiteral>(new StringLiteral());
strLit.Append(new PointCount()
{
Val = new UInt32Value((uint)stat.Diffs.Count)
});
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((uint)stat.Diffs.Count)
});
uint i = 0;
foreach (var diff in stat.Diffs)
{
strLit.AppendChild <StringPoint>(new StringPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(diff.TimeStamp.ToString()));
numLit.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(diff.Value.ToString()));
i++;
}
barChart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
AppendCategoryAxis(plotArea, 48650112u, "Time, ms", 48672768U);
AppendValueAxis(plotArea, 48672768u, "Duration, ms", 48650112U);
// 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)
});
// Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
AppendGraphicFrame(drawingsPart, chartPart);
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
}
private ValueAxis AppendValueAxis()
{
ChartSpace chartSpace = this.chartPart.ChartSpace;
Chart chart = chartSpace.GetFirstChild <Chart>() ?? chartSpace.AppendChild(new Chart());
PlotArea plotArea = chart.GetFirstChild <PlotArea>() ?? chart.AppendChild(new PlotArea());
uint id = plotArea.Elements <ValueAxis>().Select(axis => axis.AxisId.Val.Value).DefaultIfEmpty(417314071u).Max() + 1;
ValueAxis valueAxis = plotArea.AppendChild(
new ValueAxis()
{
AxisId = new AxisId()
{
Val = id
},
Scaling = new Scaling()
{
Orientation = new Orientation()
{
Val = OrientationValues.MinMax
}
},
Delete = new Delete()
{
Val = false
},
AxisPosition = new AxisPosition()
{
Val = AxisPositionValues.Left
},
MajorGridlines = new MajorGridlines()
{
ChartShapeProperties = new ChartShapeProperties()
{
// TODO
}
},
NumberingFormat = new NumberingFormat()
{
FormatCode = "General", SourceLinked = true
},
MajorTickMark = new MajorTickMark()
{
Val = TickMarkValues.None
},
MinorTickMark = new MinorTickMark()
{
Val = TickMarkValues.None
},
TickLabelPosition = new TickLabelPosition()
{
Val = TickLabelPositionValues.NextTo
},
ChartShapeProperties = new ChartShapeProperties()
.AppendChildFluent(new Drawing.NoFill())
.AppendChildFluent(new Drawing.Outline().AppendChildFluent(new Drawing.NoFill()))
.AppendChildFluent(new Drawing.EffectList()),
TextProperties = new TextProperties()
{
BodyProperties = new Drawing.BodyProperties(),
ListStyle = new Drawing.ListStyle()
}
.AppendChildFluent(
new Drawing.Paragraph()
{
ParagraphProperties = new Drawing.ParagraphProperties().AppendChildFluent(
new Drawing.DefaultRunProperties()
{
FontSize = 1197,
Bold = false,
Italic = false,
Underline = Drawing.TextUnderlineValues.None,
Strike = Drawing.TextStrikeValues.NoStrike,
Kerning = 1200,
Baseline = 0
}
.AppendChildFluent(
new Drawing.SolidFill()
{
SchemeColor = new Drawing.SchemeColor()
{
Val = Drawing.SchemeColorValues.Text1
}
.AppendChildFluent(new Drawing.LuminanceModulation()
{
Val = 65000
})
.AppendChildFluent(new Drawing.LuminanceOffset()
{
Val = 35000
})
}
)
.AppendChildFluent(new Drawing.LatinFont()
{
Typeface = "+mn-lt"
})
.AppendChildFluent(new Drawing.EastAsianFont()
{
Typeface = "+mn-ea"
})
.AppendChildFluent(new Drawing.ComplexScriptFont()
{
Typeface = "+mn-cs"
})
)
}
)
}
.AppendChildFluent(new Crosses()
{
Val = CrossesValues.AutoZero
})
.AppendChildFluent(new CrossBetween()
{
Val = CrossBetweenValues.Between
})
);
return(valueAxis);
}
private void CreateCumulative(PlotArea plotArea, Statistics stat, uint index,
uint categoryAxisId, uint valueAxisId)
{
LineChart lineChart = plotArea.AppendChild <LineChart>(new LineChart(
new ShowMarker()
{
Val = true
},
new Smooth()
{
Val = false
},
new Grouping()
{
Val = GroupingValues.Standard
},
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
})));
LineChartSeries lineChartSeries = lineChart.AppendChild(
new LineChartSeries(new Index()
{
Val = new UInt32Value(index),
},
new Order()
{
Val = new UInt32Value(index)
},
new SeriesText(new NumericValue()
{
Text = "Cumulative %"
})));
StringLiteral strLit = lineChartSeries.AppendChild(new CategoryAxisData()).AppendChild(new StringLiteral());
strLit.Append(new PointCount()
{
Val = new UInt32Value((uint)stat.Frequencies.Count)
});
NumberLiteral numLit =
lineChartSeries.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values())
.AppendChild(new NumberLiteral());
numLit.Append(new FormatCode("0.00%"));
numLit.Append(new PointCount()
{
Val = new UInt32Value((uint)stat.Frequencies.Count)
});
for (uint i = 0; i < stat.Frequencies.Count; i++)
{
strLit.AppendChild <StringPoint>(new StringPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(stat.Frequencies[(int)i].Value.ToString()));
numLit.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue((stat.Frequencies[(int)i].TotalCountPercent / 100).ToString()));
}
lineChart.Append(new AxisId()
{
Val = new UInt32Value(categoryAxisId)
});
lineChart.Append(new AxisId()
{
Val = new UInt32Value(valueAxisId)
});
AppendCategoryAxis(plotArea, categoryAxisId, "Time, ms", valueAxisId, false);
AppendValueAxis(plotArea, valueAxisId, "", categoryAxisId, AxisPositionValues.Right, TickLabelPositionValues.High, false);
}
private void CreateHistogram(PlotArea plotArea, Statistics stat, uint index, uint categoryAxisId, uint valueAxisId)
{
BarChart barChart =
plotArea.AppendChild <BarChart>(
new BarChart(
new BarDirection()
{
Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
},
new BarGrouping()
{
Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
},
new VaryColors()
{
Val = false
}
));
BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(new Index()
{
Val = new UInt32Value(index)
},
new Order()
{
Val = new UInt32Value(index)
},
new SeriesText(new NumericValue()
{
Text = "Histogram"
})));
StringLiteral strLit =
barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData())
.AppendChild <StringLiteral>(new StringLiteral());
strLit.Append(new PointCount()
{
Val = new UInt32Value((uint)stat.Frequencies.Count)
});
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((uint)stat.Frequencies.Count)
});
for (uint i = 0; i < stat.Frequencies.Count; i++)
{
strLit.AppendChild <StringPoint>(new StringPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(stat.Frequencies[(int)i].Value.ToString()));
numLit.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(stat.Frequencies[(int)i].Count.ToString()));
}
barChart.Append(new AxisId()
{
Val = new UInt32Value(categoryAxisId)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(valueAxisId)
});
AppendCategoryAxis(plotArea, categoryAxisId, "Time, ms", valueAxisId);
AppendValueAxis(plotArea, valueAxisId, "Number of samples", categoryAxisId);
}
/// <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
}
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();
}
}
/// <summary>
/// Design settings for Y axis.
/// </summary>
public virtual ValueAxis SetValueAxis(PlotArea plotArea)
{
// Postavljanje Gridline-a.
MajorGridlines majorGridlines = new MajorGridlines();
ChartShapeProperties chartShapeProperties = new ChartShapeProperties();
Outline outline = new Outline();
SolidFill solidFill = new SolidFill();
SchemeColor schemeColor = new SchemeColor()
{
Val = SchemeColorValues.Accent1
};
Alpha alpha = new Alpha()
{
Val = 10000
};
schemeColor.Append(alpha);
solidFill.Append(schemeColor);
outline.Append(solidFill);
chartShapeProperties.Append(outline);
majorGridlines.Append(chartShapeProperties);
var valueAxis = 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 Delete()
{
Val = !ChartProperties.AxisY
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Left)
},
majorGridlines,
SetTitle(ChartProperties.AxisYTitle),
new NumberingFormat()
{
FormatCode = ChartProperties.AxisYFormatCode,
SourceLinked = new BooleanValue(true)
},
new MajorTickMark()
{
Val = TickMarkValues.None
},
new MinorTickMark()
{
Val = TickMarkValues.None
},
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)
}));
if (ChartProperties.AxisYFormatCategory == "Time")
{
valueAxis.Append(new MajorUnit()
{
Val = getMajorUnitFromSeconds((int)yAxisValue)
});
}
return(valueAxis);
}
/// <summary>
/// Design settings for X axis.
/// </summary>
/// <param name="title">Optional parameter to set axis title</param>
/// <param name="hide">Optiional parameter to set axis visiblity</param>
public virtual OpenXmlElement SetLineCategoryAxis(PlotArea plotArea)
{
List <OpenXmlElement> axisChildElements = new List <OpenXmlElement> ()
{
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 Delete()
{
Val = !ChartProperties.AxisX
},
new AxisPosition()
{
Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
},
new NumberingFormat()
{
FormatCode = "General", SourceLinked = true
},
new MajorTickMark()
{
Val = TickMarkValues.None
},
new MinorTickMark()
{
Val = TickMarkValues.Outside
},
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)
}
};
if (this.isArgumentDate)
{
axisChildElements.Add(new MajorUnit()
{
Val = dataCount > 20 ? (int)(dataCount / 10) : 1
});
}
var categoryAxis = isArgumentDate ? (OpenXmlElement)plotArea.AppendChild(new DateAxis(axisChildElements)) : plotArea.AppendChild(new CategoryAxis(axisChildElements));
if (ChartProperties.AxisXTitle.Length > 0)
{
categoryAxis.Append(SetTitle(ChartProperties.AxisXTitle));
}
return(categoryAxis);
}