reading xlsx example issue

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
LVL 7
gudii9Asked:
Who is Participating?
 
CEHJCommented:
Correct. we did earlier for xls not for xlsx file.
No difference - the rows and cells are still indexed and accessible by index
0
 
CEHJCommented:
You need something like
String value = null;
if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
  value=cell.getStringCellValue();
}

Open in new window

// else
0
 
gudii9Author Commented:
how i have to modify the code. where i have to incoprorate above code. please advise
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
CEHJCommented:
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
 
gudii9Author Commented:
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
 
CEHJCommented:
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
 
gudii9Author Commented:
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
 
gudii9Author Commented:
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
 
gudii9Author Commented:
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
 
CEHJCommented:
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
 
gudii9Author Commented:
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
 
CEHJCommented:
Why would you print a Row? You should be getting the cells you want from that row. See api docs
0
 
gudii9Author Commented:
>>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
 
gudii9Author Commented:
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
 
gudii9Author Commented:
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
 
CEHJCommented:
I'm not sure what your problem is now
0
 
gudii9Author Commented:
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
 
gudii9Author Commented:
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
 
CEHJCommented:
Again, we've been through this before. You've already produced code that reads the two column values for ALL rows
0
 
gudii9Author Commented:
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
 
gudii9Author Commented:
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
 
gudii9Author Commented:
http://www.coderanch.com/t/532005/java/java/little-help

above link bit close to my requirement but not complete
0
 
gudii9Author Commented:
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
 
CEHJCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.