Access Protected Excel File with ExcelDataReader and Epplus

Question

title pretty much says it all. Looking for a way to access a password protected excel file with ExcelDataReader and Epplus, can't find a proper answer.

If using ExcelDataReader, my code looks like

                excelStream = File.Open(excelFilePath, FileMode.Open, FileAccess.Read);
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelStream);
                excelDataSet = excelReader.AsDataSet();

If using EPPlus my connection code looks like

            excelPackage = new ExcelPackage(new FileInfo(excelFilePath));
            excelWorkbook = excelPackage.Workbook;
            excelSheet = excelWorkbook.Worksheets[1];

EPPlus has some protection related methods but i can't figure out how to use them. ExcelDataReader doesnt seem to have any protection related methods.

Any tips appreciated, thanks.

EDIT: I do already know the password

Historical

@hellyale yes i am opposed to using Interop for various irrelevant reasons, trying to accomplish it with these packages – Historical

Answer 1

With EPPlus you can use

excelPackage = new ExcelPackage(new FileInfo(excelFilePath), "mypassword");

ExcelDataReader now supports opening password protected sheets. I opened an issue on their GitHub asking if they have such support and received a response saying that they do not, but after sometime they added support for some password methods. Details on the password methods they still do not support are in the link.

Gould

Thanks for that. Easier than expected! Any tips on ExcelDataReader? Is it even possible using that package? – Historical

Hey @MikeH a maintainer of ExcelDataReader responded saying their library does not support the desired functionality. Sorry for the bad news. If this answer helped you please consider upvoting and accepting it as the answer. Thanks – Gould

hey man is there a way to private message on this site? I know its not proper to respond this way but i wanted to say thank you a bunch for going out of your way to help me out, its much appreciated – Historical

@MikeH just wanted to let you know ExcelDataReader now supports opening some password protected sheets – Gould

How'd you find that out, did they contact you? I appreciate keeping me posted! I wish it was the case a month ago haha i already switched everything over to EPPlus unfortunately – Historical

I just had notifications set on the issue I origianally opened on github. So I saw when they reopened it and resolved it. – Gould

Answer 2

With ExcelDataReadr you can access your protected file like this:

var conf = new ExcelReaderConfiguration { Password = "yourPassword" };
excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelStream, conf);

June