Apache POI – Java Read & Write Excel

Apache POI is an excellent library to read and write MS-Office files. Here we will see simple example to read/write “create” excel files.

java-excel

Objective:

  • How to use Apache POI to read or write excel file?

Environment & Tools:

  • Eclipse
  • Maven

Library:

  • Apache POI 3.9

( 1 ) Write “Create” Excel File

  1. Create workbook “document”
  2. Create sheet “you can have more than one”
  3. Create row
  4. Create cell & set the content
  5. Create the file
package com.hmkcode.poi;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class AppWrite
{
    public static void main( String[] args )
    {
       
        //1. Create a new Workbook
        Workbook wb = new XSSFWorkbook();

        //2. Create a new sheet
        Sheet sheet = wb.createSheet("sheet 1");

        //3. Create a row
        Row row = sheet.createRow((short) 0);

        //4. Create cells 

        	//4.1 number cell
        	row.createCell(0).setCellValue(1);
        	//4.2 text 
        	row.createCell(1).setCellValue("Text");
        
	        //4.3 date cell
	        CreationHelper createHelper = wb.getCreationHelper();
	        CellStyle cellStyle = wb.createCellStyle();
	        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
	        Cell cell = row.createCell(2);
	        cell.setCellValue(new Date());
	        cell.setCellStyle(cellStyle);
	
	        //4.4 boolean cell
	        row.createCell(3).setCellValue(true);
        
        //5. create excel file
        FileOutputStream fileOut;
        try {
			
			fileOut = new FileOutputStream("workbook.xlsx");
			wb.write(fileOut);
			fileOut.close();
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
        
        System.out.println( "File created!" );

    }
}

( 2 ) Read Excel File

package com.hmkcode.poi;


import java.io.File;
import java.io.IOException;
import java.util.Date;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class AppRead
{
    public static void main( String[] args )
    {
    	Workbook wb = null;
    	
    	//1. Open the file
    	  try {
			wb = WorkbookFactory.create(new File("workbook.xlsx"));
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
    	 
    	//2. Open a sheet
    	Sheet sheet = wb.getSheetAt(0);
    	
    	
    	//3. Get each cell by row & column number
    	Cell cell = sheet.getRow(0).getCell(0);
    	double numberVal = cell.getNumericCellValue();
    	System.out.println("Row: 0 - Column: 0 = "+numberVal);
    	//-----------------------------
    	cell = sheet.getRow(0).getCell(1);
    	String stringVal = cell.getStringCellValue();
    	System.out.println("Row: 0 - Column: 1 = "+stringVal);
    	//-----------------------------
    	cell = sheet.getRow(0).getCell(2);
    	Date dateVal = cell.getDateCellValue();
    	System.out.println("Row: 0 - Column: 2 = "+dateVal);
    	//-----------------------------
    	cell = sheet.getRow(0).getCell(3);
    	boolean booleanVal = cell.getBooleanCellValue();
    	System.out.println("Row: 0 - Column: 3 = "+booleanVal);
    	//-----------------------------
    	
    }
}

Source Code @ GitHub