How to apply the format as "Text" and "Accounting" using xlsxwriter

Question

I have been using xlsxwriter for a while and find it really useful. I used it for several purposes, such as custom data validation, custom formats, etc. However, there are two things that I cannot accomplish.

• set the cell format to "text" rather than "general". I tried apis write_string, write_blank, but they always use a common format

• set the format as accounting. I tried add_format ({'num_format': '#, ###'}, but this just sets the format as "custom" instead of accounting

Please, help.

Answer 1

• To set the format of the text cell, you need to apply the text format to the cell (as in Excel). To do this, you set the num_format property to "@" format.
• If you set the line format, for example #,### , then it will be displayed in Excel as the custom format, even if it matches one of the built-in formatting categories, such as accountancy . This is the same behavior as Excel.

To get one of the built-in formats, you need to use the index format instead of the string. The table in the num_format section of the documentation shows the available indexes and their equivalent string formats. For accountancy you need to use one of the format indexes similar to the account, for example 44 ( _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_) ). The example below shows both of them:

 import xlsxwriter workbook = xlsxwriter.Workbook('test.xlsx') worksheet = workbook.add_worksheet() worksheet.set_column('A:A', 20) format1 = workbook.add_format({'num_format': '@'}) format2 = workbook.add_format({'num_format': 44}) worksheet.write(0, 0, 1234) worksheet.write(1, 0, 1234, format1) worksheet.write(2, 0, 1234, format2) workbook.close() 

Conclusion: