Read Data from Excel in Java

Question

I'm really confused I've read many articles but none helped, I want to know how to read data from Excel using java (I'm using NetBeans). I need to know which library to use, and source code. I appreciate your help.

Answer 1

You can use Apache Poi to read Excel, Word, etc.

There is a good tutorial on TutorialsPoint Apache Poi Quick Guide, you should check it out there first.

Below, there is an example code for you, it reads from the WriteSheet.xlsx excel file and prints all the rows-columns to console:

FileInputStream fis = new FileInputStream(new File("WriteSheet.xlsx"));

  XSSFWorkbook workbook = new XSSFWorkbook(fis);
  XSSFSheet spreadsheet = workbook.getSheetAt(0);
  Iterator < Row >  rowIterator = spreadsheet.iterator();

  while (rowIterator.hasNext()) {
     row = (XSSFRow) rowIterator.next();
     Iterator < Cell >  cellIterator = row.cellIterator();

     while ( cellIterator.hasNext()) {
        Cell cell = cellIterator.next();

        switch (cell.getCellType()) {
           case Cell.CELL_TYPE_NUMERIC:
              System.out.print(cell.getNumericCellValue() + " \t\t ");
              break;

           case Cell.CELL_TYPE_STRING:
              System.out.print(
              cell.getStringCellValue() + " \t\t ");
              break;
        }
     }
     System.out.println();
  }
  fis.close();

Edit: You should check out this link if you have got problems in adding jar file to netbeans.

But, if you have got problems in adding the necessary jar files, first you should download the poi-bin zip file in the Apache Poi Download Page. After that, you should add these jars to your env(Versions may vary in time):

poi-3.17.jar
ooxml-lib/xmlbeans-2.6.0.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
lib/commons-collections4-4.1.jar