Problem occurred while reading excel file (.xlsx) using poi

[Prabes Suwal]

I am stuck at a point when I am making a program to read an excel file (.xslx). I am usin POI for a web project.

Following libraries are used:
poi-3.12.jar
commons-codec-1.9.jar
poi-ooxml-3.11.jar
poi-ooxml-schemas-3.11.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar


Code is as follows:

<%@ page import="org.apache.poi.ss.usermodel.Cell" %>
<%@ page import="org.apache.poi.ss.usermodel.CellStyle" %>
<%@ page import="org.apache.poi.ss.usermodel.DataFormat" %>
<%@ page import="org.apache.poi.ss.usermodel.Row" %>
<%@ page import="org.apache.poi.ss.usermodel.Sheet" %>
<%@ page import="org.apache.poi.ss.usermodel.Workbook" %>

<%@ page import="org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFRow" %>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import="org.apache.poi.*" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell" %>

<%@ page import="org.apache.poi.util.DocumentHelper" %>
<%@ page import="org.apache.poi.openxml4j.opc.internal.*" %


String fileName = path + "/" + file.getFileName(); 
// final String  fileName = "E:/basanta/New folder/WORKBOOK.xlsx";
//String fileName = "E:/basanta/New folder/WORKBOOK.xlsx";
System.out.println("filename: "+fileName);

try {
	
	FileInputStream fis = new FileInputStream(new File(fileName));
	 System.out.println("inside file input"+fis);

	//Get the workbook instance for XLSx file 
	XSSFWorkbook workbook = new XSSFWorkbook(fis);
	 System.out.println("inside workbook"+fis);
	//Get first sheet from the workbook
	XSSFSheet sheet = workbook.getSheetAt(0);
	
	//Iterate through each rows from first sheet
	Iterator<Row> rowIterator = sheet.iterator();
	while(rowIterator.hasNext()) {
		Row row = rowIterator.next();
		
		//For each row, iterate through each columns
		Iterator<Cell> cellIterator = row.cellIterator();
		while(cellIterator.hasNext()) {
			
			Cell cell = cellIterator.next();
			
			switch(cell.getCellType()) {
				case Cell.CELL_TYPE_BOOLEAN:
					System.out.print(cell.getBooleanCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_NUMERIC:
					System.out.print(cell.getNumericCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_STRING:
					System.out.print(cell.getStringCellValue() + "\t\t");
					break;
			}
		}
		System.out.println("the last");
	}
	
	workbook.close();
}

AND the error is:

exception

javax.servlet.ServletException: javax.xml.parsers.DocumentBuilderFactory.setFeature(Ljava/lang/String;Z)V
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:825)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:758)
org.apache.jsp.wfp.doUploadWfp_jsp._jspService(doUploadWfp_jsp.java:275)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
root cause

java.lang.NoSuchMethodError: javax.xml.parsers.DocumentBuilderFactory.setFeature(Ljava/lang/String;Z)V
org.apache.poi.util.DocumentHelper.trySetSAXFeature(DocumentHelper.java:62)
org.apache.poi.util.DocumentHelper.<clinit>(DocumentHelper.java:56)
org.apache.poi.openxml4j.opc.internal.ContentTypeManager.parseContentTypesFile(ContentTypeManager.java:376)
org.apache.poi.openxml4j.opc.internal.ContentTypeManager.<init>(ContentTypeManager.java:102)
org.apache.poi.openxml4j.opc.internal.ZipContentTypeManager.<init>(ZipContentTypeManager.java:54)
org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:192)
org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:684)
org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:275)
org.apache.poi.util.PackageHelper.open(PackageHelper.java:37)
org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:266)
org.apache.jsp.wfp.doUploadWfp_jsp._jspService(doUploadWfp_jsp.java:216)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

[Dave Tolls]

First off, do not do this sort of thing in a JSP page.
Tis should be done from a servlet, probably in a service.
You should not have Java code in a JSP page.

That aside, something in those jars is expecting the setFeature method to be available and one of those jar files possibly has an older version?
My guess is the stax-api one.
What happens if you remove that from your libs.

[Tim Moores]

+1 about not doing anything like this in a JSP.  

Mixing and matching library versions is a bad idea. poi-3.12.jar is not designed to work with poi-ooxml-3.11.jar or poi-ooxml-schemas-3.11.jar. Get a fresh download of POI (preferably 3.15, not 3.12) and use the libraries that come with it.

[Prabes Suwal]

Tim Moores wrote:+1 about not doing anything like this in a JSP.  

Mixing and matching library versions is a bad idea. poi-3.12.jar is not designed to work with poi-ooxml-3.11.jar or poi-ooxml-schemas-3.11.jar. Get a fresh download of POI (preferably 3.15, not 3.12) and use the libraries that come with it.

Can you please provide me the url or any source to download lates POI. It would be a great help. Thank you

[Prabes Suwal]

