class Program
{
static void Main(string[] args)
{
string docName = @"..\..\Documents\Sheet5.xlsx";
string worksheetName = "Joe";
string title = "New Chart";
Dictionary data = new Dictionary();
data.Add("abc", 1);
InsertChartInSpreadsheet(docName, worksheetName, title, data);
}
// Given a document name, a worksheet name, a chart title, and a Dictionary collection of text keys
// and corresponding integer data, creates a column chart with the text as the series and the integers as the values.
private static void InsertChartInSpreadsheet(string docName, string worksheetName, string title,
Dictionary data)
{
// Open the document for editing.
using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
{
IEnumerable sheets = document.WorkbookPart.Workbook.Descendants().
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();
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.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") });
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());
Layout layout = plotArea.AppendChild(new Layout());
BarChart barChart = plotArea.AppendChild(new BarChart(new BarDirection()
{ Val = new EnumValue(BarDirectionValues.Column) },
new BarGrouping() { Val = new EnumValue(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(new BarChartSeries(new Index()
{
Val =
new UInt32Value(i)
},
new Order() { Val = new UInt32Value(i) },
new SeriesText(new NumericValue() { Text = key })));
StringLiteral strLit = barChartSeries.AppendChild(new CategoryAxisData()).AppendChild(new StringLiteral());
strLit.Append(new PointCount() { Val = new UInt32Value(1U) });
strLit.AppendChild(new StringPoint() { Index = new UInt32Value(0U) }).Append(new NumericValue(title));
NumberLiteral numLit = barChartSeries.AppendChild(
new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild(new NumberLiteral());
numLit.Append(new FormatCode("General"));
numLit.Append(new PointCount() { Val = new UInt32Value(1U) });
numLit.AppendChild(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(new CategoryAxis(new AxisId()
{ Val = new UInt32Value(48650112u) }, new Scaling(new Orientation()
{
Val = new EnumValue(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition() { Val = new EnumValue(AxisPositionValues.Bottom) },
new TickLabelPosition() { Val = new EnumValue(TickLabelPositionValues.NextTo) },
new CrossingAxis() { Val = new UInt32Value(48672768U) },
new Crosses() { Val = new EnumValue(CrossesValues.AutoZero) },
new AutoLabeled() { Val = new BooleanValue(true) },
new LabelAlignment() { Val = new EnumValue(LabelAlignmentValues.Center) },
new LabelOffset() { Val = new UInt16Value((ushort)100) }));
// Add the Value Axis.
ValueAxis valAx = plotArea.AppendChild(new ValueAxis(new AxisId() { Val = new UInt32Value(48672768u) },
new Scaling(new Orientation()
{
Val = new EnumValue(
DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
}),
new AxisPosition() { Val = new EnumValue(AxisPositionValues.Left) },
new MajorGridlines(),
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = new StringValue("General"),
SourceLinked = new BooleanValue(true)
}, new TickLabelPosition()
{
Val = new EnumValue
(TickLabelPositionValues.NextTo)
}, new CrossingAxis() { Val = new UInt32Value(48650112U) },
new Crosses() { Val = new EnumValue(CrossesValues.AutoZero) },
new CrossBetween() { Val = new EnumValue(CrossBetweenValues.Between) }));
// Add the chart Legend.
Legend legend = chart.AppendChild
