Unable to open an EPPlus-created, protected workbook after Excel has opened it

Nov 18, 2011 at 5:37 PM

First and most importantly,... Thank You Jan!  EPPlus is amazing and I greatly appreciate your efforts creating and supporting it!  You have simplified a difficult task for me and before anything else, I wanted to say Thank you!

I'm using EPPlus to allow some laptop users to edit a SQL database table without providing them with a direct connection to the database.  They identify a set of rows which they want to change and  through EPPlus, my software creates an excel workbook and emails it to them.  The workbook that is created is protected as are each of the worksheets it contains.  It is an xlsm file created using an unprotected xlsm file as a template which allows it to be macro enabled with data validations and interactive instruction displays throughout.  The users are only allowed to modify the values of a few columns and they do not have the ability to add or delete worksheets, columns or in most cases, rows.  On one worksheet, a macro-button is available to pop up a dialog box through which users can safely add new rows.  Over-all, the created spreadsheet provided these users with an easy and realtime-validated interface through which they can modify specific parts of rows they selected, all in a disconnected manner.  Finally, protected nature of the workbook and worksheets assures me that when the spreadsheet gets emailed back to my program, only its contents (and not its structure) will have changed.  That makes parsing out the user's changes and posting those changes to the SQL database table easy.  ...and none of it would be possible without EPPlus.

So again... Thanks!


Now on to the reason for this forum entry...

Given a workbook-protected EPPlus-created xlsm file which has previously been opened by Microsoft Office Excel 2007 without a password; has received modifications to unprotected cells and been subsequently saved to disk.
The following code...

FileInfo newFile = new FileInfo(strSourceFileWithPath);
ExcelPackage package = null;
try
{
    package = new ExcelPackage(newFile);
}
catch(Exception ex1)
{
    error_message += "Open Without Password Result [" + ex1.Message + "]\n";
    try
    {
        package = new ExcelPackage(newFile, true, "Password");
    }
    catch(Exception ex2)
    {
        error_message += "Open With Password Result [" + ex2.Message + "]\n\n";
    }
}

...produces the following error_message contents...

Open Without Password Result [Can not open the package. Package is an OLE compound document. If this is an encrypted package, please supply the password]
Open With Password Result [Invalid password]

...If I then open the file again with Microsoft Office Excel 2007 and use the Unprotect WorkBook button on the Review tab to remove the workbook protection, the code above will open the spreadsheet without firing off any exceptions.

Any ideas what is causing this or what I can do avoid having to manually open each file in Excel and manually unprotect it?

Thanks in advance for any assistance!

 

Dec 12, 2011 at 9:47 PM

I would like to also find out what the resolution to this issue is. I will be using the component to generate scoring files from a template. Those scoring files are distributed to users, and then the users upload the files back into my system. At this point, I open the files and read in the data. These files have to be protected, workbook and worksheet(s) since there are hidden columns with identifier information. We also do not want the users to sort the rows, or any other manipulation. So the files that get imported back into the system will be protected workbooks and worksheet(s).

Can I open the file and read in data from those protected sheets? Currently we use excel automation, but we would like to get away from this since it requires an installation of Excel 2003 on the server.

Thank You!

Dec 13, 2011 at 11:53 AM

Hi Gordonp,

Sorry your also experiencing this difficulty.

I never found a resolution for it, so every time one of my remote users mails me back a workbook, I have to open it in Excel (using the password that only I know) and turn off the protection.  I then save the workbook and move it to a directory where a filesystemwatcher detects its arrival and using epplus, processes it.  My current solution is all manual right now and like yourself I am hoping there will someday be a solution.

It sounds like your excel automation solution may be superior to what I've done for the import portion of the project, but I still strongly recommend using EPPlus for the file generation portion of your project.  With the exception of this one unresolved issue, I am very happy with the library.

Best of Luck with your project!  Drop me an email if you find a solution to our problem!

- HenryETaylor

Editor
Dec 13, 2011 at 12:15 PM

I have a similar process and I never got this error. All my files are protected but doesnt have any macros.

 

 

Dec 13, 2011 at 2:05 PM

Hi Mrxrsd,

Are your users opening your epplus-created protected workbook in between when you create them and when you later attempt to open and read them?
If so, what spreadsheet application are they using?

When I just create the sheet (using a template with macros and an epplus-based program) and then import them with another epplus-based program, everything works.

Only when Excel 2010 or Excel 2007 opens the workbook and then saves it back to disk does the import program have trouble.

Also, are you protecting just the workbook, the individual spreadsheets within the workbook, or both?  I'm doing both.

When I started this thread a couple months ago, I had plans to create an extremely simplified version of my creator and importer programs and publish the source here so that others could replicate my finding and maybe identify what I am doing wrong.  Unfortunately, the project got sidelined so I am handling what little traffic it is generating is being handled manually by yours truely.  I never got back here with the simplified source code.  I hope to pursue this again after the holidays.

Thanks for your interest and let me know on the questions above.

Happy Holidays,

- HenryETaylor

 

Editor
Dec 14, 2011 at 10:52 AM

Yes, we are using Excel 2007/2010. I'm just protecting the individual sheets. Did you try to create your sheet without template-based?

Dec 14, 2011 at 11:53 AM

No, in my case I couldn't... I needed the macros and an xlsm template is the only way to make that happen.

What I haven't tried is not protecting the workbook.  I wanted to keep them from adding sheets but what would be the big deal if they did add sheets; it wouldn't affect them import process.

Thanks for helping me think about this.  I'll give that a try and get back here with the results... and that simplified test code that I described earlier.

Thanks again,

- HenryETaylor

Editor
Dec 14, 2011 at 12:06 PM
Edited Dec 14, 2011 at 12:06 PM

I know, but if you try without macro and works, you will know where the problem is. Maybe you can remove them before open with epplus like Jan said here:

http://epplus.codeplex.com/discussions/282220

Dec 14, 2011 at 12:15 PM

Not a bad idea.   Something to try.  

If removing the macros works then its not the locked workbook that's causing the grief.

If removing the locked workbook as I was already planning to do, works then it would prove that to be the cause.

If neither works alone then it is the combination of the two.

I'll get back to you.

Thanks Again

- HenryETaylor

Dec 14, 2011 at 5:44 PM

Thanks for your reply and information. Based on some of the other posts on this thread, I think I might try to get the import process to work. All I want to do is unlock the sheets so I can then use OLEDB to extract the data. If this does not work, then maybe I'll just try to extract the data with EPPlus. The only issue before I test is that I only have excel 2003 files that were created through automation. I could probably dummy one up quickly, but that would not give me the example from end to end as you have described. (Creating with EPPlus, Opening in excel, adding data to the file in excel, saving, and then importing with EPPlus)

Still, I'll let you know what happens. Also, I have thought about looking into the source code to see if I can figure out a way to add a method that will unlock sheets.

Thanks

Jan 16, 2013 at 8:46 AM

I have the same problem.

the problem is connected to Internet Explorer uploaded to server (i dont have the problem under Chrome or Firefox).