Dave Tolls wrote:First off, do not do this sort of thing in a JSP page.
Tis should be done from a servlet, probably in a service.
You should not have Java code in a JSP page.

That aside, something in those jars is expecting the setFeature method to be available and one of those jar files possibly has an older version?
My guess is the stax-api one.
What happens if you remove that from your libs.

Thank you for your concern. Now i have build it on a servlet and also changed the stax-api but still facing the same problem.

[Prabes Suwal]

Prabes Suwal wrote:I am stuck at a point when I am making a program to read an excel file (.xslx). I am usin POI for a web project.

Following libraries are used:
poi-3.12.jar
commons-codec-1.9.jar
poi-ooxml-3.11.jar
poi-ooxml-schemas-3.11.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar


Code is as follows:

<%@ page import="org.apache.poi.ss.usermodel.Cell" %>
<%@ page import="org.apache.poi.ss.usermodel.CellStyle" %>
<%@ page import="org.apache.poi.ss.usermodel.DataFormat" %>
<%@ page import="org.apache.poi.ss.usermodel.Row" %>
<%@ page import="org.apache.poi.ss.usermodel.Sheet" %>
<%@ page import="org.apache.poi.ss.usermodel.Workbook" %>

<%@ page import="org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFRow" %>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import="org.apache.poi.*" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell" %>

<%@ page import="org.apache.poi.util.DocumentHelper" %>
<%@ page import="org.apache.poi.openxml4j.opc.internal.*" %


String fileName = path + "/" + file.getFileName(); 
// final String  fileName = "E:/basanta/New folder/WORKBOOK.xlsx";
//String fileName = "E:/basanta/New folder/WORKBOOK.xlsx";
System.out.println("filename: "+fileName);

try {
	
	FileInputStream fis = new FileInputStream(new File(fileName));
	 System.out.println("inside file input"+fis);

	//Get the workbook instance for XLSx file 
	XSSFWorkbook workbook = new XSSFWorkbook(fis);
	 System.out.println("inside workbook"+fis);
	//Get first sheet from the workbook
	XSSFSheet sheet = workbook.getSheetAt(0);
	
	//Iterate through each rows from first sheet
	Iterator<Row> rowIterator = sheet.iterator();
	while(rowIterator.hasNext()) {
		Row row = rowIterator.next();
		
		//For each row, iterate through each columns
		Iterator<Cell> cellIterator = row.cellIterator();
		while(cellIterator.hasNext()) {
			
			Cell cell = cellIterator.next();
			
			switch(cell.getCellType()) {
				case Cell.CELL_TYPE_BOOLEAN:
					System.out.print(cell.getBooleanCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_NUMERIC:
					System.out.print(cell.getNumericCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_STRING:
					System.out.print(cell.getStringCellValue() + "\t\t");
					break;
			}
		}
		System.out.println("the last");
	}
	
	workbook.close();
}

AND the error is:

exception

javax.servlet.ServletException: javax.xml.parsers.DocumentBuilderFactory.setFeature(Ljava/lang/String;Z)V
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:825)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:758)
org.apache.jsp.wfp.doUploadWfp_jsp._jspService(doUploadWfp_jsp.java:275)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
root cause

java.lang.NoSuchMethodError: javax.xml.parsers.DocumentBuilderFactory.setFeature(Ljava/lang/String;Z)V
org.apache.poi.util.DocumentHelper.trySetSAXFeature(DocumentHelper.java:62)
org.apache.poi.util.DocumentHelper.<clinit>(DocumentHelper.java:56)
org.apache.poi.openxml4j.opc.internal.ContentTypeManager.parseContentTypesFile(ContentTypeManager.java:376)
org.apache.poi.openxml4j.opc.internal.ContentTypeManager.<init>(ContentTypeManager.java:102)
org.apache.poi.openxml4j.opc.internal.ZipContentTypeManager.<init>(ZipContentTypeManager.java:54)
org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:192)
org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:684)
org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:275)
org.apache.poi.util.PackageHelper.open(PackageHelper.java:37)
org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:266)
org.apache.jsp.wfp.doUploadWfp_jsp._jspService(doUploadWfp_jsp.java:216)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

I have used the above codes in a servlet and try to run in a  main method in a static way and it is showing the output in a console. But when i Try to import the a .xslx file through the browser and define the path of that file, then it is showing this error.

[Tim Moores]

NoSuchMethodErrors are typical consequences of using incompatible library versions alongside each other. Make sure, you have one version of each involved jar file in the classpath, and that they all belong to the same POI version.

As to downloading, the first result when searching for "download poi" goes directly to the download page.

[Dave Tolls]

The other possibility (and I just pulled this as the first item off the Apache POI FAQ) is that there is an old version of POI on your classpath.

Of course, the simplest explanation is, as Tim pointed out, that your jar files don't match up.
If you're on 3.12 then your OOXML jars should also be on 3.12.