Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

reading xlsx example issue

Posted on 2012-09-18
24
Medium Priority
?
3,266 Views
Last Modified: 2012-09-23
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import java.util.Iterator;

import java.io.*;

public class sssssssss {

 

 

    public void ReadSheet() throws Exception

    {

        String filename = "C:\\cccc.xlsx";

        FileInputStream fis = null;

        try {

            fis = new FileInputStream(filename);

           

            XSSFWorkbook workbook = new XSSFWorkbook(fis);

            XSSFSheet sheet = workbook.getSheetAt(0);

            Iterator rows = sheet.rowIterator();

            int number=sheet.getLastRowNum();

            System.out.println(" number of rows---->"+ number);

            while (rows.hasNext())

            {

 

                XSSFRow row = ((XSSFRow) rows.next());

                Iterator cells = row.cellIterator();

                while(cells.hasNext())

                {

                    XSSFCell cell = (XSSFCell) cells.next();

                    String Value=cell.getStringCellValue();

                    System.out.println(Value);

                }

             }

        } catch (IOException e) {

            e.printStackTrace();

        } finally {

            if (fis != null) {

                fis.close();

            }

        }

       

    }

 

 

    public static void main(String[] args) {

 

        sssssssss object=new sssssssss();

        try{

        object.ReadSheet();

 

        }catch(Exception e)

        {

            e.printStackTrace();

        }

}

 

}


I was trying above example from link


http://techtadka.net/articles/programming/208-code-for-reading-a-xlsx-file-in-java.html


using attached cccc.xlsx.

I am getting following error. Please advise
 number of rows---->1
java.lang.IllegalStateException: Cannot get a text value from a numeric cell
      at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:845)
      at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:294)
      at org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue(XSSFCell.java:246)
      at sssssssss.ReadSheet(sssssssss.java:60)
      at sssssssss.main(sssssssss.java:98)





Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance
cccc.xlsx
0
Comment
Question by:gudii9
  • 15
  • 9
24 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 38410198
You need something like
String value = null;
if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
  value=cell.getStringCellValue();
}

Open in new window

// else
0
 
LVL 7

Author Comment

by:gudii9
ID: 38410280
how i have to modify the code. where i have to incoprorate above code. please advise
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38410335
String Value=cell.getStringCellValue();

Open in new window

Substitute the code i gave you for the above (and change the case of 'Value' - java variables should begin lower case)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:gudii9
ID: 38410336
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import java.util.Iterator;

import java.io.*;

public class sssssssss {

 

 

    public void ReadSheet() throws Exception

    {

        String filename = "C:\\cccc.xlsx";

        FileInputStream fis = null;

        try {

            fis = new FileInputStream(filename);

           

            XSSFWorkbook workbook = new XSSFWorkbook(fis);

            XSSFSheet sheet1 = workbook.getSheetAt(0);

            Iterator rows = sheet1.rowIterator();

            int number=sheet1.getLastRowNum();

            System.out.println(" number of rows---->"+ number);

            while (rows.hasNext())

            {

 

                XSSFRow row = ((XSSFRow) rows.next());

                Iterator cells = row.cellIterator();

                while(cells.hasNext())

                {

                    XSSFCell cell = (XSSFCell) cells.next();
                    String Value=null;
                    if(cell.getCellType() == cell.CELL_TYPE_STRING) {
                     Value=cell.getStringCellValue();

                    System.out.println(Value);
                    }

                }

             }

        } catch (IOException e) {

            e.printStackTrace();

        } finally {

            if (fis != null) {

                fis.close();

            }

        }

       

    }

 

 

    public static void main(String[] args) {

 

        sssssssss object=new sssssssss();

        try{

        object.ReadSheet();

 

        }catch(Exception e)

        {

            e.printStackTrace();

        }

}

 

}


when i changed like above getting only few values like below. Please advise


 number of rows---->1
aa
77-SS_001
DONE
aa
77-SS_002
NoDONE
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38410353
when i changed like above getting only few values like below. Please advise
Of course - since you're only testing for string cell types. If you want to see other types, you need to go through the other cell types too, and test for them
0
 
LVL 7

Author Comment

by:gudii9
ID: 38410370
how to see Numerical types. How to get first two column values irrespective of they are string or numeric.

I want to use those two column values to send to query later on. Please advise
0
 
LVL 7

Author Comment

by:gudii9
ID: 38410752
I have identifiied row index(0,1), as well as column index (0,1)

while(cells.hasNext())

                {

                    XSSFCell cell = (XSSFCell) cells.next();
                    String Value=null;
                    if(cell.getCellType() == cell.CELL_TYPE_STRING) {
                    Value=cell.getStringCellValue();

                    System.out.println("string values-->"+Value);
                    }
                    else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
                          //int Value2=(Integer) null;
                      double  Value2=cell.getNumericCellValue();
                        System.out.println("numbercal values--->"+Value2);
                       
                        int  Value3=cell.getColumnIndex();
                        System.out.println("col index--->"+Value3);
                        int  Value4=cell.getRowIndex();
                        System.out.println("Row index--->"+Value4);
                        }

 number of rows---->1
numbercal values--->22.0
col index--->0
Row index--->0
numbercal values--->33.0
col index--->1
Row index--->0
numbercal values--->44.0
col index--->2
Row index--->0
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->0
string values-->77-SS_001
string values-->DONE
numbercal values--->23.0
col index--->0
Row index--->1
numbercal values--->34.0
col index--->1
Row index--->1
numbercal values--->45.0
col index--->2
Row index--->1
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->1
string values-->77-SS_002
string values-->NoDONE



I need to print

22 33(row index 0 and column index 0.1)
and
23 34(row index 1 and column index 0,1)

I was not sure how to print this kind of 2 dimentional cell value. I could not find corresponding method in API yet. Please advise
0
 
LVL 7

Author Comment

by:gudii9
ID: 38411042
i do not know how to print  cell value(34 in the attached excel screenshot i put in this question) from xlsx sheet with a position of

row index=1
and
column index=1

by specifying both the indexes. Please advise
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38411620
We seem to go around in circles. I answered this issue in one of your earliest questions. If you're only interested in certain cells, DON'T use an iterator. Use http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html#getRow(int)
0
 
LVL 7

Author Comment

by:gudii9
ID: 38414502
when i try to read using getRow()


  XSSFRow x= sheet1.getRow(0);
            System.out.println(" row content 0---->"+ x);
           
            XSSFRow x1= sheet1.getRow(0);
            System.out.println(" row content 1---->"+ x1);
            int number=sheet1.getLastRowNum();

getting results as below

 row content 0----><xml-fragment r="1" spans="1:7" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <main:c r="A1" s="1">
    <main:v>22</main:v>
  </main:c>
  <main:c r="B1" s="1">
    <main:v>33</main:v>
  </main:c>
  <main:c r="C1" s="1">
    <main:v>44</main:v>
  </main:c>
  <main:c r="D1" s="1" t="s">
    <main:v>0</main:v>
  </main:c>
  <main:c r="E1" s="2">
    <main:v>43988.659085648149</main:v>
  </main:c>
  <main:c r="F1" s="1" t="s">
    <main:v>1</main:v>
  </main:c>
  <main:c r="G1" s="1" t="s">
    <main:v>2</main:v>
  </main:c>
</xml-fragment>
 row content 1----><xml-fragment r="1" spans="1:7" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <main:c r="A1" s="1">
    <main:v>22</main:v>
  </main:c>
  <main:c r="B1" s="1">
    <main:v>33</main:v>
  </main:c>
  <main:c r="C1" s="1">
    <main:v>44</main:v>
  </main:c>
  <main:c r="D1" s="1" t="s">
    <main:v>0</main:v>
  </main:c>
  <main:c r="E1" s="2">
    <main:v>43988.659085648149</main:v>
  </main:c>
  <main:c r="F1" s="1" t="s">
    <main:v>1</main:v>
  </main:c>
  <main:c r="G1" s="1" t="s">
    <main:v>2</main:v>
  </main:c>
