How to update Series.values in C#?

10y2.2kVisual Basic .NET

I have an issue with the excel , please let me help how can i solve it. Below is the code which is i'm using now , but its not working .

at last side code series.values should update excel file as recently updated , so how can i use it. i'm doing this to increase the chart range.

Excel.Application app = new Excel.Application();

// Excel.Application xlApp;

Excel.Workbook xlWorkBook;

Excel._Worksheet sheet;

Excel.ChartObject chartObj;

Excel._Chart chart;

Excel.Series series;

object useDefault = Type.Missing;

Excel.Workbook book;

Excel.ChartObjects grph;

xlWorkBook = app.Workbooks.Open(@"E:\\test\\DTS-CODE");//Open the work book

sheet = xlWorkBook.Sheets["Trend_NCM"];//Select the sheet the chart is on

char oldformula , newformula;

long i, s;

Excel.Range orng = null;

string[] stmp, sbase;

string cha, rta;

string startingpoint, endpoint;

chart = sheet.ChartObjects("Chart 1").chart;

chart.SetSourceData(sheet.Range["A2:A7"], Excel.XlRowCol.xlRows);

for (s = 1; s < chart.SeriesCollection().count;s++)

{

string sting = "=SERIES(Trend_NCM!$A$3,Trend_NCM!$B$2:$AH$2,Trend_NCM!$B$3:$AH$3,1)";

//series.Values

rta = chart.SeriesCollection(s).Formula;

// foreach(sbase in chart.SeriesCollection().count)

//series = rta.Chart.SeriesCollection();

// series = chart.SeriesCollection(s).Formula;

series= null ;

cha = chart.SeriesCollection(s).Formula;

string[] cha1 = cha.Split(',');

string[] cha2 = cha1[2].Split(':');

startingpoint = cha2[0];

//endpoint = cha2[1];

Range xlrange = null;

Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);

Excel.Range range = sheet.get_Range("A1", last);

int iTotalColumns = sheet.UsedRange.Columns.Count;

// Excel.Range xlRange = sheet.UsedRange;

// xlrange = sheet.get_Range(xlrange.EntireRow.Address, useDefault);

// endpoint = iTotalColumns.ToString();

endpoint = "Trend_NCM!$AH$5";

**series.Values = startingpoint+ "&"+ ":" +"&"+ endpoint;**

}

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

10y ago
Hello @Madhanmohan Devarajan ,
Pleasse check the images and sample code . please let me know how can i solve it.
In the attached image you can see FW28,FW29 ,FW30 which is i have updated by code but FW27,FW26 which is already there in excel and also reflected in graph ,what i need to do to update FW28 in graph . and that should reflect in graph , that is issue now.

// Macro result which i got

ActiveChart.SetSourceData Source:=Range("Trend_NCM!$A$2:$AD$7")
ActiveChart.SetSourceData Source:=Range("Trend_NCM!$A$2:$AE$7")
ActiveChart.SetSourceData Source:=Range("Trend_NCM!$A$2:$AF$7")

//

// c# code
xlWorkBook = app.Workbooks.Open(@"E:\\test\\DTS-CODE");//Open the work book
sheet = xlWorkBook.Sheets["Trend_NCM"];//Select the sheet the chart is on
char oldformula , newformula;
long i, s;
Excel.Range orng = null;
string[] stmp, sbase;
string cha, rta;
string startingpoint, endpoint;
chart = sheet.ChartObjects("Chart 1").chart;
chart.PlotArea.Select();
ChartObjects chartObjs = (ChartObjects)sheet.ChartObjects();
ChartObject chartObj1;
Chart xlChart = sheet.ChartObjects("Chart 1").chart;
Range rg = sheet.get_Range("A2", "AP7");
chart.SetSourceData(sheet.Range["$A$2:$AP$7"], Excel.XlRowCol.xlRows);
chart.SetSourceData(sheet.Range["$A$2:$AP$7"], Excel.XlRowCol.xlColumns);
chart.SetSourceData(rg, Excel.XlRowCol.xlColumns);
Range xValues = sheet.Range["A2", "A7"];
Range values = sheet.Range["A2", "A7"];
SeriesCollection seriesCollection = xlChart.SeriesCollection();
Series series1 = seriesCollection.NewSeries();
series1.XValues = xValues;
series1.Values = values;
this is the code which i'm tring now to update excel file .
0
Madhanmohan Devarajan10y ago
Mallesh,
Please upload your excel file with sample data, so that it will be easy to check what is happening in your case.
0