How to set Custom Number Format for Y Axis Value of Excel Chart using Java

Aspose.Cells Product Family
, ,
1

Hi Aspose Team,

I’m trying to set custom number format options for y axis value (using axis options), i.e., the Category as custom and change the Type using Aspose.Cells for Java API.
Please find attached screenshot below for your reference.
screenshot-newtab-2021.10.19-18_54_13.png (19.6 KB)

Could you please help me in achieving it?

Thanks,
Sri

2

@SriG,

Aspose.Cells does support to set custom number format for x or y axis value of the chart in Excel XLSX files. You can use TickLabels.setNumberFormat attribute to set to your desired custom number formatting string for your needs. See the steps details with sample code for your reference.

Steps to set Custom Number Format for Y Axis value using Java

  1. Add reference to maven repos. Aspose.Cells library in the project
  2. Import Workbook, Worksheet and other required classes in your program or import all the classes from com.aspose.cells package
  3. Fill data (for chart data source) into the newly added worksheet in the workbook
  4. Create a chart for example, a line chart using relevant APIs
  5. Set chart data series and category data
  6. Set custom number format for y axis value tick labels
  7. Save the workbook containing the chart with data

Sample code to set Custom Number Format for Y Axis value using Java

....
import com.aspose.cells.*;
....

        //Instantiate a new Workbook
        Workbook workbook = new Workbook();
        //Remove the existing (default) worksheet
        workbook.getWorksheets().clear();

        //Add a new sheet to the workbook
        Worksheet worksheet = workbook.getWorksheets().add("MySheet1");

        //Instantiate Random object
        Random rnd = new Random();

        final int mNum = 100 * 12 * 31;

        //Fill the two columns with some values
        int valNumber = 5;
        for (int i = 0; i < valNumber; i++)
        {

            worksheet.getCells().get(i, 0).putValue(LocalDate.now().minusDays(new Random().nextInt(mNum)));
            worksheet.getCells().get(i, 1).putValue(rnd.nextInt(99999999) + 9999999);
        }

        //Add a line chart to the sheet
        Chart chart = worksheet.getCharts().get(worksheet.getCharts().add(ChartType.LINE, 1, 5, 31, 25));

        //Set chart series data
        chart.getNSeries().add("B1:B5", true);
        //Set Category data
        chart.getNSeries().setCategoryData("A1:A5");

        //Set Category axis type and unit scale
        chart.getCategoryAxis().setCategoryType(CategoryType.TIME_SCALE);
        chart.getCategoryAxis().setBaseUnitScale(TimeUnit.MONTHS);

        //Set custom number format for y axis value labels
        chart.getValueAxis().getTickLabels().setNumberFormat("0,##0");
        //Set custom number format for x axis value labels
        chart.getCategoryAxis().getTickLabels().setNumberFormat("mm/yyyy");
        //Set rotation angle
        chart.getCategoryAxis().getTickLabels().setRotationAngle(-90);

        //Save the Excel file
        workbook.save("f:\\files\\customnumberformat.xlsx");