Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Create a New Workbook With LineChart,then I opened it with office Excel,but office excel will repair it ,then my LineChart will disappear #1716

Closed
xq1029 opened this issue May 13, 2024 · 1 comment

Comments

@xq1029
Copy link

xq1029 commented May 13, 2024

Describe the bug
Create a New Workbook With LineChart,then I opened it with office Excel,but office excel will repair it ,then my LineChart will disappear

Here's the File
408064545445-231008161302-A-1-1-2.xlsx

Screenshots
1715562216191

Here's the code

static void InsertLineChartInSpreadsheet(){
string docName = "D:\\408064545445-231008161302-A-1-1-2.xlsx";
Dictionary<string, List<Tuple<string, int>>> models = new Dictionary<string, List<Tuple<string, int>>>();
models.Add("abc", new List<Tuple<string, int>>() { new("Time1", 1), new("Time2", 2), new("Time3", 3), new("Time4", 4), new("Time5", 
5), new("Time6", 6) });
models.Add("adcd", new List<Tuple<string, int>>() { new("Time1", 2), new("Time2", 3), new("Time3", 4), new("Time4", 5), new("Time5", 
6), new("Time6", 7) });
using SpreadsheetDocument document = SpreadsheetDocument.Create(docName, SpreadsheetDocumentType.Workbook);
{
    WorkbookPart workbookPart = document.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();
    workbookPart.Workbook.AppendChild(new Sheets());
    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());

    Sheet sheet = new()
    {
        SheetId = 2,
        Name = "Chart",
        Id = workbookPart.GetIdOfPart(worksheetPart)
    };
    workbookPart.Workbook.Sheets?.AppendChild(sheet);
    var shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
    if (shareStringPart.SharedStringTable == null)
    {
        shareStringPart.SharedStringTable = new SharedStringTable();
    }
    DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();

    worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
    { Id = worksheetPart.GetIdOfPart(drawingsPart) });

    worksheetPart.Worksheet.Save();

    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());
    PlotArea plotArea = chart.AppendChild<PlotArea>(new PlotArea());
    Layout layout = plotArea.AppendChild<Layout>(new Layout());
    var barChart = plotArea.AppendChild<LineChart>(new LineChart(
        new Grouping() { Val = new EnumValue<GroupingValues>(GroupingValues.Standard) }
        ));
    uint i = 0;
    foreach (var model in models)
    {
        shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new 
        DocumentFormat.OpenXml.Spreadsheet.Text(model.Key)));
        LineChartSeries barChartSeries = barChart.AppendChild<LineChartSeries>(new LineChartSeries(new Index()
        {
            Val = new UInt32Value(i)
        },
        new Order() { Val = new UInt32Value(i) },
        new SeriesText(new NumericValue() { Text = model.Key })));
        var strLit = barChartSeries.AppendChild<CategoryAxisData>(new CategoryAxisData())
            .AppendChild<StringLiteral>(new StringLiteral());
        NumberLiteral numLit = barChartSeries.AppendChild<DocumentFormat.OpenXml.Drawing.Charts.Values>(
          new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild<NumberLiteral>(new NumberLiteral());
        numLit.Append(new FormatCode("General"));
        var index = 0U;
        var xmlElementsX = new List<OpenXmlElement>();
        var xmlElementsData = new List<OpenXmlElement>();
        xmlElementsX.Add(new PointCount() { Val = new UInt32Value((uint)model.Value.Count) });
        xmlElementsData.Add(new PointCount() { Val = new UInt32Value((uint)model.Value.Count) });
        foreach (var item in model.Value)
        {
            xmlElementsX.Add(new StringPoint() { Index = new UInt32Value(index), NumericValue = new NumericValue(item.Item1) });
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(item.Item1)));
            xmlElementsData.Add(new NumericPoint() { Index = new UInt32Value(index), NumericValue = new NumericValue(item.Item2.ToString()) });
            index++;
        }
        shareStringPart.SharedStringTable.Count = new UInt32Value((uint)model.Value.Count);
        strLit.Append(xmlElementsX);
        numLit.Append(xmlElementsData);
        i++;
    }
    barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) });
    barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) });
    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) }));
    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) }));
    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) });
    chartPart.ChartSpace.Save();
    drawingsPart.WorksheetDrawing = new WorksheetDrawing();
    TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild<TwoCellAnchor>(new TwoCellAnchor());
    twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("0"),
        new ColumnOffset("581025"),
        new RowId("1"),
        new RowOffset("114300")));
    twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("25"),
        new ColumnOffset("276225"),
        new RowId("39"),
        new RowOffset("0")));
    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 = "https://schemas.openxmlformats.org/drawingml/2006/chart" }));
    twoCellAnchor.Append(new ClientData());
    drawingsPart.WorksheetDrawing.Save();
    workbookPart.Workbook.Save();
}
@xq1029
Copy link
Author

xq1029 commented May 13, 2024

Repaired Records: Drawing from /xl/drawings/drawing1.xml part (Drawing shape)

@xq1029 xq1029 closed this as completed May 30, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant