CODEWITHSUNDEEP
pythonexcelxlsxwriter
Alex Tereshenkov
Alex Tereshenkov

Reputation: 3620

How to avoid overwriting of cell style format in xlsxwriter (Python)?

I am on Python 2.7, xlsxwriter 0.8.4.

I have a list of values, ints and floats and I want to be able to apply different number format when writing the data into the sheet's cell.

However, when using the set_num_format method, the lastly applied format will be applied to already written cells. I wonder whether it's possible to apply a cell formatting for every write operation individually? If not, how else one could manage multiple format objects without hard coding them as in:

simple_style_percent_int = workbook.add_format({'bold': False, 'font_color': 'black','font_size': 11, 'font_name': 'Calibri','text_wrap': True, 'num_format': '0"%"'}) simple_style_percent_decimal = workbook.add_format({'bold': False, 'font_color': 'black','font_size': 11, 'font_name': 'Calibri','text_wrap': True, 'num_format': '0.0"%"'})

The code I run:

import xlsxwriter

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

percent_format = workbook.add_format()

for item in [22.0, 34.68]:

    if item.is_integer():
        percent_format.set_num_format('0"%"')
        worksheet.write(1,1, int(item), percent_format)
    else:
        percent_format.set_num_format('0.00"%"')
        worksheet.write(2,1, item, percent_format)

workbook.close()

The script will apply the lastly used (i.e. decimal percent) format to all cells that had this format during previous write operations (however, I want 22 to be represented as 22%, not as 22.00%.

enter image description here

Upvotes: 2

Views: 2515

Answers (1)

Martin Evans
Martin Evans

Reputation: 46759

There is nothing stopping you having multiple formats defined as follows:

import xlsxwriter

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

percent_format_1 = workbook.add_format()
percent_format_1.set_num_format('0"%"')

percent_format_2 = workbook.add_format()
percent_format_2.set_num_format('0.00"%"')

for item in [22.0, 34.68]:
    if item.is_integer():
        worksheet.write(1,1, int(item), percent_format_1)
    else:
        worksheet.write(2,1, item, percent_format_2)

workbook.close()

This would give you:

excel screenshot

Note, changing a format will cause the change to be applied to cells that have already been written using that format. Namely the final format is used to display all associated cells, which is why you saw it change. See Modifying formats for more information.

To apply this to a whole column of data using these two different formats, you could enumerate the data as follows:

import xlsxwriter

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

percent_format_1 = workbook.add_format()
percent_format_1.set_num_format('0"%"')

percent_format_2 = workbook.add_format()
percent_format_2.set_num_format('0.00"%"')

for rowy, item in enumerate([22.0, 34.68, 1.0, 5.0, 6.8], start=1):
    if item.is_integer():
        worksheet.write(rowy, 1, int(item), percent_format_1)
    else:
        worksheet.write(rowy, 1, item, percent_format_2)

workbook.close()

Upvotes: 4

Related Questions