EPPlus throws DataValidations exception "Requested value 'date' was not found." when trying to save xlsm

Question

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

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.

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.

NOTE: apparently .xlsx files work just fine, but the the .xlsm format contain VBA macros that can't be saved in the .xlsx format, so the file-type cannot be converted.

an unanswered SO question also suggests that EPPlus doesn't work for xlsm files: stackoverflow.com/questions/6336803/… — Michael Paulukonis

Michael Paulukonis Michael Paulukonis · Accepted Answer · 2011-11-28T21:51:24

As usual, within sixty seconds of posting I find new information online.

There is a discussion at the EPPlus codeplex site that suggests that "there is no real support for [xlsm files] in the component."

The may be the source of the problem.

EPPlus throws DataValidations exception "Requested value 'date' was not found." when trying to save xlsm

3 Answers