Table of Contents
In this post, we will see how to read excel in java using Apache POI example.
The Apache POI Project’s mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java.
Some basics about Apache POI:
There are two prefixes which you encounter while reading/writing excel in java
HSSF: Used for dealing with files excel 2003 or earlier(.xls). Some of classes with HSSF prefix are HSSFWorkbook , HSSFSheet , HSSFRow and HSSFCell.
XSSF: Used for dealing with files excel 2007 or later(.xlsx). Some of classes with XSSF prefix are XSSFWorkbook , XSSFSheet , XSSFRow and XSSFCell.
Here are few classes which you need to aware of.
- Workbook : This is high level class for representing excel workbook.
- Sheet :Â This is high level class for representing excel sheet.
- Row :Â This is high level class for representing excel row. It has methods which are related to row.
- Cell:Â This is high level class for representing individual excel cell. It has methods which are related to cell for example : getDataType().
Dependency:
If you are using maven, then you need to add below dependency in pom.xml.
|
1 2 3 4 5 6 7 8 9 |
org.apache.poi poi 3.13 org.apache.poi poi-ooxml 3.13Â |
If you are not using maven, then you need to add below jars in classpath.
- poi-3.13.jar
- commons-codec-1.9.jar
- poi-ooxml-3.13.jar
- poi-ooxml-schemas-3.13.jar
- xmlbeans-2.6.0.jar
- stax-api-1.0.1.jar
Read excel file using poi:
Java Program:
We are going to read countries.xlsx. Its content is :
Create ReadWriteExcelMain.java as below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
package org.arpit.java2blog; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.Iterator; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ReadExcelMain { public static void main(String[] args) throws IOException { readFileUsingPOI(); } public static void readFileUsingPOI() throws IOException { ClassLoader classLoader = ReadWriteExcelMain.class.getClassLoader(); String excelFilePath = "Countries.xlsx"; FileInputStream inputStream = new FileInputStream(new File(classLoader.getResource(excelFilePath).getFile())); Workbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); Iterator iterator = sheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); Iterator cellIterator = nextRow.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue()); break; } System.out.print(" | "); } System.out.println(); } workbook.close(); inputStream.close(); } } |
|
1 2 3 4 5 6 7 |
Country | Capital | Population | India | Delhi | 10000.0 | France | Paris | 40000.0 | Germany | Berlin | 20000.0 | England | London | 30000.0 | |
Lets be more object oriented.
We will read each row and create country object. Obviously we will skip header row.
Create a class called Country.java in package com.arpit.java2blog.model
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
package com.arpit.java2blog.model; public class Country { String name; String capital; double population; //getter and setter methods public String getName() { return name; } public void setName(String name) { this.name = name; } public double getPopulation() { return population; } public void setPopulation(double population) { this.population = population; } public String getCapital() { return capital; } public void setCapital(String capital) { this.capital = capital; } public String toString() { return name+" | "+capital+" | "+population ; } } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
package org.arpit.java2blog; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.arpit.java2blog.model.Country; public class ReadExcelWithCountryMain { public static void main(String[] args) throws IOException { List countries=readFileUsingPOI(); for(Country country:countries) { System.out.println(country.toString()); } } public static List readFileUsingPOI() throws IOException { List countries=new ArrayList(); ClassLoader classLoader = ReadExcelWithCountryMain.class.getClassLoader(); String excelFilePath = "Countries.xlsx"; FileInputStream inputStream = new FileInputStream(new File(classLoader.getResource(excelFilePath).getFile())); Workbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); Iterator iterator = sheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); // Not creating country object for header if(nextRow.getRowNum()==0) continue; Country countryObj=new Country(); Iterator cellIterator = nextRow.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int columnIndex=cell.getColumnIndex(); switch (columnIndex+1) { case 1: countryObj.setName(cell.getStringCellValue()); break; case 2: countryObj.setCapital(cell.getStringCellValue()); break; case 3: countryObj.setPopulation(cell.getNumericCellValue()); break; } } countries.add(countryObj); } workbook.close(); inputStream.close(); return countries; } } |
|
1 2 3 4 5 6 |
India | Delhi | 10000.0 France | Paris | 40000.0 Germany | Berlin | 20000.0 England | London | 30000.0 |
Was this post helpful?
Let us know if this post was helpful. Feedbacks are monitored on daily basis. Please do provide feedback as that\'s the only way to improve.
I cannot run program,i use 2010 excel version.my excel file path is under D,so
String excelFilePath = “D:\\Countries.xlsx”;
FileInputStream inputStream = new FileInputStream(new File(classLoader.getResource(excelFilePath).getFile()));
i have that Exception.
Exception in thread “main” java.lang.NullPointerException
at com.excel.ReadExcelMain.readFileUsingPOI(ReadExcelMain.java:26)
at com.excel.ReadExcelMain.main(ReadExcelMain.java:19)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)
Thank & Regards
SuLabt
Hi,
If you are directly using absolute path of the file, please use below code
String excelFilePath = “D:\\\\Countries.xlsx”;
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
Let me know if it works now
Thanks