exceljs: [BUG] Template data validation lost

🐛 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.

About this issue

Original URL
State: open
Created 4 years ago
Comments: 20 (7 by maintainers)

Most upvoted comments

So todo list:

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

Siemienik on May 26, 2020

Any updates on this issue or the #1752 PR?

honeylizard on Jun 9, 2023

Hi,

Any news about this issue?

florianLucas on May 13, 2020

@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…

Siemienik on Mar 31, 2020

@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 on Mar 28, 2020

@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>

Alanscut on Mar 28, 2020

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 !

adnaanbheda on Aug 28, 2020

@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 on Jul 5, 2020

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 on Mar 30, 2020