Bug: Issues while creating multiple formats in new file

Current behavior

When creating a brand new file and creating formats, my formats get replaced with some default ones. In my specific case, when the line is odd, the default format "Hyperlink" is selected. When the line is even, the format seems correct.

The issue is, at first it worked fine. I had only one default format, but now I have two default formats (see comments in the sample code)

Expected behavior

I should just have one default format, while for some reason now I have two. The oddrows in the example should have my first custom format, the even ones should have the second custom format.

Sample code to reproduce

import xlsxwriter

# Create excel file
workbook = xlsxwriter.Workbook('test.xlsx')

print(len(workbook.formats)) # Why does it print 2? Should just be 1 (default)

workbook.add_format({'border': 1, 'font_size': 8})                        # expected workbook.formats[1]
workbook.add_format({'bg_color': '#d9d9d9', 'border': 1, 'font_size': 8}) # expected workbook.formats[2]

print(len(workbook.formats)) # Prints 4

worksheet = workbook.add_worksheet('Test')

# Random data
for i in range(6):
    if i % 2 == 0:
        worksheet.write_row(i, 0, ['a', 'b'], workbook.formats[1])
        worksheet.merge_range(i, 2, i, 3, 'a', workbook.formats[1])
    else:
        worksheet.write_row(i, 0, ['a', 'b'], workbook.formats[2])
        worksheet.merge_range(i, 2, i, 3, 'a', workbook.formats[2])

workbook.close()

Environment

- XlsxWriter version: 3.1.9
- Python version: 3.10.4
- Excel version: Version 2401 Build 16.0.17231.20194
- OS: Windows 11

Any other information

No response

OpenOffice and LibreOffice users

[X] I have tested the output file with Excel.

Thanks for the detailed report.

The issue is that the code shouldn't be accessing workbook.formats[] directly. That is an internal data struct and isn't guaranteed to follow the creation/use order of the users formats. Instead the user should track the formats themselves in their own list/data structure. Something like this:

import xlsxwriter

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

my_formats = []

my_formats.append(workbook.add_format({'border': 1, 'font_size': 8}))
my_formats.append(workbook.add_format({'bg_color': '#d9d9d9', 'border': 1, 'font_size': 8}))

worksheet = workbook.add_worksheet('Test')

# Random data
for i in range(6):
    worksheet.write_row(i, 0, ['a', 'b'], my_formats[i % 2])
    worksheet.merge_range(i, 2, i, 3, 'a',  my_formats[i % 2])

workbook.close()

screenshot

jmcnamara / XlsxWriter