In this Blog, we will find out

  1. How to read xls, xlsx format Excel sheet using Apache POI API
  2. How to write into an Excel sheet using Apache POI API

Java doesn’t provide built-in support for working with excel files so we need to rely on  Open Source APIs. I came across Apache POI.

Reason I choose

  • Apache POI provides stream-based processing. So memory usage is less.
  • It supports xlsx format as well.

I implemented this in Spring Maven-based Project.

Apache POI API

  • We need to implement XSSFWorkbook for xlsx format keeping the interface as Workbook common.
  • We need to implement HSSFWorkbook for xls format
Workbook workbook = null;
if(fileName.endsWith("xlsx")){
workbook = new XSSFWorkbook(fis);
}else if(fileName.endsWith("xls")){
workbook = new HSSFWorkbook(fis;
}else{
throw new Exception("Not a valid excel format");
}

In the above code based on file extension, we will implement either xls or xlsx format.
Please note that we have a common interface Workbook.

Once we are done with File Type now we need to read Sheet, Row, Column or Cell.
The following code will demonstrate that

Sheet sheet = workbook.getSheetAt(0); // Read first Sheet
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i); // get Row of Sheet
if (row != null) {
PolicyBaseTO mydata = new PolicyBaseTO();
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j); // Getting Cell or Column for each Row

}
}
}

To write in Excel sheet and add some formula Logic to a Cell (Here Sum of Columns)

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Numbers");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(10); // Creating Cell at 0 position of Row
row.createCell(1).setCellValue(20);
row.createCell(2).setCellValue(30);
//set formula cell
row.createCell(3).setCellFormula("A1*B1*C1"); // Setting formula to sum Up values of Cell 0,1,2

Full Code

// pom.xml for Apache POI jar

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>


// Helper Class

iimport org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import com.test.dfx.common.PolicyBaseTO;

@Component
public class ExcelPOIHelper {


public List<PolicyBaseTO> readExcel(String fileLocation) throws IOException {

List<PolicyBaseTO> data = new ArrayList<PolicyBaseTO>();
FileInputStream fis = new FileInputStream(new File(fileLocation));
data = readExcelSheet(fileLocation, fis);
return data;
}

// Get Cell Data based on Type
private String readCellContent(Cell cell) {
String content;
switch (cell.getCellTypeEnum()) {
case STRING:
content = cell.getStringCellValue();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
content = cell.getDateCellValue() + "";
} else {
content = cell.getNumericCellValue() + "";
}
break;
case BOOLEAN:
content = cell.getBooleanCellValue() + "";
break;
case FORMULA:
content = cell.getCellFormula() + "";
break;
default:
content = "";
}
return content;
}

// Read xls File
private List<PolicyBaseTO> readExcelSheet(String fileName, FileInputStream fis) throws IOException {
List<PolicyBaseTO> lstMyData = new ArrayList<PolicyBaseTO>();
Workbook workbook = null;
if(fileName.endsWith("xlsx")){
workbook = new XSSFWorkbook(fis);
}else if(fileName.endsWith("xls")){
workbook = new HSSFWorkbook(fis;
}else{
throw new Exception("Not a valid excel format");
}
try {
Boolean actualRowStarted = false;
Sheet sheet = workbook.getSheetAt(0); // Read first Sheet
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i); // get Row of Sheet
if (row != null) {
PolicyBaseTO mydata = new PolicyBaseTO();
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j); // Getting Cell or Column for each Row
if (cell != null) {
String cellContent = readCellContent(cell);
if(cellContent != null && cellContent.equalsIgnoreCase("Data")){
actualRowStarted = true;
}else if(actualRowStarted){
if(cell.getColumnIndex() == 1){
mydata.setSeqId(cellContent);
}else if(cell.getColumnIndex() == 11){
mydata.setPolicyDesc(cellContent);
}
}
}
}
if(mydata.getSourceZone() != null && !mydata.getSourceZone().trim().isEmpty()){
lstMyData.add(mydata);
}
}
}
} finally {
if (workbook != null) {
workbook.close();
}
}
return lstMyData;
}


private void writeExcelFile(String fileName, List<User> data){

Workbook workbook = null;
if(fileName.endsWith("xlsx")){
workbook = new XSSFWorkbook();
}else if(fileName.endsWith("xls")){
workbook = new HSSFWorkbook();
}else{
throw new Exception("Not a valid excel format");
}

Sheet sheet = workbook.createSheet("User");

Iterator<User> iterator = countryList.iterator();

int rowIndex = 0;
while(iterator.hasNext()){
User user = iterator.next();
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(user.getValue1()); // Creating Cell at 0 position of Row
row.createCell(1).setCellValue(20);
row.createCell(2).setCellValue(30);
row.createCell(3).setCellFormula("A1*B1*C1"); // Setting formula to sum Up values of Cell 0,1,2
}

FileOutputStream fos = new FileOutputStream(fileName);
workbook.write(fos);
fos.close();
}

}

Reference:

Journaldev: Apache POI

Categories: JAVA

0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *