In this Blog, we will find out
- How to read xls, xlsx format Excel sheet using Apache POI API
- 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
0 Comments