Data Browser - Viewing Site  Sector 23 Code Bank Logged in as:  Guest  




           


How to Create a Bar Graph / 3D Pie Chart in PowerPoint using .NET OpenXML API
There is not much info on the web regarding how to create a bar chart or pie chart in PowerPoint using the OpenXML API.
You can reflect a file, but you will get hundreds of lines of code without a clear sense of what is going on.
Another common suggestion is to open an existing file and then edit the data. This is not very scalable. Also these suggestions often do not supporting editing the chart in PowerPoint (the open Excel data command fails).

So, here is a full solution of how to generate a Bar or Pie Chart for PowerPoint.

I will note that I placed all of this code in a static class called PowerpointTools with the following references, but you may organize however you like:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Presentation;
using C = DocumentFormat.OpenXml.Drawing.Charts;
using Drawing = DocumentFormat.OpenXml.Drawing;


Step 1.
Create your slide with a title and chart frame. If you already have a slide, you can just use the lines where the chart is added to the slide.


/// <summary>
/// Creates a slide and adds it to a pptx file
/// </summary>
/// <param name="presentationDocument"></param>
/// <param name="position"></param>
/// <param name="slideTitle"></param>
/// <param name="borderAroundTitle"></param>
/// <param name="centerTitle"></param>
/// <param name="createTextBody"></param>
/// <param name="addChart"></param>
/// <param name="chartId"></param>
/// <param name="chartHeightScale">reduce height by scale factor .1 - .9</param>
/// <returns></returns>
public static SlidePart CreateSlide(PresentationDocument presentationDocument, int position, string slideTitle,
bool borderAroundTitle, bool centerTitle, bool createTextBody, bool addChart, string chartId, decimal? chartHeightScale)
{
PresentationPart presentationPart = presentationDocument.PresentationPart;

// Verify that the presentation is not empty.
if (presentationPart == null)
{
throw new InvalidOperationException("The presentation document is empty.");
}

// Declare and instantiate a new slide.
Slide slide = new Slide(new CommonSlideData(new ShapeTree()));
uint drawingObjectId = 1;

// Construct the slide content.
// Specify the non-visual properties of the new slide.
NonVisualGroupShapeProperties nonVisualProperties = slide.CommonSlideData.ShapeTree.AppendChild(new NonVisualGroupShapeProperties());
nonVisualProperties.NonVisualDrawingProperties = new NonVisualDrawingProperties() { Id = 1, Name = "" };
nonVisualProperties.NonVisualGroupShapeDrawingProperties = new NonVisualGroupShapeDrawingProperties();
nonVisualProperties.ApplicationNonVisualDrawingProperties = new ApplicationNonVisualDrawingProperties();
GroupShapeProperties groupShapeProperties1 = new GroupShapeProperties();
Drawing.TransformGroup transformGroup1 = new Drawing.TransformGroup();
Drawing.Offset offset1 = new Drawing.Offset() { X = 0L, Y = 0L };
Drawing.Extents extents1 = new Drawing.Extents() { Cx = 0L, Cy = 0L };
Drawing.ChildOffset childOffset1 = new Drawing.ChildOffset() { X = 0L, Y = 0L };
Drawing.ChildExtents childExtents1 = new Drawing.ChildExtents() { Cx = 0L, Cy = 0L };
transformGroup1.Append(offset1);
transformGroup1.Append(extents1);
transformGroup1.Append(childOffset1);
transformGroup1.Append(childExtents1);
groupShapeProperties1.Append(transformGroup1);

// Specify the group shape properties of the new slide.
slide.CommonSlideData.ShapeTree.AppendChild(groupShapeProperties1);

// Declare and instantiate the title shape of the new slide.
Shape titleShape = slide.CommonSlideData.ShapeTree.AppendChild(new Shape());

drawingObjectId++;

// Specify the required shape properties for the title shape.
titleShape.NonVisualShapeProperties = new NonVisualShapeProperties
(new NonVisualDrawingProperties() { Id = drawingObjectId, Name = "Title" },
new NonVisualShapeDrawingProperties(new Drawing.ShapeLocks() { NoGrouping = true }),
new ApplicationNonVisualDrawingProperties(new PlaceholderShape() { Type = PlaceholderValues.Title }));
titleShape.ShapeProperties = new ShapeProperties();

if (borderAroundTitle)
{
Drawing.Outline outline1 = new Drawing.Outline() { CompoundLineType = Drawing.CompoundLineValues.Single };
Drawing.SolidFill solidFill1 = new Drawing.SolidFill();
Drawing.SchemeColor schemeColor1 = new Drawing.SchemeColor() { Val = Drawing.SchemeColorValues.Text1 };
solidFill1.Append(schemeColor1);
outline1.Append(solidFill1);
titleShape.ShapeProperties.Append(outline1);
}

var titlePar = new Drawing.Paragraph();
if (centerTitle)
{
Drawing.ParagraphProperties paragraphProperties1 = new Drawing.ParagraphProperties() { Alignment = Drawing.TextAlignmentTypeValues.Center };
titlePar.Append(paragraphProperties1);
}
var run =new Drawing.Run();
var runProperties2 = new Drawing.RunProperties() { Language = "en-US", FontSize = 2000, Dirty = false };//Set Font-Size to 10px.
run.Append(runProperties2);
run.Append(new Drawing.Text() { Text = slideTitle });
titlePar.Append(run);


// Specify the text of the title shape.
titleShape.TextBody = new TextBody(new Drawing.BodyProperties(),
new Drawing.ListStyle(), titlePar);

if (createTextBody)
{
// Declare and instantiate the body shape of the new slide.
Shape bodyShape = slide.CommonSlideData.ShapeTree.AppendChild(new Shape());
drawingObjectId++;
// Specify the required shape properties for the body shape.
bodyShape.NonVisualShapeProperties = new NonVisualShapeProperties(
new NonVisualDrawingProperties() { Id = drawingObjectId, Name = "Content Placeholder" },
new NonVisualShapeDrawingProperties(new Drawing.ShapeLocks() { NoGrouping = true }),
new ApplicationNonVisualDrawingProperties(new PlaceholderShape() { Index = 1 }));
bodyShape.ShapeProperties = new ShapeProperties();
// Specify the text of the body shape.
bodyShape.TextBody = new TextBody(new Drawing.BodyProperties(),
new Drawing.ListStyle(),
new Drawing.Paragraph());
}

if (addChart)
{
drawingObjectId++;
// adds a chart to the frame
var height = 4800000L;
if (chartHeightScale.HasValue)
height = (long)((decimal)height * chartHeightScale.Value);
AddChartFrameToSlide(slide.CommonSlideData.ShapeTree, 1440000L, 1450000L, 7500000L, height, drawingObjectId, chartId);
}

AddPageNumber(slide.CommonSlideData.ShapeTree);

// Create the slide part for the new slide.
SlidePart slidePart = presentationPart.AddNewPart<SlidePart>();

// Save the new slide part.
slide.Save(slidePart);

// Modify the slide ID list in the presentation part.
// The slide ID list should not be null.
SlideIdList slideIdList = presentationPart.Presentation.SlideIdList;

// Find the highest slide ID in the current list.
uint maxSlideId = 1;
SlideId prevSlideId = null;

foreach (SlideId slideId in slideIdList.ChildElements)
{
if (slideId.Id > maxSlideId)
{
maxSlideId = slideId.Id;
}

position--;
if (position == 0)
{
prevSlideId = slideId;
}

}

maxSlideId++;

// Get the ID of the previous slide.
SlidePart lastSlidePart;

if (prevSlideId != null)
{
lastSlidePart = (SlidePart)presentationPart.GetPartById(prevSlideId.RelationshipId);
}
else
{
lastSlidePart = (SlidePart)presentationPart.GetPartById(((SlideId)(slideIdList.ChildElements[0])).RelationshipId);
}

// Use the same slide layout as that of the previous slide.
if (null != lastSlidePart.SlideLayoutPart)
{
slidePart.AddPart(lastSlidePart.SlideLayoutPart);
}

// Insert the new slide into the slide list after the previous slide.
SlideId newSlideId = slideIdList.InsertAfter(new SlideId(), prevSlideId);
newSlideId.Id = maxSlideId;
newSlideId.RelationshipId = presentationPart.GetIdOfPart(slidePart);

// Save the modified presentation.
presentationPart.Presentation.Save();
return slidePart;
}

note: for 'AddPageNumber()', reference my other post or comment out that line.


Step 2.
Add a graphic frame to the slide to hold your chart.

/// <summary>
/// Adds a frame to a slide to show a referenced chart in (a chart object must be created with the referenced id)
/// </summary>
/// <param name="slideShapeTree"></param>
/// <param name="xPos">x pos of chart (recommend 1440000)</param>
/// <param name="yPos">y pos of chart (recommend 1450000)</param>
/// <param name="width">width of chart (recommend 7500000)</param>
/// <param name="height">height of chart (recommend 4800000)</param>
/// <param name="drawingObjectId">unique id of object in slide</param>
/// <param name="chartId">string name of chart object</param>
static void AddChartFrameToSlide(ShapeTree slideShapeTree, long xPos, long yPos, long width, long height, uint drawingObjectId, string chartId)
{
GraphicFrame graphicFrame1 = new GraphicFrame();
NonVisualGraphicFrameProperties nonVisualGraphicFrameProperties1 = new NonVisualGraphicFrameProperties();
NonVisualDrawingProperties nonVisualDrawingProperties2 = new NonVisualDrawingProperties() { Id = drawingObjectId, Name = "Chart" };
NonVisualGraphicFrameDrawingProperties nonVisualGraphicFrameDrawingProperties1 = new NonVisualGraphicFrameDrawingProperties();
ApplicationNonVisualDrawingProperties applicationNonVisualDrawingProperties2 = new ApplicationNonVisualDrawingProperties();
nonVisualGraphicFrameProperties1.Append(nonVisualDrawingProperties2);
nonVisualGraphicFrameProperties1.Append(nonVisualGraphicFrameDrawingProperties1);
nonVisualGraphicFrameProperties1.Append(applicationNonVisualDrawingProperties2);
Transform transform1 = new Transform();
Drawing.Offset offset2 = new Drawing.Offset() { X = xPos, Y = yPos };
Drawing.Extents extents2 = new Drawing.Extents() { Cx = width, Cy = height };
transform1.Append(offset2);
transform1.Append(extents2);
Drawing.Graphic graphic1 = new Drawing.Graphic();
Drawing.GraphicData graphicData1 = new Drawing.GraphicData() { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" };
C.ChartReference chartReference1 = new C.ChartReference() { Id = chartId };
chartReference1.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
chartReference1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
graphicData1.Append(chartReference1);
graphic1.Append(graphicData1);
graphicFrame1.Append(nonVisualGraphicFrameProperties1);
graphicFrame1.Append(transform1);
graphicFrame1.Append(graphic1);
slideShapeTree.Append(graphicFrame1);
}

Step 3.
Now you need to create the chart object referenced by the frame.
This supports integers or decimals in the data, and will format accordingly.
Text will be auto-resized to fit within a standard slide size.
There are 2 callable methods; one for bar, another for pie - but most of the code is shared.

/// <summary>
/// Creates a BarGraph ChartSpace instance and adds its children.
/// </summary>
/// <param name="slidePart">Slide part to add the chart part definition</param>
/// <param name="chartId">String Id of the Chart</param>
/// <param name="data">source data. key is list of objects, data is list of values; you may include multiple values for multiple series.
/// Example keys: Alex, Bob, Dave. Example data: {130}, {140}, {150} (1 series - weights) OR {130,30}, {140,32}, {150,40} (2 series - weights + age on same chart)
/// </param>
/// <param name="categoryTitle">Title of category, example "Name of Person"</param>
/// <param name="seriesTitles">Titles of series. Count should match series provided. Example: {'Weight', 'Age'} if there are 2 series</param>
/// <param name="graphFirstSeriesOnly">If true, only the first series will display as a bar graph. Regardless, all series are displayed in the text table.</param>
/// <param name="workingTempFolder">Path to a folder with read/write permissions to store temporary file data while processing</param>
/// <returns></returns>
public static ChartPart GenerateBarGraphChartPart(SlidePart slidePart,
string chartId, Dictionary<string, decimal[]> data,
string categoryTitle, List<string> seriesTitles, bool graphFirstSeriesOnly,
string workingTempFolder)
{
return GenerateChartPart(slidePart, chartId, data, categoryTitle, seriesTitles, graphFirstSeriesOnly, workingTempFolder, false, null);
}

// same as above, but renders a pie chart...
public static ChartPart GeneratePieChartPart(SlidePart slidePart,
string chartId, Dictionary<string, decimal[]> data,
string categoryTitle, List<string> seriesTitles, string workingTempFolder, string customPieChartLabel)
{
return GenerateChartPart(slidePart, chartId, data, categoryTitle, seriesTitles, false, workingTempFolder, true, customPieChartLabel);
}

static ChartPart GenerateChartPart(SlidePart slidePart,
string chartId, Dictionary<string, decimal[]> data,
string categoryTitle, List<string> seriesTitles, bool graphFirstSeriesOnly,
string workingTempFolder, bool isPieChart, string customPieChartLabel)
{
if (data.Count == 0)
return null; // no data to graph

var excelSourceId = "rId1";

C.ChartSpace chartSpace1 = new C.ChartSpace();
chartSpace1.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
chartSpace1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
chartSpace1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

C.EditingLanguage editingLanguage1 = new C.EditingLanguage() { Val = "en-US" };
C.Style style1 = new C.Style() { Val = 1 };
C.Chart chart1 = new C.Chart();
C.PlotArea plotArea1 = new C.PlotArea();
C.Layout layout1 = new C.Layout();


OpenXmlCompositeElement barPieChart1 = new C.BarChart();
if (isPieChart)
{
barPieChart1 = new C.Pie3DChart();

C.View3D view3D1 = new C.View3D();
C.RotateX rotateX1 = new C.RotateX() { Val = 50 };
C.Perspective perspective1 = new C.Perspective() { Val = 30 };
C.RightAngleAxes rax1 = new C.RightAngleAxes() { Val = BooleanValue.FromBoolean(false) };

view3D1.Append(rotateX1);
view3D1.Append(rax1); // XLSX2010 support
view3D1.Append(perspective1);
chart1.Append(view3D1);// 3D look and feel


C.Legend legend1 = new C.Legend();
C.LegendPosition legendPosition1 = new C.LegendPosition() { Val = C.LegendPositionValues.Right };
C.Layout layout3 = new C.Layout();
legend1.Append(legendPosition1);
legend1.Append(layout3);

C.Overlay overlay = new C.Overlay() { Val = BooleanValue.FromBoolean(false) };
legend1.Append(overlay); // XLSX 2010 support

chart1.Append(legend1);

C.VaryColors varyColors1 = new C.VaryColors() { Val = true };
barPieChart1.Append(varyColors1);
}
else
{
C.BarDirection barDirection1 = new C.BarDirection() { Val = C.BarDirectionValues.Column };
C.BarGrouping barGrouping1 = new C.BarGrouping() { Val = C.BarGroupingValues.Clustered };
barPieChart1.Append(barDirection1);
barPieChart1.Append(barGrouping1);
}

// add each series defined in data
for (int i = 0; i < data.First().Value.Length; i++)
{
AddChartSeries(barPieChart1, data, i, seriesTitles[i], graphFirstSeriesOnly, isPieChart, customPieChartLabel);
//break;
}

if (!isPieChart && graphFirstSeriesOnly)
{
// if showing multiple series' graphs, you can set overlap gap to 100% so other series don't take up space
C.Overlap overlap1 = new C.Overlap() { Val = 100 };
barPieChart1.Append(overlap1);
}

// add the chart to the plot area
plotArea1.Append(layout1);
plotArea1.Append(barPieChart1);

// barchart must have axis ids as last children; these point to actual axises as children of plotArea (category axis and value axis)
if (!isPieChart)
{
C.AxisId axisId1 = new C.AxisId() { Val = (UInt32Value)83888000U }; // cat
C.AxisId axisId2 = new C.AxisId() { Val = (UInt32Value)83886464U }; // val
barPieChart1.Append(axisId1);
barPieChart1.Append(axisId2);

C.ValueAxis valueAxis1 = new C.ValueAxis();
C.AxisId axisId3 = new C.AxisId() { Val = (UInt32Value)83886464U };
C.Scaling scaling1 = new C.Scaling();
C.Orientation orientation1 = new C.Orientation() { Val = C.OrientationValues.MinMax };
scaling1.Append(orientation1);
if (graphFirstSeriesOnly)
{
C.MinAxisValue minAV1 = new C.MinAxisValue() { Val = (double)0 };

int max = (int)Math.Ceiling(data.Values.Max(d => d[0] * 1.1M)); // generate range manually so that series 2+ are not used as the axis range
if (max < 2)
max = 2; // never less than 2.
C.MaxAxisValue maxAxisValue1 = new C.MaxAxisValue() { Val = (double)max };
scaling1.Append(maxAxisValue1); // max must be before min
scaling1.Append(minAV1);
}

C.AxisPosition axisPosition1 = new C.AxisPosition() { Val = C.AxisPositionValues.Left };
C.MajorGridlines majorGridlines1 = new C.MajorGridlines();
C.NumberingFormat numberingFormat1 = new C.NumberingFormat() { FormatCode = "#,##0", SourceLinked = true };
C.MajorTickMark majorTickMark1 = new C.MajorTickMark() { Val = C.TickMarkValues.None };
C.MinorTickMark minorTickMark = new C.MinorTickMark() { Val = C.TickMarkValues.None };
C.TickLabelPosition tickLabelPosition1 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo };
C.CrossingAxis crossingAxis1 = new C.CrossingAxis() { Val = (UInt32Value)83888000U };
C.Crosses crosses1 = new C.Crosses() { Val = C.CrossesValues.AutoZero };
C.CrossBetween crossBetween1 = new C.CrossBetween() { Val = C.CrossBetweenValues.Between };
valueAxis1.Append(axisId3);
valueAxis1.Append(scaling1);

C.Delete cDel = new C.Delete();
cDel.Val = BooleanValue.FromBoolean(false);
valueAxis1.Append(cDel); // req for v2010

valueAxis1.Append(axisPosition1);
valueAxis1.Append(majorGridlines1);
valueAxis1.Append(numberingFormat1);
valueAxis1.Append(majorTickMark1);
valueAxis1.Append(minorTickMark);
valueAxis1.Append(tickLabelPosition1);
valueAxis1.Append(crossingAxis1);
valueAxis1.Append(crosses1);
valueAxis1.Append(crossBetween1);

//C.AutoLabeled autoLabeledV = new C.AutoLabeled() { Val = true };
//valueAxis1.Append(autoLabeledV); // doesn't seem to do anything

C.CategoryAxis categoryAxis1 = new C.CategoryAxis();
C.AxisId axisId4 = new C.AxisId() { Val = (UInt32Value)83888000U };
C.Scaling scaling2 = new C.Scaling();
C.Orientation orientation2 = new C.Orientation() { Val = C.OrientationValues.MinMax };
scaling2.Append(orientation2);
C.AxisPosition axisPosition2 = new C.AxisPosition() { Val = C.AxisPositionValues.Bottom };
C.MajorTickMark majorTickMark2 = new C.MajorTickMark() { Val = C.TickMarkValues.None };
C.MinorTickMark minorTickMark2 = new C.MinorTickMark() { Val = C.TickMarkValues.None };
C.TickLabelPosition tickLabelPosition2 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo };
C.CrossingAxis crossingAxis2 = new C.CrossingAxis() { Val = (UInt32Value)83886464U };
C.Crosses crosses2 = new C.Crosses() { Val = C.CrossesValues.AutoZero };
C.LabelAlignment labelAlignment1 = new C.LabelAlignment() { Val = C.LabelAlignmentValues.Center };
C.LabelOffset labelOffset1 = new C.LabelOffset() { Val = (UInt16Value)100U };
categoryAxis1.Append(axisId4);
categoryAxis1.Append(scaling2);

C.Delete cDel2 = new C.Delete();
cDel2.Val = BooleanValue.FromBoolean(false);
categoryAxis1.Append(cDel2); // req for v2010

categoryAxis1.Append(axisPosition2);
categoryAxis1.Append(majorTickMark2);
categoryAxis1.Append(minorTickMark2);
categoryAxis1.Append(tickLabelPosition2);
categoryAxis1.Append(crossingAxis2);
categoryAxis1.Append(crosses2);

// reqd for 2010
C.AutoLabeled autoLabeled1 = new C.AutoLabeled() { Val = true };
categoryAxis1.Append(autoLabeled1);

categoryAxis1.Append(labelAlignment1);
categoryAxis1.Append(labelOffset1);

// I think you can name the axis but it doesn't seem to matter
//C.StringCache stringCache4 = new C.StringCache();
//C.PointCount pointCount5 = new C.PointCount() { Val = (UInt32Value)2U };
//C.StringPoint stringPoint5 = new C.StringPoint() { Index = (UInt32Value)0U };
//C.NumericValue numericValue7 = new C.NumericValue();
//numericValue7.Text = "Series 1";
//stringPoint5.Append(numericValue7);
//var stringPoint6 = new C.StringPoint() { Index = (UInt32Value)1U };
//var numericValue8 = new C.NumericValue();
//numericValue8.Text = "Series 2";
//stringPoint6.Append(numericValue8);
//stringCache4.Append(pointCount5);
//stringCache4.Append(stringPoint5);
//stringCache4.Append(stringPoint6);
//var stringReference4 = new C.StringReference();
//stringReference4.Append(formula5)
//stringReference4.Append(stringCache4);
//categoryAxis1.Append(stringReference4);

// add axis
plotArea1.Append(valueAxis1);
plotArea1.Append(categoryAxis1);

// create a table to hold your text data for the bar graph
C.DataTable dataTable1 = new C.DataTable();
C.ShowHorizontalBorder showHorizontalBorder1 = new C.ShowHorizontalBorder() { Val = true };
C.ShowVerticalBorder showVerticalBorder1 = new C.ShowVerticalBorder() { Val = true };
C.ShowOutlineBorder showOutlineBorder1 = new C.ShowOutlineBorder() { Val = true };
C.ShowKeys showKeys1 = new C.ShowKeys() { Val = true };
dataTable1.Append(showHorizontalBorder1);
dataTable1.Append(showVerticalBorder1);
dataTable1.Append(showOutlineBorder1);
dataTable1.Append(showKeys1);
plotArea1.Append(dataTable1);
} // is bar

