[BUG] Template data validation lost

[cduff]

🐛 Bug Report

I have a template Excel file which includes data validations. When I use exceljs to write data to the template the data validations are lost.

Lib version: 3.8.2

Steps To Reproduce

// index.js
const Excel = require("exceljs");
(async () => {
  const workbook = new Excel.Workbook();
  await workbook.xlsx.readFile("./template.xlsx"); // Has data validation
  await workbook.xlsx.writeFile("./output.xlsx"); // Data validation lost
})();

See attached sample project: exceljs-bug.zip

The expected behaviour:

Data validation is retained.

[Alanscut]

@cduff Inconsistent xml structure in Sheet1.xml will cause data validation to be lost. See data-validations-xform.js.

@Siemienik @guyonroche There is more than one such error, See Issue #918.

template.xlsx

// sheet1.xml
  <sheetData />
  <extLst>
    <ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
      <dataValidations count="1" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
        <dataValidation type="list" showInputMessage="1" showErrorMessage="1" xr:uid="{A951C8AD-DA7D-45C7-ADAA-9BC7A5FE4CC1}">
          <formula1>
            <f>Sheet2!$A:$A</f>
          </formula1>
          <sqref>A1:A1048576</sqref>
        </dataValidation>
      </dataValidations>
    </ext>
  </extLst>

File generated by workbook.xlsx.writeFile()

// sheet1.xml
  <sheetData>
    <row r="1" spans="1:1" x14ac:dyDescent="0.25" />
  </sheetData>
  <dataValidations count="1">
    <dataValidation type="list" showInputMessage="1" showErrorMessage="1" sqref="A1">
      <formula1>Sheet2$A:$A</formula1>
    </dataValidation>
  </dataValidations>

[Siemienik]

@Alanscut @cduff thank you for that precise information, I'll attempt to fix it.

@cduff btw. I create a library for generating xlsx from templates: https://github.com/Siemienik/xlsx-renderer It's based on exceljs, so this bug will occur there too, but I think It should be quite useful for you :)

[Siemienik]

As I found here: [MS-XLSX]-120411.pdf, our library read and parse data validations in 100% wrong, I'm still working on this issue.

[Siemienik]

So todo list:

6. [ ] fix xlsx parser and renderer classes to be consistent with: [MS-XLSX]-120411.pdf
7. [ ] typings to current existed Worksheet.dataValidations https://github.com/exceljs/exceljs/blob/master/index.d.ts#L955
8. [ ] add dataValidation getter and setter for Column
9. [ ] add dataValidation getter and setter for Row
10. [ ] add posibility to set dataValidation by Range
11. [ ] typings to all above
12. [ ] update documentation

[mpalavrov]

@Siemienik, I would like to add here an example of a Sheet XML before and after the workbook.xlsx.writeFile, as I think it is related to the same issue, but I am not sure if you have located it.

XML of Sheet BEFORE xlsx.writeFile:

