Uploaded image for project: 'Keikai'
  1. Keikai
  2. KEIKAI-464

Excel xml store DataValidation from other sheet in a different xml element, fails to load in keikai


    • Icon: Improvement Improvement
    • Resolution: Done
    • Icon: Normal Normal
    • 5.9.0
    • 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:


      	<dataValidations count="1">
      		<dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B2 B3" xr:uid="{E86B0D71-A4C2-41B0-916E-C7AC03EDB63A}">


       Excel stores validations referencing a different sheet in

      		<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}">


      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.

            jumperchen jumperchen
            MDuchemin MDuchemin
            0 Vote for this issue
            1 Start watching this issue