</xml-fragment>
 number of rows---->1
numbercal values--->22.0
col index--->0
Row index--->0
numbercal values--->33.0
col index--->1
Row index--->0
numbercal values--->44.0
col index--->2
Row index--->0
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->0
string values-->77-SS_001
string values-->DONE
numbercal values--->23.0
col index--->0
Row index--->1
numbercal values--->34.0
col index--->1
Row index--->1
numbercal values--->45.0
col index--->2
Row index--->1
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->1
string values-->77-SS_002
string values-->NoDONE


some reason for both rownum 0 and 1 it is still printing same results ie row 0 results. Please advise
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38414553
Why would you print a Row? You should be getting the cells you want from that row. See api docs
0
 
LVL 7

Author Comment

by:gudii9
ID: 38414667
>>You should be getting the cells

I want to get cells from all the rows of the xlsx sheet. If i cannot get to rownum1 then i cannot get first two values from that. It did not printed rownum 1. It printed rownum0 both times. Please advise
0
 
LVL 7

Author Comment

by:gudii9
ID: 38414731
i see I made mistake I gave 0 at both places whike calling getROW() method. Row content coming fine but now i have issue with cell content. I am getting 22 both times. I supposed to get 22 and then 23



 XSSFRow x= sheet1.getRow(0);            
            System.out.println(" row content 0---->"+ x);
            XSSFCell y =  x.getCell(0);
            System.out.println(" cell content 0---->"+ y);
           
            XSSFRow x1= sheet1.getRow(1);
            System.out.println(" row content 1---->"+ x1);
            XSSFCell y1 =  x1.getCell(0);
            System.out.println(" cell content 1****---->"+ y);




console output



 row content 0----><xml-fragment r="1" spans="1:7" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <main:c r="A1" s="1">
    <main:v>22</main:v>
  </main:c>
  <main:c r="B1" s="1">
    <main:v>33</main:v>
  </main:c>
  <main:c r="C1" s="1">
    <main:v>44</main:v>
  </main:c>
  <main:c r="D1" s="1" t="s">
    <main:v>0</main:v>
  </main:c>
  <main:c r="E1" s="2">
    <main:v>43988.659085648149</main:v>
  </main:c>
  <main:c r="F1" s="1" t="s">
    <main:v>1</main:v>
  </main:c>
  <main:c r="G1" s="1" t="s">
    <main:v>2</main:v>
  </main:c>
</xml-fragment>
 cell content 0---->22.0
 row content 1----><xml-fragment r="2" spans="1:7" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <main:c r="A2" s="1">
    <main:v>23</main:v>
  </main:c>
  <main:c r="B2" s="1">
    <main:v>34</main:v>
  </main:c>
  <main:c r="C2" s="1">
    <main:v>45</main:v>
  </main:c>
  <main:c r="D2" s="1" t="s">
    <main:v>0</main:v>
  </main:c>
  <main:c r="E2" s="2">
    <main:v>43988.659085648149</main:v>
  </main:c>
  <main:c r="F2" s="1" t="s">
    <main:v>3</main:v>
  </main:c>
  <main:c r="G2" s="1" t="s">
    <main:v>4</main:v>
  </main:c>
</xml-fragment>
 cell content 1****---->22.0
 number of rows---->1
numbercal values--->22.0
col index--->0
Row index--->0
numbercal values--->33.0
col index--->1
Row index--->0
numbercal values--->44.0
col index--->2
Row index--->0
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->0
string values-->77-SS_001
string values-->DONE
numbercal values--->23.0
col index--->0
Row index--->1
numbercal values--->34.0
col index--->1
Row index--->1
numbercal values--->45.0
col index--->2
Row index--->1
string values-->aa
numbercal values--->43988.65908564815
col index--->4
Row index--->1
string values-->77-SS_002
string values-->NoDONE
0
 
LVL 7

Author Comment

