Results 1 to 6 of 6
  1. #1
    sandeepsai17 is offline Member
    Join Date
    Jun 2009
    Posts
    30
    Rep Power
    0

    Default problem with reading excel sheet data reading using poi libraries

    hi to all,
    i am using apache poi libraries to read excel sheet data using java, my code goes like below
    Java Code:
    HSSFWorkbook workBook;
    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    workBook = new HSSFWorkbook(filelocatiion );
    sheet = workBook.getSheet("Sheet1");
          int row_count = sheet.getPhysicalNumberOfRows();
                System.out.println("no_rows  "+ row_count);            
                row = sheet.getRow((short)0);
                cell = row.getCell(((short)0));                         
                l1:for (int i = 0; i < row_count;i++) {
                row  = sheet.getRow((short)i);
                try{
                if(row==null){
               /* defined exception */     throw  new EmptyRowException("row is empty at " + i);
                }
                }
                catch (EmptyRowException e){
                System.out.println("sheet contains empty row at " +                   
                row.getPhysicalNumberOfCells() + "delete emmpty rows " );
    
            }
                   l2:for (int j = 0; j < row.getPhysicalNumberOfCells() ; j++) 
                  {
                     System.out.println(" cell is " + j  );
                     System.out.println("cell value at " + (i+1) + "th row " + (j+1) +"th column is " + cell.getNumericCellValue()) ;  
                   cell = row.getCell((short)j);
    my problem is when i read a value in excel sheet for example ,excel sheet contains integer value like 15 at cell A4 , i am getting output in the form of
    15.0 ,even though it i printed value of cell.toString() or cell.getRichStringValue().getStringvalue();

    please help me.
    thank you

  2. #2
    dlorde is offline Senior Member
    Join Date
    Jun 2008
    Posts
    339
    Rep Power
    7

    Default

    Quote Originally Posted by sandeepsai17 View Post
    my problem is when i read a value in excel sheet for example ,excel sheet contains integer value like 15 at cell A4 , i am getting output in the form of
    15.0 ,even though it i printed value of cell.toString() or cell.getRichStringValue().getStringvalue();
    The method getNumericCellValue returns a double, which is a floating-point numeric type. If you want it as an int, you can cast it:
    Java Code:
    (int)cell.getNumericCellValue()
    If you're using that in a larger expression, you might need to put parentheses around it.

  3. #3
    sandeepsai17 is offline Member
    Join Date
    Jun 2009
    Posts
    30
    Rep Power
    0

    Default

    hi dlorde ,
    thank u for your response ,
    but i am using it in server side and actually excel sheet is on client side ,and i want to insert client excel sheet data into database in which if client makes a mistake by inserting a decimal ,for example if he enters 15.0 instead of 150,
    as you said (int) (cell.getNumericValue()) returns 15 instead 150. I want to give a message to client that decimal values are not allowed ,if he or she makes mistake.I hope you know that cell.getNumericValue() returns 15 as 15.0 for cell
    containing a value 15 as integer though.

  4. #4
    dlorde is offline Senior Member
    Join Date
    Jun 2008
    Posts
    339
    Rep Power
    7

    Default

    Quote Originally Posted by sandeepsai17 View Post
    I want to give a message to client that decimal values are not allowed ,if he or she makes mistake.
    Looking at the HSSFCell Javadocs, I can't see how to do this if the cell is numeric, but I'm no expert. I would be inclined to configure the spreadsheet data entry to prevent them entering floating-point numbers. Try setting a custom format for the cell that only allows numeric characters without '.'. It's always best to prevent the input of bad data rather than try to deal with it later.
    Last edited by dlorde; 08-21-2009 at 01:45 AM.

  5. #5
    sandeepsai17 is offline Member
    Join Date
    Jun 2009
    Posts
    30
    Rep Power
    0

    Default

    i tried for defining custom cell type also ,but there is no class or interface defining custom cell types in poi api ,that is the problem.

  6. #6
    dlorde is offline Senior Member
    Join Date
    Jun 2008
    Posts
    339
    Rep Power
    7

    Default

    Quote Originally Posted by sandeepsai17 View Post
    i tried for defining custom cell type also ,but there is no class or interface defining custom cell types in poi api ,that is the problem.
    No, what I suggested was changing the spreadsheet cell input format so the user can only enter valid data.

Similar Threads

  1. Reading Libraries.
    By leiferouis in forum New To Java
    Replies: 4
    Last Post: 02-07-2009, 09:34 PM
  2. Reading data from Micrsoft excel and writing to notepad
    By abhishek.jain in forum New To Java
    Replies: 4
    Last Post: 01-29-2009, 09:12 AM
  3. Save data from Excel sheet to servlet
    By chandu.v in forum Java Servlet
    Replies: 2
    Last Post: 04-10-2008, 02:51 PM
  4. Reading data form Excel using Jdbc (example)
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-13-2008, 12:23 PM
  5. Reading excel worksheet
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-13-2008, 12:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •