Chart range not working

[morees]

Hi there



if I use the following code



string tmp = “NS3P per Visit Hour Data!B” + (iRangeStart + 1) + “:N” + (iRangeCnt + iRangeStart);



// — the same as =‘NS3P per Visit Hour Data’!$B$3:$N$8



excel.Worksheets[“NS3P per Visit Hour Charts”].DesignCharts[iChartCnt].NSeries.Add(tmp, false);



the chart does not update correctly.



Attached is an example of what the chart looks like after export.



Regards

morne

[morees]

The ‘Chem’ series is repeating, and it is not picking up the rest.



Is there another way of updating a charts range?



Regards

Me

[Laurence]

Are all charts created manually and updated at run time? If yes, please add a line of code before changing the source data:

excel.Worksheets["NS3P per Visit Hour Charts"].DesignCharts[iChartCnt].NSeries.Clear();

[morees]

Hi



Yes, all charts are created manually and I have the …NSeries.Clear() line before I start changing the source data.



When I’m adding data to the Series, do I only add the data. (NS3P per Visit Hour Charts!C4:N8)



If so, where do I specify the Series names to be used?



Regards

[Laurence]

Could you please post all of you sample code here?

[Laurence]

And you template file, please.

[morees]

yea sure…



this is my code for the generation of the file.



if (dataTable1.Rows.Count > 0)

{

//Range range = excel.Worksheets[“NS3PperHrData”].Cells.CreateRange(1, 0, dataTable1.Rows.Count + 1, 28);

///range.Name = “PerVisitHour”;



sCurBC = (string) dataTable1.Rows[0][“business_centre”];



// — write the detail — //

for (int i = 0; i [“business_centre”];

if (sPrevBC != sCurBC && i != 0)

{

excel.Worksheets[“NS3P per Visit Hour Charts”].DesignCharts[iChartCnt].NSeries.Clear();



tmp = “NS3P per Visit Hour Data!C” + (iRangeStart + 2) + “:N” + (iRangeStart + iRangeCnt);

excel.Worksheets[“NS3P per Visit Hour Charts”].DesignCharts[iChartCnt].NSeries.Add(tmp, false);



tmp = “NS3P per Visit Hour Data!C” + (iRangeStart + 1) + “:N” + (iRangeStart + 1);

excel.Worksheets[“NS3P per Visit Hour Charts”].DesignCharts[iChartCnt].NSeries.CategoryData = tmp;



iChartCnt += 1;



// — Write new Header — //

iStartRow += 1;

iStartRow += 1;

cells.CopyRow(cells, 2, iStartRow);

iStartRow += 1;



iRangeStart = iStartRow - 1;

iRangeCnt = 1;

}





cells[iStartRow, 0].PutValue((string)dataTable1.Rows[“business_centre”]);

cells[iStartRow, 1].PutValue(dataTable1.Rows[“bt_marketinggroup”]);



dSales = Convert.ToDouble(dataTable1.Rows[“bJanuary_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“JanHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 2].PutValue(0); }

else { cells[iStartRow, 2].PutValue(dPerVHr); }



dSales = Convert.ToDouble(dataTable1.Rows[“bFebruary_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“FebHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 3].PutValue(0); }

else { cells[iStartRow, 3].PutValue(dPerVHr); }





dSales = Convert.ToDouble(dataTable1.Rows[“bMarch_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“MarHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 4].PutValue(0); }

else { cells[iStartRow, 4].PutValue(dPerVHr); }





dSales = Convert.ToDouble(dataTable1.Rows[“bApril_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“AprHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 5].PutValue(0); }

else { cells[iStartRow, 5].PutValue(dPerVHr); }





dSales = Convert.ToDouble(dataTable1.Rows[“bMay_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“MayHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 6].PutValue(0); }

else { cells[iStartRow, 6].PutValue(dPerVHr); }





dSales = Convert.ToDouble(dataTable1.Rows[“bJune_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“JunHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 7].PutValue(0); }

else { cells[iStartRow, 7].PutValue(dPerVHr); }





dSales = Convert.ToDouble(dataTable1.Rows[“bJuly_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“JulHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 8].PutValue(0); }

else { cells[iStartRow, 8].PutValue(dPerVHr); }





dSales = Convert.ToDouble(dataTable1.Rows[“bAugust_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“AugHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 9].PutValue(0); }

else { cells[iStartRow, 9].PutValue(dPerVHr); }





dSales = Convert.ToDouble(dataTable1.Rows[“bSeptember_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“SepHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 10].PutValue(0); }

else { cells[iStartRow, 10].PutValue(dPerVHr); }





dSales = Convert.ToDouble(dataTable1.Rows[“bOctober_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“OctHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 11].PutValue(0); }

else { cells[iStartRow, 11].PutValue(dPerVHr); }





dSales = Convert.ToDouble(dataTable1.Rows[“bNovember_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“NovHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 12].PutValue(0); }

else { cells[iStartRow, 12].PutValue(dPerVHr); }





dSales = Convert.ToDouble(dataTable1.Rows[“bDecember_CYV”]);

dHrs = Convert.ToDouble(dataTable1.Rows[“DecHrs”]);

dPerVHr = dSales / dHrs;

if (dHrs == 0.00)

{ cells[iStartRow, 13].PutValue(0); }

else { cells[iStartRow, 13].PutValue(dPerVHr); }





iStartRow += 1;

iRangeCnt += 1;

sPrevBC = (string) dataTable1.Rows[“business_centre”];



}



tmp = “NS3P per Visit Hour Data!C” + (iRangeStart + 2) + “:N” + (iRangeStart + iRangeCnt);

excel.Worksheets[“NS3P per Visit Hour Charts”].DesignCharts[iChartCnt].NSeries.Add(tmp, false);



excel.Worksheets[“NS3P per Visit Hour Charts”].DesignCharts[iChartCnt].NSeries[0].Name = “A”;

excel.Worksheets[“NS3P per Visit Hour Charts”].DesignCharts[iChartCnt].NSeries[1].Name = “2”;



tmp = “NS3P per Visit Hour Data!C” + (iRangeStart + 1) + “:N” + (iRangeStart + 1);

excel.Worksheets[“NS3P per Visit Hour Charts”].DesignCharts[iChartCnt].NSeries.CategoryData = tmp;



}