<worksheet>
  <dataValidations count="12">
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B6">
      <formula1>ENV</formula1>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B7">
      <formula1>INT</formula1>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B19">
      <formula1>TRANS</formula1>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B26:B29 B48:B49 B37:B43">
      <formula1>SUP</formula1>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B36">
      <formula1>Payout</formula1>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B31">
      <formula1>MPI</formula1>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B59">
      <formula1>account_managers</formula1>
    </dataValidation>
    <dataValidation type="textLength" operator="lessThanOrEqual" allowBlank="1" showInputMessage="1" showErrorMessage="1" errorTitle="Descriptor length validation" error="The length must be maximum 23 symbols" promptTitle="Please input Descriptor " prompt="Please input Descriptor - UP to 23 Symbols" sqref="B22">
      <formula1>23</formula1>
    </dataValidation>
    <dataValidation type="textLength" operator="lessThanOrEqual" allowBlank="1" showInputMessage="1" showErrorMessage="1" errorTitle="City Field length validation" error="The length must be maximum 13 symbols" promptTitle="Please input City Field " prompt="Please input City Field - UP to 13 Symbols" sqref="B23">
      <formula1>13</formula1>
    </dataValidation>
    <dataValidation type="whole" allowBlank="1" showInputMessage="1" showErrorMessage="1" promptTitle="Please input Refund Timeframe" prompt="Please input Refund Timeframe - Between 1 and 365" sqref="B33">
      <formula1>1</formula1>
      <formula2>365</formula2>
    </dataValidation>
    <dataValidation type="whole" allowBlank="1" showInputMessage="1" showErrorMessage="1" promptTitle="Please input Authorize Timeframe" prompt="Please input Authorize Timeframe - Between 1 and 365" sqref="B34">
      <formula1>1</formula1>
      <formula2>365</formula2>
    </dataValidation>
    <dataValidation type="textLength" allowBlank="1" showInputMessage="1" showErrorMessage="1" errorTitle="MCC  Validation" error="The MCC must be 4 digits number" promptTitle="Please input MCC" prompt="The MCC should be exactly 4 digits long." sqref="B21">
      <formula1>4</formula1>
      <formula2>4</formula2>
    </dataValidation>
  </dataValidations>
  <hyperlinks>
    <hyperlink ref="B52" r:id="rId1"/>
    <hyperlink ref="B10" r:id="rId2"/>
  </hyperlinks>
  <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
  <pageSetup paperSize="9" orientation="portrait" r:id="rId3"/>
  <legacyDrawing r:id="rId4"/>
  <extLst>
    <ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}">
      <x14:dataValidations xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main" count="3">
        <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
          <x14:formula1>
            <xm:f>'DO NOT DELETE'!$D$24:$D$30</xm:f>
          </x14:formula1>
          <xm:sqref>B18</xm:sqref>
        </x14:dataValidation>
        <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
          <x14:formula1>
            <xm:f>'DO NOT DELETE'!$E$24:$E$27</xm:f>
          </x14:formula1>
          <xm:sqref>B45</xm:sqref>
        </x14:dataValidation>
        <x14:dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1">
          <x14:formula1>
            <xm:f>'DO NOT DELETE'!$N$1:$N$4</xm:f>
          </x14:formula1>
          <xm:sqref>B32</xm:sqref>
        </x14:dataValidation>
      </x14:dataValidations>
    </ext>
  </extLst>
</worksheet>

XML of Sheet AFTER xlsx.writeFile:

<worksheet>
  <dataValidations count="12">
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B19">
      <formula1>TRANS</formula1>
    </dataValidation>
    <dataValidation type="textLength" allowBlank="1" showInputMessage="1" promptTitle="Please input MCC" prompt="The MCC should be exactly 4 digits long." showErrorMessage="1" errorTitle="MCC  Validation" error="The MCC must be 4 digits number" sqref="B21">
      <formula1>4</formula1>
      <formula2>4</formula2>
    </dataValidation>
    <dataValidation type="textLength" operator="lessThanOrEqual" allowBlank="1" showInputMessage="1" promptTitle="Please input Descriptor " prompt="Please input Descriptor - UP to 23 Symbols" showErrorMessage="1" errorTitle="Descriptor length validation" error="The length must be maximum 23 symbols" sqref="B22">
      <formula1>23</formula1>
    </dataValidation>
    <dataValidation type="textLength" operator="lessThanOrEqual" allowBlank="1" showInputMessage="1" promptTitle="Please input City Field " prompt="Please input City Field - UP to 13 Symbols" showErrorMessage="1" errorTitle="City Field length validation" error="The length must be maximum 13 symbols" sqref="B23">
      <formula1>13</formula1>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B26:B29">
      <formula1>SUP</formula1>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B31">
      <formula1>MPI</formula1>
    </dataValidation>
    <dataValidation type="whole" allowBlank="1" showInputMessage="1" promptTitle="Please input Refund Timeframe" prompt="Please input Refund Timeframe - Between 1 and 365" showErrorMessage="1" sqref="B33">
      <formula1>1</formula1>
      <formula2>365</formula2>
    </dataValidation>
    <dataValidation type="whole" allowBlank="1" showInputMessage="1" promptTitle="Please input Authorize Timeframe" prompt="Please input Authorize Timeframe - Between 1 and 365" showErrorMessage="1" sqref="B34">
      <formula1>1</formula1>
      <formula2>365</formula2>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B36">
      <formula1>Payout</formula1>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B59">
      <formula1>account_managers</formula1>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B6">
      <formula1>ENV</formula1>
    </dataValidation>
    <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="B7">
      <formula1>INT</formula1>
    </dataValidation>
  </dataValidations>
  <hyperlinks>
    <hyperlink ref="B10" r:id="rId1"/>
    <hyperlink ref="B52" r:id="rId2"/>
  </hyperlinks>
  <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
  <pageSetup paperSize="9" orientation="portrait" horizontalDpi="4294967295" verticalDpi="4294967295" scale="100" fitToWidth="1" fitToHeight="1" firstPageNumber="1" useFirstPageNumber="1" copies="1"/>
  <legacyDrawing r:id="rId4"/>
