How to use Application.International in the xlwings API way

The Application.International property in Excel is a read-only property that returns information about the current country/region and international settings in Excel. This is particularly useful for creating locale-aware macros or scripts that need to adapt to different regional formats, such as date formats, currency symbols, list separators, and more. In xlwings, this property can be accessed via the api object, which provides a direct gateway to Excel’s underlying object model. Understanding how to use International allows developers to write more robust and portable code that functions correctly across various international versions of Excel.

The syntax for accessing the International property in xlwings is straightforward. Since International is a property of the Application object, you first need to get a reference to the Excel application through xlwings, typically via app = xw.App() or by using the active app. Then, you can access the property using app.api.International. The key aspect is that International accepts an index argument (a constant or numeric value) that specifies which setting to return. This index corresponds to Excel’s XlApplicationInternational constants, which are enumerations defining various international parameters. For example, xlCountryCode (value 1) returns the country/region code, while xlCurrencyDigits (value 25) returns the number of decimal digits used in currency formats. The available indices are numerous, and developers should refer to the official Excel VBA documentation for a comprehensive list, as xlwings does not redefine these constants but relies on Excel’s built-in enumerations.

Here is a table of some common XlApplicationInternational indices and their meanings, which can be used with International in xlwings:

Index Constant (VBA Name)	Value	Description
xlCountryCode	1	Returns the country/region code for the current system.
xlCountrySetting	2	Returns the country/region setting from the Windows Control Panel.
xlCurrencyDigits	25	Returns the number of decimal digits used in currency formats.
xlCurrencyCode	27	Returns the currency symbol for the current locale.
xlDateSeparator	17	Returns the date separator character (e.g., “/” or “-“).
xlTimeSeparator	18	Returns the time separator character (e.g., “:”).
xlListSeparator	5	Returns the list separator character (e.g., “,” or “;”).
xlDayCode	21	Returns the day symbol used in date formats.
xlMonthCode	20	Returns the month symbol used in date formats.
xlYearCode	19	Returns the year symbol used in date formats.

In xlwings, you can use these indices by their numeric values directly, as the constants are not natively provided in the xlwings module. However, for clarity, you can define them in your code based on the VBA enumerations. The property returns a value that can be a string, number, or character, depending on the index. It is important to note that the behavior might vary slightly across different Excel versions, so testing in the target environment is recommended.

Below are practical examples of using the Application.International property with xlwings in Python. These examples demonstrate how to retrieve various international settings and use them in data processing or formatting tasks.

Example 1: Retrieving basic locale information.

import xlwings as xw

# Connect to the active Excel application
app = xw.apps.active

# Get the country/region code (index 1)
country_code = app.api.International[1]
print(f"Country/Region Code: {country_code}")

# Get the list separator (index 5)
list_separator = app.api.International[5]
print(f"List Separator: '{list_separator}'")

# This can be used to dynamically format CSV files or split text based on locale.

Example 2: Working with date and currency formats.

import xlwings as xw

app = xw.apps.active

# Get date and time separators
date_sep = app.api.International[17] # xlDateSeparator
time_sep = app.api.International[18] # xlTimeSeparator
print(f"Date Separator: {date_sep}, Time Separator: {time_sep}")

# Get currency digits and symbol
currency_digits = app.api.International[25] # xlCurrencyDigits
currency_symbol = app.api.International[27] # xlCurrencyCode
print(f"Currency Digits: {currency_digits}, Symbol: {currency_symbol}")

# Use these to format numbers in a worksheet dynamically
sheet = app.books.active.sheets[0]
cell = sheet.range("A1")
cell.value = 1234.56
cell.number_format = f"#{currency_symbol}0.{'0' * currency_digits}" # Custom format based on locale

Example 3: Adapting data parsing based on international settings.

import xlwings as xw

app = xw.apps.active

# Get the day, month, and year codes for date formats
day_code = app.api.International[21] # xlDayCode
month_code = app.api.International[20] # xlMonthCode
year_code = app.api.International[19] # xlYearCode
print(f"Date Format Codes: Day={day_code}, Month={month_code}, Year={year_code}")

# This information can help in parsing date strings from different locales,
# especially when dealing with text data imported into Excel.