Bug 58616 - Excel 2007 unable to read the Excel file content. But the same file working fine after manual open and re-save the excel file.
Summary: Excel 2007 unable to read the Excel file content. But the same file working f...
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.13-FINAL
Hardware: PC All
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-11-16 21:10 UTC by Prakash
Modified: 2016-07-24 19:36 UTC (History)
0 users



Attachments
Attached the Standalone Java code to display the sheet name for the 2 different template file names. (217.34 KB, application/zip)
2015-11-16 21:10 UTC, Prakash
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Prakash 2015-11-16 21:10:44 UTC
Created attachment 33278 [details]
Attached the Standalone Java code to display the sheet name for the 2 different template file names.

Team,

I'm trying to read the excel 2007 file (available in the attachment B4Save.xlsx), but its giving the null pointer exception in the  Decryptor.getDataStream . 

But the same file can open and resave(Not changing anything and save with the same/ different filename AfterSave.xlsx) in the windows system was working file in the attached java code.

To reproduce this issue swap the comments in the java code line 17 and 18 with the file placed in the appropriate folders.

Used the latest stable jar file poi-bin-3.13-20150929.zip
http://mirrors.sonic.net/apache/poi/release/bin/poi-bin-3.13-20150929.zip

Please do the needful from your end and let me know if you requires any more details related to this issue.

The issue was given below:
java.lang.NullPointerException
	at org.apache.poi.poifs.crypt.CryptoFunctions.getCipher(CryptoFunctions.java:202)
	at org.apache.poi.poifs.crypt.CryptoFunctions.getCipher(CryptoFunctions.java:182)
	at org.apache.poi.poifs.crypt.standard.StandardDecryptor.getCipher(StandardDecryptor.java:122)
	at org.apache.poi.poifs.crypt.standard.StandardDecryptor.getDataStream(StandardDecryptor.java:135)
	at org.apache.poi.poifs.crypt.Decryptor.getDataStream(Decryptor.java:92)
	at StandAloneExcel.main(StandAloneExcel.java:36)
Comment 1 Dominik Stadler 2015-12-12 13:18:43 UTC
I could make it work by adding the following BEFORE the call to dfs.getDataStream():

        dec.verifyPassword(null);

Can you verify if this makes it work for you as well?
Comment 2 Prakash 2015-12-14 16:07:11 UTC
Hi Dominik,

Thanks for your help. 

By adding the dec.verifyPassword(null); before the dfs.getDataStream(); resolves that particular line issue. But its giving the "error in opening zip file"  wb = new XSSFWorkbook(fileLocation);.

Can you please help to resolve this issue. The expected output for this jave code to read that Excel sheet and print the SheetName.

Please find below the details error message after adding the line dec.verifyPassword(null); as suggested by you in the previous comments.


==================================================================================
FileLocation:C:\ExcelProb\B4Save.xlsx
org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: 'C:\ExcelProb\B4Save.xlsx'
	at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:112)
	at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:225)
	at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:187)
	at org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:84)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:318)
	at StandAloneExcel.main(StandAloneExcel.java:40)
Caused by: java.util.zip.ZipException: error in opening zip file
	at java.util.zip.ZipFile.open(Native Method)
	at java.util.zip.ZipFile.<init>(Unknown Source)
	at java.util.zip.ZipFile.<init>(Unknown Source)
	at java.util.zip.ZipFile.<init>(Unknown Source)
	at org.apache.poi.openxml4j.util.ZipSecureFile.<init>(ZipSecureFile.java:105)
	at org.apache.poi.openxml4j.opc.internal.ZipHelper.openZipFile(ZipHelper.java:175)
	at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:110)
	... 5 more


====================================================================================
Java code
====================================================================================
import java.io.FileInputStream;
import java.io.FileNotFoundException;

import org.apache.poi.poifs.crypt.Decryptor;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class StandAloneExcel {

	/**
	 * @param args
	 */
//	 public static String excelfilename = "C:\\ExcelProb\\AfterSave.xlsx";
	 public static String excelfilename = "C:\\ExcelProb\\B4Save.xlsx";
	
	
	public static void main(String[] args) {
		
		XSSFWorkbook wb;
	    XSSFSheet sheet;
		POIFSFileSystem pfs;
		String fileLocation;
	
		fileLocation=excelfilename;
		System.out.println("FileLocation:"+fileLocation);

		try{
			
                pfs = new POIFSFileSystem(new FileInputStream(fileLocation));                
                EncryptionInfo info = new EncryptionInfo(pfs);			   
			    Decryptor dec = Decryptor.getInstance(info);	
			    
			    dec.verifyPassword(null);
			    dec.getDataStream(pfs);
			    
                wb = new XSSFWorkbook(fileLocation);                
	            sheet = wb.getSheetAt(0);	           
	            System.out.println("Sheet  Name"+ sheet.getSheetName());

		}catch(OfficeXmlFileException e){
			try{
				e.printStackTrace();
			wb = new XSSFWorkbook(new FileInputStream(fileLocation));		   
			sheet = wb.getSheetAt(0);			
			 System.out.println("Sheet  Name"+ sheet.getSheetName());

			}catch(FileNotFoundException ex)
		    {
				ex.printStackTrace();
				System.out.println(" Exception:" + ex);
				
			
		    }
			catch(Exception ex){
				ex.printStackTrace();
            }
		}catch(Exception e){
			e.printStackTrace();
		
		}

	}

}

====================================================================================
Comment 3 Dominik Stadler 2015-12-15 13:13:31 UTC
This exception indicates that the file that you try to process is not an actual xlsx-file. The XLSX-Formatted files are actually zip-files with XML-files inside. Can you check if this is the case? Maybe you actually have an .xls (older Excel file format) file that has an incorrect file extension?
Comment 4 Prakash 2015-12-16 20:17:21 UTC
Hi Dominik,

Thanks for the analysis. I also tries to open it in the ZIP files extractor it doesn't extract. Its giving the file damaged/ corrupted. 
For the noraml working version of the apache POI extract the xlsx file by the zip extractor and read the content.

Now the unknown areas was the same file opened by the Windows office and is their any way Apache POI can support to open this file problematically.

Thanks,
Prakash
Comment 5 Dominik Stadler 2015-12-27 08:55:27 UTC
Sorry but I do not really understand your last comment, can you describe again what works and what not? Maybe by providing a sample XLSX file and the java code that you use to test it?
Comment 6 Dominik Stadler 2015-12-30 20:32:26 UTC
FYI, the NullPointerException which was initially reported here should be fixed via r1722406
Comment 7 Dominik Stadler 2015-12-30 20:33:39 UTC
Sorry, the correct SVN revision is r1722407
Comment 8 Dominik Stadler 2016-07-24 19:36:54 UTC
I think the original issues reported here have been fixed since some time and there was no response on the last questions for a long time. Please report new issues if there is still anything that looks like a bug.