I have an excel file with a couple of charts, and I can’t extract some text from those charts. I can’t get legend entries text (“series1”, “series2”, “series3”) from the first sheet, and axis title (with the text “Axis Title”) from the second sheet. I attached the excel file and the corresponding screenshots. Can you tell me is there a way to get the mentioned text through aspose (I couldn’t find this on aspose.cells API documentation)?
Is there a way to check through aspose if legend entries names are taken from the chart’s source table, or they are changed (different than in table)? (In the file I attached, in the first sheet, first chart have legend entries names different than in source table, and the second chart have legend entries names same as in source table)
I am afraid, there is no better way to cope with it but I think if Series.getName() gives you null/empty string then you might think that legend items are changed or not taken from source data table for the chart. So, in that case, you may use Series.getDisplayName(); method to retrieve the display names.
Thanks for the answer. I guess that when Series.getName() string starts with equal sign, then legend items are for sure taken for source data and are unchanged, so that works good for me.
One more question, when I tried to take category axis name from the second sheet’s chart without executing chart1.calculate() command, I get null as a result. With using calculate() method I get the expected text. I tried to get axis titles from many other charts from different excel files and I didn’t have to execute chart.calculate() to get axis titles, I found that case only for this chart. Perhaps you know why I needed to use calculate() method here, is this normal behaviour?
Well, it is always better to use Chart.calculate() method before retrieving axis labels, titles or series data. The method calculates the custom positions of plot area, X/Y axis if the position were automatically assigned.
Well, there may not be any other way to get Category/Value axis labels except you may evaluate/get SeriesCollection.getCategoryData or Series.getValues() etc., area where you will loop through the range and get respective cells value accordingly.
Thanks.
Is there a way to check for legend is it currently visible in Excel file (legend, as other chart items, have visibility state which can be changed in Microsoft Excel)?
I think you may try to use Chart.getShowLegend() method to evaluate if chart’s legend is visible or not, see the following sample code for your reference:
e.g Sample code:
Your answer was helpful as always. I got another problem now, I can’t get the axis title text from pivotchart with chart.getCategoryAxis().getTitle().getText(). Is there any way to get this through aspose if chart type is pivot chart? Excel file and screenshoot are attached. (4th sheet, 1st chart)
Workbook book = new Workbook("Pivot table charts with TITLES(1).xlsx");
Worksheet sheet = book.getWorksheets().get(3);
Chart chart = sheet.getCharts().get(0);
chart.calculate();
System.out.println(chart.getCategoryAxis().getTitle().getText()); //output is null
Thanks for the template file (containing Pivot Table and pivot chart) and screenshot.
After an initial test, I am able to observe the issue as you mentioned by using your sample code with your template file. I found that we cannot get the category axis title text from pivot chart with chart.getCategoryAxis().getTitle().getText() API. I guess since the pivot chart’s source pivot table has external references, so it is giving null value for title’s text of the category axis.
I have logged a ticket with an id “CELLSJAVA-42603” for your issue. We will look into it if this can be done or not.
Once we have an update on it, we will let you know here.
Please try with v18.5.2 for getting legend items using the following code:
e.g Sample code:
for (int k = 0; k < chart1.getLegend().getLegendEntriesLabels().size(); k++)
{
System.out.println(chart1.getLegend().getLegendEntriesLabels().get(k));
}
Good to know that your issue is sorted out by the suggested code segment and new fix/version. Feel free to contact us any time if you need further help or have some other issue or queries,we will be happy to assist you soon.