Display Pie Chart with percentage values only in axis by hiding the % sign using Aspose.Cells for Java

Aspose.Cells Product Family
1

Hi there,

because of limited space, I need a barChart with percentage values to not show the percentage-Sign in the bar labels, but only in the tickLabels of the value axis.
How can I achieve this without having values like 9000% instead of 90% in the value axis?

Thanks a lot,
Christiane

2

Hi,


I am not sure about your needs, please elaborate your requirements. Please create your sample chart with your desired format and give us the steps involved to accomplish this in MS Excel manually. You may also provide some screen shots if it may help a bit. We will check and help you on how to do it via Aspose.Cells APIs.

By the way, you may use setPercentageShown() method to set it to false to not to show the percentage on the data labels.
e.g

//Set the DataLabels in the chart

DataLabels datalabels;

for (int i = 0; i < chart.getNSeries().getCount(); i++)

{

datalabels = chart.getNSeries().get(i).getDataLabels();

//Set the position of DataLabels

datalabels.setPosition(LabelPositionType.INSIDE_BASE);

//Show the category name in the DataLabels

datalabels.setCategoryNameShown(true);

//Show the value in the DataLabels

datalabels.setValueShown(true);

//Not show the percentage in the DataLabels

datalabels.setPercentageShown(false);

//Not show the legend key.

datalabels.setLegendKeyShown(false);

}


Thank you.
3

Hi!

datalabels.setPercentageShown(false); 
is exactly what I’ve been looking for - but it’s not working. It still shows the percentage signs in the chart.

Here’s my sample code:

        final Worksheet dataSheet = this.workbook.getWorksheets().get(0);
        dataSheet.setName(“Data”);

        final Cells cells = dataSheet.getCells();
        Cell cell;

        final Style style = this.workbook.createStyle();
        style.setCustom(“0%”);

        cell = cells.get(0, 1);
        cell.setValue(“Column 1”);
        cell = cells.get(0, 2);
        cell.setValue(“Column 2”);
        cell = cells.get(1, 0);
        cell.setValue(“Row 1”);
        cell = cells.get(1, 1);
        cell.setValue(0.1);
        cell.setStyle(style);
        cell = cells.get(1, 2);
        cell.setValue(0.3);
        cell.setStyle(style);

        final WorksheetCollection worksheets = this.workbook.getWorksheets();
        final Worksheet chartSheet = worksheets.add(“Chart”);
        this.workbook.getWorksheets().setActiveSheetIndex(chartSheet.getIndex());

        final ChartCollection charts = chartSheet.getCharts();
        this.chart = charts.get(charts.add(ChartType.COLUMN, 0, 0, 10, 20));

        final SeriesCollection nSeries = this.chart.getNSeries();
        nSeries.add(“Data!B2:C2”, true);

        Series aSeries = nSeries.get(0);
        aSeries.getDataLabels().setNumberFormat(“0%”);
        aSeries.getDataLabels().setShowValue(true);

        aSeries = nSeries.get(1);
        aSeries.getDataLabels().setNumberFormat(“0%”);
        aSeries.getDataLabels().setShowValue(true);
        aSeries.getDataLabels().setShowPercentage(false);

        this.chart.getValueAxis().getTickLabels().setNumberFormat(“0%”);

        final File file = new File("C:\test.xls);
        fos = new FileOutputStream(file.getAbsoluteFile());
        XlsSaveOptions xlsSaveOptions = new XlsSaveOptions(SaveFormat.EXCEL_97_TO_2003);
        this.workbook.save(fos, xlsSaveOptions);
        fos.flush();

I also attached the resultfile from this code.
Maybe some combination of formatting does not work with the showPercentage(false)?

Thx for your answers,
Christiane
4

Hi,


Well, I have evaluated your scenario and checked your code a bit. For your kind information, this line: “datalabels.setPercentageShown(false);” will only works and available (as an option in MS Excel) for PIE chart types only, this option won’t work for other chart types e.g Column, Bar charts etc. You may confirm this in MS Excel manaully.

So, you cannot eliminate “%” from the value, you may either display complete value (including “percentage” symbol) or hide it. You may also remove the “percentage” number formatting in the source cells range for the chart and don’t apply the numbers formatting for those data labels of the series.

If you still think this is possible in MS Excel, kindly do provide us a sample file with your desired chart whose one of the series’ labels are rendered without percentage symbol, we will check it on how to do it soon. Also gives us the steps involved and MS Excel chart’s options to perform what you are looking for.

Thank you.