How to use Application.ThousandsSeparator in the xlwings API way

The ThousandsSeparator property of the Application object in Excel is a global setting that controls the character used to separate thousands in numbers when formatting and displaying numerical data. This property is part of Excel’s internationalization features, allowing customization to match regional formatting standards. For instance, in many European countries, a period (.) is used as the thousands separator, while in English-speaking countries like the United States, a comma (,) is typically used. By accessing this property via xlwings, you can programmatically read or modify the thousands separator setting for the active Excel instance, which can be useful for ensuring consistent number formatting across different locales in automated reports or data processing scripts.

Syntax in xlwings:
In xlwings, you can access the ThousandsSeparator property through the app object, which represents the Excel application. The property is available as an attribute of the app object, and it can be both read and written. The syntax is straightforward:

• To get the current thousands separator: separator = app.separators['thousands']
• To set a new thousands separator: app.separators['thousands'] = new_separator
  Note that in xlwings, the ThousandsSeparator is accessed via the separators dictionary under the app object, rather than as a direct property like in VBA. This dictionary includes other separators like decimal separators as well. The new_separator parameter should be a string containing a single character (e.g., “,” or “.”), and it must be a valid separator character supported by Excel for the current system locale.

Code Examples:
Here are some practical examples using xlwings to work with the ThousandsSeparator property:

1. Reading the current thousands separator:

import xlwings as xw
# Connect to the active Excel application
app = xw.apps.active
# Get the current thousands separator
current_separator = app.separators['thousands']
print(f"The current thousands separator is: '{current_separator}'")

This code snippet retrieves and prints the thousands separator currently set in Excel, which might output something like ',' for a comma.

2. Changing the thousands separator:

import xlwings as xw
# Connect to the active Excel application
app = xw.apps.active
# Set the thousands separator to a period (common in some European formats)
app.separators['thousands'] = '.'
print("Thousands separator updated to period.")

After running this, Excel will use a period as the thousands separator for new number formatting, affecting how numbers are displayed in cells.

3. Applying the thousands separator in a workbook:

import xlwings as xw
# Start a new Excel instance and open a workbook
app = xw.App()
workbook = app.books.open('example.xlsx')
sheet = workbook.sheets[0]
# Change the thousands separator to a space (less common but possible)
app.separators['thousands'] = ' '
# Format a cell with a number to use the new separator
sheet.range('A1').value = 1234567
sheet.range('A1').number_format = '#,##0'
# The displayed value in Excel will now show as "1 234 567" if formatting is applied
workbook.save()
app.quit()