C.PlotVisibleOnly plotVisibleOnly1 = new C.PlotVisibleOnly() { Val = true };
C.DisplayBlanksAs displayBlanksAs1 = new C.DisplayBlanksAs() { Val = C.DisplayBlanksAsValues.Gap };

// no idea what this does
C.AutoTitleDeleted autoTitleDeleted1 = new C.AutoTitleDeleted() { Val = true };
chart1.Append(autoTitleDeleted1);

chart1.Append(plotArea1);
chart1.Append(plotVisibleOnly1);
chart1.Append(displayBlanksAs1);

C.ChartShapeProperties chartShapeProperties3 = new C.ChartShapeProperties();

Drawing.Outline outline1 = new Drawing.Outline() { CompoundLineType = Drawing.CompoundLineValues.Single };

Drawing.SolidFill solidFill2 = new Drawing.SolidFill();
Drawing.SchemeColor schemeColor2 = new Drawing.SchemeColor() { Val = Drawing.SchemeColorValues.Text1 };
solidFill2.Append(schemeColor2);
outline1.Append(solidFill2);
chartShapeProperties3.Append(outline1);

// set chart font size/name
var textProperties1 = new C.TextProperties();
var bodyProperties1 = new Drawing.BodyProperties();
var listStyle1 = new Drawing.ListStyle();
var paragraph1 = new Drawing.Paragraph();
var paragraphProperties1 = new Drawing.ParagraphProperties();

