Chart created as a new sheet

Aspose.Cells Product Family
1

I would like to create a new chart as a sheet (similar to Excel’s chart wizard option “As Sheet”).

Charts charts = excel.Worksheets[excel.Worksheets.Add(SheetType.Worksheet)].Charts;
int chartIndex = charts.Add(ChartType.ScatterConnectedByLinesWithDataMarker, 0, 0, 40, 20);
Chart oChart = charts[chartIndex];

This uses coordinates for the four corners but I want the chart to fill the page as a complete sheet.

How would I do this?
2

I am asking the same as this posting:
Re: Putting a chart on a new chart worksheet
https://forum.aspose.com/t/118812

3

This feature is supported. Please download the latest version and try the following sample code:

Excel excel = new Excel();

excel.Worksheets.Add(SheetType.Chart);

Cells cells = excel.Worksheets[0].Cells;
cells["c2"].PutValue(5000);
cells["c3"].PutValue(3000);
cells["c4"].PutValue(4000);
cells["c5"].PutValue(5000);
cells["c6"].PutValue(6000);



Charts charts = excel.Worksheets[1].Charts;


int chartIndex = charts.Add(ChartType.Column, 10,10,20,20);
Chart chart = charts[chartIndex];
chart.NSeries.Add("Sheet1!C2:C6", true);

4

I ran this code:
Excel excel = new Excel();
excel.Worksheets.Add(SheetType.Chart);
Cells cells = excel.Worksheets[0].Cells;
cells[“c2”].PutValue(5000);
cells[“c3”].PutValue(3000);
cells[“c4”].PutValue(4000);
cells[“c5”].PutValue(5000);
cells[“c6”].PutValue(6000);
Charts charts = excel.Worksheets[0].Charts;
int chartIndex = charts.Add(ChartType.Column, 10,10,20,20);
Chart chart = charts[chartIndex];
chart.NSeries.Add(“C2:C6”, true);
excel.Save(“book1.xls”, SaveType.OpenInBrowser, FileFormatType.Default, this.Response);

I got an exception, "Object reference not set to an instance of an object."
If I change chart type from Chart to Worksheet, everything works fine.

I have version 3.2.2.7 which fixed XValues property for all XYscatter type charts, thank you!

5

I got the exception because I was using data entered into the same sheet so please ignore my previous posting as it works okay for chart type.
thank you!

6

When you create a new chart sheet, you should put chart setting in it.

My code is to add a new chart sheet, while your code is to add a chart in a normal worksheet. Please try my sample code.

If you want to add a chart in a normal worksheet, please remove this line of code:

excel.Worksheets.Add(SheetType.Chart);

7

@tedc,
Aspose.Cells has replaced Aspose.Excel that is no more under active development now. This new product contains lot of advanced features to generate charts supported by different latest versions of MS Excel. Here is an example that creates a chart on a separate chart sheet.

    // For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
    // Instantiating a Workbook object
    Workbook workbook = new Workbook();

    Worksheet dataWorksheet = workbook.Worksheets[0];
    // Adding a new worksheet to the Workbook object
    int sheetIndex = workbook.Worksheets.Add(SheetType.Chart);

    // Obtaining the reference of the newly added worksheet by passing its sheet index
    Worksheet chartWorksheet = workbook.Worksheets[sheetIndex];

    // Adding sample values to cells
    dataWorksheet.Cells["A1"].PutValue(50);
    dataWorksheet.Cells["A2"].PutValue(100);
    dataWorksheet.Cells["A3"].PutValue(150);
    dataWorksheet.Cells["A4"].PutValue(110);
    dataWorksheet.Cells["B1"].PutValue(260);
    dataWorksheet.Cells["B2"].PutValue(12);
    dataWorksheet.Cells["B3"].PutValue(50);
    dataWorksheet.Cells["B4"].PutValue(100);

    // Adding a chart to the worksheet
    int chartIndex = chartWorksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 5, 0, 15, 5);

    // Accessing the instance of the newly added chart
    Aspose.Cells.Charts.Chart chart = chartWorksheet.Charts[chartIndex];

    // Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B4"
    chart.NSeries.Add("='Sheet1'!A1:'Sheet1'!B4", true);

    // Setting the chart type of 2nd NSeries to display as line chart
    chart.NSeries[1].Type = Aspose.Cells.Charts.ChartType.Line;

    // Saving the Excel file
    workbook.Save("output.xls");
}

You may refer to the following article for more information about charts:
Charts

Download the latest trial version here:
Aspose.Cells for .NET (Latest Version)

A complete runnable solution is available here for testing the product features without writing any code.