hope this helps

Regards

[Laurence]

Changing charts in template files has some limitations: the number of series should be same as series in template file. I think you may have to create the chart totally with code if the series data is dynamic.

[morees]

To me this should have been dynamic…If you can set a different source data range, one should be able to set a new series range as well.



Can’t I set the names of the series with something like this or something similar (btw…this also just brings back the ‘Chem’ series)



for (int aa = 0; aa < itmp; aa++)

{

tmp = “NS3P per Visit Hour Data!B” + (iRangeStart + 2 + aa) + “”;

excel.Worksheets[“NS3P per Visit Hour Charts”].DesignCharts[iChartCnt].NSeries[aa].Name = tmp;

}







regards

[Laurence]

I think you have to create these charts totally with code. Generally these charts are not complex and creating at run time won’t take much more effort. Please check Aspose.Cells.

[morees]

Cool,



another thing…

I’m still using Aspose.Excel, do you have a document / link where a can get the steps to update to Aspose.Cells and what about our lisence file. Does that need to be upgraded as well.



Regards

[Laurence]

Please check the expiration date of your license file. If you want to use a version of Aspose.Cells which is released before the expiration date, you don't need to change the license file. Otherwise, you have to renew the subscription.

To update to Aspose.Cells, please check http://www.aspose.com/Wiki/default.aspx/Aspose.Purchase/HowToUpdateProductName.html. And since v3.9, class Excel is changed to class Workbook and class ExcelDesigner is changed to WorkbookDesigner.