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 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 support xlsx format as well.

I implemented this in Spring Maven based Project.

Apache POI API

  • We need to implement XSSFWorkbook for xlsx format keeping 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 above code based on file extension we will implement either xls or xlsx format.
Please note that we have common interface Workbook.

Once we are done with File Type now we need to read Sheet, Row, Column or Cell.
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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.