Hi community,
I have a large Excel Workbook (approximately 80,000 rows) with data validation enabled that is generated from a system process using Apache POI. There are 2 columns that have data validation enabled. So that is approximately 160,000 data validation references.
When this Workbook is loaded by Excel it detects an error; and fixes it by removing the data validation. This is problematic.
When I use the same process to generate a small Excel Workbook (approximately 8,000 rows) it is fine.
The structure of the Workbook has 4 internal sheets. The bulk of the data is in the 2nd sheet. The lookups for data validation is in the 4th sheet. The other 2 sheets have instructions and auxiliary data; nothing related to data validation.
In the problematic Workbook there are 48 lookups; and in there other there are 4 lookups. The largest lookup has 21 values, where as bulk of the lookups have 10 or less values. The lookup values and data are encoded as inline string by the Apache POI framework.
I have a feeling that the problem is with the quantity of the data as it is good with a small batch. It is either with the number of rows or the number of lookups; or a combination of both. I have found no documentation to indicate maximum numbers.
I'm happy to answer further questions to resolve this problem I have.
Please help,
Brett Walker
