Free Support Forum - aspose.com

Creating a worksheet using ChartSheet

I’m trying to create a chart that is on it’s own worksheet. So far my attempts have been unsuccessful. What I’ve done so far is create the workbook, add a worksheet, populate worksheet with data. This all works.

Now the next thing I do is create a worksheet with the ChartSheet type, then add a chart to this worksheet, the datasource is set to the first sheet using the format: =FirstSheet!$A$1:$A$13.

Then I attempt to save the workbook, which throws an exception. I’ve skipped everything having to do with the chart, including the creation of the second worksheet, and the file saves and opens just fine. I’ve gone to the adding of the second worksheet and tried to save it and an error is thrown. I’m not sure if this is because the second sheet needs to have a chart added to it first or not.

Now I’m curious as to whether there is a specific way to add a worksheet that uses ChartSheet or is there possible a value that I’m not setting that needs to be set. The values that I’m setting are: the chart title, the category title, the value title, the legend is showing, and NSeries has a value added. Everything else is left to what ever it defaults to, if anything.

Hi,

Please see the code below, it adds a chart in chart sheet and data is another sheet. Please also see the output xlsx file and the screenshot.

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);



int chartSheetIndex = workbook.Worksheets.Add(SheetType.Chart);

Worksheet chartSheet = workbook.Worksheets[chartSheetIndex];


//Adding a chart to the worksheet

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


//Accessing the instance of the newly added chart

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


String st = worksheet.Name + “!A1:B3”;

chart.NSeries.Add(st, true);


//Saving the Excel file

workbook.Save(“F:\Shak-Data-RW\Downloads\chartSheet.xlsx”, SaveFormat.Xlsx);

Screenshot: