Multiple charts on a chart sheet

Aspose.Cells Product Family
1

I have an excel template in which I have 2 charts on a Chart sheet. I am not able to access the second chart using the Aspose.Cells library. Please let me know how can I access the seeocnd chart object. I am attaching the template for refernce.

2

Hi,

Thanks for sharing your template excel file.

Well, we have checked your charts in "BarCascade" chartype sheet. Actually the other chart in the sheet is Shape type, it is in the Chart.Shapes Attribute.

Following sample code works fine, I can get the second chart using Chart.Shapes attribute.

Sample code:

Workbook workbook = new Workbook();

workbook.Open("f:\\test\\ChartsTemplate.xls", FileFormatType.Default);

Worksheet sheet = workbook.Worksheets["BarCascade"];

Charts charts = sheet.Charts;

MessageBox.Show(charts[0].Name);

MessageBox.Show(charts[0].Shapes[0].Name);

Thank you.

3

Thanks for quick response.

The following line gives error -

chart = workbook.Worksheet[sheetname].Charts[0].Shapes[0] as Chart

Is there a way to convert this shape object to chart, since I need to set the source data and other properties.

4

Hi,

Thank you for considering Aspose.

Please try to cast Shape to ChartShape & use ChartShape.Chart to access the chart object as per your requirement. Please see the following sample code:

Workbook workbook = new Workbook();

string infn = "C:\\ChartsTemplate.xls";

workbook.Open(infn);

Worksheet sheet = workbook.Worksheets["BarCascade"];

Charts charts = sheet.Charts;

ChartShape chartShape = (ChartShape)charts[0].Shapes[0];

Chart chart2 = (Chart)chartShape.Chart;

Thank You & Best Regards,

5

How can I insert more than one chart into a single worksheet ?
Thank you.

6

Hi,

Thanks for your posting and considering Aspose.Cells.

Please use Worksheet.Charts.Add() method to add as many charts in your worksheets as you want.

Please see the following documentation article that explains how to create/add a chart inside the worksheet.

(Create and Manage Chart|Documentation)

For example, see the following code, it adds a Column chart in Sheet2 of the workbook. I have attached the output Excel file generated by the code and screenshot showing the chart for your reference.

C#


//Instantiating a Workbook object

Workbook workbook = new Workbook();


//Adding a new worksheet to the Excel object

int sheetIndex = workbook.Worksheets.Add();


//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[sheetIndex];


//Adding a sample value to “A1” cell

worksheet.Cells[“A1”].PutValue(50);


//Adding a sample value to “A2” cell

worksheet.Cells[“A2”].PutValue(100);


//Adding a sample value to “A3” cell

worksheet.Cells[“A3”].PutValue(150);


//Adding a sample value to “B1” cell

worksheet.Cells[“B1”].PutValue(4);


//Adding a sample value to “B2” cell

worksheet.Cells[“B2”].PutValue(20);


//Adding a sample value to “B3” cell

worksheet.Cells[“B3”].PutValue(50);


//Adding a chart to the worksheet

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 5, 0, 15, 5);


//Accessing the instance of the newly added chart

Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];


//Adding SeriesCollection (chart data source) to the chart ranging from “A1” cell to “B3”

chart.NSeries.Add(“A1:B3”, true);


//Saving the Excel file

workbook.Save(“book1.xlsx”);

7

Thank you for your quick response, but I already tried that and the
excel file which should contain both charts appeared as corrupted. As
example see below:

//dochart
asposeXL.Worksheet newSheet;
Chart myChart1;
Chart myChart2;
if (myBook.Worksheets[0].Name == “Chart”)
{
newSheet = myBook.Worksheets[0];
}
else
{
newSheet = myBook.Worksheets.Insert(0, asposeXL.SheetType.Worksheet, “Chart”);
}

myChart1 = newSheet.Charts[newSheet.Charts.Add(ChartType.PieExploded, 5, 0, 25, 10)];
myChart1.NSeries.Add(“A1:A50”, true);

myChart2 = newSheet.Charts[newSheet.Charts.Add(ChartType.PieExploded, 20, 0, 15, 5)];
myChart2.NSeries.Add(“A1:A50”, true);

8

Hi Ionut,


Thank you for writing back.

Please execute the below provided code against the latest version of Aspose.Cells for .NET 8.2.1, and feed us back with your results.

C#

//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];
//Adding a sample value to “A1” cell
worksheet.Cells[“A1”].PutValue(50);
//Adding a sample value to “A2” cell
worksheet.Cells[“A2”].PutValue(100);
//Adding a sample value to “A3” cell
worksheet.Cells[“A3”].PutValue(150);
//Adding a sample value to “B1” cell
worksheet.Cells[“B1”].PutValue(4);
//Adding a sample value to “B2” cell
worksheet.Cells[“B2”].PutValue(20);
//Adding a sample value to “B3” cell
worksheet.Cells[“B3”].PutValue(50);

var myChart1 = worksheet.Charts[worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.PieExploded, 5, 0, 15, 5)];
myChart1.NSeries.Add(“A1:b3”, true);

var myChart2 = worksheet.Charts[worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.PieExploded, 15, 0, 25, 5)];
myChart2.NSeries.Add(“A1:b3”, true);

//Saving the Excel file
workbook.Save(myDir + “book1.xlsx”);


In case the problem persists with v8.2.1, please provide us a console application along with your template spreadsheet (if any).
9

It worked! Thank you!

10

Hi,


Good to know that your issue is resolved now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.