Hi,
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)?
ChartFiles.zip (97.2 KB)
Regards,
Zeljko
Thanks for the template file and screenshots.
Please try the following sample code with latest version/fix: Aspose.Cells for Java v18.3.x:
e.g
Sample code:
Workbook book = new Workbook("f:\\files\\charts.xlsx");
Worksheet sheet = book.getWorksheets().get(0);
Chart chart = sheet.getCharts().get(0);
chart.calculate();
SeriesCollection seriesCollection = chart.getNSeries();
for (int j = 0; j < seriesCollection.getCount(); j++) {
Series series = seriesCollection.get(j);
String displayname = series.getDisplayName();
System.out.println(displayname);
}
//Second Sheet Chart.
Worksheet sheet1 = book.getWorksheets().get(1);
Chart chart1 = sheet1.getCharts().get(0);
chart1.calculate();
System.out.println(chart1.getCategoryAxis().getTitle().getText());
Hope, this helps a bit.
Thanks for your answer, it was very helpful.
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.
Is there a way to get axis data labels directly from chart(not from the source table)? Corresponding screenshot is attached.
Capture.zip (13.3 KB)
Thanks for the screenshot.
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)?
Capture.zip (15.8 KB)
Thanks for the the screenshot.
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:
Workbook workbook = new Workbook ("f:\\files\\Bkchart1.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
System.out.println(worksheet.getCharts().get(0).getShowLegend());
Hope, this helps a bit.
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
100.zip (106.0 KB)
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.
Could you try the following code to get axis’s labels:
e.g
Sample code:
Chart chart1 = sheet.getCharts().get(0);
chart1.calculate();
for (int k = 0; k < chart1.getCategoryAxis().getAxisLabels().size(); k++)
{
System.out.println(chart1.getCategoryAxis().getAxisLabels().get(k));
}
Let us know your feedback.
Please try our latest version/fix: Aspose.Cells for Java v18.5.2:
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.