An Apache POI is an open-source Java API to manipulate various Microsoft Office documents, Office Open XML (OOXML) standard, and OLE2 standard from Microsoft. We can Read and Write Excel files, Word, and PowerPoint documents. In this tutorial, we are going to see how to read and write Excel files in the format .xls and .xlsx.
Apache POI classes usually start with HSSF, XSSF, or SXSSF.
HSSF – is for the Excel 97-2003 workbook .xls file format. Example: HSSFWorkbook, HSSFSheet.
XSSF – is for the Excel 2007 .xlsx file format. Example: Example: XSSFWorkbook, XSSFSheet.
Apache POI setup through Maven :
Open the pom.xml file in the Maven workspace and paste the below dependencies
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-lite</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>5.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.20.0</version>
</dependency>
Read Excel File .xls format:
Steps :
1. Open the Excel File
2. Access the Sheet
3. Iterate till the Last row
4. Iterate each cell
5. Display the cell value based on Cell Type, Numberic, String, and Boolean value.
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
public class ExcelTest {
public static void main(String[] args) {
HSSFWorkbook xssfWorkbook = null;
HSSFSheet sheet1;
try {
// New file will be created in the root folder of the workspace
FileInputStream file = new FileInputStream(new File("Testdata.xls"));
HSSFWorkbook workBook = new HSSFWorkbook(file);
// Read the data in the Sheet 1
sheet1 = workBook.getSheetAt(0);
Iterator rowIterator = sheet1.iterator();
// Get Last Row number
int rowCount = sheet1.getLastRowNum();
XSSFRow roww ;
int currentColumnNum = 0;
Iterator cellIterator;
while (rowIterator.hasNext())
{
roww = rowIterator.next();
//For each row, iterate through all the columns
cellIterator = roww.cellIterator();
int colu = 0;
// Iterate Each cell
while (cellIterator.hasNext())
{
cell = cellIterator.next();
//Check the cell type and display
switch (cell.getCellType())
{
case NUMERIC:
System.out.print(cell.getNumericCellValue() + " - Numeric value");
break;
case STRING:
System.out.print(cell.getStringCellValue() + " - String value");
break;
case BOOLEAN:
System.out.print(cell.getBooleanCellValue() + " - Boolean value");
break;
default:
break;
}
} |
}
} catch (Exception e) {
System.out.println(e);
}
}
Read Excel File .xlsx format:
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
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;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
public class ExcelTest {
public static void main(String[] args) {
XSSFWorkbook xssfWorkbook = null;
XSSFSheet sheet1;
try {
// New file will be created under the root folder of the workspace
FileInputStream file = new FileInputStream(new File("Testdata.xlsx"));
XSSFWorkbook workBook = new XSSFWorkbook(file);
sheet1 = workBook.getSheetAt(0);
Iterator rowIterator = sheet1.iterator();
Row roww;
Iterator cellIterator;
while (rowIterator.hasNext())
{
roww = rowIterator.next();
//For each row, iterate through all the columns
cellIterator = roww.cellIterator();
int colu = 0;
while (cellIterator.hasNext())
{
cell = cellIterator.next();
//Check the cell type and display
switch (cell.getCellType())
{
case NUMERIC:
System.out.print(cell.getNumericCellValue() + " - Numberic value");
break;
case STRING:
System.out.print(cell.getStringCellValue() + " - String value");
break;
case BOOLEAN:
System.out.print(cell.getBooleanCellValue() + " - Boolean value");
break;
default:
break;
} |
}
}
} catch (Exception e) {
System.out.println(e);
}
}
}
Write Excel File:
Steps:
1. Open the Excel File
2. Create a row and cells
3. Set cell values to each cell
4. Write to the Output stream
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
public class WriteExcelFile {
public static void main(String[] args) {
XSSFSheet sheet;
int rowCount = 0;
try {
// Open the file in the root folder of the workspace
FileInputStream file = new FileInputStream(new File("Testdata.xlsx"));
XSSFWorkbook workBook = new XSSFWorkbook(file);
sheet = workBook.getSheetAt(0);
Object[][] studentInfo = {
{"PersonName", "Age", "Adult"},
{"Daniel", "23", true},
{"Smith", "55", true},
{"Regan", "11", false},
{"Paul", "33", true},
};
for (Object[] sinfo : studentInfo) {
Row row = sheet.createRow(++rowCount);
int columnCount = 0;
for (Object field : sinfo) {
Cell cell = row.createCell(++columnCount);
if (field instanceof String) {
cell.setCellValue((String) field);
} else if (field instanceof Integer) {
cell.setCellValue((Integer) field);
}else if (field instanceof Boolean) {
cell.setCellValue((Boolean) field);
}
}
}
try (FileOutputStream outputStream = new FileOutputStream("Testdata.xlsx")) {
workBook.write(outputStream);
}
} catch (Exception e) {
System.out.println(e);
}
}
}
