Customize Globalization Settings in Excel Using Java

In this blog post, you’ll learn how to customize globalization settings in Excel using Java. Sometimes you want to customize the Pivot Total, Sub Total, Grand Total, All Items, Multiple Items, Column Labels, Row Labels, Blank Values text as per your requirements. Aspose.Cells allows you to customize the globalization settings of the pivot table to deal with such scenarios using PivotGlobalizationSettings class. You can also use this feature to change the labels to other languages like Arabic, Hindi, Polish, etc. For Chart, Aspose.Cells APIs have exposed the ChartGlobalizationSettings class in order to deal with the scenarios where the user wishes to set chart component to different language. Let’s get started!

This article covers the following topics:

Java Excel Library to Customize Globalization Settings

Aspose.Cells for Java is a powerful library that simplifies the process of customizing globalization settings in Excel. It provides robust features for manipulating Excel files programmatically. With Aspose.Cells, developers can easily create, modify, and customize charts and graphs. This library supports various Excel formats and offers advanced functionalities, making it an ideal choice for project managers and developers alike.

To install Aspose.Cells for Java, use Maven:

<dependency>
  <groupId>com.aspose</groupId>
  <artifactId>aspose-cells</artifactId>
  <version>25.6</version>
</dependency>

Or download it from the official releases page.

How to Customize Globalization Settings in Excel using Java

Sample Data for Chart Globalization Settings

Sample Data for Chart Globalization Settings

Sample Data for Chart Globalization Settings

How to Customize Globalization Settings for Chart

Follow these steps to customize globalization settings for chart using Java with Aspose.Cells for Java. We will create a waterfall chart based on the following data. The names of chart components will be displayed in English in the chart. We will use a Turkish language example to show how to display the Chart Title, Legend Increase/Decrease names, Total name, and Axis Title in Turkish.

  1. Create a custom language class for chart component.
  2. Load an existing Excel file using the Workbook class.
  3. Set custom ChartGlobalizationSettings.
  4. Access the first worksheet using workbook.getWorksheets().get(0).
  5. Access the first chart using the worksheet.getCharts().get(0).
  6. Call Chart.calculate() method for calculation.
  7. Output relevant information and view the results.

Here’s a Java code snippet that implements the above steps:

Output generated by the sample above code:

Workbook chart title: Grafik Başlığı

Workbook chart legend: Artış

Workbook chart legend: Düşüş

Workbook chart legend: Toplam

Workbook category axis tile: Eksen Başlığı

How to Customize Globalization Settings for Formula

Follow these steps to customize globalization settings for formula using Java with Aspose.Cells for Java. The following sample code explains how to implement GlobalizationSettings.GetLocalFunctionName(string standardName) method. The method returns the local name of the standard function. If the standard function name is SUM, it returns UserFormulaLocal_SUM. You can change the code as per your needs and return the correct local function names e.g. SUM is SUMME in German and TEXT is ТЕКСТ in Russian. Please also see the console output of the sample code given below for a reference.

  1. Create a custom globalization settings class for formula.
  2. Create a new workbook using the Workbook class.
  3. Set custom GlobalizationSettings.
  4. Access the first worksheet using workbook.getWorksheets().get(0).
  5. Access cells and setting formula properties.
  6. Output relevant information and view the results.

Here’s a Java code snippet that implements the above steps:

Output generated by the sample above code:

Formula Local: =UserFormulaLocal_SUM(A1:A2)
Formula Local: =UserFormulaLocal_AVERAGE(B1:B2,B5)

Sample Data for Errors and Boolean Globalization Settings

Sample Data for Errors and Boolean Globalization Settings

Sample Data for Errors and Boolean Globalization Settings

How to Customize Globalization Settings for Errors and Boolean

If you are using Microsoft Excel in Russian Locale or Language or any other Locale or Language, it will display Errors and Boolean values according to that Locale or Language. You can achieve a similar behavior using Aspose.Cells by using the Workbook.Settings.GlobalizationSettings property. You will have to override the GlobalizationSettings.getErrorValueString() and GlobalizationSettings.getBooleanValueString() methods of GlobalizationSettings class. Follow these steps to customize globalization settings for Errors and Boolean Value using Java with Aspose.Cells for Java.

  1. Create a custom language class for Errors and Boolean Value.
  2. Load an existing Excel file using the Workbook class.
  3. Set custom GlobalizationSettings.
  4. Call Workbook.calculateFormula() method for calculation.
  5. Save the file to pdf using workbook.save().

Here’s a Java code snippet that implements the above steps:

Customize Globalization Settings for PivotTable using Java

Customize Globalization Settings for PivotTable using Java

Sample Data for PivotTable Globalization Settings

Sample Data for Errors and Boolean Globalization Settings

Sample Data for Errors and Boolean Globalization Settings

How to Customize Globalization Settings for PivotTable

Follow these steps to customize globalization settings for Pivot Table using Java with Aspose.Cells for Java. It creates a class CustomPivotTableGlobalizationSettings derived from a base class PivotGlobalizationSettings and overrides all of its necessary methods. These methods return the customized text for the Pivot Total, Sub Total, Grand Total, All Items, Multiple Items, Column Labels, Row Labels, Blank Values. Then it assigns the object of this class to WorkbookSettings.GlobalizationSettings.PivotSettings property. The code loads the source excel file that contains the pivot table, refreshes and calculate its data and saves it as output PDF file.

  1. Create a CustomPivotTableGlobalizationSettings class for Pivot Table.
  2. Load an existing Excel file using the Workbook class.
  3. Set custom PivotGlobalizationSettings.
  4. Access the first worksheet using workbook.getWorksheets().get(0).
  5. Access the first PivotTable using the worksheet.getPivotTables().get(0).
  6. Call PivotTable.refreshData() and PivotTable.calculateData() methods to refresh and calculate Pivot Table.
  7. Save the file to pdf using workbook.save().

Here’s a Java code snippet that implements the above steps:

Customize Globalization Settings for PivotTable using Java

Customize Globalization Settings for PivotTable using Java

Get a Free License

Interested in exploring Aspose products? Visit the license page to obtain a free temporary license. It’s easy to get started and unlock the full potential of Aspose.Cells for your projects!

Customize Globalization Settings: Free Resources

Don’t stop here! Explore additional resources to further enhance your understanding of Aspose.Cells. These resources will help you master Excel automation and strengthen your skills.

Conclusion

In this blog post, we explored how to customize globalization settings in Excel using Java. Aspose.Cells for Java simplifies this process with its powerful features and flexibility. We encourage you to explore more about Aspose.Cells for Java to enhance your project management capabilities.

If you have any questions or need further assistance, please feel free to reach out at our free support forum.

See Also