I have the following function call in our VB.NET application:
ChartObject.Chart.ChartWizard(Source:= “A1:C422;AQ1:AR422”, PlotBy:=2, CategoryLabels:=1, SeriesLabels:=1)
How can I replicate this functionality with Aspose.Cells?
I tried already
- Chart.SetChartDataRange(“A1:C422;AQ1:AR422”, True)
- Chart.ChartObject.SetInputRange(“A1:C422;AQ1:AR422”, False, False)
Both function calls throw an error with the second one complaining about the “;” being an array seperator. If I restrict the range to “A1:C422”, the chart still displays the data from the range “AQ1:AR422”.
Any help would be really appreciated since I’m currently evaluating Aspose.Cells as an replacement for Excel.
@casswarry0,
Could you please share your template file and expected output for our analysis that which chart you are trying to insert. You can also refer to Chart section documents.
You can find the stripped down template here: Aspose.xslx
In the Workbook in the Worksheet “In-Out” you find the Chart “ReflWerte” at cell A614. On this Chart the function ChartWizard is called to restrict the number of lines displayed to only “Standard”, “Trial 1”, “Standard(2)”, “Trial(2)-1”.
So I’m not trying to insert a new chart, I only want do edit an existing chart (i.e. set a new data source).
You can restrict the range to “A1:C422” with below code, it will update the range as in the attached screenshot. Range.PNG (37.2 KB)
Workbook workbook = new Workbook("Aspose.xlsx");
Worksheet worksheet = workbook.Worksheets["In-Out"];
Charts.Chart chart = worksheet.Charts[0];
chart.SetChartDataRange("A1:C422", true);
chart.Calculate();
workbook.Save("RangeUpdate.xlsx");
Let us know your feedback.
Thank you for your quick response. I tried Chart.SetChartDataRange already and it has several problems:
- with two ranges combined (e.g. A1:C422;AQ1:AR422) it throws an error: ‘Invalid formula:“A1:C422;AQ1:AR422”’
- with only one range (e.g. A1:C422) it works but the chart is displayed wrongly (e.g. data is only displayed from 300…400 instead of from 330…740)
And we definitely need two ranges combined.
@casswarry0,
We have logged the issue as “CELLSNET-47780” in our database for investigations. Once we will have some news for you, we will update you in this topic.
Could you try using “A1:C422,AQ1:AR422” range instead (use “,” instead of “;”) and let us know your feedback.
I tried Chart.SetChartDataRange “A1:C422,AQ1:AR422” (with ",” instead of “;”) and it doesn’t throw an error but it also seems to ignore the second range AQ1:AR422. It displays the same chart like with the range A1:C422 only.
One other problem. The column “A” is the scale for the X-Axis and the row “1” is the name of the corresponding line in the chart. When I make the range “A1:C422” it displays the line names “Version 5.00.009”, “Standard” and “Trial 1”. That is also different behavior compared to Excel which displays only the line names “Standard” and “Trial 1” and also displays no line for column “A”.
@casswarry0,
Thank you for the feedback. We have recorded it along with the ticket for our reference and will provide our feedback soon.
@casswarry0,
This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
@casswarry0,
Please try our latest version/fix: Aspose.Cells for .NET v20.12.1 (attached)
Your issue should be fixed in it.
Let us know your feedback.
Aspose.Cells20.12.1 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells20.12.1 For .Net4.0.Zip (5.5 MB)
Aspose.Cells20.12.1 For .NetStandard20.Zip (5.4 MB)
I’m sorry, but I can’t confirm that it fixed.
Here you can see how the chart should look like with 22 graphs enabled and only 6 available (look how the graphs are named)
Before-SetChartDataRange.png (45.8 KB)
The call SetChartDataRange(“A1:C422;AQ1:AR422”, False) - the ranges separated with “;” - still fails while the call SetChartDataRange(“A1:C422,AQ1:AR422”, False) - the ranges separated with “,” - succeeds but the resulting chart is wrong.
After-SetChartDataRange-A1.png (21.5 KB)
The call SetChartDataRange(“B2:C422,AQ2:AR422”, False) - only the data range specified but not the x and y scale nameings - looks better but still is completely wrong.
After-SetChartDataRange-B2.png (21.3 KB)
I’m sorry for the bad news but this behavior is pretty much unusable.
@casswarry0,
Thank you for providing more information. We will analyze it and provide our feedback accordingly.
The issues you have found earlier (filed as CELLSNET-47780) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao
I’m sorry, but I still can’t confirm that it fixed. Still the same behavior like in the above screen shot After-SetChartDataRange-A1.png (21.5 KB).
@casswarry0,
We have noted your comments and will share our feedback after detailed analysis.
It looks much better now. But after Chart.SetChartDataRange(“A1:C422,AQ1:AR422”, True) the chart lines are far to thick. See the original (without SetChartDataRange): Original.png (44.7 KB) and the limited (with SetChartDataRange): Range.png (26.5 KB)
@casswarry0,
Thank you for the feedback. We will analyze it and share our feedback soon.