Bug: Ignoring multiple, different errors over same column range

[alex-muehleisen]

Current behavior

When using the ignore_errors() function to ignore multiple errors across the worksheet, only one of the errors is ignored. For example, if we have a "number_stored_as_text" error in "B2" and a "eval_error" in "B3", both of these errors should be able to be ignored using worksheet.ignore_errors({"number_stored_as_text": "A:Z", "eval_error": "A:Z"}). However, this currently only results in the "eval_error" being ignored. The "B2" cell with the "number_stored_as_text" error would still throw an error and would not be ignored.

Expected behavior

It would be great if we could ignore multiple errors across the same column range. This means that if we have a "number_stored_as_text" error in "B2" and a "eval_error" in "B3", both of these errors could be ignored using worksheet.ignore_errors({"number_stored_as_text": "A:Z", "eval_error": "A:Z"}). This is especially important when working with outputs where you are not sure where the errors will be.

Sample code to reproduce

import xlsxwriter

workbook = xlsxwriter.Workbook("ignore_errors.xlsx")
worksheet = workbook.add_worksheet()

# Write strings that looks like numbers. This will cause an Excel warning.
worksheet.write_string("C2", "123")
worksheet.write_string("C3", "123")

# Write a divide by zero formula. This will also cause an Excel warning.
worksheet.write_formula("C5", "=1/0")
worksheet.write_formula("C6", "=1/0")

# Turn off some of the warnings:
worksheet.ignore_errors({"number_stored_as_text": "A:C", "eval_error": "A:C"})

workbook.close()

Environment

- XlsxWriter version:
- Python version:
- Excel version:
- OS:

Any other information

No response

OpenOffice and LibreOffice users

• I have tested the output file with Excel.

[alex-muehleisen]

An alternative solution would be adding a method to turn off all errors for the entire worksheet. That would also be really cool. Thanks for your help with this! :)

[jmcnamara]

It would be great if we could ignore multiple errors across the same column range.

I don't think this is possible in Excel.

An Excel range like A:C is stored internally in Excel as A1:C1048576, see the XlsxWriter docs on Row and Column Ranges.

You can use this to turn off errors in distinct non-overlapping columns like the example below (note some of the errors are in column C and some in column D):

import xlsxwriter

workbook = xlsxwriter.Workbook("ignore_errors.xlsx")
worksheet = workbook.add_worksheet()

# Write strings that looks like numbers. This will cause an Excel warning.
worksheet.write_string("C2", "123")
worksheet.write_string("C3", "123")

# Write a divide by zero formula. This will also cause an Excel warning.
worksheet.write_formula("D5", "=1/0")
worksheet.write_formula("D6", "=1/0")

# Turn off some of the warnings:
worksheet.ignore_errors({"number_stored_as_text": "C1:C1048576", "eval_error": "D1:D1048576"})

workbook.close()

Output:

However, from my analysis It seems like Excel doesn't support multiple ignored errors for the same range.

If you think it is possible and you can produce a file in Excel for multiple errors turned off for the same column then I will take a look at it.

[jmcnamara]

Any other feedback on this before I close the issue?

[alex-muehleisen]

Ok understood. Thanks for looking into this!!

Do you know if it's possible to completely turn off Error Checking, or any of the Error checking rules, for the outputted Excel? I'm specifically referring to the "Enable background error checking" checkbox and any of the Error checking rules like "Formulas inconsistent with other formulas in the region" in the attached image below.

[jmcnamara]

Do you know if it's possible to completely turn off Error Checking, or any of the Error checking rules, for the outputted Excel?

I've looked into that in the past and unfortunately there isn't any setting in the file format to turn off those warnings. Those settings that you highlighted in the image are stored locally for the app.

[alex-muehleisen]

Ok got it. Looks like there's nothing else we can do then. Happy to close this ticket. Thanks again for your time and help here!

[Cito]

If it is like that, it should be documented though. The example in the documentation here gives the impression that it is possible

[jmcnamara]

If it is like that, it should be documented though.

Agreed. I'll update the docs to say Excel doesn't support/allow this.

The example in the documentation here gives the impression that it is possible

Which example specifically?

[Cito]

Which example specifically?

The example with the comment # Or for more than one option: which has two different errors but the same range A1:H50.

The documentation should explain that while this is possible, for a specific range only the error defined last in the dict is ignored.

For instance, if you set

worksheet.ignore_errors({
    'number_stored_as_text': 'A1:H50',
    'eval_error': 'E1:E50'})

then, in column E, only the eval_error will be ignored, and for all other cells, only number_stored_as_text will be ignored.

In the example where the ranges are the same, only eval_error will be ignored for all cells.

[jmcnamara]

Re-opening as a reminder to fix documentation.