We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Not able to set chart data range dynamically

Hi,

We are trying to generate stacked column chart using Dynamic legend entries(Series), but we are not able to generate the chart as per our requirement.
In my requirement maximum legends entries(series) should be 18. For set of parameters it might be 1,2,5,10…18 and in X-axis we are showing month names like Jan, Feb,…Dec
In our chart we designed 18 legend entries using name manager formulas.

Ex:-For set of parameters I have received only 6 columns(legends) from the database like Col1,Col2,Col3,Col4,Col5,Col6 … and remaining 12 columns & its values are Null values.

Month Col1 Col2 Col3 Col4 Col5 Col6 …
Jan 10 15 5 10 12 15
Feb 50 15 5 10 20 6
Mar 20 45 5 10 12 10
Apr 70 25 5 10 12 15
May 60 15 5 10 12 25

For the above parameters Report generated successfully and chart is displaying properly with 6 legends but while opening the chart sheet we are getting dialogue box throwing an exception like
" A formula in the worksheet contains one or more invalid references. Verify that your formulas contains a valid path,workbook,range name,and cell reference."
To avoid this exception I replaced the null values with zero’s for remaining 12 columns like the below.

Month Col1 Col2 Col3 Col4 Col5 Col6 …
Jan 10 15 5 10 12 15 0 0 0 0 0 0 0 0 0 0 0 0
Feb 50 15 5 10 20 6 0 0 0 0 0 0 0 0 0 0 0 0
Mar 20 45 5 10 12 10 0 0 0 0 0 0 0 0 0 0 0 0
Apr 70 25 5 10 12 15 0 0 0 0 0 0 0 0 0 0 0 0
May 60 15 5 10 12 25 0 0 0 0 0 0 0 0 0 0 0 0

After replacing null values with zero’s my chart is showing 12 blank legends. Now I am not able to remove the blank legends. Also I have tried to set the chart data range dynamic but It is not working.

So could you please suggest the solution for above chart problem, Currently we are using
we are using Aspose.cells for Java 8.4.0 version.

Regards,
Gattu Kumar.

@gattukumar,

Thanks for providing us details.

Please create your desired chart in MS Excel manually based on your dataset in the worksheet and save the file. Now provide us your file with the steps involved. Also provide us your output file (containing the chart) via Aspose.Cells APIs. Aspose.Cells follows Ms Excel standards and specifications while generating charts, so anything you achieve (to generate the chart) in MS Excel, you may accomplish the same via Aspose.Cells. Moreover, we cannot evaluate or fix any issue based on older version of the product. We only evaluate your task or issue based on the latest APIs set. So, you may try our latest Aspose.Cells for Java version (e.g v18.9) to write your code segment (using latest APIs set) to accomplish the task. Also, if we are to provide the code segment to generate your desired chart, we will use latest version of the product.