This project has moved. For the latest updates, please go here.
2

Closed

Error doing data validation from another sheet

description

When using closedxml to add data validation to a file, all validations are written in the <x:dataValidations> element. However, this is not how excel saves data validation from other sheets.

Here's how closed xml saves it:
  <x:dataValidations count="3">
    <x:dataValidation type="list" errorStyle="warning" operator="between" allowBlank="0" showDropDown="0" showInputMessage="1" showErrorMessage="1" errorTitle="" error="" promptTitle="" prompt="" sqref="B6:B6">
      <x:formula1>"Foo,Bar,FooBar"</x:formula1>
      <x:formula2></x:formula2>
    </x:dataValidation>
    <x:dataValidation type="list" errorStyle="warning" operator="between" allowBlank="0" showDropDown="0" showInputMessage="1" showErrorMessage="1" errorTitle="" error="" promptTitle="" prompt="" sqref="C6:C6">
      <x:formula1>'HiddenDataValSheet'!$A$1:$A$1000</x:formula1>
      <x:formula2></x:formula2>
    </x:dataValidation>
    <x:dataValidation type="list" errorStyle="warning" operator="between" allowBlank="0" showDropDown="0" showInputMessage="1" showErrorMessage="1" errorTitle="" error="" promptTitle="" prompt="" sqref="D6:D6">
      <x:formula1>'HiddenDataValSheet'!$B$1:$B$1000</x:formula1>
      <x:formula2></x:formula2>
    </x:dataValidation>
  </x:dataValidations>
And here's how excel saves it:
  <extLst>
    <ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
      <x14:dataValidations count="2" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
        <x14:dataValidation type="list" errorStyle="warning" showInputMessage="1" showErrorMessage="1">
          <x14:formula1>
            <xm:f>HiddenDataValSheet!$A$1:$A$1000</xm:f>
          </x14:formula1>
          <xm:sqref>C6</xm:sqref>
        </x14:dataValidation>
        <x14:dataValidation type="list" errorStyle="warning" showInputMessage="1" showErrorMessage="1">
          <x14:formula1>
            <xm:f>HiddenDataValSheet!$B$1:$B$1000</xm:f>
          </x14:formula1>
          <xm:sqref>D6</xm:sqref>
        </x14:dataValidation>
      </x14:dataValidations>
    </ext>
I'll need to modify the closed xml code to be able to read the data validations from the <extLst> element and treat them like normal data validations.
Closed Jan 24 at 2:02 PM by igitur

comments

jesta1215 wrote Aug 11, 2016 at 6:37 PM

It turns out that the XLWorkBook_Load.cs completely ignores the WorksheetExtensions node, which is the cause of this bug.

The fix is to add the following on line 172:
                    if (reader.ElementType == typeof (WorksheetExtensionList))
                        LoadWorksheetExtensions((WorksheetExtensionList)reader.LoadCurrentElement(), ws);
Then implement the LoadWorksheetExtensions function, which I'm working on now.

jesta1215 wrote Aug 11, 2016 at 7:10 PM

I got this working. Here's the code I added:
        private static void LoadWorksheetExtensions(WorksheetExtensionList extensions, XLWorksheet ws)
        {
            if (extensions == null)
                return;

            // get each extension element
            foreach (WorksheetExtension we in extensions.Elements<WorksheetExtension>())
            {
                // get data validations element
                var dataValidationList = we.Elements<DocumentFormat.OpenXml.Office2010.Excel.DataValidations>().ToList();
                foreach (var dataValidations in dataValidationList)
                { 
                    LoadWorksheetExtensionDataValidations(dataValidations, ws);
                }
            }
        }
        private static void LoadWorksheetExtensionDataValidations(
            DocumentFormat.OpenXml.Office2010.Excel.DataValidations dataValidations, XLWorksheet ws)
        {
            if (dataValidations == null)
                return;

            // get each datavalidation element
            foreach (DocumentFormat.OpenXml.Office2010.Excel.DataValidation dv in dataValidations.Elements<DocumentFormat.OpenXml.Office2010.Excel.DataValidation>())
            {
                // get the data validation object we want to modify based on the cell reference
                var txt = dv.ReferenceSequence.InnerText;
                if (XLHelper.IsNullOrWhiteSpace(txt)) continue;
                foreach (var dvt in txt.Split(' ').Select(rangeAddress => ws.Range(rangeAddress).DataValidation))
                {
                    if (dv.AllowBlank != null) dvt.IgnoreBlanks = dv.AllowBlank;
                    if (dv.ShowDropDown != null) dvt.InCellDropdown = !dv.ShowDropDown.Value;
                    if (dv.ShowErrorMessage != null) dvt.ShowErrorMessage = dv.ShowErrorMessage;
                    if (dv.ShowInputMessage != null) dvt.ShowInputMessage = dv.ShowInputMessage;
                    if (dv.PromptTitle != null) dvt.InputTitle = dv.PromptTitle;
                    if (dv.Prompt != null) dvt.InputMessage = dv.Prompt;
                    if (dv.ErrorTitle != null) dvt.ErrorTitle = dv.ErrorTitle;
                    if (dv.Error != null) dvt.ErrorMessage = dv.Error;
                    if (dv.ErrorStyle != null) dvt.ErrorStyle = dv.ErrorStyle.Value.ToClosedXml();
                    if (dv.Type != null) dvt.AllowedValues = dv.Type.Value.ToClosedXml();
                    if (dv.Operator != null) dvt.Operator = dv.Operator.Value.ToClosedXml();
                    if (dv.DataValidationForumla1 != null) dvt.MinValue = dv.DataValidationForumla1.InnerText;
                    if (dv.DataValidationForumla2 != null) dvt.MaxValue = dv.DataValidationForumla2.InnerText;
                }
            }
        }

igitur wrote Nov 11, 2016 at 9:00 AM

Do you perhaps have a test case to illustrate the problem and hence this fix?