How to change number format to percentage upto one decimal place

Aspose.Cells Product Family
1

I’m attaching a sample excel file. In the file as shown for the data labels we need the percentage format up to 1 decimal place. How can I set that?
PieChartDecimalFormat.zip (10.7 KB)

2

@VaradS

To change the number format to percentage up to one decimal place in your Excel file using Aspose.Cells, you can modify the existing style for the relevant cells. Here’s how you can do it:

  1. Load your Excel workbook.
  2. Access the named style that you want to modify (for example, “Percent”).
  3. Set the number format to “0.0%” to display the percentage with one decimal place.
  4. Update the style to apply the changes to the specified range.
  5. Save the modified workbook.

Here is a sample code snippet that demonstrates this process:

import com.aspose.cells.Color;
import com.aspose.cells.Style;
import com.aspose.cells.Workbook;
import AsposeCellsExamples.Utils;

public class ModifyPercentageStyle {
    public static void main(String[] args) throws Exception {
        // Load the workbook
        String dataDir = Utils.getSharedDataDir(ModifyPercentageStyle.class) + "TechnicalArticles/";
        Workbook workbook = new Workbook(dataDir + "yourfile.xlsx"); // Replace with your file name

        // Get the named style for percentage
        Style style = workbook.getNamedStyle("Percent");

        // Change the number format to "0.0%" for one decimal place
        style.setNumber(10); // 10 corresponds to the percentage format

        // Optionally, set the font color if needed
        style.getFont().setColor(Color.getRed());

        // Update the style to apply changes
        style.update();

        // Save the modified workbook
        workbook.save(dataDir + "ModifiedPercentageStyle_out.xlsx");
    }
}

Make sure to replace "yourfile.xlsx" with the actual name of your Excel file. This code will modify the percentage format for the specified range in your workbook to show one decimal place.

If you have any further questions or need additional assistance, feel free to ask!

3

@VaradS,

Thanks for the template Excel file containing the chart.

Please see the following sample code snippet (especially the lines in bold) for your reference.

…
ChartCollection charts = worksheet.getCharts();
Chart chart = charts.get(0);

SeriesCollection serieses = chart.getNSeries();
//Get the first data series of the chart
Series series = serieses.get(0);
// Set percentages (display) option on
series.getDataLabels().setShowPercentage(true);
// Set one decimal place number format, you may use “0.00%” for two decimal places
series.getDataLabels().setNumberFormat(“0.0%”);
…

Hope, this helps a bit.

4

Thanks for you response @amjad.sahi ,

Based on the percentage calculated by MS Excel we need to set the number format.
For Ex. If the value is less than 1% then we need to set number format as “0.000%” else we need to set “0.0%”. Is there any way we can achieve this in excel?
Could you please let us know if there is a way we can extract the actual percentage number from the data labels and perform the above operations?

5

@VaradS
Please set number format as
series.getDataLabels().setNumberFormat(“[>0.01]0.0%;0.000%”);

6

Thanks for the quick response @simon.zhao . It helped.

7

@VaradS
You are welcome!