none
Delete legend entries from an Excel chart RRS feed

  • Question

  • Aiming to remove legend entries from an Excel chart from code I tried:

    new LegendEntry(
                    new Index() { Val = (UInt32Value)i },
                    new Delete { Val = true }));

    where i is the index of the entry.

    What is strange is that if I open the Excel generated from code, and then manually remove an entry, I see the same code being generated:

    C.LegendEntry legendEntry1 = new C.LegendEntry();
    C.Index index4 = new C.Index(){ Val = (UInt32Value)1U };
    C.Delete delete3 = new C.Delete(){ Val = true };
    
    legendEntry1.Append(index4);
    legendEntry1.Append(delete3);

    But when the Excel is generated, even though the code is there the legend entry is not deleted.

    Is there a way to remove legend entries from an Excel chart from code?


    Tuesday, November 10, 2015 12:15 PM

Answers

  • I was having the same issue when trying to modify XML generated by EPPLUS.

    I found the solution (For me at least) was rather strange.

    I had to make sure the following tags existed. Once i did that the legend entries would be removed.

    c:chartSpace\c:chart\c:plotArea\c:lineChart\c:ser\c:val\c:numRef\c:numCache\c:formatCode

    c:chartSpace\c:chart\c:plotArea\c:lineChart\c:ser\c:val\c:numRef\c:numCache\c:ptCount

    The EPPLUS library only generated:

    c:chartSpace\c:chart\c:plotArea\c:lineChart\c:ser\c:val\c:numRef

    i had to manually add c:numCache\c:formatCode and c:numCache\c:ptCount for each series in each of my linecharts. so it looks something like this:

            
    <c:val>
      <c:numRef>
        <c:f>'Power Good'!$I$19:$I$501</c:f>
        <c:numCache>
          <c:formatCode>General</c:formatCode>
          <c:ptCount val="483" />
        </c:numCache>
      </c:numRef>
    </c:val>

    once these tags were added, the legend entries would be removed.

    Again i was using EPPLUS library so my solution might not be useful

    But ff your still having the issue i hope this helps.

    • Proposed as answer by TDawg82 Monday, November 16, 2015 7:45 PM
    • Marked as answer by dani_s Friday, November 20, 2015 1:48 PM
    Monday, November 16, 2015 7:39 PM

