/// <summary>
/// Create and insert data to Axis
/// </summary>
public void SetChartAxis(List <GanttSpreadsheetChart.GanttDataPairedSeries> data, List <IGrouping <string, GanttData> > groupedData)
{
Dictionary <uint, TimeSpan> lastPointEnd = new Dictionary <uint, TimeSpan>();
foreach (var ganttDataPairedSeries in data)
{
// Y axis - first bar is for the starting position (is not visible)
StringLiteral stringLiteral1 = ganttDataPairedSeries.BarChartSeriesHidden.AppendChild <CategoryAxisData>(new CategoryAxisData())
.AppendChild <StringLiteral>(new StringLiteral());
stringLiteral1.Append(new PointCount()
{
Val = new UInt32Value((uint)ganttDataPairedSeries.Values.Count)
});
StringLiteral stringLiteral2 = ganttDataPairedSeries.BarChartSeriesValue.AppendChild <CategoryAxisData>(new CategoryAxisData())
.AppendChild <StringLiteral>(new StringLiteral());
stringLiteral2.Append(new PointCount()
{
Val = new UInt32Value((uint)ganttDataPairedSeries.Values.Count)
});
// X axis - first bar is for the starting position (is not visible)
NumberLiteral numberLiteral1 = ganttDataPairedSeries.BarChartSeriesHidden.AppendChild <Values>(new Values())
.AppendChild <NumberLiteral>(new NumberLiteral());
numberLiteral1.Append(new FormatCode("General"));
numberLiteral1.Append(new PointCount()
{
Val = new UInt32Value((uint)ganttDataPairedSeries.Values.Count)
});
NumberLiteral numberLiteral2 = ganttDataPairedSeries.BarChartSeriesValue.AppendChild <Values>(new Values())
.AppendChild <NumberLiteral>(new NumberLiteral());
numberLiteral2.Append(new FormatCode("General"));
numberLiteral2.Append(new PointCount()
{
Val = new UInt32Value((uint)ganttDataPairedSeries.Values.Count)
});
// Set values to X and Y axis.
foreach (GanttData key in ganttDataPairedSeries.Values)
{
var i = (uint)groupedData.FindIndex(x => x.Key == key.Name);
stringLiteral1.AppendChild <StringPoint>(new StringPoint()
{
Index = new UInt32Value(i)
})
.AppendChild <NumericValue>(new NumericValue(key.Name));
stringLiteral2.AppendChild <StringPoint>(new StringPoint()
{
Index = new UInt32Value(i)
})
.AppendChild <NumericValue>(new NumericValue(key.Name));
numberLiteral1.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(CalculateExcelTime(lastPointEnd.ContainsKey(i) ? key.Start.Subtract(lastPointEnd[i]) : key.Start).ToString(System.Globalization.CultureInfo.InvariantCulture)));
numberLiteral2.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(CalculateExcelTime(lastPointEnd.ContainsKey(i) ? key.End.Subtract(key.Start.Subtract(lastPointEnd[i]) + lastPointEnd[i]) : key.End.Subtract(key.Start)).ToString(System.Globalization.CultureInfo.InvariantCulture)));
if (lastPointEnd.ContainsKey(i))
{
lastPointEnd[i] = key.End;
}
else
{
lastPointEnd.Add(i, key.End);
}
}
}
}
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();
}
}
//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 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>
/// Create and insert data to Axis
/// </summary>
public virtual void SetChartAxis(OpenXmlCompositeElement chartSeries, List <ChartModel> data)
{
dataCount = data.Count;
if (dataCount > 0 && !isArgumentDate)
{
DateTime parsedDate;
isArgumentDate = DateTime.TryParse(data[0].Argument, out parsedDate);
}
uint i = 0;
// X axis
StringLiteral stringLiteral = new StringLiteral();
stringLiteral.Append(new PointCount()
{
Val = new UInt32Value((uint)dataCount)
});
NumberLiteral numberLiteralX = new NumberLiteral();
numberLiteralX.Append(new FormatCode("mmm dd"));
numberLiteralX.Append(new PointCount()
{
Val = new UInt32Value((uint)dataCount)
});
// Y axis
NumberLiteral numberLiteralY = new NumberLiteral();
numberLiteralY.Append(new FormatCode(ChartProperties.AxisYFormatCode));
numberLiteralY.Append(new PointCount()
{
Val = new UInt32Value((uint)dataCount)
});
yAxisValue = data.Max(x => x.Value) > yAxisValue?data.Max(x => x.Value) : yAxisValue;
// Set values to X and Y axis.
foreach (var chartModel in data)
{
if (isArgumentDate)
{
numberLiteralX.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(CalculateExcelDate(chartModel.Argument)));
}
else
{
stringLiteral.Append(new StringPoint()
{
Index = new UInt32Value(i), NumericValue = new NumericValue()
{
Text = chartModel.Argument
}
});
}
numberLiteralY.AppendChild <NumericPoint>(new NumericPoint()
{
Index = new UInt32Value(i)
})
.Append(new NumericValue(ChartProperties.AxisYFormatCategory == "Time" ? ((double)chartModel.Value / 86400).ToString(System.Globalization.CultureInfo.InvariantCulture) : chartModel.Value.ToString(System.Globalization.CultureInfo.InvariantCulture)));
i++;
}
if (isArgumentDate)
{
chartSeries.Append(new CategoryAxisData()
{
NumberLiteral = numberLiteralX
});
}
else
{
chartSeries.Append(new CategoryAxisData()
{
StringLiteral = stringLiteral
});
}
chartSeries.Append(new Values()
{
NumberLiteral = numberLiteralY
});
}