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

DataValidations exception "Requested value 'date' was not found." when trying to save package

Nov 28, 2011 at 7:21 PM

I'm attempting to make changes to a customer-supplied file (a C# application will fill with data from a web-form and submit to a CMS).

But on package.Save(name) -- or packagae.SaveAs(name) -- an exception is thrown by the DataValidations collection:

2011-11-28 14:17:51,380 [9] ERROR app.ExcelConverter - System.ArgumentException: Requested value 'date' was not found.
   at System.Enum.EnumResult.SetFailure(ParseFailureKind failure, String failureMessageID, Object failureMessageFormatArgument)
   at System.Enum.TryParseEnum(Type enumType, String value, Boolean ignoreCase, EnumResult& parseResult)
   at System.Enum.Parse(Type enumType, String value, Boolean ignoreCase)
   at OfficeOpenXml.DataValidation.ExcelDataValidationCollection..ctor(ExcelWorksheet worksheet)
   at OfficeOpenXml.ExcelWorksheet.get_DataValidations()
   at FItoExcelCore.ExcelConverter.RunSample(String filePath) in \path\to\class.cs:line 32

Even attempting worksheet.DataValidations.Clear() throws the same exception, and  this is without any changes to the file:

            try
            {
                using (ExcelPackage package = new ExcelPackage(output))
                {
                    package.SaveAs(output);
                }
            }
            catch (Exception ex)
            {
                _log.Error(ex.ToString());
            }

 

 

I do not have this behavior in an excel file that I create from scratch, but I need to use the supplied file.

 

Nov 28, 2011 at 8:07 PM

I don't know if it's significant, but I'm working in the .Net 4 Framework, and it's an 1.01 MB .xlsm file. The file saves/saves-as fine (even with new data) from within Excel.

Nov 28, 2011 at 8:48 PM

I just noticed another thread that suggests that "there is no real support for [xlsm files] in the component."

 

Is that the source of my problem?

Nov 30, 2011 at 5:05 PM

Can anybody confirm that xlsm files are or are not supported by EPPlus?

Developer
Nov 30, 2011 at 8:36 PM

I think this is a bug related to data validations and I will have a look at it... EPPlus has no support for macros, but I dont think that opening a xlsm file should cause a crash.

What kinds of data validations exists in your xlsm file before you try to open it with EPPlus?

Developer
Nov 30, 2011 at 8:53 PM

This was a bug and it is now fixed in the latest source.  Until the next release you will have to download and build the EPPlus source code locally.

/Mats

Dec 1, 2011 at 2:07 PM

Thanks, I built the latest out of SVN this morning, but now I'm getting a different error, and sooner -- when I attempt to open a worksheet.

Before, I could open sheets and read data, and the error only appeared during Save() or SaveAs() -- now I can't even read anything. :-(

2011-12-01 10:01:58,038 [10] ERROR CoreTester.Program - System.UriFormatException: Invalid URI: The format of the URI could not be determined.
   at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)
   at System.Uri..ctor(String uriString)
   at OfficeOpenXml.ExcelHyperLink..ctor(String uriString) in D:\dev\EPPlus-dev\trunk\ExcelPackage\ExcelHyperLink.cs:line 45
   at OfficeOpenXml.ExcelWorksheet.LoadHyperLinks(XmlTextReader xr) in D:\dev\EPPlus-dev\trunk\ExcelPackage\ExcelWorksheet.cs:line 819
   at OfficeOpenXml.ExcelWorksheet.CreateXml() in D:\dev\EPPlus-dev\trunk\ExcelPackage\ExcelWorksheet.cs:line 579
   at OfficeOpenXml.ExcelWorksheet..ctor(XmlNamespaceManager ns, ExcelPackage excelPackage, String relID, Uri uriWorksheet, String sheetName, Int32 sheetID, Int32 positionID, eWorkSheetHidden hide) in D:\dev\EPPlus-dev\trunk\ExcelPackage\ExcelWorksheet.cs:line 184
   at OfficeOpenXml.ExcelWorksheets..ctor(ExcelPackage pck, XmlNamespaceManager nsm, XmlNode topNode) in D:\dev\EPPlus-dev\trunk\ExcelPackage\ExcelWorksheets.cs:line 93
   at OfficeOpenXml.ExcelWorkbook.get_Worksheets() in D:\dev\EPPlus-dev\trunk\ExcelPackage\ExcelWorkbook.cs:line 252
   at OfficeOpenXml.ExcelWorkbook.GetDefinedNames() in D:\dev\EPPlus-dev\trunk\ExcelPackage\ExcelWorkbook.cs:line 160
   at OfficeOpenXml.ExcelPackage.get_Workbook() in D:\dev\EPPlus-dev\trunk\ExcelPackage\ExcelPackage.cs:line 560
   at FormItConverter.EpPlusTest.RunSample(String input) in D:\projects\Axis_Financial\FormItToExcel\FItoExcelCore\EpPlusTest.cs:line 38

Dec 1, 2011 at 2:32 PM
reference to code was skipped, before:

                using (ExcelPackage package = new ExcelPackage(output))                 {                     // get the first worksheet in the workbook                     ExcelWorksheet worksheet = package.Workbook.Worksheets[1];                     package.Save();                 }

Reducing the code as shown in the original post (above) to just a package.Save() request results in the same error,
as save ultimately goes through GetDefinedNames and chokes on the Worksheets reference.