CODEWITHSUNDEEP
javaapache-poi
JavaTweets
JavaTweets

Reputation: 91

Read xlsx file using POIFSFileSystem

I need to unprotect a protected xlsx file.e.g Book1.xlsx Below code runs fine for the first time, Reads Book1.xlsx, decrypt it and again write it to the same filename. 

public static void unprotectXLSXSheet(String fileName, String password) {
        try{

            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileName));
            EncryptionInfo info = new EncryptionInfo(fs);
            Decryptor d = Decryptor.getInstance(info);
            d.verifyPassword(password);
            InputStream is = d.getDataStream(fs);
            System.out.println(is.available());
            XSSFWorkbook wb = new XSSFWorkbook(OPCPackage.open(is));
            FileOutputStream fileOut;
            fileOut = new FileOutputStream(fileName);
            wb.write(fileOut);
             fileOut.flush();
            fileOut.close();
            }catch(FileNotFoundException ex){
                ex.printStackTrace();
            }catch(IOException ex){
                ex.printStackTrace();

But when the same code tries to access the newly created unprotected Book1.xlsx(or anyother unprotected xlsx file) it fails and showing 

Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:131)
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:104)
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:138)
    at com.wolseley.Excel.TestMainDummy.unprotectXLSXSheet(TestMainDummy.java:113)
    at com.wolseley.Excel.TestMainDummy.main(TestMainDummy.java:52)

i need help in reading xlsx file and also unlock it using password, as done above.

Upvotes: 5

Views: 17268

Answers (2)

jmn
jmn

Reputation: 564

Basically the following line of code doesn't work for Office 2007+ XML documents:

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileName));

So you'll first need to check the header in the input stream whether it's supported by calling this:

POIFSFileSystem.hasPOIFSHeader(is)

and only decrypting if the above returns true. The hasPOIFSHeader method requires an input stream that supports mark/reset, so check that as well and wrap it in a PushbackInputStream if not.

Putting it all together then becomes something like this:

public static void unprotectXLSXSheet(String fileName, String password) throws Exception {
    InputStream is = null;
    FileOutputStream fileOut = null;

    try {
        is = new FileInputStream(fileName);
        if (!is.markSupported()) {
            is = new PushbackInputStream(is, 8);
        }

        if (POIFSFileSystem.hasPOIFSHeader(is)) {
            POIFSFileSystem fs = new POIFSFileSystem(is);
            EncryptionInfo info = new EncryptionInfo(fs);
            Decryptor d = Decryptor.getInstance(info);
            d.verifyPassword(password);
            is = d.getDataStream(fs);
        }

        System.out.println(is.available());
        XSSFWorkbook wb = new XSSFWorkbook(OPCPackage.open(is));
        fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        fileOut.flush();
    } finally {
        if (is != null) {
            is.close();
        }
        if (fileOut != null) {
            fileOut.close();
        }
    }
}

Upvotes: 6

Shishir Kumar
Shishir Kumar

Reputation: 8191

Below old Stack Overflow answer may be help you out from this.

Reading property sets from Office 2007+ documents with java poi

The class you'll want is POIXMLProperties, something like:

OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));
POIXMLProperties props = new POIXMLProperties(pkg);
System.out.println("The title is " + props.getCorePart().getTitle());

From POIXMLProperties you can get access to all the built-in properties, and the custom ones too!

Upvotes: 0

Related Questions