Reading and writing data to Excel files

How to read and write excel file in Java using Apache POI?

• Apache POI is a powerful Java library to work with different Microsoft Office file formats such as Excel, Power point, Visio, MS Word etc. 
• The name POI was originally an acronym for Poor Obfuscation Implementation, referring humorously to the fact that the file formats seemed to be deliberately obfuscated, but poorly, since they were successfully reverse-engineered.
• In this tutorial we will use Apache POI library to perform different functions on Microsoft Excel spreadsheet.

Before you start, you have to add required JAR files in your classpath.

1. Download poi-2.5.1.jar(or in this case 3.8) jar file.
2. Include this file in your projects class path.
3. Create new java project in eclipse with auto generated main function.

Example demonstrating how to read excel file:

package com.latest;
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelDemo 
{
   public static void main(String[] args) 
   {
       try
       {
           FileInputStream file = new FileInputStream(new File("howtodoinjava_demo.xlsx"));
 
           //Create Workbook instance holding reference to .xlsx file
           XSSFWorkbook workbook = new XSSFWorkbook(file);
 
           //Get first/desired sheet from the workbook
           XSSFSheet sheet = workbook.getSheetAt(0);
 
           //Iterate through each rows one by one
           Iterator<Row> rowIterator = sheet.iterator();
           while (rowIterator.hasNext()) 
           {
               Row row = rowIterator.next();
               //For each row, iterate through all the columns
               Iterator<Cell> cellIterator = row.cellIterator();
                
               while (cellIterator.hasNext()) 
               {
                   Cell cell = cellIterator.next();
                   //Check the cell type and format accordingly
                   switch (cell.getCellType()) 
                   {
                       case Cell.CELL_TYPE_NUMERIC:
                           System.out.print(cell.getNumericCellValue() + "t");
                           break;
                       case Cell.CELL_TYPE_STRING:
                           System.out.print(cell.getStringCellValue() + "t");
                           break;
                   }
               }
               System.out.println("");
           }
           file.close();
       } 
       catch (Exception e) 
       {
           e.printStackTrace();
       }
   }
}


Example demonstrating how to write to Excel file :

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcelDemo 
{
    public static void main(String[] args) 
    {
        //Blank workbook
        XSSFWorkbook workbook = new XSSFWorkbook(); 
         
        //Create a blank sheet
        XSSFSheet sheet = workbook.createSheet("Employee Data");
          
        //This data needs to be written (Object[])
        Map<String, Object[]> data = new TreeMap<String, Object[]>();
        data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
        data.put("2", new Object[] {1, "Amit", "Shukla"});
        data.put("3", new Object[] {2, "Lokesh", "Gupta"});
        data.put("4", new Object[] {3, "John", "Adwards"});
        data.put("5", new Object[] {4, "Brian", "Schultz"});
          
        //Iterate over data and write to sheet
        Set<String> keyset = data.keySet();
        int rownum = 0;
        for (String key : keyset)
        {
            Row row = sheet.createRow(rownum++);
            Object [] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr)
            {
               Cell cell = row.createCell(cellnum++);
               if(obj instanceof String)
                    cell.setCellValue((String)obj);
                else if(obj instanceof Integer)
                    cell.setCellValue((Integer)obj);
            }
        }
        try
        {
            //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
        }
    }
}

<-- Previous || Next -->