by:gudii9
ID: 38414773
I made other mistake

        XSSFRow x= sheet1.getRow(0);            
            System.out.println(" row content 0---->"+ x);
            XSSFCell y =  x.getCell(0);
            System.out.println(" cell content 0---->"+ y);
           
            XSSFRow x1= sheet1.getRow(1);
            System.out.println(" row content 1---->"+ x1);
            XSSFCell y1 =  x1.getCell(0);
            System.out.println(" cell content 1****---->"+ y1);


above code printed 22,23 both as well
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38415000
I'm not sure what your problem is now
0
 
LVL 7

Author Comment

by:gudii9
ID: 38415022
my problem now is

I have to read for all the lines of the .xlsx file sheet and get those first two cell content values of each line and check againest database table and generate csv file of missing report and email to user(emailing part code is already there with me). Please advise
0
 
LVL 7

Author Comment

by:gudii9
ID: 38415066
xls file earlier i read like

      public static Vector read(String fileName)    {
            Vector cellVectorHolder = new Vector();
            try{
                  FileInputStream myInput = new FileInputStream(fileName);
                  POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
                  HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
                  HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                  Iterator rowIter = mySheet.rowIterator();
                  while(rowIter.hasNext()){
                        HSSFRow myRow = (HSSFRow) rowIter.next();
                        Iterator cellIter = myRow.cellIterator();
                        Vector cellStoreVector=new Vector();
                        while(cellIter.hasNext()){
                              HSSFCell myCell = (HSSFCell) cellIter.next();
                              cellStoreVector.addElement(myCell);
                        }
                        cellVectorHolder.addElement(cellStoreVector);
                  }
            }catch (Exception e){e.printStackTrace(); }
            return cellVectorHolder;
      }


but for xlsx method i am not sure how can i achieve the same effect

 public static Vector ReadXlsx(String filename) throws Exception

    {

     //   String filename = "C:\\cccc.xlsx";

        FileInputStream fis = null;

        try {

            fis = new FileInputStream(filename);

           

            XSSFWorkbook workbook = new XSSFWorkbook(fis);

            XSSFSheet sheet1 = workbook.getSheetAt(0);

            Iterator rows = sheet1.rowIterator();
           
            XSSFRow x= sheet1.getRow(0);            
            System.out.println(" row content 0---->"+ x);
            XSSFCell y =  x.getCell(0);
            System.out.println(" cell content 0---->"+ y);
           
            XSSFRow x1= sheet1.getRow(1);
            System.out.println(" row content 1---->"+ x1);
            XSSFCell y1 =  x1.getCell(0);
            System.out.println(" cell content 1****---->"+ y1);
         //   getCell
       
           
          //  System.out.println(" row content 0---->"+ x);
           
            int number=sheet1.getLastRowNum();

            System.out.println(" number of rows---->"+ number);

            while (rows.hasNext())

            {

 

                XSSFRow row = ((XSSFRow) rows.next());

                Iterator cells = row.cellIterator();

                while(cells.hasNext())

                {

                    XSSFCell cell = (XSSFCell) cells.next();
                    String Value=null;
                 //   cell.
                    if(cell.getCellType() == cell.CELL_TYPE_STRING) {
                    Value=cell.getStringCellValue();

                    System.out.println("string values-->"+Value);
                    }
                    else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
                          //int Value2=(Integer) null;
                      double  Value2=cell.getNumericCellValue();
                        System.out.println("numbercal values--->"+Value2);
                       
                        int  Value3=cell.getColumnIndex();
                        System.out.println("col index--->"+Value3);
                        int  Value4=cell.getRowIndex();
                        //XSSFRow
                        System.out.println("Row index--->"+Value4);
                 

                        }
                   
                   

                }

             }

        } catch (IOException e) {

            e.printStackTrace();

        } finally {

            if (fis != null) {

                fis.close();

            }

        }
            return null;

       

    }




ie iterating through all the lines of the xlsx sheet and generatae cellVectorHolder  so that i can pass that vector to checkDB() method to check the database table for missing records.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38415068
Again, we've been through this before. You've already produced code that reads the two column values for ALL rows
0
 
