Num_format overrides formula in add_table

[mklwong]

Hi,

I am using XlsxWriter to create a spreadsheet where a column has a formula definition and currency formatting but the formula appears to be overridden (table 1). When the formatting is removed, the formula reappears (table 2)

Interestingly, if a default value is added in one row, the formula persists for that row and excel shows an "Inconsistent Calculated Column Formula" error for that cell (table 3).

I am using Python version 3.7.6 and XlsxWriter 1.3.2 and Excel version 260 (Version 2002 Build 12527.20880).

Here is some code that demonstrates the problem:

import xlsxwriter

workbook = xlsxwriter.Workbook(r'E:/test.xlsx')
worksheet = workbook.add_worksheet()
currency = workbook.add_format({'num_format':'_-$* #,##0.00_-;-$* #,##0.00_-;_-$* "-"??_-;_-@_-'})
# Table 1
worksheet.add_table('A1:C3', {'data': [[1,2,None], [1,2,None]],
                              'columns': [{'header': 'A'},
                                          {'header': 'B'},
                                          {'header': 'C', 'formula':'[@A]*[@B]', 'format':currency}
                                          ]})
# Table 2
worksheet.add_table('A5:C7', {'data': [[1,2,None], [1,2,5]],
                              'columns': [{'header': 'A'},
                                          {'header': 'B'},
                                          {'header': 'C', 'formula':'[@A]*[@B]'}
                                          ]})
# Table 1
worksheet.add_table('A9:C11', {'data': [[1,2,None], [1,2,5]],
                              'columns': [{'header': 'A'},
                                          {'header': 'B'},
                                          {'header': 'C', 'formula':'[@A]*[@B]', 'format':currency}
                                          ]})
workbook.close()

[jmcnamara]

I think the issues is that you are overriding the values in Column C with None. If you leave that out the program should work as expected:

import xlsxwriter

workbook = xlsxwriter.Workbook(r'test.xlsx')
worksheet = workbook.add_worksheet()
currency = workbook.add_format({'num_format': '_-$* #,##0.00_-;-$* #,##0.00_-;_-$* "-"??_-;_-@_-'})

# Table 1
worksheet.add_table('A1:C3', {'data': [[1,2], [1,2]],
                              'columns': [{'header': 'A'},
                                          {'header': 'B'},
                                          {'header': 'C', 'formula': '[@A]*[@B]', 'format': currency}]})
workbook.close()

Output:

[mklwong]

Ah seems like "data" doesn't have to have the same shape as the table. This also worked.

worksheet.add_table('A9:C11', {'data': [[1,2], [1,2,5]],
                              'columns': [{'header': 'A'},
                                          {'header': 'B'},
                                          {'header': 'C', 'formula':'[@A]*[@B]', 'format':currency}
                                          ]})

Thanks!