// shrink text if too long
// 1. check width
int cols = data.Keys.Count; // 225 chars fit at 18 pt
int supportedCharsPerCol = 225 / cols;
int maxSeriesTitleLen = data.Keys.Max(k => k.Length);
decimal fontScaling = (decimal)supportedCharsPerCol / (decimal)maxSeriesTitleLen; // example if max is 10, but supported is 5, we must scale by 1/2.

// 2. check height
int supportedRows = 5; // max rows that fit at 18 pt is about 5 rows + 3 for the title
int actualSeriesRows = seriesTitles.Select(s => 1 + (s.Length / 25)).Sum(); // 25 chars per row
decimal rowFontScaling = (decimal)supportedRows / (decimal)actualSeriesRows;
if (rowFontScaling < fontScaling)
{
fontScaling = rowFontScaling;
}

// now resize
int fontSize = 1800;
if (fontScaling < .3M)
fontScaling = .3M; // never reduce more than 70%
if (fontScaling < 1)
fontSize = (int)((decimal)fontSize * fontScaling);

var defaultRunProperties1 = new Drawing.DefaultRunProperties() { FontSize = fontSize };

var latinFont1 = new Drawing.LatinFont() { Typeface = "Verdana", PitchFamily = 34, CharacterSet = 0 };
var complexScriptFont1 = new Drawing.ComplexScriptFont() { Typeface = "Verdana", PitchFamily = 34, CharacterSet = 0 };
defaultRunProperties1.Append(latinFont1);
defaultRunProperties1.Append(complexScriptFont1);
paragraphProperties1.Append(defaultRunProperties1);
var endParagraphRunProperties1 = new Drawing.EndParagraphRunProperties() { Language = "en-US" };
paragraph1.Append(paragraphProperties1);
paragraph1.Append(endParagraphRunProperties1);
textProperties1.Append(bodyProperties1);
textProperties1.Append(listStyle1);
textProperties1.Append(paragraph1);

