0

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.

Michael Paulukonis
  • 9,020
  • 5
  • 48
  • 68
  • an unanswered SO question also suggests that EPPlus doesn't work for xlsm files: http://stackoverflow.com/questions/6336803/library-for-c-sharp-to-read-excel-2007-xlsm-file – Michael Paulukonis Nov 28 '11 at 21:59

3 Answers3

1

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.

Michael Paulukonis
  • 9,020
  • 5
  • 48
  • 68
  • I haven't been able to get official confirmation of this this within the EPPlus forums, so I'm leaving this open a little longer. – Michael Paulukonis Nov 30 '11 at 18:05
  • I got a response from somebody at EPPlus that there was a bug, but the provided fix still didn't work (in fact, wouldn't even open at all, much less get to saving). https://epplus.codeplex.com/discussions/281085 – Michael Paulukonis Dec 08 '11 at 13:54
  • So I'm going with the de facto answer of "EPPlus doesn't [fully] support .xslm files." – Michael Paulukonis Dec 08 '11 at 17:01
0

Do you have the latest version of EPPlus?

They fixed this issue March of 2011. It had to do with ValidationTypes being case insensitive.

See: http://epplus.codeplex.com/workitem/13246

Edit: You can also try saving it as .xlsx if xlsm isn't supported.

Ed B
  • 6,028
  • 3
  • 26
  • 35
  • I'm using EPPlus 2.9.0.1 from (I believe) Tue May 31 2011 at 3:00 AM -- I just downloaded it on Wed, Nov 23. – Michael Paulukonis Nov 28 '11 at 22:04
  • Hmm..well that's definitely an enum error..same as you'd get with regular enums in C# when trying to find a match for an item that's not in the enum. Are you using 'date' anywhere in your code? I believe EPPlus uses 'datetime' for the Excel cells. – Ed B Nov 28 '11 at 22:10
  • Not using it anywhere. Can't even find it in the existing file (other than labels for fields where a date should be entered). – Michael Paulukonis Nov 28 '11 at 22:12
  • you can't save the VB project (macro-code that is additional to the .xlsm format) in the .xlsx format. – Michael Paulukonis Nov 29 '11 at 14:29
0

As Ed Wrote i have donwloaded the last version of EPPlus which is from Jan 31 2012,and it worked very well with its validation. The excel im reading and writting is a .xlsx format.

lorbrito
  • 58
  • 2
  • 10