8 Replies Latest reply on Apr 10, 2009 5:59 AM by 807588

    how to read data from .xlsx file using poi 3.5

    807588
      hi

      i am reading data from an .xlsx file and i am using jsp. i have importes the following files C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\poi-3.5-beta5-20090219.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\poi-ooxml-3.5-beta5-20090219.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\openxml4j-bin-beta-080728.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\xbean.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\xbean_xpath.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\xmlbeans-qname.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\xmlpublic.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\ooxml-schemas-1.0.jar.

      Now i am just trying to open a new Work Book and read the contents.

      when i run the program it's showing the following error.
      java.lang.NoClassDefFoundError
           org.apache.poi.xssf.usermodel.XSSFWorkbook.newPackage(XSSFWorkbook.java:245)
           org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:145)
           org.apache.jsp.read_jsp._jspService(org.apache.jsp.read_jsp:69)
           org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
           javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
           org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
           org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
           org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
           javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

      please anyone help me to correct the error. Also please send code to read the data if you have.
      My code is
      <%@page import="org.apache.poi.xssf.usermodel.XSSFWorkbook"%>
      <%@page import="org.apache.poi.xssf.usermodel.XSSFSheet"%>
      <%@page import="org.apache.poi.ss.usermodel.*"%>
      <%@page import="org.apache.poi.ss.util.CellRangeAddress"%>
      <%@page import="org.apache.poi.xssf.usermodel.XSSFRow"%>
      <%@page import="org.apache.poi.xssf.usermodel.XSSFCell"%>
      <%@page import="org.apache.poi.openxml4j.opc.Package"%>
      <%@page import="org.apache.xmlbeans.*"%>

      <%@page import="java.io.*" %>
      <% Workbook wb = new XSSFWorkbook();
      FileOutputStream fileOut = new FileOutputStream("C://test//workbook.xlsx");
      wb.write(fileOut);
      fileOut.close();%>.
      I wrote the above code to open a WorkBook. Is it right

      Please reply

      Thanks friends
        • 1. Re: how to read data from .xlsx file using poi 3.5
          JoachimSauer
          infoscion wrote:
          hi
          Hi,

          Why do you keep creating new [user accounts|http://forums.sun.com/profile.jspa?userID=1125372]?

          I also suggest you read [How To Ask Questions The Smart Way|http://www.catb.org/~esr/faqs/smart-questions.html], it will help you find a solution.
          i am reading data from an .xlsx file and i am using jsp. i have importes the following files C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\poi-3.5-beta5-20090219.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\poi-ooxml-3.5-beta5-20090219.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\openxml4j-bin-beta-080728.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\xbean.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\xbean_xpath.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\xmlbeans-qname.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\xmlpublic.jar,C:\Users\401345\Desktop\SE5\Tomcat 5.5\common\lib\ooxml-schemas-1.0.jar.
          Your libraries should go in WEB-INF/lib of your web application, not in common/lib of your Tomcat installation!
          Now i am just trying to open a new Work Book and read the contents.
          Why don't you try that without a web application first? Try to run the POI samples to find out how it works.

          My code is
          <%@page import="org.apache.poi.xssf.usermodel.XSSFWorkbook"%>
          <%@page import="org.apache.poi.xssf.usermodel.XSSFSheet"%>
          <%@page import="org.apache.poi.ss.usermodel.*"%>
          <%@page import="org.apache.poi.ss.util.CellRangeAddress"%>
          <%@page import="org.apache.poi.xssf.usermodel.XSSFRow"%>
          <%@page import="org.apache.poi.xssf.usermodel.XSSFCell"%>
          <%@page import="org.apache.poi.openxml4j.opc.Package"%>
          <%@page import="org.apache.xmlbeans.*"%>
          Why do you import all that when you only use Workbook and XSSFWorkbook?
          <%@page import="java.io.*" %>
          <% Workbook wb = new XSSFWorkbook();
          FileOutputStream fileOut = new FileOutputStream("C://test//workbook.xlsx");
          wb.write(fileOut);
          fileOut.close();%>.
          1.) When posting code, use the CODE button.
          2.) Don't write Code like that in a JSP page, write a real class to do that work.
          • 2. Re: how to read data from .xlsx file using poi 3.5
            807588
            hi

            i copied those jar files and pasted into the WEB_INF/lib folder but still it's showing the same error. Can you tell me whether the code i written is correct os not

            Sorry for not following the correct fomat for sending mails
            • 3. Re: how to read data from .xlsx file using poi 3.5
              JoachimSauer
              As I said: don't go messing with Tomcat + JSP until you've run a simple example without that stuff (and don't go writing Java code in JSPs at all! That code should be in a Servlet (or Action)).

              Only for you I went and downloaded poi-bin-3.5-beta5-20090219.tar.gz. I untarred it, went into poi-3.5-beta5 and wrote the following program:
              import org.apache.poi.xssf.usermodel.XSSFWorkbook;
              import java.io.FileOutputStream;
              import java.io.IOException;
              
              public class TestPOI {
                public static void main(String[] args) throws IOException {
                  XSSFWorkbook wb = new XSSFWorkbook();
                  FileOutputStream fileOut = new FileOutputStream("workbooks.xlsx");
                  wb.write(fileOut);
                  fileOut.close();
                }
              }
              Then I compiled and ran it using all .jar files on the classpath. So my classpath looked like that:

              .:poi-3.5-beta5-20090219.jar:poi-contrib-3.5-beta5-20090219.jar:poi-ooxml-3.5-beta5-20090219.jar:poi-scratchpad-3.5-beta5-20090219.jar:lib/commons-logging-1.1.jar:lib/junit-3.8.1.jar:lib/log4j-1.2.13.jar:ooxml-lib/dom4j-1.6.1.jar:ooxml-lib/jsr173_1.0_api.jar:ooxml-lib/ooxml-schemas-1.0.jar:ooxml-lib/xmlbeans-2.3.0.jar

              This worked just fine and produced workbook.xlsx.

              The rest is up to you.
              • 4. Re: how to read data from .xlsx file using poi 3.5
                807588
                hi
                Thanks for your help. I ran the code in a simple java class. But still its showing error. I am using eclipse. The following is the error.
                Exception in thread "main" java.lang.NoClassDefFoundError: javax/xml/stream/XMLStreamException
                     at java.lang.Class.getDeclaredMethods0(Native Method)
                     at java.lang.Class.privateGetDeclaredMethods(Class.java:2365)
                     at java.lang.Class.getMethod0(Class.java:2611)
                     at java.lang.Class.getMethod(Class.java:1579)
                     at org.apache.xmlbeans.XmlBeans.buildMethod(XmlBeans.java:174)
                     at org.apache.xmlbeans.XmlBeans.buildNodeMethod(XmlBeans.java:195)
                     at org.apache.xmlbeans.XmlBeans.buildNodeToCursorMethod(XmlBeans.java:232)
                     at org.apache.xmlbeans.XmlBeans.<clinit>(XmlBeans.java:131)
                     at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook$Factory.newInstance(Unknown Source)
                     at org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:227)
                     at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:146)
                     at test.TestPOI.main(TestPOI.java:9)
                • 5. Re: how to read data from .xlsx file using poi 3.5
                  JoachimSauer
                  That class was introduced in Jav 6, so it seems you need to use Java 6.
                  • 6. Re: how to read data from .xlsx file using poi 3.5
                    807588
                    Thank you friend


                    I need another help. I saved an xlsx file to xls 2003 format. The excel sheet contains sevral macros for some columns. I entered the values the macro functions are working perfectly. but when try to insert the data from the excelsheet into the oracle database using poi3.2 it shows the following error.

                    org.apache.poi.hssf.record.RecordFormatException: Unable to construct record instance.

                    Please help me with this also. I hope that those macros in the excel sheet is the reason for the error.

                    Thank You
                    • 7. Re: how to read data from .xlsx file using poi 3.5
                      JoachimSauer
                      Go talk to the POI guys. This is not the correct place for such specific problems with third party libraries.
                      • 8. Re: how to read data from .xlsx file using poi 3.5
                        807588
                        Can someone give a small tutorial of how to use the SS model in POI 3.5 package