chartSpace1.Append(editingLanguage1);
//chartSpace1.Append(style1);
chartSpace1.Append(chart1);
chartSpace1.Append(chartShapeProperties3);
chartSpace1.Append(textProperties1);
C.ExternalData externalData1 = new C.ExternalData() { Id = excelSourceId };
chartSpace1.Append(externalData1);

var chartPart = slidePart.AddNewPart<ChartPart>(chartId);
chartPart.ChartSpace = chartSpace1;

// now add the Excel Data Source
EmbeddedPackagePart embeddedPackagePart1 = chartPart.AddNewPart<EmbeddedPackagePart>(
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelSourceId);
GenerateEmbeddedPackageContent(embeddedPackagePart1, data, seriesTitles, categoryTitle, workingTempFolder);

return chartPart;
} // add chart

Step 4.
This code supports multiple (or one) series. Each is added using this method.

/// <summary>
/// Adds a series to a chart
/// </summary>
/// <param name="barPieChart1"></param>
/// <param name="data"></param>
/// <param name="dataIndex"></param>
/// <param name="seriesTitle"></param>
/// <param name="graphFirstSeriesOnly"></param>
/// <param name="isPieChart"></param>
/// <param name="customPieChartLabel">optional label format if pie chart</param>
static void AddChartSeries(OpenXmlCompositeElement barPieChart1, Dictionary<string, decimal[]> data,
int dataIndex, string seriesTitle, bool graphFirstSeriesOnly, bool isPieChart, string customPieChartLabel)
{
char excelCol = 'B';
if (dataIndex >= 26)
throw new NotSupportedException("Column indexes after Z not supported - need to add 'AA' etc");
if (dataIndex > 0)
excelCol = (char)((int)excelCol + dataIndex);

OpenXmlCompositeElement chartSeries = null;
if (isPieChart)
{
chartSeries = barPieChart1.AppendChild<C.PieChartSeries>(new C.PieChartSeries(new C.Index() { Val = (uint)dataIndex },
new C.Order() { Val = (uint)dataIndex }));

C.DataLabels dataLabels1 = new C.DataLabels();

// set font color of pie chart labels to gray - to show up on white, blue, or black bg
var bodyProperties1 = new Drawing.BodyProperties();
var listStyle1 = new Drawing.ListStyle();
C.TextProperties textProperties1 = new C.TextProperties();
Drawing.SolidFill solidFill10 = new Drawing.SolidFill();
Drawing.RgbColorModelHex rgbColorModelHex1 = new Drawing.RgbColorModelHex() { Val = "262626" }; // FFFFFF
solidFill10.Append(rgbColorModelHex1);
var defaultRunProperties11 = new Drawing.DefaultRunProperties() { Baseline = 0, Bold = true};
defaultRunProperties11.Append(solidFill10);
var paragraph1 = new Drawing.Paragraph();
var paragraphProperties1 = new Drawing.ParagraphProperties();
paragraphProperties1.Append(defaultRunProperties11);
var endParagraphRunProperties1 = new Drawing.EndParagraphRunProperties() { Language = "en-US" };
paragraph1.Append(paragraphProperties1);
paragraph1.Append(endParagraphRunProperties1);
textProperties1.Append(bodyProperties1);
textProperties1.Append(listStyle1);

textProperties1.Append(paragraph1);
dataLabels1.Append(textProperties1);


// XLSX2010 Support
C.ShowLegendKey slk = new C.ShowLegendKey() { Val = BooleanValue.FromBoolean(false) };
dataLabels1.Append(slk);
C.ShowValue showValue1 = new C.ShowValue() { Val = true };
dataLabels1.Append(showValue1);
C.ShowCategoryName scn = new C.ShowCategoryName() { Val = BooleanValue.FromBoolean(false) };
dataLabels1.Append(scn);
C.ShowSeriesName ssn = new C.ShowSeriesName() { Val = BooleanValue.FromBoolean(false) };
dataLabels1.Append(ssn);
C.ShowPercent sp = new C.ShowPercent() { Val = BooleanValue.FromBoolean(false) };
dataLabels1.Append(sp);
C.ShowBubbleSize sbs = new C.ShowBubbleSize() { Val = BooleanValue.FromBoolean(false) };
dataLabels1.Append(sbs);
// end XLSX Support


C.ShowLeaderLines showLeaderLines1 = new C.ShowLeaderLines() { Val = true };
dataLabels1.Append(showLeaderLines1);

chartSeries.Append(dataLabels1);
}
else
{
chartSeries = barPieChart1.AppendChild<C.BarChartSeries>(new C.BarChartSeries(new C.Index() { Val = (uint)dataIndex },
new C.Order() { Val = (uint)dataIndex }));
}
// title
var seriesText2 = new C.SeriesText();
var stringReference3 = new C.StringReference();
var formula4 = new C.Formula();
formula4.Text = string.Format("Sheet1!${0}$1", excelCol);
var stringCache3 = new C.StringCache();
var pointCount4 = new C.PointCount() { Val = (UInt32Value)1U };
var stringPoint4 = new C.StringPoint() { Index = (UInt32Value)0U };
var numericValue6 = new C.NumericValue();
numericValue6.Text = RemoveInvalidXMLChars( seriesTitle);
stringPoint4.Append(numericValue6);
stringCache3.Append(pointCount4);
stringCache3.Append(stringPoint4);
stringReference3.Append(formula4);
stringReference3.Append(stringCache3);
seriesText2.Append(stringReference3);
chartSeries.AppendChild(seriesText2);


if (!isPieChart)
{ // bar color for series; suppress addtl series if flag is set to true
C.ChartShapeProperties chartShapeProperties1 = new C.ChartShapeProperties();
if (dataIndex == 0)
{
Drawing.SolidFill solidFill1 = new Drawing.SolidFill();
Drawing.SchemeColor schemeColor1 = new Drawing.SchemeColor() { Val = Drawing.SchemeColorValues.Text1 };
solidFill1.Append(schemeColor1);
chartShapeProperties1.Append(solidFill1);
}
else if(graphFirstSeriesOnly)
{
var noFill1 = new Drawing.NoFill();
chartShapeProperties1.Append(noFill1);
}
chartSeries.AppendChild(chartShapeProperties1);
}


// cache to hold the data points in series 1
var stringReference2 = new C.StringReference();
var stringCache2 = new C.StringCache();
var pointCount2 = new C.PointCount() { Val = new UInt32Value((uint)data.Keys.Count()) };
stringCache2.Append(pointCount2);

// cache to hold the data values in series 1
var numberReference1 = new C.NumberReference();
var numberingCache1 = new C.NumberingCache();
var formatCode1 = new C.FormatCode();

if (data.Any(d => d.Value.Any(v => v != (int)v)))
{ // any non-integral data, format as decimal
formatCode1.Text = "#,##0.00";
}
else
{
formatCode1.Text = "#,##0";
}
var pointCount3 = new C.PointCount() { Val = new UInt32Value((uint)data.Keys.Count()) };
numberingCache1.Append(formatCode1);
numberingCache1.Append(pointCount3);

var values = new C.Values();

uint i = 0;
foreach (string key in data.Keys)
{
if (isPieChart)
{
if (i == 1)
{ // override color for second item to black, indicating % complete. If you have more items you may want to extend this.
C.DataPoint dataPoint = new C.DataPoint();
dataPoint.Append(new C.Index() { Val = i });
var chartShapePros = new C.ChartShapeProperties();
var color = (i == 0 ? Drawing.SchemeColorValues.Text1 : Drawing.SchemeColorValues.Accent3);
chartShapePros.Append(new Drawing.SolidFill(new Drawing.SchemeColor() { Val = color }));
dataPoint.Append(chartShapePros);
chartSeries.Append(dataPoint);
}
}

var stringPoint2 = new C.StringPoint() { Index = (UInt32Value)i };
var numericValue2 = new C.NumericValue();
numericValue2.Text = RemoveInvalidXMLChars( key );
stringPoint2.Append(numericValue2);
stringCache2.Append(stringPoint2);

var numericPoint1 = new C.NumericPoint() { Index = (UInt32Value)i };
var numericValue4 = new C.NumericValue();
numericValue4.Text = RemoveInvalidXMLChars( data[key][dataIndex].ToString());
numericPoint1.Append(numericValue4);
numberingCache1.Append(numericPoint1);
i++;
}

// SP14.6 data labels
if(isPieChart && !string.IsNullOrWhiteSpace(customPieChartLabel))
{
C.DataLabels dls = new C.DataLabels();
i = 0;
foreach (string key in data.Keys)
{
C.DataLabel dataLabel1 = new C.DataLabel() { Index = new C.Index() { Val = (UInt32Value)i } };
C.Layout layout1 = new C.Layout();
dataLabel1.Append(layout1);
C.ChartText chartText1 = new C.ChartText();
C.RichText richText1 = new C.RichText();

Drawing.BodyProperties bodyProperties1 = new Drawing.BodyProperties();
richText1.Append(bodyProperties1);
Drawing.ListStyle listStyle1 = new Drawing.ListStyle();
richText1.Append(listStyle1);

var paragraph = CreateParagraph(Rms.Common.RmsUtilities.Pluralize(customPieChartLabel, (int)data[key][0] ), true, true, null, false);
richText1.Append(paragraph);
chartText1.Append(richText1);
dataLabel1.Append(chartText1);

// XLSX2010 Support
C.ShowLegendKey slk = new C.ShowLegendKey() { Val = BooleanValue.FromBoolean(false) };
dataLabel1.Append(slk);
C.ShowValue sv = new C.ShowValue() { Val = BooleanValue.FromBoolean(false) };
dataLabel1.Append(sv);
C.ShowCategoryName scn = new C.ShowCategoryName() { Val = BooleanValue.FromBoolean(false) };
dataLabel1.Append(scn);
C.ShowSeriesName ssn = new C.ShowSeriesName() { Val = BooleanValue.FromBoolean(false) };
dataLabel1.Append(ssn);
C.ShowPercent sp = new C.ShowPercent() { Val = BooleanValue.FromBoolean(false) };
dataLabel1.Append(sp);
C.ShowBubbleSize sbs = new C.ShowBubbleSize() { Val = BooleanValue.FromBoolean(false) };
dataLabel1.Append(sbs);
// end XLSX Support

dls.Append(dataLabel1);
i++;
}
chartSeries.Append(dls);
}

// text
int rowCount = data.Count;
var formula2 = new C.Formula();
formula2.Text = string.Format("Sheet1!$A$2:$A${0}", (rowCount + 1));
stringReference2.Append(formula2);
stringReference2.Append(stringCache2);

// values series 1
var formula3 = new C.Formula();
formula3.Text = string.Format("Sheet1!${0}$2:${0}${1}", excelCol, (rowCount + 1));
numberReference1.Append(formula3);
numberReference1.Append(numberingCache1);
values.Append(numberReference1);

var categoryAxisData1 = new C.CategoryAxisData();
categoryAxisData1.Append(stringReference2);
chartSeries.AppendChild(categoryAxisData1);

chartSeries.AppendChild<DocumentFormat.OpenXml.Drawing.Charts.Values>(values);
} // add chart series