LVL 7

Author Comment

by:gudii9
ID: 38415108
Correct. we did earlier for xls not for xlsx file.


for XLS we took advantage of api like below
public static Vector read(String fileName)    {
            Vector cellVectorHolder = new Vector();
            try{
                  FileInputStream myInput = new FileInputStream(fileName);
                  POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
                  HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
                  HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                  Iterator rowIter = mySheet.rowIterator();
                  while(rowIter.hasNext()){
                        HSSFRow myRow = (HSSFRow) rowIter.next();
                        Iterator cellIter = myRow.cellIterator();
                        Vector cellStoreVector=new Vector();
                        while(cellIter.hasNext()){
                              HSSFCell myCell = (HSSFCell) cellIter.next();
                              cellStoreVector.addElement(myCell);
                        }
                        cellVectorHolder.addElement(cellStoreVector);
                  }
            }catch (Exception e){e.printStackTrace(); }
            return cellVectorHolder;
      }





 for XLSX file I am not sure how to do it.


 I know i have to use xssf instead of hssf but I do not know how to do it. I kind of put together some code but not correct

public static Vector ReadXlsx(String filename) throws Exception

    {

     //   String filename = "C:\\cccc.xlsx";

        FileInputStream fis = null;

        try {

            fis = new FileInputStream(filename);

           

            XSSFWorkbook workbook = new XSSFWorkbook(fis);

            XSSFSheet sheet1 = workbook.getSheetAt(0);

            Iterator rows = sheet1.rowIterator();
           
            XSSFRow x= sheet1.getRow(0);            
            System.out.println(" row content 0---->"+ x);
            XSSFCell y =  x.getCell(0);
            System.out.println(" cell content 0---->"+ y);
           
            XSSFRow x1= sheet1.getRow(1);
            System.out.println(" row content 1---->"+ x1);
            XSSFCell y1 =  x1.getCell(0);
            System.out.println(" cell content 1****---->"+ y1);
         //   getCell
       
           
          //  System.out.println(" row content 0---->"+ x);
           
            int number=sheet1.getLastRowNum();

            System.out.println(" number of rows---->"+ number);

            while (rows.hasNext())

            {

 

                XSSFRow row = ((XSSFRow) rows.next());

                Iterator cells = row.cellIterator();

                while(cells.hasNext())

                {

                    XSSFCell cell = (XSSFCell) cells.next();
                    String Value=null;
                 //   cell.
                    if(cell.getCellType() == cell.CELL_TYPE_STRING) {
                    Value=cell.getStringCellValue();

                    System.out.println("string values-->"+Value);
                    }
                    else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
                          //int Value2=(Integer) null;
                      double  Value2=cell.getNumericCellValue();
                        System.out.println("numbercal values--->"+Value2);
                       
                        int  Value3=cell.getColumnIndex();
                        System.out.println("col index--->"+Value3);
                        int  Value4=cell.getRowIndex();
                        //XSSFRow
                        System.out.println("Row index--->"+Value4);
                 

                        }
                   
                   

                }

             }

        } catch (IOException e) {

            e.printStackTrace();

        } finally {

            if (fis != null) {

                fis.close();

            }

        }
            return null;

       

    }




Please advise
0
 
LVL 7

Author Comment

by:gudii9
ID: 38415210
I wrote earlier like below for XLS

