As we know the main difference with Java JXL which does not support the Excel 2007 ".xlsx" file format. It only supports the old BIFF (binary) ".xls" format. Where as Apache POI supports both xls and xlsx file formats.
To read an Excel 2007 (.xlsx) we need to use XSSF (XML SpreadSheet Format) and we will use the below classes to work with xlsx files by importing the below statements
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;
// Will get the workbook instance for XLS and takes excel file to read
XSSFWorkbook workbook = new XSSFWorkbook(XlsxFileToRead);
// We will pass the name / index of the sheet which starts from '0'.
XSSFSheet sheet = workbook.getSheet("Sheet1");
or
XSSFSheet sheet=wb.getSheetAt(0);
//We will iterate all the rows in the sheet
Iterator rows = sheet.rowIterator();
//We will be iterating all the cells of the current row
Iterator cells = row.cellIterator();
Cells can be numeric, formula-based, string-based (text) or Blank cell. To get the CellType CellType getCellTypeEnum()
, where the CellType is the enum describing the type of the cell. You can use this to compare value CellType
if(cell.getCellTypeEnum() == CellType.STRING){
//write your code here
}
Now lets us take an same example which was used to read xls file and the read the xlsx file content.
The above xlsx file has 2 columns 'Username' and 'Password' with 5 rows . We should be able to read the content using below example.
package com.read;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
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 ReadXlsx {
public void readXLSXFile(String fileName) {
InputStream XlsxFileToRead = null;
XSSFWorkbook workbook = null;
try {
XlsxFileToRead = new FileInputStream(fileName);
//Getting the workbook instance for xlsx file
workbook = new XSSFWorkbook(XlsxFileToRead);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
//getting the first sheet from the workbook using sheet name.
// We can also pass the index of the sheet which starts from '0'.
XSSFSheet sheet = workbook.getSheet("Sheet1");
XSSFRow row;
XSSFCell cell;
//Iterating all the rows in the sheet
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
row = (XSSFRow) rows.next();
//Iterating all the cells of the current row
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
cell = (XSSFCell) cells.next();
if (cell.getCellTypeEnum() == CellType.STRING) {
System.out.print(cell.getStringCellValue() + " ");
} else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
System.out.print(cell.getNumericCellValue() + " ");
} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
System.out.print(cell.getBooleanCellValue() + " ");
} else { // //Here if require, we can also add below methods to
// read the cell content
// XSSFCell.CELL_TYPE_BLANK
// XSSFCell.CELL_TYPE_FORMULA
// XSSFCell.CELL_TYPE_ERROR
}
}
System.out.println();
try {
XlsxFileToRead.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
ReadXlsx readXlsx = new ReadXlsx();
readXlsx.readXLSXFile("C:/testXlsxRead.xlsx");
}
}
You may see below exception if you don't add 'xmlbeans-2.x.x'
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject
And you may also come across the below exception if you don't add jar 'poi-ooxml-schemas-3.x-xxx'
Exception in thread "main" java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet
The below is the other exception that you get, if we don't add jar 'dom4j-1.x.x '
Exception in thread "main" java.lang.NoClassDefFoundError: org/dom4j/DocumentException
If you are using Maven project, please add below two dependencies
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
After adding all the jars the program will get executed successfully.
Output of the above program should look like below:
Username Password
test1 1234.0
test2 test123
test3 test456
test4 test789
test5 test012
Hope the article helps you, Please feel free to comment, if you have any questions or issues with the above code.
Comments
getCellType() deprecated
What is the substitute for getCellType() bcoz it is showing deprecated?
Substitute for getCellType()
Hi Shweta,
Have you tried using getCellTypeEnum, instead? It looks like a possible substitute to me. Let know how it works for you!
Substitute for getCellType()
Hi Shweta!
Have you tried using getCellTypeEnum, instead. It looks like a possible replacement to me. Let me know how it works for you!
good program
Thank you seleniumeasy.com team and I executed above program successfully.
Thanks for this article.
Thanks for this article.
Problem with Iteraotor
Hello,
I have a problem with "Iterator" part of code from this site when I am trying to repeat it using Eclipse:
First isuue:
"iterator is a raw type. references to generic type iterator e should be parameterized"
and the second one is:
"the method rowiterator() from the type xssfsheet refers to the missing type row"
If you have any suggestions please let me know.
Best Regards,
Iterator
Hi kamil11,
I am guessing you are using a more recent version of Java that the one used for demonstration on this awesome tutorial, and therefore running into the generic-type related error. Try following the conflict resolution options provided by Eclipse and that could fix it for you automatically. Your code might resemble this:
Iterator<Row> rowItrtr = sheet.rowIterator();
You can find more information about generic types here:
https://en.wikipedia.org/wiki/Generics_in_Java
This sample is not only
This sample is not only helpful with breaking down the basic read/ write process but also throws light into extrapolating the program further, on reading a plethora of possible inputs. On a side note, I think with Java 8 you don't even need to explicitly typecast the row handle to XSSFRow anymore.
Add new comment