Step 5.
You will need the ability to generate the Excel Data Source which is stored in the PPTX file. This allows the user to edit the chart. Here I reference ExcelTools to build an XLSX file, but you can use any tool you like, or just import an existing Excel file. Refer my other post for ExcelTools source.

/// <summary>
/// Embeds an excel data file into the package part.
/// </summary>
/// <param name="embeddedPackagePart1"></param>
/// <param name="dict"></param>
/// <param name="workingTempFolder"></param>
static void GenerateEmbeddedPackageContent(EmbeddedPackagePart embeddedPackagePart1,
Dictionary<string, decimal[]> dict, List<string> seriesTitles, string categoryTitle,
string workingTempFolder)
{
var dt = new System.Data.DataTable();
dt.Columns.Add(categoryTitle);
foreach (string st in seriesTitles)
{
dt.Columns.Add(st, typeof(decimal));
}
foreach (string key in dict.Keys)
{
var dr = dt.NewRow();
dr[0] = key;
decimal[] arr = dict[key];
for (int i = 1; i <= arr.Length; i++)
dr[i] = arr[i - 1];
dt.Rows.Add(dr);
}

string tempCsv = Path.Combine(workingTempFolder, Guid.NewGuid().ToString() + ".xlsx"); // get random file name
if (File.Exists(tempCsv))
File.Delete(tempCsv);
ExcelTools.CreatePackage(tempCsv, dt, true);
using(System.IO.Stream data = new System.IO.MemoryStream(File.ReadAllBytes(tempCsv)))
{
embeddedPackagePart1.FeedData(data);
data.Close();
}
if (File.Exists(tempCsv))
File.Delete(tempCsv); // remove our file we created
}

Step 6.
Call the methods to create your slide and chart.
You can build the dictionary however you like.
In this example, I have random data in which I graph 2 series on one chart. Each series has 3 values (10,20,30 and 100,150,80).

using (PresentationDocument package = PresentationDocument.Open(outputFile, true))
{
CreateSlide(package, slideIndex, sq.Text, true, true, false, true, chartId);
var dict = new Dictionary<string, decimal[]>();
dict.Add("AAA", new decimal[] { 10.0, 100.0 });
dict.Add("BBB", new decimal[] { 20.0, 150.5 });
dict.Add("CCC", new decimal[] { 30.0, 80.33 });
List<string> seriesTitles = new List<string>();
seriesTitles.Add("Total");
seriesTitles.Add("Avg");

GenerateBarGraphChartPart(slidePart, chartId, dict, "Chart", seriesTitles, true, strTempUploadDirectory);
// Save the modified presentation.
package.PresentationPart.Presentation.Save();
package.Close();
}

Created By: amos 4/18/2014 2:17:58 PM
Updated: 3/10/2015 12:10:38 PM


 Comments:
 > Guest 10/30/2015 4:53:26 AM
Hello,

Thank you for your Great Article,
I would like to Insert some notes about implementation.

1. You Can Edit Data In excel in your code the problem comes from ExcelTools, in the excel tools there are using DataTable approach and that's why in the save time you have problems with excel file itself. I fixed this in my side and you can normally edit in the excel file. So when you write data to the excel file you need to be sure that after writing excel file is not corrupted, if its corrupted your edit data functionality will not open anything.

2. If you want that after writing to the excel file and opening the excel file that you can edit data you need to reference your created datavalue to the column in the excel file.

