8

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

Mike H
  • 387
  • 9
  • 25
  • @hellyale yes i am opposed to using Interop for various irrelevant reasons, trying to accomplish it with these packages – Mike H Jul 07 '17 at 20:55

2 Answers2

8

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.

M Y
  • 1,831
  • 4
  • 24
  • 52
  • Thanks for that. Easier than expected! Any tips on ExcelDataReader? Is it even possible using that package? – Mike H Jul 07 '17 at 20:57
  • 1
    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 – M Y Jul 07 '17 at 22:34
  • 1
    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 – Mike H Jul 07 '17 at 23:34
  • 1
    @MikeH just wanted to let you know ExcelDataReader now supports opening some password protected sheets – M Y Aug 03 '17 at 14:13
  • 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 – Mike H Aug 04 '17 at 15:41
  • 1
    I just had notifications set on the issue I origianally opened on github. So I saw when they reopened it and resolved it. – M Y Aug 04 '17 at 16:00
7

With ExcelDataReadr you can access your protected file like this:

var conf = new ExcelReaderConfiguration { Password = "yourPassword" };
excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelStream, conf);
shA.t
  • 16,580
  • 5
  • 54
  • 111