-
Improvement
-
Resolution: Done
-
Normal
-
None
-
None
-
Security Level: Jimmy
-
None
Steps to Reproduce
Run attached excel file in keikai
Go to sheet1 B2, it has a validation dropdown (list from Sheet1!A4:A6)
Go to sheet2 B2, it has a validation dropdown (list from Sheet1!A4:A6)
Current Result
Sheet1 validation list works
Sheet2 validation list is not found
Expected Result
Validation lists are found in both cases
Debug Information
XslxExtractor is unable to load validations that contain a reference to a different sheet:
io.keikai.importer.XlsxImporter.importValidation(XlsxSheetExtractor, SSheet)
XlsxExtractor.XlsxDataValidationsExtractor dataValidations = xSheet XlsxExtractor.XlsxDataValidationsExtractor dataValidations = xSheet .getDataValidations();
Doesn't find the sheet2 validation
Excel stores normal validations in:
<worksheet> <dataValidations count="1"> <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B2 B3" xr:uid="{E86B0D71-A4C2-41B0-916E-C7AC03EDB63A}"> <formula1>$A$4:$A$6</formula1> </dataValidation> </dataValidations> </worksheet>
Excel stores validations referencing a different sheet in
<worksheet> <extLst> <ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"> <x14:dataValidations count="1" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"> <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" xr:uid="{D50F3D6B-A2CE-4A11-87BE-FA51E3CD2528}"> <x14:formula1> <xm:f>Sheet1!$A$4:$A$6</xm:f> </x14:formula1> <xm:sqref>B2</xm:sqref> </x14:dataValidation> </x14:dataValidations> </ext> </extLst> </worksheet>
Workaround
Declare the target range as a named range, then use that named range as the datasource for the validation list.
Named ranges do not cause the validation to be stored in the extList event if they reference a different sheet.
