Adding, deleting, or modifing chart series

Question

0.00/5 (No votes)

See more:

I have a chart with three series on the first sheet, and I want to delete and then add a series, or maybe I can just modify the series data values if that is possible.

As each additional month comes, the series will grow.

The series are the months, the number of households served and the total number of people in the households.

The Data is collected from columns b,c, and d on the sheet. The rows it uses grow with each month.

I have been fighting this for three days and have not found much help on the net.

What I have tried:

 //var chart = theWorkSheet.ChartObjects();
 //.SeriesCollection() = sheets.get_Item(1) as Excel.Chart;
var chart = sheets.get_Item(1) as Excel.Chart;
//Collection<string> chart = new Collection<string>();

//Excel.SeriesCollection serColl = chart.SeriesCollection();
//Excel.Series ser = serColl.Item(1);
//ser.Delete();

chart.SeriesCollection("Series 1").Clear();
//chart.Remove("Series1");
//chart.SeriesCollection().Item(1).Remove();
//chart.SeriesCollection(2).Delete();

Posted 4-Aug-22 14:57pm

PaulaJoannAllen

Updated 7-Aug-22 13:21pm

Add a Solution

Comments

[no name] 5-Aug-22 11:45am

Is the point "a chart" or is it EXCEL charts in particular. If it's Excel, tag it as such, because there are better ways to work with c# and "charts".

https://www.c-sharpcorner.com/UploadFile/1e050f/chart-control-in-windows-form-application/

PaulaJoannAllen 5-Aug-22 12:49pm

It's an Excel Histogram chart with a monthly series for the X axis and persons and households for the second and third series.

2 solutions

Solution 2

I decided to just do a workbook copy; that works fine.

Posted 7-Aug-22 13:21pm

PaulaJoannAllen

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

0x01AA · Accepted Answer · 2022-08-05T10:13:00

Warning: A really ugly trial implementation. But I hope it helps and you can see how one can update an existing chart series. It was the first time for me to work with excel chart series.

Prerequirements:
- It exists a Sheet with name 'Sheet1'
- It exists a Chart in 'Sheet1' which has already a series

TODO:
- Extend it for your 3 series

With this (ugly) code I was able to update the series

private void buttonExcelChart_Click(object sender, EventArgs e)
{
    // Excel Application
    Microsoft.Office.Interop.Excel.Application _excel = new _Excel.Application();

    // Open workbook
    Workbook workbook = _excel.Workbooks.Open(@"c:\temp\cp.excel\ExcelChart.xlsx");

    // Sheet with data
    Worksheet worksheet = workbook.Worksheets["Sheet1"];

    // The chart
    Microsoft.Office.Interop.Excel.Chart chart = worksheet.ChartObjects(1).Chart;

    // Series 1
    Microsoft.Office.Interop.Excel.Series series1= (Microsoft.Office.Interop.Excel.Series)chart.SeriesCollection(1);

    // Select values for Series 1

    /* Fix for first trial
    series1.XValues = worksheet.get_Range("A2", "A6");
    series1.Values = worksheet.get_Range("B2", "B6");
    */

    // Trial to make selection more 'dynamic'
    Microsoft.Office.Interop.Excel.Range startX = worksheet.get_Range("A2");
    Microsoft.Office.Interop.Excel.Range rangeX = worksheet.get_Range(startX, startX.End[XlDirection.xlDown]);

    Microsoft.Office.Interop.Excel.Range startY = worksheet.get_Range("B2");
    Microsoft.Office.Interop.Excel.Range rangeY = worksheet.get_Range(startY, startY.End[XlDirection.xlDown]);

    // Series 1 values
    series1.XValues = rangeX;
    series1.Values = rangeY;

    // Save the workbook
    workbook.Save();
}

Good luck ;)