Write data into Excel file using Apache POI

IN my previous post, I posted code for reading excel file using Apache POI library. In this post I'll cover, how can we write data into excel. Here is the code:

package com.qa.test;

import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class SpreadSheetWriter {
	
	 /** This method writes data to new excel file **/
    public static void writeDataToExcelFile(List> data, String fileName) 
    {
    	try{
	    	HSSFWorkbook myWorkBook = new HSSFWorkbook();
	        HSSFSheet mySheet = myWorkBook.createSheet();
	        HSSFRow myRow = null;
	        HSSFCell myCell = null;
	        
	        //Create header row
	        createHeaderRow(mySheet);
	        
	        int rowNum = 1;
	        Iterator> iter = data.iterator();
	        while(iter.hasNext())
	        {
	        	myRow = mySheet.createRow(rowNum++);
	        	int cellNum = 0;
	        	
				List key = iter.next();
				for(String values: key)
				{
					myCell = myRow.createCell(cellNum++);
					myCell.setCellValue(values);
				}
			}
	
	        FileOutputStream out = new FileOutputStream(fileName);
            myWorkBook.write(out);
            out.close();
        }catch(Exception e){ 
        	e.printStackTrace();
        }         
   }
    
 }

Read Excel file using apache POI library

If you want to read Excel file in Java, Apache POI is the library which supports to read/write microsoft documents. Here is the code to read an Excel file.

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;

public class ReadExcel {
 
 public static void main(String[] args){
  
  try{
   InputStream myxls = new FileInputStream("C:\\exceltest.xls");
   HSSFWorkbook wb     = new HSSFWorkbook(myxls);
   
   HSSFSheet sheet = wb.getSheetAt(0); 
   
   Iterator rows = sheet.rowIterator();
   
   System.out.println("Total Rows :: " + sheet.getLastRowNum());
   
   //Iterate over each row
   while (rows.hasNext()) 
   {
    HSSFRow row = (HSSFRow) rows.next();
    if(checkIfRowEmpty(row)) 
     continue;
    
    int minColIndex = row.getFirstCellNum();
    int maxColIndex = row.getLastCellNum();
    
    for(int colIndex = minColIndex; colIndex <= maxColIndex; colIndex++) 
    {
     HSSFCell cell = row.getCell(colIndex);
     
     // POI can't recognize empty cells of Excel, so we should create new cells with a blank value
     if(cell == null) 
     {
        cell = row.createCell(colIndex);
           cell.setCellValue(" ");
     }    
     String value = "";
     
     switch(cell.getCellType())
     {
      case HSSFCell.CELL_TYPE_NUMERIC:
       value = Double.toString(cell.getNumericCellValue());
       break;
      case HSSFCell.CELL_TYPE_BOOLEAN:
       value = Boolean.toString(cell.getBooleanCellValue());
       break;
      case HSSFCell.CELL_TYPE_BLANK:
       value = "";
       break;
      case HSSFCell.CELL_TYPE_ERROR:
       value = "";
       break;
      case HSSFCell.CELL_TYPE_FORMULA:
       value = "";
       break;
      default:
       value = cell.getStringCellValue();
       break;
     }
     System.out.print(value + "|");
    }
    System.out.println();
   }
  }
  catch(Exception e){
   e.printStackTrace();
  }
 }
 
 public static boolean checkIfRowEmpty(HSSFRow row) 
 {
  int minColumIndex = row.getFirstCellNum();
  int maxColumnIndex = row.getLastCellNum();
  
  boolean isRowEmpty = true;
  for(int columIndex = minColumIndex; columIndex <= maxColumnIndex; columIndex++) 
  {
   HSSFCell cell = row.getCell(columIndex);
   if(cell == null || cell.toString().trim().isEmpty() || cell.toString().length() == 0){
    
   }
   else{
    isRowEmpty = false;
    return isRowEmpty;
   }
  }
  return isRowEmpty;
 }
}