var shapePr = new C.ChartShapeProperties(ChartHelper.GetFill(serieIndex, chType, title));
var barChartSeries = barChart.AppendChild<C.BarChartSeries>(new C.BarChartSeries(
new C.Index() { Val = new UInt32Value(id) },
new C.Order() { Val = new UInt32Value(id) },
shapePr,
new C.InvertIfNegative() { Val = false }
));
barChartSeries.Append(new C.SeriesText(new C.NumericValue() { Text = title }));



var dataLabels = barChartSerie.AppendChild<C.DataLabels>(new C.DataLabels());
dataLabels.Append(new C.DataLabel(new C.Index() { Val = 0 }, new C.Layout(new C.ManualLayout(new C.Left() { Val = 1.1747033642769491E-2 },
new C.Top() { Val = 0 })), new C.ShowLegendKey() { Val = false }, new C.ShowValue() { Val = true }, new C.ShowCategoryName() { Val = false },
new C.ShowSeriesName() { Val = false }, new C.ShowPercent() { Val = false }, new C.ShowBubbleSize() { Val = false }));

dataLabels.Append(new C.NumberingFormat() { SourceLinked = false, FormatCode = "#,##0" });
dataLabels.Append(new C.TextProperties(new Drawing.BodyProperties(), new Drawing.ListStyle(),
new Drawing.Paragraph(new Drawing.ParagraphProperties(
new Drawing.DefaultRunProperties(new Drawing.SolidFill(
new Drawing.SchemeColor() { Val = Drawing.SchemeColorValues.Background1 }),
new Drawing.LatinFont() { Typeface = "Calibri" }, new Drawing.ComplexScriptFont() { Typeface = "Calibri" }) { FontSize = 800 }), new Drawing.EndParagraphRunProperties() { Language = "en-US" })));

dataLabels.Append(new C.ShowLegendKey() { Val = false });
dataLabels.Append(new C.ShowValue() { Val = true });
dataLabels.Append(new C.ShowCategoryName() { Val = false });
dataLabels.Append(new C.ShowSeriesName() { Val = false });
dataLabels.Append(new C.ShowPercent() { Val = false });
dataLabels.Append(new C.ShowBubbleSize() { Val = false });
dataLabels.Append(new C.ShowLeaderLines() { Val = false });



var startRowPosition = 1;
char currentColumnWord = (char)((int)'A' + i + 1);

string catFormulaStr = String.Format("Sheet1!$A${0}:$A${1}", startRowPosition, (startRowPosition + serie.Cells.Count - 1));
var catAxData = barChartSerie.AppendChild<C.CategoryAxisData>(new C.CategoryAxisData()).AppendChild<C.StringReference>(new C.StringReference((
new C.Formula() { Text = catFormulaStr }))).AppendChild<C.StringCache>(new C.StringCache());
catAxData.Append(new C.PointCount() { Val = Convert.ToUInt32(serie.Cells.Count) });

string valFormulaStr = String.Format("Sheet1!${0}${1}:${2}${3}", currentColumnWord, startRowPosition, currentColumnWord, (startRowPosition + serie.Cells.Count - 1));
var valuesSerie = barChartSerie.AppendChild<C.Values>(new C.Values()).AppendChild<C.NumberReference>(new C.NumberReference(new C.Formula() { Text = valFormulaStr })).AppendChild<C.NumberingCache>(new C.NumberingCache());
valuesSerie.Append(new C.PointCount() { Val = Convert.ToUInt32(serie.Cells.Count) });

for (var j = 0; j < serie.Cells.Count; j++)
{
uint id = Convert.ToUInt32(j);
var valueCell = serie.Cells[j];
var catCell = catSerie.Cells[j];
catAxData.AppendChild<C.StringPoint>(new C.StringPoint() { Index = id }).Append(new C.NumericValue(catCell.Value));
valuesSerie.AppendChild<C.NumericPoint>(new C.NumericPoint() { Index = new UInt32Value(id) }).Append(new C.NumericValue(valueCell.Value));
}
 > Guest 1/4/2017 9:35:00 PM
The charts are not refreshing automatically. I need to right click on the chart and select Edit Data, after this process the data appears in the chart. Please help.
 > amos 1/4/2017 10:36:29 PM
Answer to Guest:
The excel file has to be written in a specific way to support auto-bind/refresh when in the pptx file. Namely it has to be enclosed in a data table so the file knows where the data is.
(After you edit it this is added automatically.)
See my other post 'Create an XLSX File using OpenXML API (ExcelTools.cs)' for implementation, specifically the flag 'encloseDataInTable'.
If it still doesn't work, you are missing something from the above code.
 > Guest 1/5/2017 5:00:21 AM
Hi, thanks for quick reply and also thanks for this wonderful post.

Here is my code where I am calling Excel Tools:

var dt = new System.Data.DataTable();
dt.Columns.Add(categoryTitle);
foreach (string st in seriesTitles)
{
dt.Columns.Add(st, typeof(int));
}
foreach (string key in dict.Keys)
{
var dr = dt.NewRow();
dr[0] = key;
int[] arr = dict[key];
for (int i = 1; i <= arr.Length; i++)
dr[i] = arr[i - 1];
dt.Rows.Add(dr);
}

string tempCsv = Path.Combine(workingTempFolder, Guid.NewGuid().ToString() + ".xlsx"); // get random file name
if (File.Exists(tempCsv))
File.Delete(tempCsv);
ExcelTools.CreatePackage(tempCsv, dt, true);

using (Stream data = new MemoryStream(File.ReadAllBytes(tempCsv)))
{
embeddedPackagePart1.FeedData(data);
data.Close();
}

if (File.Exists(tempCsv))
File.Delete(tempCsv); // remove our file we created
 > Guest 1/6/2017 12:37:30 AM
Hi, It's done now. Yes it was my mistake, I commented one line of code where it was updating the values in cache. Thanks again for this wonderful post.
 > Guest 5/24/2021 8:01:18 AM
thank you soo much for this you are a life saver