Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 12 of 12

Thread: Importing excel data

  1. #1
    Junior Member
    Join Date
    Jul 2009
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Importing excel data

    while importing excel data i am getting this error

    jxl.read.biff.BiffException: Unable to recognize OLE stream

    Note: my datas in excel are of type xml spreadsheet.

    can u please help me?


  2. #2
    Super Moderator Json's Avatar
    Join Date
    Jul 2009
    Location
    Warrington, United Kingdom
    Posts
    1,274
    My Mood
    Happy
    Thanks
    70
    Thanked 156 Times in 152 Posts

    Default Re: Importing excel data

    Wll I've never used the excel API you are using so I would have no clue why you are seeing that exception, have you tried this with a pre 2007 excel file which is not XML?

    See if that works and if it does my conclusion would be that the api you are using isnt able to read the XML version, or maybe it is but you aren't calling the right piece of code for it.

    It would also be nice if you could show us your code and the full stack trace.

    Cheers!

    Edit: I just had a read at jexcelapi's website and it says the following.

    Reads data from Excel 95, 97, 2000, XP, and 2003 workbooks
    Reads and writes formulas (Excel 97 and later only)
    Generates spreadsheets in Excel 2000 format
    Supports font, number and date formatting
    Supports shading, bordering, and coloring of cells
    Modifies existing worksheets
    Is internationalized, enabling processing in almost any locale, country, language, or character encoding (formulas are currently only supported in English, French, Spanish, and German, but more can be added if translated)
    Supports copying of charts
    Supports insertion and copying of images into spreadsheets
    Supports logging with Jakarta Commons Logging, log4j, JDK 1.4 Logger, etc
    ...and much more
    I cant find anywhere it says it is able to read the 2007 version (XML).

    // Json
    Last edited by Json; October 19th, 2009 at 03:01 AM.

  3. #3
    Junior Member
    Join Date
    Jul 2009
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: Importing excel data

    hey thanks .
    If i save the file with type as Excel Workbook, my code is working fine.
    Also please suggest if i could use someother api...

    I have attached the code and stacktrace also.

    try {
    Workbook wb = Workbook.getWorkbook(fp);
    Sheet sheet = wb.getSheet(0);
    int columns = sheet.getColumns();
    int rows = sheet.getRows();

    String data = null;
    for(int row = 0;row < rows;row++) {
    for(int col = 0;col < columns;col++) {
    data = sheet.getCell(col, row).getContents();
    System.out.println("data====="+data);
    // Your code here
    }
    }
    } catch(Exception ioe) {
    System.out.println("Error: " + ioe);
    ioe.printStackTrace();
    }

    StackTrace is:

    Error: jxl.read.biff.BiffException: Unable to recognize OLE stream
    jxl.read.biff.BiffException: Unable to recognize OLE stream
    at jxl.read.biff.CompoundFile.<init>(CompoundFile.jav a:116)
    at jxl.read.biff.File.<init>(File.java:127)
    at jxl.Workbook.getWorkbook(Workbook.java:268)
    at jxl.Workbook.getWorkbook(Workbook.java:253)
    at com.ramco.rvw.pl.ExcelImport.main(ExcelImport.java :24)

  4. #4
    Super Moderator Json's Avatar
    Join Date
    Jul 2009
    Location
    Warrington, United Kingdom
    Posts
    1,274
    My Mood
    Happy
    Thanks
    70
    Thanked 156 Times in 152 Posts

    Default Re: Importing excel data


  5. #5
    Junior Member
    Join Date
    Jul 2009
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: Importing excel data

    hi i have lookined into that API but could not find the solution.
    can u please provide the sample code?

    Thanks

  6. #6
    Super Moderator Json's Avatar
    Join Date
    Jul 2009
    Location
    Warrington, United Kingdom
    Posts
    1,274
    My Mood
    Happy
    Thanks
    70
    Thanked 156 Times in 152 Posts

    Default Re: Importing excel data

    Search for "XSSF and SAX (Event API)" on The New Halloween Document

    // Json

  7. #7
    Junior Member
    Join Date
    Jul 2009
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: Importing excel data

    Hi,
    Thanks,

    I have tried but getting this error

    org.apache.poi.openxml4j.exceptions.InvalidOperati onException: Can't open the specified file: 'D:\grdview.xls'

    can u pls help me?

  8. #8
    Super Moderator Json's Avatar
    Join Date
    Jul 2009
    Location
    Warrington, United Kingdom
    Posts
    1,274
    My Mood
    Happy
    Thanks
    70
    Thanked 156 Times in 152 Posts

    Default Re: Importing excel data

    It's been a very long time since I used POI. If I get a chance later I shall give it a go.

    Could you attach an Office 2007 Excel file to this thread please which I can use for testing?

    // Json

  9. #9
    Junior Member
    Join Date
    Jul 2009
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: Importing excel data

    Thanks.
    Sure. I have attached the file for ur reference.

  10. #10
    Junior Member
    Join Date
    Aug 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up Re: Importing excel data

    I opened Office 2007 file and saved as 97 - 2003 xls format. it is working fine.

    Here is my code:


    import java.io.FileInputStream;
    import jxl.Sheet;
    import jxl.Workbook;
    //import org.junit.AfterClass;
    //import org.junit.BeforeClass;
    //import org.junit.Test;
    import org.openqa.selenium.server.RemoteControlConfigurat ion;
    import org.openqa.selenium.server.SeleniumServer;
    import com.thoughtworks.selenium.*;

    public class DatadrivenJUnit extends SeleneseTestCase{
    public Selenium selenium;
    public SeleniumServer seleniumserver;
    //@BeforeClass
    public void setUp() throws Exception {
    RemoteControlConfiguration rc = new RemoteControlConfiguration();
    //rc.setSingleWindow(true);
    seleniumserver = new SeleniumServer(rc);
    selenium = new DefaultSelenium("localhost", 4444, "*iexplore", "http://");
    seleniumserver.start();
    selenium.start();
    }
    //@Test
    public void testDatadrivenJUnit() throws Exception
    {
    FileInputStream fi=new FileInputStream("C:\\Selenium\\DDT\\search1.xls");
    Workbook w=Workbook.getWorkbook(fi);
    Sheet s=w.getSheet(0);
    selenium.open("http://www.google.com");
    selenium.windowMaximize();
    for (int i = 1; i < s.getRows(); i++)
    { //Read data from excel sheet
    selenium.type("name=q",s.getCell(0,i).getContents( ));
    selenium.click("btnG");
    Thread.sleep(1000); } }
    //@AfterClass
    public void tearDown() throws InterruptedException{
    selenium.stop();
    seleniumserver.stop();
    }
    }

  11. #11
    Junior Member
    Join Date
    Oct 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: Importing excel data

    Quote Originally Posted by srikanthpunuru View Post
    I opened Office 2007 file and saved as 97 - 2003 xls format. it is working fine.

    Here is my code:


    import java.io.FileInputStream;
    import jxl.Sheet;
    import jxl.Workbook;
    //import org.junit.AfterClass;
    //import org.junit.BeforeClass;
    //import org.junit.Test;
    import org.openqa.selenium.server.RemoteControlConfigurat ion;
    import org.openqa.selenium.server.SeleniumServer;
    import com.thoughtworks.selenium.*;

    public class DatadrivenJUnit extends SeleneseTestCase{
    public Selenium selenium;
    public SeleniumServer seleniumserver;
    //@BeforeClass
    public void setUp() throws Exception {
    RemoteControlConfiguration rc = new RemoteControlConfiguration();
    //rc.setSingleWindow(true);
    seleniumserver = new SeleniumServer(rc);
    selenium = new DefaultSelenium("localhost", 4444, "*iexplore", "http://");
    seleniumserver.start();
    selenium.start();
    }
    //@Test
    public void testDatadrivenJUnit() throws Exception
    {
    FileInputStream fi=new FileInputStream("C:\\Selenium\\DDT\\search1.xls");
    Workbook w=Workbook.getWorkbook(fi);
    Sheet s=w.getSheet(0);
    selenium.open("http://www.google.com");
    selenium.windowMaximize();
    for (int i = 1; i < s.getRows(); i++)
    { //Read data from excel sheet
    selenium.type("name=q",s.getCell(0,i).getContents( ));
    selenium.click("btnG");
    Thread.sleep(1000); } }
    //@AfterClass
    public void tearDown() throws InterruptedException{
    selenium.stop();
    seleniumserver.stop();
    }
    }


    Hi,
    this code is not working properly.i got some errors "unable to recognize the OLE stream"
    plz send me the problem.As soon as............

  12. #12
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,318
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

    Default Re: Importing excel data

    Please don't resurrect a 3 year old post. If you have a question, then please start a new thread with all code, errors, and a specific question. Thread locked

Similar Threads

  1. How to Connect to an Excel Spreadsheet using JDBC in Java
    By JavaPF in forum JDBC and Database Tutorials
    Replies: 14
    Last Post: August 27th, 2013, 11:57 AM
  2. Searching Data
    By kalees in forum JavaServer Pages: JSP & JSTL
    Replies: 3
    Last Post: October 2nd, 2009, 03:23 AM
  3. .xls data structure
    By helloworld922 in forum JDBC & Databases
    Replies: 3
    Last Post: August 20th, 2009, 07:12 PM
  4. I can't key in the data
    By muffin in forum File I/O & Other I/O Streams
    Replies: 1
    Last Post: August 10th, 2009, 11:03 PM
  5. Export to excel
    By ebosysindia in forum File I/O & Other I/O Streams
    Replies: 7
    Last Post: May 14th, 2009, 06:25 AM