</worksheet>

Expected result after xlsx.writeFile: Excel file to have the same Data Validations as the original xlsx file:

Actual result after xlsx.writeFile: Excel file Data Validation is broken for many of the Cells, like cell B37:

Thank you @Siemienik for looking on this issue.

[Siemienik]

@mpalavrov thank you, your example is pretty good because it shows that what I really don't understand yet.

Does anyone have any idea why some data validations is under worksheet (worksheet>dataValidations) and some are inside worksheet>extLst>ext>x14:dataValidations ?

by following this: [MS-XLSX]-120411.pdf they should be written into worksheet>extLst>ext>x14:dataValidations but as we see in Your example, it's not always true.

so, our library handles one of two ways for data validations, I need to make more research for now..

[florianLucas]

Hi,

Any news about this issue?

[Siemienik]

unfortunately not, I had to work more for my professional job last time. If it will be able, I want to spent more time on open-source projects,

[mpalavrov]

Hello all,

I hope someone found a fix on this or at least a workaround ?

Cheers, Milen

[mpalavrov]

@Siemienik, I Would like to tell you that I don't have this issue with the latest version of the exceljs. You can test it yourself if you want, but for me, it is solved.

[cduff]

@Siemienik, I Would like to tell you that I don't have this issue with the latest version of the exceljs. You can test it yourself if you want, but for me, it is solved.

@mpalavrov @Siemienik I can't see that this is fixed. I just tested again using my original attached sample project, against the latest version of exceljs (4.1.1), and the issue remains.

[adnaanbheda]

Is there any development on this ? I tested it today (4.1.1) and it's not working properly.

@Siemienik would love to help you out on this issue, I have a project that requires persistence of data validations !

[mpalavrov]

Hm, strange, at least for me the issue I had was solved with the newer versions :(

[matthewhampton]

Would this issue not be fixed by #1752? (DataValidation rules that reference another sheet appear to show up in x14:dataValidations in the extList rather)

[OB42]

Hi, any news?

[dbaldy]

This issue is fixed by #1752 , but now I need to update the MR because it's outdated with the master branch. Before I do that, could @Siemienik tell if that's worth and it will be reviewed and merged?

[JE-lee]

This issue is fixed by #1752 , but now I need to update the MR because it's outdated with the master branch. Before I do that, could @Siemienik tell if that's worth and it will be reviewed and merged?

hi, how is #1752 going?

[dbaldy]

hi, how is #1752 going?

Nowhere, nobody is responding.

[Siemienik]

Sorry, I'm overloaded recently

[honeylizard]

Any updates on this issue or the #1752 PR?