How to Write Excel File in Java using POI

In the previous article, we have learned how to read an Excel file using POI in this article we will learn how to Write Excel File in Java using POI API.

In order to use POI we need to have the following dependencies added in your project.

• poi-3.15.jar
• poi-ooxml-3.15.jar
• poi-ooxml-schemas-3.15.jar
• xmlbeans-2.6.jar

If you are running on maven add the below dependency to your pom.xml

 <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.15</version>
 </dependency>
 <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.15</version>
 </dependency>

Write Excel File in Java using POI – XLS format (2003 or earlier)

package com.javainterviewpoint;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

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

public class XLSWriter
{
    public static void main(String[] args)
    {
        try
        {
            String excelPath = "C:\\Football.xls";
            
            FileOutputStream fileOutputStream = new FileOutputStream(new File(excelPath));

            // Create Workbook instance holding .xls file
            HSSFWorkbook workbook = new HSSFWorkbook();

            // Create a new Worksheet
            HSSFSheet sheet = workbook.createSheet("Footbal Players");
            
            Object[][] footballPlayers = {
                    {"Name","Country"},
                    {"Ronaldo","Portugal"},
                    {"Rooney","England"},
                    {"Roben","Netherland"},
                    {"Messi","Argentina"}
            };
            
            int rownum = 0;
            
            for(Object[] player : footballPlayers)
            {
                Row row = sheet.createRow(rownum++);
                
                int colnum = 0;
                for(Object value : player)
                {
                    Cell  cell = row.createCell(colnum++);
                    if (value instanceof String) {
                        cell.setCellValue((String) value);
                    } else if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    }
                }
            }
            //Write workbook into the excel
            workbook.write(fileOutputStream);
            //Close the workbook
            workbook.close();

        } catch (IOException ie)
        {
            ie.printStackTrace();
        }
    }
}

• Create a new HSSFWorkbook instance

HSSFWorkbook workbook = new HSSFWorkbook();

• Create a new Worksheet “Football Players”

HSSFSheet sheet = workbook.createSheet("Footbal Players");

• footballPlayers[][] array will hold the array of string which has to be written into the sheet.

 Object[][] footballPlayers = {
                    {"Name","Country"},
                    {"Ronaldo","Portugal"},
                    {"Rooney","England"},
                    {"Roben","Netherland"},
                    {"Messi","Argentina"}
            };

• Iterate each object and create a new row for each object

 for(Object[] player : footballPlayers)
 {
    Row row = sheet.createRow(rownum++);

• Get the individual cells from the above-created rows and create a new cell using createCell() method

 for(Object value : player)
 {
    Cell  cell = row.createCell(colnum++);

• Finally, using the write() method of the workbook instance write it into the fileOutputStream and close the workbook.

 workbook.write(fileOutputStream);
 workbook.close();

Output :

You will have the Football.xls created with the below content

Read Excel File in Java using POI – XLSX format (2007 or later)

In order to write XLSX file format we just need to replace HSSF to XSSF in the above code.

package com.javainterviewpoint;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

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 XLSXWriter
{
    public static void main(String[] args)
    {
        try
        {
            String excelPath = "C:\\Jackson\\Football.xlsx";
            
            FileOutputStream fileOutputStream = new FileOutputStream(new File(excelPath));

            // Create Workbook instance holding .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook();

            // Create a new Worksheet
            XSSFSheet sheet = workbook.createSheet("Footbal Players");
            
            Object[][] footballPlayers = {
                    {"Name","Country"},
                    {"Ronaldo","Portugal"},
                    {"Rooney","England"},
                    {"Roben","Netherland"},
                    {"Messi","Argentina"}
            };
            
            int rownum = 0;
            
            for(Object[] player : footballPlayers)
            {
                Row row = sheet.createRow(rownum++);
                
                int colnum = 0;
                for(Object value : player)
                {
                    Cell  cell = row.createCell(colnum++);
                    if (value instanceof String) {
                        cell.setCellValue((String) value);
                    } else if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    }
                }
            }
            //Write workbook into the excel
            workbook.write(fileOutputStream);
            //Close the workbook
            workbook.close();

        } catch (IOException ie)
        {
            ie.printStackTrace();
        }
    }
}

Convert Java Object To Excel

In order to convert Java objectTo Excel, we will be writing real-time java object instead of writing String array to the excel. Let’s see how we can achieve this.

package com.javainterviewpoint;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

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

public class ConvertJavaToExcel
{
    public static void main(String[] args)
    {
        List footballPlayers = new ArrayList();
        
        footballPlayers.add(new Football("Klose","Germany"));
        footballPlayers.add(new Football("Gerrard","England"));
        footballPlayers.add(new Football("Drogba","Ivory Coast"));
        footballPlayers.add(new Football("Zidane","France"));
        
        ConvertJavaToExcel(footballPlayers);
    }
    public static void ConvertJavaToExcel(List players)
    {
        try
        {
            String excelPath = "C:\\Jackson\\Football1.xlsx";
            FileOutputStream fileOutputStream = new FileOutputStream(new File(excelPath));

            // Create Workbook instance holding .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook();

            // Create a new Worksheet
            XSSFSheet sheet = workbook.createSheet("Footbal Players");
            
            int rownum = 0;
            
            for(Football player : players)
            {
                Row row = sheet.createRow(rownum++);
                
                int cellnum = 0;
                //Get the name put in the first cell
                row.createCell(cellnum++).setCellValue(player.getName());

                //Get the country put in the second cell
                row.createCell(cellnum++).setCellValue(player.getCountry());
            }
            //Write workbook into the excel
            workbook.write(fileOutputStream);
            //Close the workbook
            workbook.close();
        } catch (IOException ie)
        {
            ie.printStackTrace();
        }
    }
}

Output: