Obtaining a chart data source

#1

Hi,

I have read the documentation on how to set a chart source range on a freshly created chart in excel using the .Nseries method. However I am clueless on how to obtain and modify this data source range from an existing worksheet where the chart position is unknown. This is often the case when importing a chart and dataset from powerpoint as an OLE Excel object

Any ideas please?

Thanks

James

#2

To change data source of an existing chart in an Excel file, please try:

sheet.DesignCharts[0].NSeries.Add("A1:A4", true);

#3

Hi Laurence

Thanks for the fast reply. I am still having problems, i've enclosed the code segment below and attached the powerpoint template. If you could spare the time to see where I am going wrong it would really be appreciated.

Thanks

James

// open file

string fileName = MapPath(".") + \\1hd,1x3dpie.ppt;

Presentation pres = new Presentation(fileName);

OleObjectFrame shape = (OleObjectFrame)pres.GetSlideByPosition(1).Shapes[1];

byte[] xlsData = shape.ObjectData;

MemoryStream stream1 = new MemoryStream(xlsData);

Excel excel = new Excel();

excel.Open(stream1);

// edit chart and dataset

excel.Worksheets[1].Cells["B1"].PutValue(ChartData[0]);

// etc etc works fine here

excel.Worksheets[0].DesignCharts[0].NSeries.Add("B1:E1", true);

// chart is on 1st worksheet in the ppt template, datasource is on 2nd.

// ** Outputs Invalid chart data source when designcharts.nseries is executed **

// Save changes

MemoryStream stream2 = new MemoryStream();

excel.Save(stream2, FileFormatType.Default);

shape.ObjectData = stream2.ToArray();

pres.Write(MapPath(".") + "\\pres_new.ppt");

#4

What happens if you change your code to:

excel.Worksheets[0].DesignCharts[0].NSeries.Add("Sheet1!B1:E1", true);

#5

Hmmz, still no effect. The datasource retains the settings in the ppt file.


#6

Please try the following sample code with the attached template file:

1. -------------

Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("d:\\aspose.custom.lic");


Excel excel = new Excel();

excel.Open("d:\\test\\book9.xls");

excel.Worksheets[0].DesignCharts[0].NSeries.Clear();
excel.Worksheets[0].DesignCharts[0].NSeries.Add("Sheet1!B3:E3", false);

for(int i = 1; i < 5; i ++)
{
excel.Worksheets[1].Cells[ 2, i].PutValue(i);
}


excel.Save("d:\\test\\abc.xls");

2. -------------

Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("d:\\aspose.custom.lic");


Excel excel = new Excel();

excel.Open("d:\\test\\book9.xls");

for(int i = 1; i < 5; i ++)
{
excel.Worksheets[1].Cells[ 1, i].PutValue(i);
}


excel.Save("d:\\test\\abc.xls");

#7

Thanks very much for your help Laurence, that did the trick :)

It seems NSeries.Clear() was required before any modifications could be made with NSeries.Add.

James