public class POIRIRaw4EEenhance {
      public static void main( String [] args ) {
            String fileName="C:\\Reporttt.xls";
            Vector dataHolder=read(fileName);
            checkDatabase(dataHolder);
      }
      public static Vector read(String fileName)    {
            Vector cellVectorHolder = new Vector();
            try{
                  FileInputStream myInput = new FileInputStream(fileName);
                  POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
                  HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
                  HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                  Iterator rowIter = mySheet.rowIterator();
                  while(rowIter.hasNext()){
                        HSSFRow myRow = (HSSFRow) rowIter.next();
                        Iterator cellIter = myRow.cellIterator();
                        Vector cellStoreVector=new Vector();
                        while(cellIter.hasNext()){
                              HSSFCell myCell = (HSSFCell) cellIter.next();
                              cellStoreVector.addElement(myCell);
                        }
                        cellVectorHolder.addElement(cellStoreVector);
                  }
            }catch (Exception e){e.printStackTrace(); }
            return cellVectorHolder;
      }
      private static void checkDatabase(Vector dataHolder) {
            String username="";
            String password="";
            PreparedStatement pstmt = null;      
            for (int i=1;i<dataHolder.size(); i++){
                  Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
                  HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(0);
                  String stringCellValue = myCell.toString();
                  username = stringCellValue;
                  
                  myCell = (HSSFCell)cellStoreVector.elementAt(1);
                  stringCellValue = myCell.toString();
                  password=stringCellValue;
                  
                  System.out.println("value username--->"+username);
                  System.out.println("value password-->"+password);
                  
                  try{
                        Class.forName("com.mysql.jdbc.Driver").newInstance();
                        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
                        Statement stmt = con.createStatement();
                        ResultSet rs1 = stmt.executeQuery("select * from login where username='"+username+"' and password='"+password+"'");
                        System.out.println("rs1-->"+rs1);
                        while (rs1.next()){
                              String rec1 = rs1.getString(1);
                              String rec2 = rs1.getString(2);
                              System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);
                        }


                        pstmt.close();
                        con.close();
                  }
                  catch(Exception e){
                        
                        System.out.println("e-->"+e);
                  }
            }
      }
}


Now I wrote similar way changing HSSF to XSSF wherever HSSF present in above program

I am getting errors like


java.lang.ClassCastException: org.apache.poi.xssf.usermodel.XSSFCell cannot be cast to org.apache.poi.hssf.usermodel.HSSFCell
      at sssssssss.checkDB(sssssssss.java:214)
      at sssssssss.main(sssssssss.java:185)

it did not like

XSSFCell myCell = (XSSFCell)cellStoreVector.elementAt(0);


Please advise how to resolve above issue
0
 
LVL 7

Author Comment

by:gudii9
ID: 38415273
http://www.coderanch.com/t/532005/java/java/little-help

above link bit close to my requirement but not complete
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 2000 total points
ID: 38415378
Correct. we did earlier for xls not for xlsx file.
No difference - the rows and cells are still indexed and accessible by index
0
 
LVL 7

Author Comment

by:gudii9
ID: 38415400
i keep on getting following error

I wrote earlier like below for XLS


public class POIRIRaw4EEenhance {
      public static void main( String [] args ) {
            String fileName="C:\\Reporttt.xls";
            Vector dataHolder=read(fileName);
            checkDatabase(dataHolder);
      }
      public static Vector read(String fileName)    {
            Vector cellVectorHolder = new Vector();
            try{
                  FileInputStream myInput = new FileInputStream(fileName);
                  POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
                  HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
                  HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                  Iterator rowIter = mySheet.rowIterator();
                  while(rowIter.hasNext()){
                        HSSFRow myRow = (HSSFRow) rowIter.next();
                        Iterator cellIter = myRow.cellIterator();
                        Vector cellStoreVector=new Vector();
                        while(cellIter.hasNext()){
                              HSSFCell myCell = (HSSFCell) cellIter.next();
                              cellStoreVector.addElement(myCell);
                        }
                        cellVectorHolder.addElement(cellStoreVector);
                  }
            }catch (Exception e){e.printStackTrace(); }
            return cellVectorHolder;
      }
      private static void checkDatabase(Vector dataHolder) {
            String username="";
            String password="";
            PreparedStatement pstmt = null;      
            for (int i=1;i<dataHolder.size(); i++){
                  Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
                  HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(0);
                  String stringCellValue = myCell.toString();
                  username = stringCellValue;
                 
                  myCell = (HSSFCell)cellStoreVector.elementAt(1);
                  stringCellValue = myCell.toString();
                  password=stringCellValue;
                 
                  System.out.println("value username--->"+username);
                  System.out.println("value password-->"+password);
                 
                  try{
                        Class.forName("com.mysql.jdbc.Driver").newInstance();
                        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
                        Statement stmt = con.createStatement();
                        ResultSet rs1 = stmt.executeQuery("select * from login where username='"+username+"' and password='"+password+"'");
                        System.out.println("rs1-->"+rs1);
                        while (rs1.next()){
                              String rec1 = rs1.getString(1);
                              String rec2 = rs1.getString(2);
                              System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);
                        }


                        pstmt.close();
                        con.close();
                  }
                  catch(Exception e){
                       
                        System.out.println("e-->"+e);
                  }
            }
      }
}




