Can we Update row and column of password protected excel sheet using java?

Question

NPOIFSFileSystem fs = new NPOIFSFileSystem(new File("C://Users//RK5026051//Downloads//500_Lanes.xls"));
        EncryptionInfo info = new EncryptionInfo(fs);
        Decryptor d = Decryptor.getInstance(info);
        if (d.verifyPassword("manh.com")) {
           HSSFWorkbook wb = new HSSFWorkbook(d.getDataStream(fs));
        } else {
           System.out.println("wrong password");
        }
        // TODO Auto-generated method stub
    }

Error--

Exception in thread "main" java.io.FileNotFoundException: no such entry: "EncryptionInfo", had: [_VBA_PROJECT_CUR, SummaryInformation, DocumentSummaryInformation, Workbook]
    at org.apache.poi.poifs.filesystem.DirectoryNode.getEntry(DirectoryNode.java:370)
    at org.apache.poi.poifs.filesystem.DirectoryNode.createDocumentInputStream(DirectoryNode.java:177)
    at org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:51)
    at org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:47)
    at auto.excel.main(excel.java:15)

I am downloading a file from a application using selenium , I need to update some fields in the excel file and then upload it again.**

I know the password of the excel file i am downloading , Now i need to write a code so that it updates specific rows and columns , But I am unable to any operation on that excel file and i guess that is because its password protected .

I have tried some answers on stack Overflow but i is not working. Also i did not get many materials on internet about this topic. Here is a sample code i tried

NPOIFSFileSystem fs = new NPOIFSFileSystem(new File("D://protectedfile.xlsx"));
EncryptionInfo info = new EncryptionInfo(fs);
Decryptor d = Decryptor.getInstance(info);
if (d.verifyPassword("password")) {
   XSSFWorkbook wb = new XSSFWorkbook(d.getDataStream(fs));
} else {
   // Password is wrong
}

possible duplicate :- http://stackoverflow.com/questions/25994772/read-password-protected-excel-file-xlsx-using-java — Shubham Jain, Mar 30 '16 at 10:23
you don't mention where your code fails?I mean, does `verifyPassword` block executed or `else`? — Sabir Khan, Mar 30 '16 at 10:24
What kind of file is it you're trying to open? A `.xls` or a `.xlsx`? (They're different!) — Gagravarr, Mar 30 '16 at 11:15
its a xls file. is there a way we can un-protect a sheet by java code? — rishav kumar, Mar 30 '16 at 11:23

score 1 · Answer 1 · answered Mar 30 '16 at 13:49

First up, I know it isn't trendy these days, but you should really really read the Apache POI documentation on reading protected files. From that, you'll see that you're trying to use the XLSX decryption method for a protected XLS file, which unsurprisingly won't work!

However, there's an even easier way to go - WorkbookFactory can take the password of your spreadsheet when opening

So, just change your code to the very simple:

Workbook wb = WorkbookFactory.create(new File("protected.xls"), "password", true);

That will open the workbook in read-only mode, using the supplied password to read it, and identifying the required type automatically for you

Can we Update row and column of password protected excel sheet using java?

1 Answers1