Apache POI - Java Library For Generating Excel Report
1. Basic
definitions for Apache POI library
This section briefly describes about basic classes we’ll use during
Excel Read and Write.
1. HSSF indicates
operations related to a Microsoft Excel 2003 file.
2. XSSF indicates
operations related to a Microsoft Excel 2007 file or later.
3. XSSFWorkbook and HSSFWorkbook are classes used
to use excel workbook
4. HSSFSheet and XSSFSheet are classes used
to use excel workbook
5. Row indicates an
Excel row
6. Cell indicates an
Excel cell addressed in reference to a row.
2. Download Apache POI
Apache POI library is easily available using Maven Dependencies.
pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
3. Apache POI library – Writing a
Simple Excel
The following code shows how to write a simple Excel file using Apache POI libraries. Here we’ll use a 2-dimensional data array to hold the data. The data is written to a XSSFWorkbook object which created the excel file and XSSFSheet creates and uses work sheet. The code is as shown below:
ApachePOIExcelWrite.java
package com.codingthunder;
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.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriteApachePOI {
private static final String FILE_NAME = "/MyFirstExcel.xlsx";
public static void main(String[]
args) {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet =
wb.createSheet("Datatypes in Java");
Object[][] datatypes = {
{"Datatype", "Type", "Size(in
bytes)"},
{"int", "Primitive", 2},
{"float", "Primitive", 4},
{"double", "Primitive", 8},
{"char", "Primitive", 1},
{"String", "Non-Primitive", "No
fixed size"}
};
int
rowNum = 0;
for (Object[]
datatype : datatypes) {
Row row
= sheet.createRow(rowNum++);
int
colNo = 0;
for (Object field :
datatype) {
Cell cell = row.createCell(colNo++);
if (field instanceof String) {
cell.setCellValue((String) field);
} else if (field instanceof Integer) {
cell.setCellValue((Integer)
field);
}
}
}
try {
FileOutputStream os = new FileOutputStream(FILE_NAME);
wb.write(os); //write into the file
wb.close(); //closing the file
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("Done");
}
}
After executing the above code, you get below excel as an output.
4. Apache POI library – Reading an
Excel file
The following code explains how to read an Excel file using Apache POI
libraries. The function getCellTypeEnum is deprecated
now from version 3.15 and has been renamed to getCellType from version
4.0 onwards.
ExcelReadApachePOI.java
package com.codingthunder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
public class ExcelReadApachePOI {
private static final String FILE_NAME = "/MyFirstExcel.xlsx";
public static void main(String[]
args) {
try {
FileInputStream file = new FileInputStream(new File(FILE_NAME));
Workbook wb = new XSSFWorkbook(file);
Sheet datatypeSheet = wb.getSheetAt(0);
Iterator<Row> iterator = datatypeSheet.iterator();
while (iterator.hasNext()) {
Row currentRow = iterator.next();
Iterator<Cell> cellIterator =
currentRow.iterator();
while (cellIterator.hasNext()) {
Cell currentCell =
cellIterator.next();
//getCellTypeEnum shown as deprecated
for version 3.15
//getCellTypeEnum ill be renamed to
getCellType starting from version 4.0
if (currentCell.getCellTypeEnum() == CellType.STRING) {
System.out.print(currentCell.getStringCellValue() + "--");
} else if (currentCell.getCellTypeEnum() == CellType.NUMERIC) {
System.out.print(currentCell.getNumericCellValue() + "--");
}
}
System.out.println();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
After we execute the above code, we'll get the below output.
Datatype--Type--Size(in bytes)--
int--Primitive--2.0--
float--Primitive--4.0--
double--Primitive--8.0--
char--Primitive--1.0--
String--Non-Primitive--No
fixed size--
Comments
Post a Comment
Please Share Your Views