Now I wrote similar way changing HSSF to XSSF wherever HSSF present in above program

I am getting errors like


java.lang.ClassCastException: org.apache.poi.xssf.usermodel.XSSFCell cannot be cast to org.apache.poi.hssf.usermodel.HSSFCell
      at sssssssss.checkDB(sssssssss.java:214)
      at sssssssss.main(sssssssss.java:185)
in my program.

this sample application from link i sent earlier works fine

import java.io.FileInputStream;  
import java.util.Iterator;  
import java.util.Vector;  
import org.apache.poi.xssf.usermodel.XSSFCell;  
import org.apache.poi.xssf.usermodel.XSSFRow;  
import org.apache.poi.xssf.usermodel.XSSFSheet;  
import org.apache.poi.xssf.usermodel.XSSFWorkbook;  

public class InsertClass {  
      @SuppressWarnings("rawtypes")  
      public static void main(String[] args) {  
            String fileName = "C:\\cccc.xlsx";  
            Vector dataHolder = read(fileName);  

            try {  
                  print(dataHolder);  
            } catch (InterruptedException e) {  
                  e.printStackTrace();  
            }  
            // saveToDatabase(dataHolder);  
      }  

      @SuppressWarnings({ "rawtypes", "unchecked" })  
      public static Vector read(String fileName) {  
            Vector cellVectorHolder = new Vector();  
            try {  
                  FileInputStream myInput = new FileInputStream(fileName);  
                  XSSFWorkbook myWorkBook = new XSSFWorkbook(myInput);  
                  XSSFSheet mySheet = myWorkBook.getSheetAt(0);  
                  Iterator rowIter = mySheet.rowIterator();  
                  while (rowIter.hasNext()) {  
                        XSSFRow myRow = (XSSFRow) rowIter.next();  
                        Iterator cellIter = myRow.cellIterator();  
                        Vector cellStoreVector = new Vector();  
                        while (cellIter.hasNext()) {  
                              XSSFCell myCell = (XSSFCell) cellIter.next();  
                              cellStoreVector.addElement(myCell);  

                        }  
                        cellVectorHolder.addElement(cellStoreVector);  
                  }  
            } catch (Exception e) {  
                  e.printStackTrace();  
            }  
            return cellVectorHolder;  
      }  

      @SuppressWarnings("rawtypes")  
      private static void print(Vector dataHolder) throws InterruptedException {  
            String username = "";  
            String password = "";  
            String st = "";  
            for (int i = 0; i < dataHolder.size(); i++) {  
                  Vector cellStoreVector = (Vector) dataHolder.elementAt(i);  
                  // System.out.println(cellStoreVector);  
                  Thread.sleep(500);  
                  for (int j = 0; j < cellStoreVector.size(); j++) {  
                        XSSFCell myCell = (XSSFCell) cellStoreVector.elementAt(j);  
                        st = myCell.toString();  
                        Thread.sleep(500);  
                        System.out.println(st);      
                        //prints the cell value for a row one by one  
                  }  
            }  
      }  
}

How do I get first two values without cast errors from HSSF to XSSF etc. Please error
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38415661
Since that code uses an iterator and i've said several times that's exactly what you DON'T want, i'm not going to comment on it
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This video teaches viewers about errors in exception handling.
Suggested Courses
Course of the Month12 days, 6 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question