New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Issue with Data Validation and long item list #89

Closed
pedrombsr opened this Issue Jan 9, 2014 · 4 comments

Comments

2 participants
@pedrombsr

I am using XlsxWriter to write a XLS file with Data Validation, by source, with a list inside a variable.

In some situations that list is large (hundreds of lines) and, in those situations, I get the following problem :

  1. With LIBREOFFICE : The validation is not functioning.
  2. With MICROSOFT EXCEL : When opening, MS EXCEL 2007 (identical in MS Excel 2013) returns the following error (and deactivates Data Validation) :
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error081480_01.xml</logFileName>
<summary>Errors were detected in file 'D:\pmbsr\attribute.xlsx'</summary>
<removedFeatures summary="Following is a list of removed features:">
     <removedFeature>
         Removed Feature: Data validation from /xl/worksheets/sheet1.xml part
     </removedFeature>
</removedFeatures>
</recoveryLog>

I am using Python 2.7.3. Here is the code :

sheetItem.data_validation(columnLetter, {'validate': 'list',  'source': nameValList})

@ghost ghost assigned jmcnamara Jan 9, 2014

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Jan 9, 2014

Owner

The issue is that Excel limits the total list length (including comma separators) to 256 characters.

If your list is "hundreds of lines" of lines then it probably exceeds that limit.

In the next release I'll add warnings about this so that the module doesn't create non-compatible Excel files.

The workaround for this, which is the workaround required by Excel, is to write the list to cells (perhaps in a hidden column) and reference that range from source.

worksheet.data_validation('B10', {'validate': 'list', 'source': '=$E$1:$E$42'})

John

Owner

jmcnamara commented Jan 9, 2014

The issue is that Excel limits the total list length (including comma separators) to 256 characters.

If your list is "hundreds of lines" of lines then it probably exceeds that limit.

In the next release I'll add warnings about this so that the module doesn't create non-compatible Excel files.

The workaround for this, which is the workaround required by Excel, is to write the list to cells (perhaps in a hidden column) and reference that range from source.

worksheet.data_validation('B10', {'validate': 'list', 'source': '=$E$1:$E$42'})

John

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Jan 9, 2014

Owner

P.S. If you confirm that is the issue can you let me know.

Owner

jmcnamara commented Jan 9, 2014

P.S. If you confirm that is the issue can you let me know.

@pedrombsr

This comment has been minimized.

Show comment
Hide comment
@pedrombsr

pedrombsr Jan 9, 2014

Yes, and that is the workaround I'm already using also. Thanks.

Yes, and that is the workaround I'm already using also. Thanks.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Feb 20, 2014

Owner

This issue is now fixed in XlsxWriter version 0.5.3.

Thanks for the input,

John

Owner

jmcnamara commented Feb 20, 2014

This issue is now fixed in XlsxWriter version 0.5.3.

Thanks for the input,

John

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment