python xslxwriter validating an internationalzed boolean

user_16454907 0
Viewed 145

I am using xlsxwriter to write a boolean to a worksheet:

worksheet.write_boolean(i, 9, export, style_normal)

When I open the xlsx file in Excel (the language is set to Dutch by company policy), the boolean shows up in the cell as 'WAAR' or 'ONWAAR'

I am trying to add validation:

worksheet.data_validation('J{0}'.format(i+1), {'validate': 'list', 'source': [True, False]})

But the dropdown I see in Excel from this data_validation list contains 'True' and 'False' instead of 'WAAR' and 'ONWAAR'. And if you select one of the values in the dropdown the value in the cell changes to True or False.

How can I make validation for a boolean cell with the same (translated) values as in the cell WAAR/ONWAAR?

If I make the validation like this, it will not work for people with a different language setting?

worksheet.data_validation('J{0}'.format(i+1), {'validate': 'list', 'source': ['WAAR', 'ONWAAR']})
xlsxwriter internationalization excel python
1 Answers

I don't think it is possible to do what you want to do in Excel (or by extension in XlsxWriter).

Excel doesn't have an explicit boolean data validation option. The usual way of creating a boolean data validation is to use a list with "TRUE,FALSE". However, these are stored as a formula string and I don't think Excel translates these when loading the file.

If you do think it is possible and you manage to create an Excel file that loads with "TRUE,FALSE" in one locale and "WAAR/ONWAAR" in another then open a feature request on the XlsxWriter GitHub with a sample file and I will implement it.

One possible workaround would be to put actual boolean values in some cells (say A1 and A2) and then use a range reference like "=A1:A2" in the data validation. That should translate between locales.

Like this:

import xlsxwriter

workbook = xlsxwriter.Workbook('international_boolean.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write('A1', True)
worksheet.write('A2', False)


worksheet.data_validation('C1', {'validate': 'list', 'source': '=$A$1:$A$2'})

workbook.close()
user_102380
Related