All replies

  • >>>Is there a way to remove legend entries from an Excel chart from code?

    Yes, you can remove legend entries from an Excel chart by Open Xml.

    Before remove legend entries

     

     You could refer to below code:

    using (SpreadsheetDocument document = SpreadsheetDocument.Open("excel file path", true))
    
    {
    
          WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(document.WorkbookPart.Workbook.Descendants<Sheet>().First().Id);
    
          DrawingsPart drawingsPart = worksheetPart.DrawingsPart;
    
          ChartPart chartPart=drawingsPart.ChartParts.First();
    
          C.ChartSpace chartSpace1 = chartPart.ChartSpace;
    
          C.Chart chart = chartSpace1.GetFirstChild<C.Chart>();                    
    
          C.Legend legend = chart.GetFirstChild<C.Legend>();
    
          C.Layout layout = legend.GetFirstChild<C.Layout>();
    
          C.LegendEntry legendEntry = new C.LegendEntry();
    
          C.Index index = new C.Index() { Val = (UInt32Value)1U };
    
          C.Delete delete = new C.Delete() { Val = true };
    
    
          legendEntry.Append(index);
    
          legendEntry.Append(delete);
    
          legend.InsertBefore(legendEntry, layout);
    
          chartPart.ChartSpace.Save();       
    
          document.Close();
    
    }

    After remove legend entries

    Base on your sample codes, I didn’t find any codes to save your changes, So I think this issue casued by that you didn’t save the data in the DOM tree back to the part.

    For more informatio, click here to refer about ChartSpace.Save Method

    Thursday, November 12, 2015 9:16 AM
  • I was having the same issue when trying to modify XML generated by EPPLUS.

    I found the solution (For me at least) was rather strange.

    I had to make sure the following tags existed. Once i did that the legend entries would be removed.

    c:chartSpace\c:chart\c:plotArea\c:lineChart\c:ser\c:val\c:numRef\c:numCache\c:formatCode

    c:chartSpace\c:chart\c:plotArea\c:lineChart\c:ser\c:val\c:numRef\c:numCache\c:ptCount

    The EPPLUS library only generated:

    c:chartSpace\c:chart\c:plotArea\c:lineChart\c:ser\c:val\c:numRef

    i had to manually add c:numCache\c:formatCode and c:numCache\c:ptCount for each series in each of my linecharts. so it looks something like this:

            
    <c:val>
      <c:numRef>
        <c:f>'Power Good'!$I$19:$I$501</c:f>
        <c:numCache>
          <c:formatCode>General</c:formatCode>
          <c:ptCount val="483" />
        </c:numCache>
      </c:numRef>
    </c:val>

    once these tags were added, the legend entries would be removed.

    Again i was using EPPLUS library so my solution might not be useful

    But ff your still having the issue i hope this helps.

    • Proposed as answer by TDawg82 Monday, November 16, 2015 7:45 PM
    • Marked as answer by dani_s Friday, November 20, 2015 1:48 PM
    Monday, November 16, 2015 7:39 PM
  • I added a call to Save() method on ChartSpace, but it does not work.

    Here is my code:

    var noOfLegendEntriesToExclude = seriesRangeDictionary.Count(x => !x.ShowLegend);
    		
    for (UInt32 i = 0; i < noOfLegendEntriesToExclude; i++)
    {
    	legend.Append(new LegendEntry(
    	new Index() { Val = (UInt32Value)i },
    	new Delete { Val = true }));
    }
    
    legend.Append(
    	new Layout(),
    	new Overlay() { Val = false });
    			
    // Add the chart Legend
    chart.AppendChild(legend);
    chartPart.ChartSpace.Save();

    Previously I add the legends that I want to be seen. When the Excel is generated I see the legends that are supposed to be deleted and the new legend entries I added manually. From some reason it does not delete these entries.

    One other clarification: when I look inside the generated excel file, without opening it in Excel, I see the code to delete the legends is there. But after I execute the file it's gone.

    Any idea why?


    • Edited by dani_s Tuesday, November 17, 2015 9:00 AM
    Tuesday, November 17, 2015 8:27 AM
  • Seems like I'm getting closer to solving the problem. So, the issue is the way I'm building the series..

    LineChartSeries lineChartSeries = lineChart.AppendChild(
                        new LineChartSeries(
                            new Index() { Val = new UInt32Value(i) },
                            new Order() { Val = new UInt32Value(i) },
                            new SeriesText(new NumericValue() { Text = serieName }),
                            new ChartShapeProperties(outline),
                            new Marker(new Symbol() { Val = serie.ShowMarkers ? MarkerStyleValues.Triangle : MarkerStyleValues.None }),
                            new Values() { NumberReference = new NumberReference() { Formula = new Formula(valueRange) } },
                            new CategoryAxisData() { StringReference = new StringReference() { Formula = new Formula(categoryRange) } },
                            new Smooth() { Val = serie.Smooth }));

    Here valueRange is what you enter for  "Series Y Values"(e.g: SheetName!$A$1:$A$35) and categoryRange is the correspondent "Series X Values", which generate the series on the chart. But if I open the excel file and save it without modifying it I see that for each series it generates NumericPoint for each value in Y and X cells. In this version the code to remove the legend works.

    If I play with the excel and open "xl\charts\chart1.xml" and remove <c:numRef> nodes, when opening the excel it will display everything fine, as before. But if I then try from code to delete the legends it will not work.

    Is it mandatory to also create from code NumericPoints for each point on the chart? I might have thousands of points.

    Adding

    NumberingCache = new NumberingCache() { FormatCode = new FormatCode("General"), PointCount = new PointCount() { Val = pointCount }

    to Values when creating the series did the trick.

    Thanks TDawq82 for the idea.   

    • Edited by dani_s Friday, November 20, 2015 1:52 PM
    Friday, November 20, 2015 1:40 PM
  • Excellent answer!
    I had the legend entry delete problem with EPPlus, and this suggestion solved it.
    Thank you so much!


    Wednesday, February 28, 2018 9:01 PM