Error when trying to add a chart in seperate worksheet

Aspose.Cells Product Family
1

I need to create a scatter chart in a new tab, referencing series in the 1st worksheet but I continue to get an error when creating the new worksheet. Following examples I found in another thread on this forum (https://forum.aspose.com/t/115170) I'm trying to get below to work but I get Object reference not set to an instance of an object. It is failing on the Worksheets.Add(SheetType.Chart); line

datatable dt;
m_oDT = dt;
Workbook m_oWBIMSRpt = new Workbook();
Worksheet m_oIMSDataSheet = m_oWBIMSRpt.Worksheets[0];

m_oIMSDataSheet.Cells.ImportDataTable(m_oDT, true, "A1");

m_oWBIMSRpt.Worksheets.Add(SheetType.Chart);
Charts m_oIMSChartSheet = m_oWBIMSRpt.Worksheets[1].Charts;

Below works but does not suite my need. I need the sheet to be of the type chart for later processing:

m_oWBIMSRpt.Worksheets.Add(SheetType.Worksheet);
Worksheet m_oIMSChartSheet = m_oWBIMSRpt.Worksheets[1];

Thanks in advance.

2

Hi,

Thanks for reporting it.

We will figure out your issue and get back to you soon.

Thank you.

3

Please try this attached fix.

4

Laurence,

Thanks for the fast reply. Now I'm getting "Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index "

this is on the command:

m_oWBIMSRpt.Worksheets.Add(SheetType.Chart);

Thanks,

Rob

5

Hi Rob,

Please make sure that you are using the the latest hotfix (4.1.2.12), which we sent you. I tried the following code using the version and the output is fine without any problem. (Please check the attachment file):

Workbook excel = new Workbook();

Cells cells = excel.Worksheets[0].Cells;

cells["A1"].PutValue("Daily Rainfall");

cells["B1"].PutValue("Particulate");

cells["A2"].PutValue(1.9);

cells["B2"].PutValue(137);

cells["A3"].PutValue(3.6);

cells["B3"].PutValue(128);

cells["A4"].PutValue(4.1);

cells["B4"].PutValue(122);

cells["A5"].PutValue(4.3);

cells["B5"].PutValue(117);

cells["A6"].PutValue(5);

cells["B6"].PutValue(114);

cells["A7"].PutValue(5.4);

cells["B7"].PutValue(114);

cells["A8"].PutValue(5.7);

cells["B8"].PutValue(112);

cells["A9"].PutValue(5.9);

cells["B9"].PutValue(110);

cells["A10"].PutValue(7.3);

cells["B10"].PutValue(104);

excel.Worksheets.Add(SheetType.Chart);

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

int chartIndex = charts.Add(ChartType.Scatter,1,3,25,12);

Chart chart = charts[chartIndex];

chart.MajorGridLines.IsVisible = false;

chart.Legend.Position = LegendPositionType.Top;

chart.Title.Text = "Scatter Chart:Particulate Levels in Rainfall";

chart.Title.TextFont.Color = Color.Black;

chart.Title.TextFont.IsBold = true;

chart.Title.TextFont.Size = 12;

chart.NSeries.Add ("Sheet1!B2:B10",true);

chart.NSeries[0].XValues = "Sheet1!A2:A10";

for ( int i = 0 ; i < chart.NSeries.Count ; i ++)

{

chart.NSeriesIdea [I].Name = "Particulate";

}

chart.CategoryAxis.Title.Text = cells["A1"].Value.ToString();

chart.ValueAxis.Title.Text = cells["B1"].Value.ToString();

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

Thank you.

6

Hi Rob,

I tried the following code and it worked fine.

Workbook excel = new Workbook();

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

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

Could you please create a sample console application to show your problem and post it here? I will check what caused your problem.

7

I was able to get it working. Here are my code bits:

m_oWBIMSRpt = new Workbook();

m_oWBIMSRpt.Worksheets.Add(SheetType.Chart);

Charts m_oIMSChartSheet = m_oWBIMSRpt.Worksheets[1].Charts;

//Create chart

int chartIndex = m_oIMSChartSheet.Add(ChartType.Scatter, 0, 0, 25, 12);

Chart m_oIMSOppChart = m_oIMSChartSheet[chartIndex];

I think it had to do with the m_oIMSChartSheet.Add line. I had "m_oIMSChartSheet.Charts.Add" which didn't work. Why it seemed to be failing on the Add(SheetType.Chart) line I can't say.

Thanks for your quick responses. I will continue to develop to see if it all meets our needs. I will be working on the background fill next to see if the chart background fill can be set to a picture as it can in Excel.

Rob