Eric White's Blog

Trying to insert a chart programmatically using openxml and C#. However, the chart is getting populated using Create method but the ppt generated is corrupted. If I use an existing chart and make changes in the chart data which is the embedded data on the excel, it works and gets saved without corruption. But on creating a fresh ppt from scratch and then creating a fresh chart with some test values is giving me error. Used the OpenXML Productivity Tool to understand the reason of the issue and extracted the code at the same time using 7 zip to compare the xml schema structure to get the clue of what is wrong but still could not figure out. Really want your expertise on this.

Providing code for reference –

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Charts;
using DocumentFormat.OpenXml.Office.Drawing;
using DocumentFormat.OpenXml.Office2010.Word;
using DocumentFormat.OpenXml.Office2013.Drawing.Chart;
using DocumentFormat.OpenXml.Office2016.Drawing.ChartDrawing;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Presentation;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using System.IO;
using System.Text;
using A = DocumentFormat.OpenXml.Drawing;
using C = DocumentFormat.OpenXml.Drawing.Charts;

class Program
{
public static string pptFilePath = “C:\\Testing\\Template_Creation\\makePPT\\NewFolder\\presentation.pptx”;
static void Main(string[] args)
{
CreatePowerPointWithEmbeddedSpreadsheet(pptFilePath);
}

static void CreatePowerPointWithEmbeddedSpreadsheet(string pptFilePath)
{
// Sample data
List<Student> students = new List<Student>
{
new Student(“TESTNAME00”, new List<int> { 33, 44, 23 }),
new Student(“TESTNAME01”, new List<int> { 33, 34, 33 }),
new Student(“TESTNAME02”, new List<int> { 33, 34, 33 }),
new Student(“TESTNAME03”, new List<int> { 33, 44, 23 }),
};

// Headers
string[] headers = { “”, “Sheet 1”, “Sheet 2”, “Sheet 3” };
using (PresentationDocument presentationDocument = PresentationDocument.Create(pptFilePath, PresentationDocumentType.Presentation))
{
// Create presentation part
PresentationPart presentationPart = presentationDocument.AddPresentationPart();
presentationPart.Presentation = new Presentation();

// Create slide master part
SlideMasterPart slideMasterPart = presentationPart.AddNewPart<SlideMasterPart>();
slideMasterPart.SlideMaster = new SlideMaster(new CommonSlideData(new DocumentFormat.OpenXml.Office.Drawing.ShapeTree()));
SlideLayoutPart slideLayoutPart = slideMasterPart.AddNewPart<SlideLayoutPart>();
slideLayoutPart.SlideLayout = new SlideLayout(new CommonSlideData(new DocumentFormat.OpenXml.Office.Drawing.ShapeTree()));
slideMasterPart.SlideMaster.Append(new SlideLayoutIdList(new SlideLayoutId() { Id = 1, RelationshipId = slideMasterPart.GetIdOfPart(slideLayoutPart) }));

// Create slide part
SlidePart slidePart = presentationPart.AddNewPart<SlidePart>();
slidePart.Slide = new Slide(new CommonSlideData(new DocumentFormat.OpenXml.Office.Drawing.ShapeTree()));

SlideIdList slideIdList = presentationPart.Presentation.AppendChild(new SlideIdList());
uint slideId = 256;
slideIdList.Append(new SlideId() { Id = slideId, RelationshipId = presentationPart.GetIdOfPart(slidePart) });

CommonSlideData commonSlideData2 = slidePart.Slide.CommonSlideData ?? slidePart.Slide.AppendChild(new CommonSlideData());

DocumentFormat.OpenXml.Presentation.ShapeTree shapeTree2 = commonSlideData2.ShapeTree ?? commonSlideData2.AppendChild(new DocumentFormat.OpenXml.Presentation.ShapeTree());
DocumentFormat.OpenXml.Presentation.NonVisualGroupShapeProperties nonVisualProperties2 = shapeTree2.AppendChild(new DocumentFormat.OpenXml.Presentation.NonVisualGroupShapeProperties());
nonVisualProperties2.NonVisualDrawingProperties = new DocumentFormat.OpenXml.Presentation.NonVisualDrawingProperties() { Id = 2, Name = “second” };
nonVisualProperties2.NonVisualGroupShapeDrawingProperties = new DocumentFormat.OpenXml.Presentation.NonVisualGroupShapeDrawingProperties();
nonVisualProperties2.ApplicationNonVisualDrawingProperties = new ApplicationNonVisualDrawingProperties();

shapeTree2.AppendChild(new A.ChartDrawing.GroupShapeProperties(
new Offset() { X = 0, Y = 0 },
new Extents() { Cx = 0, Cy = 0 },
new ChildOffset() { X = 0, Y = 0 },
new ChildExtents() { Cx = 0, Cy = 0 }
));

uint idx = 2U;

DocumentFormat.OpenXml.Presentation.Shape textBoxShape = new DocumentFormat.OpenXml.Presentation.Shape();
idx++;

textBoxShape.NonVisualShapeProperties = new DocumentFormat.OpenXml.Presentation.NonVisualShapeProperties(
new DocumentFormat.OpenXml.Presentation.NonVisualDrawingProperties { Id = idx, Name = “TextBox 1” },
new DocumentFormat.OpenXml.Presentation.NonVisualShapeDrawingProperties(new A.ShapeLocks { NoGrouping = true }),
new ApplicationNonVisualDrawingProperties(new PlaceholderShape())
);

textBoxShape.ShapeProperties = new DocumentFormat.OpenXml.Presentation.ShapeProperties();

textBoxShape.TextBody = new DocumentFormat.OpenXml.Presentation.TextBody(new A.BodyProperties(),
new A.ListStyle(),
new A.Paragraph(
new A.ParagraphProperties(
new NoBullet()
),
new DocumentFormat.OpenXml.Drawing.Run(
new A.RunProperties(
new SolidFill(new A.RgbColorModelHex() { Val = “2751A5” }),
new LatinFont() { Typeface = “Montserrat” }
)
{ Bold = true, FontSize = 2000 },
new DocumentFormat.OpenXml.Drawing.Text(“Market Research – Likeability with Rationale Results”)),
new EndParagraphRunProperties()
));

A.Transform2D transform2D = new A.Transform2D(new Offset { X = 0L, Y = 0L },
new Extents { Cx = 9000000L, Cy = 500000L });

textBoxShape.ShapeProperties.Append(transform2D);

slidePart.Slide.CommonSlideData.ShapeTree.Append(textBoxShape);

//slidePart.Slide.CommonSlideData.ShapeTree.Append(textBoxShape);

DocumentFormat.OpenXml.Presentation.Shape textBoxShape2 = new DocumentFormat.OpenXml.Presentation.Shape();
idx++;

textBoxShape2.NonVisualShapeProperties = new DocumentFormat.OpenXml.Presentation.NonVisualShapeProperties(
new DocumentFormat.OpenXml.Presentation.NonVisualDrawingProperties { Id = idx, Name = “TextBox 2” },
new DocumentFormat.OpenXml.Presentation.NonVisualShapeDrawingProperties(new A.ShapeLocks { NoGrouping = true }),
new ApplicationNonVisualDrawingProperties(new PlaceholderShape())
);

textBoxShape2.ShapeProperties = new DocumentFormat.OpenXml.Presentation.ShapeProperties();

textBoxShape2.TextBody = new DocumentFormat.OpenXml.Presentation.TextBody(
new A.BodyProperties(),
new A.ListStyle(),
new A.Paragraph(
new A.ParagraphProperties(
new NoBullet()
),
new A.Run(
new A.RunProperties(new A.LatinFont() { Typeface = “Open Sans” }) { FontSize = 1600 },
new A.Text(“Respondents were asked to categorize each of the test names as a “)
),
new A.Run(
new A.RunProperties(
new A.SolidFill(new A.RgbColorModelHex() { Val = “2751A5” }),
new A.LatinFont() { Typeface = “Open Sans” }
)
{ Bold = true, FontSize = 1600 },
new A.Text(“liked”)
),
new A.Run(
new A.RunProperties(
new A.SolidFill(new A.RgbColorModelHex() { Val = “009DD9” }),
new A.LatinFont() { Typeface = “Open Sans” }
)
{ Bold = true, FontSize = 1600 },
new A.Text(“, neutral or “)
),
new A.Run(
new A.RunProperties(
new A.SolidFill(new A.RgbColorModelHex() { Val = “FF7D7D” }),
new A.LatinFont() { Typeface = “Open Sans” }
)
{ Bold = true, FontSize = 1600 },
new A.Text(“disliked”)
),
new A.Run(
new A.RunProperties(new A.LatinFont() { Typeface = “Open Sans” }) { FontSize = 1600 },
new A.Text(” name and provide rationale* for their decisions.\r\n”)
)
)
);

A.Transform2D transform2D1 = new A.Transform2D(new Offset { X = 10000L, Y = 500000L },
new Extents { Cx = 5000000L, Cy = 900000L });

textBoxShape2.ShapeProperties.Append(transform2D1);

slidePart.Slide.CommonSlideData.ShapeTree.Append(textBoxShape2);

//Create a chart part in the slide part
ChartPart chartPart = slidePart.AddNewPart<ChartPart>();

chartPart.ChartSpace = new C.ChartSpace();
chartPart.ChartSpace.AddNamespaceDeclaration(“c”, “http://schemas.openxmlformats.org/drawingml/2006/chart”);
chartPart.ChartSpace.AddNamespaceDeclaration(“a”, “http://schemas.openxmlformats.org/drawingml/2006/main”);
chartPart.ChartSpace.AddNamespaceDeclaration(“r”, “http://schemas.openxmlformats.org/officeDocument/2006/relationships”);
chartPart.ChartSpace.AddNamespaceDeclaration(“c16r2”, “http://schemas.microsoft.com/office/drawing/2015/06/chart”);

chartPart.ChartSpace.Append(new C.Date1904());

chartPart.ChartSpace.AppendChild(new C.EditingLanguage() { Val = “en-US” });
C.Chart chart = chartPart.ChartSpace.AppendChild(new C.Chart());
chart.AppendChild(new C.AutoTitleDeleted() { Val = true });

// Create a plot area for the chart
C.PlotArea plotArea = chart.AppendChild(new C.PlotArea());
C.Layout layout = plotArea.AppendChild(new C.Layout());

// Create a bar chart
C.BarChart barChart = plotArea.AppendChild(new C.BarChart(
new C.BarDirection() { Val = C.BarDirectionValues.Bar },
new C.BarGrouping() { Val = C.BarGroupingValues.PercentStacked },
new C.VaryColors() { Val = false },
new GapWidth() { Val = 150 },
new Overlap() { Val = 100 }
));

// Create chart series
for (int i = 1; i < headers.Length; i++) // Skip the first column (Name)
{
C.BarChartSeries barChartSeries = barChart.AppendChild(new C.BarChartSeries(
new C.Index() { Val = (uint)i – 1 },
new C.Order() { Val = (uint)i – 1 },
new C.SeriesText(new C.NumericValue() { Text = headers[i] })
));

// Add color to the series
A.ShapeProperties chartShapeProperties = barChartSeries.AppendChild(new A.ShapeProperties());
chartShapeProperties.AppendChild(new A.SolidFill(
new A.RgbColorModelHex() { Val = GetColorHex(i – 1) }
));

// Adding category axis to the chart
C.CategoryAxisData categoryAxisData = barChartSeries.AppendChild(new C.CategoryAxisData());

// Constructing the chart category
C.StringReference stringReference = categoryAxisData.AppendChild(new C.StringReference());
C.Formula formula = stringReference.AppendChild(new C.Formula(“Sheet1!$A$2:$A$” + (students.Count + 1)));

C.StringCache stringCache = stringReference.AppendChild(new C.StringCache());
//stringCache.Append(new FormatCode(“General”));
stringCache.Append(new PointCount() { Val = new UInt32Value((uint)headers.Length) });

for (int j = 0; j < students.Count; j++)
{
stringCache.AppendChild(new C.NumericPoint() { Index = (uint)j }).Append(new C.NumericValue(students[j].Name.ToString()));
}

// Adding values to the chart series
C.Values values = barChartSeries.AppendChild(new C.Values());
C.NumberReference numberReference = values.AppendChild(new C.NumberReference());
//formula = numberReference.AppendChild(new C.Formula($”Sheet1!$” + GetExcelColumnName(i + 1) + “$2:$” + GetExcelColumnName(i + 1) + “$” + (students.Count + 1)));

numberReference.AppendChild(new C.Formula($”Sheet1!$” + GetExcelColumnName(i + 1) + “$2:$” + GetExcelColumnName(i + 1) + “$” + (students.Count + 1)));
C.NumberingCache numberingCache = numberReference.AppendChild(new C.NumberingCache());
numberingCache.Append(new PointCount() { Val = (uint)students.Count });

for (uint j = 0; j < students.Count; j++)
{
var value = students[(int)j].Values[i – 1];
numberingCache.AppendChild(new C.NumericPoint() { Index = j }).Append(new C.NumericValue(value.ToString()));
//numberingCache.AppendChild(new C.PointCount() { Val = (uint)j });
}
}

barChart.AppendChild(new C.DataLabels(
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 },
new C.DataLabelPosition() { Val = C.DataLabelPositionValues.Center },
new ChartShapeProperties(new A.Outline(new NoFill(), new A.EffectList()))
));

barChart.Append(new C.AxisId() { Val = 48650112u });
barChart.Append(new C.AxisId() { Val = 48672768u });

// Adding Category Axis
plotArea.AppendChild(
new C.CategoryAxis(
new C.AxisId() { Val = 48650112u },
new C.Scaling(new C.Orientation() { Val = C.OrientationValues.MinMax }),
new C.Delete() { Val = false },
new C.AxisPosition() { Val = C.AxisPositionValues.Left },
new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo },
new C.CrossingAxis() { Val = 48672768u },
new C.AutoLabeled() { Val = true },
new C.LabelAlignment() { Val = C.LabelAlignmentValues.Center },
new C.MajorTickMark() { Val = C.TickMarkValues.None },
new C.MinorTickMark() { Val = C.TickMarkValues.None },
new C.ShapeProperties(
new NoFill(),
new A.Outline
(
new SolidFill()
)
) // Adding Value Axis
plotArea.AppendChild(
new C.ValueAxis(
new C.AxisId() { Val = 48672768u },
new C.Scaling(new C.Orientation() { Val = C.OrientationValues.MinMax }),
new C.Delete() { Val = false },
new C.AxisPosition() { Val = C.AxisPositionValues.Left },
new C.MajorGridlines(),
new C.NumberingFormat()
{
FormatCode = “0%”,
SourceLinked = true
},
new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo },
new C.CrossingAxis() { Val = 48650112u },
new C.Crosses() { Val = C.CrossesValues.AutoZero },
new C.CrossBetween() { Val = C.CrossBetweenValues.Between },
new MajorGridlines(
new C.ShapeProperties
(
new NoFill(),
new A.Outline
(
new NoFill()
),
new EffectList()
),
new C.TextProperties
(
new BodyProperties(),
new ListStyle(),
new A.Paragraph
(
new A.ParagraphProperties
(
new DefaultParagraphProperties
(
new SolidFill
(
new A.SchemeColor
(
new A.LuminanceModulation() { Val = 65000 },
new A.LuminanceOffset() { Val = 35000 }
)
)
)
),
new EndParagraphRunProperties()
)
)
),
new MajorTickMark(),
new MinorTickMark()
)
);

chart.Append(
new C.PlotVisibleOnly() { Val = true },
new C.DisplayBlanksAs() { Val = C.DisplayBlanksAsValues.Gap },
new C.ShowDataLabelsOverMaximum() { Val = false }
);

chartPart.ChartSpace.Save();

EmbeddingMethod(chartPart, headers, students);

//Add chart to slide
DocumentFormat.OpenXml.Presentation.GraphicFrame graphicFrame = slidePart.Slide.CommonSlideData.ShapeTree.AppendChild(new DocumentFormat.OpenXml.Presentation.GraphicFrame());
graphicFrame.NonVisualGraphicFrameProperties = new DocumentFormat.OpenXml.Presentation.NonVisualGraphicFrameProperties(
new DocumentFormat.OpenXml.Presentation.NonVisualDrawingProperties() { Id = 1, Name = “Chart” },
new DocumentFormat.OpenXml.Presentation.NonVisualGraphicFrameDrawingProperties()
);

graphicFrame.Transform = new Transform(
new A.Offset() { X = 2811110L, Y = 1000000L },
new A.Extents() { Cx = 6000000L, Cy = 5086100L }
);

graphicFrame.Graphic = new A.Graphic(
new A.GraphicData(
new C.ChartReference() { Id = slidePart.GetIdOfPart(chartPart) }
)

{ Uri = “http://schemas.openxmlformats.org/drawingml/2006/chart” }
);

C.ExternalData externalData = new C.ExternalData() { Id = slidePart.GetIdOfPart(chartPart) };
AutoUpdate autoUpdate = new AutoUpdate() { Val = false };
externalData.Append(autoUpdate);
chartPart.ChartSpace.Append(externalData);
chartPart.ChartSpace.Save();

slidePart.Slide.Save();
presentationPart.Presentation.Save();
}
}

static MemoryStream MemoryEmbeddedFunction(string[] headers, List<Student> students)
{
MemoryStream memoryStream = new MemoryStream();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData = new SheetData();

// Create header row
Row headerRow = new Row();
foreach (var header in headers)
{
Cell cell = new Cell() { CellValue = new CellValue(header), DataType = CellValues.String };
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);

// Create data rows
foreach (var student in students)
{
Row row = new Row();
row.AppendChild(new Cell() { CellValue = new CellValue(student.Name), DataType = CellValues.String });
foreach (var value in student.Values)
{
row.AppendChild(new Cell() { CellValue = new CellValue(value.ToString()), DataType = CellValues.Number });
}
sheetData.AppendChild(row);
}

worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;

Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = “Sheet1” };
sheets.Append(sheet);

workbookPart.Workbook.Save();
}
memoryStream.Position = 0;
return memoryStream;
}

static void EmbeddingMethod(ChartPart chartPart, string[] headers, List<Student> students)
{
// Generate the memory stream with the Excel data
MemoryStream memoryStream = MemoryEmbeddedFunction(headers, students);

// Create the embedded package part
EmbeddedPackagePart embeddedPackagePart = chartPart.AddEmbeddedPackagePart(EmbeddedPackagePartType.Xlsx, “rId3”);

// Use the memory stream with the modified GenerateEmbeddedPackagePart1Content method
GenerateEmbeddedPackagePart1Content(embeddedPackagePart, memoryStream);
}

static void GenerateEmbeddedPackagePart1Content(EmbeddedPackagePart embeddedPackagePart, MemoryStream memoryStream)
{
memoryStream.Position = 0; // Ensure the stream position is at the beginning
embeddedPackagePart.FeedData(memoryStream); // Directly feed the binary data
memoryStream.Close();
}