question

FranoisSchuster-4648 avatar image
0 Votes"
FranoisSchuster-4648 asked JackJJun-MSFT commented

Create xls file with 2 charts openXML

I am trying to create an excel file with 2 tabs and 2 graphics.
For a tab with a graphic this works fine.
For 2 tabs with 2 graphics, the file does not open in excel.
The problem seems to come from this line (by commenting out the code from it the excel file opens):

 TwoCellAnchor twoCellAnchor2 = drawingsPart2.WorksheetDrawing.AppendChild(new TwoCellAnchor());

Here is my code:



 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" });
                 DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.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
                 DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame = twoCellAnchor.AppendChild(new DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame());
                 graphicFrame.Macro = string.Empty;
    
                 graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
                         new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
                         {
                             Id = 2u,
                             Name = "Sample Chart"
                         },
                         new DocumentFormat.OpenXml.Drawing.Spreadsheet.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();
    
    
                 WorksheetPart worksheetPart2 = workbookPart.AddNewPart<WorksheetPart>();
                 worksheetPart2.Worksheet = new Worksheet();
    
                 Sheet sheet2 = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart2), SheetId = 2, Name = "Students2" };
    
                 SheetData sheetData2 = worksheetPart2.Worksheet.AppendChild(new SheetData());
    
                 // Add drawing part to WorksheetPart
                 DrawingsPart drawingsPart2 = worksheetPart2.AddNewPart<DrawingsPart>();
                 worksheetPart2.Worksheet.Append(new Drawing() { Id = worksheetPart2.GetIdOfPart(drawingsPart2) });
                 worksheetPart2.Worksheet.Save();
    
                 drawingsPart2.WorksheetDrawing = new WorksheetDrawing();
    
                 sheets.Append(sheet2);
    
                 workbookPart.Workbook.Save();
    
                 // Add a new chart and set the chart language
                 ChartPart chartPart2 = drawingsPart2.AddNewPart<ChartPart>();
                 chartPart2.ChartSpace = new ChartSpace();
                 chartPart2.ChartSpace.AppendChild(new EditingLanguage() { Val = "en-US" });
                 DocumentFormat.OpenXml.Drawing.Charts.Chart chart2 = chartPart2.ChartSpace.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Chart());
                 chart2.AppendChild(new AutoTitleDeleted() { Val = true }); // We don't want to show the chart title
    
                 // Create a new Clustered Column Chart
                 PlotArea plotArea2 = chart2.AppendChild(new PlotArea());
                 Layout layout2 = plotArea2.AppendChild(new Layout());
    
                 BarChart barChart2 = plotArea2.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 = new Row();
                 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
                 sheetData2.AppendChild(row);
                 rowIndex++;
    
                 // Create chart series
                 for (int i = 0; i < students.Count; i++)
                 {
                     BarChartSeries barChartSeries2 = barChart2.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 categoryAxisData2 = barChartSeries2.AppendChild(new CategoryAxisData());
    
                     // Category
                     // Constructing the chart category
                     string formulaCat = "Students!$B$1:$G$1";
    
                     StringReference stringReference2 = categoryAxisData2.AppendChild(new StringReference()
                     {
                         Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaCat }
                     });
    
                     StringCache stringCache2 = stringReference2.AppendChild(new StringCache());
                     stringCache2.Append(new PointCount() { Val = (uint)Months.Short.Length });
    
                     for (int j = 0; j < Months.Short.Length; j++)
                     {
                         stringCache2.AppendChild(new NumericPoint() { Index = (uint)j }).Append(new NumericValue(Months.Short[j]));
                     }
                 }
    
                 var chartSeries2 = barChart2.Elements<BarChartSeries>().GetEnumerator();
    
                 for (int i = 0; i < students.Count; i++)
                 {
                     row = new Row();
    
                     row.AppendChild(ConstructCell(students[i].Name, CellValues.String));
    
                     chartSeries2.MoveNext();
    
                     string formulaVal = string.Format("Students!$B${0}:$G${0}", rowIndex);
                     DocumentFormat.OpenXml.Drawing.Charts.Values values = chartSeries2.Current.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values());
    
                     NumberReference numberReference2 = values.AppendChild(new NumberReference()
                     {
                         Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = formulaVal }
                     });
    
                     NumberingCache numberingCache2 = numberReference2.AppendChild(new NumberingCache());
                     numberingCache2.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));
    
                         numberingCache2.AppendChild(new NumericPoint() { Index = j }).Append(new NumericValue(value.ToString()));
                     }
    
                     sheetData2.AppendChild(row);
                     rowIndex++;
                 }
    
                 barChart2.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 }
                                 ));
    
                 barChart2.Append(new AxisId() { Val = 48650112u });
                 barChart2.Append(new AxisId() { Val = 48672768u });
    
                 // Adding Category Axis
                 plotArea2.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
                 plotArea2.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) }
                     ));
    
                 chart2.Append(
                         new PlotVisibleOnly() { Val = true },
                         new DisplayBlanksAs() { Val = new EnumValue<DisplayBlanksAsValues>(DisplayBlanksAsValues.Gap) },
                         new ShowDataLabelsOverMaximum() { Val = false }
                     );
    
                 chartPart2.ChartSpace.Save();
    
                 // Positioning the chart on the spreadsheet
                 TwoCellAnchor twoCellAnchor2 = drawingsPart2.WorksheetDrawing.AppendChild(new TwoCellAnchor());
    
                 twoCellAnchor2.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(
                         new ColumnId("0"),
                         new ColumnOffset("0"),
                         new RowId((rowIndex + 2).ToString()),
                         new RowOffset("0")
                     ));
    
                 twoCellAnchor2.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
                 DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame2 = twoCellAnchor.AppendChild(new DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame());
                 graphicFrame2.Macro = string.Empty;
    
                 graphicFrame2.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
                         new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
                         {
                             Id = 2u,
                             Name = "Sample Chart"
                         },
                         new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()
                     ));
    
                 graphicFrame2.Append(new Transform(
                         new DocumentFormat.OpenXml.Drawing.Offset() { X = 0L, Y = 0L },
                         new DocumentFormat.OpenXml.Drawing.Extents() { Cx = 0L, Cy = 0L }
                     ));
    
                 graphicFrame2.Append(new DocumentFormat.OpenXml.Drawing.Graphic(
                         new DocumentFormat.OpenXml.Drawing.GraphicData(
                                 new ChartReference() { Id = drawingsPart2.GetIdOfPart(chartPart2) }
                             )
                         { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" }
                     ));
    
                 twoCellAnchor2.Append(new ClientData());
    
                 drawingsPart2.WorksheetDrawing.Save();
    
                 worksheetPart2.Worksheet.Save();
             }
         }
    
         private Cell ConstructCell(string value, CellValues dataType)
         {
             return new Cell()
             {
                 CellValue = new CellValue(value),
                 DataType = new EnumValue<CellValues>(dataType),
             };
         }
    
         private void Initizalize(List<Student> students)
         {
             students.AddRange(new Student[] {
                 new Student
                 {
                     Name = "Liza",
                     Values = new byte[] { 10, 25, 30, 15, 20, 19 }
                 },
                 new Student
                 {
                     Name = "Macy",
                     Values = new byte[] { 20, 15, 26, 30, 10, 15 }
                 }
             });
         }
    
         public class Student
         {
             public string Name { get; set; }
             public byte[] Values { get; set; }
         }
    
         public struct Months
         {
             public static string[] Short = {
             "Jan",
             "Feb",
             "Mar",
             "Apr",
             "May",
             "Jun"
         };
         }



dotnet-csharpoffice-excel-itpro
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@FranoisSchuster-4648 , Since your problem is related to Openxml SDK, I recommend that you could ask your question in GitHub.


0 Votes 0 ·

0 Answers