edp33
April 26, 2006, 1:39am
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
To change data source of an existing chart in an Excel file, please try:
sheet.DesignCharts[0].NSeries.Add("A1:A4", true);
edp33
April 26, 2006, 2:39am
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");
What happens if you change your code to:
excel.Worksheets[0].DesignCharts[0].NSeries.Add("Sheet1!B1:E1", true);
edp33
April 27, 2006, 1:44am
5
Hmmz, still no effect. The datasource retains the settings in the ppt file.
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");
edp33
April 27, 2006, 4:45am
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