Full address name shouldn't be used for cell DataValidation?

[threadz]

trafficstars

I used a full address name such as..

using (var package = new ExcelPackage(excelFile))
{
  // add validation rules to ProblemMeta sheet
  ExcelWorksheet metaSheet = package.GetWorksheet("ProblemMetaSheet");
  if (metaSheet != null)
  { 
     var defaultValidation = metaSheet.DataValidations.AddIntegerValidation("ProblemMetaSheet!$C$2");
     ...

and it resulted in a data loss error when I opened the workbook in Excel. If I use "$C$2" as the cell address the workbook is created perfectly.

Is this a known issue?

[swmal]

Hi,

yes, this is a known issue. When a data validation refers to another worksheet than "its own" Excel requires it to be moved to an extLst element in the Office Open XML. I assume that Excel initally couldn't handle these kind of references and that support for it has been added in later versions, hence this "hack". We have fixed it for List- and Custom data validations so far and we have the rest of the validation types on our to-do list. I cannot say exactly when it will be fixed for Whole/Int data validations, but we will do it as soon as possible.

/Mats

[threadz]

Thanks for the update Mats.

[swmal]

This is somewhere between a bug and an enhancement, but I'll add the "bug" label to it and we will close this issue when it has been fixed.

[swmal]

Apologies, just saw that you are just using the full address but still on the same worksheet. In that case it should work to just remove the worksheet name from the address, i.e. metaSheet.DataValidations.AddIntegerValidation("$C$2") instead of metaSheet.DataValidations.AddIntegerValidation("ProblemMetaSheet!$C$2")

The issue I described happens only when you are refering to another worksheet.