• Java
    • JAXB Tutorial
      • What is JAXB
      • JAXB Marshalling Example
      • JAXB UnMarshalling Example
  • Spring Tutorial
    • Spring Core Tutorial
    • Spring MVC Tutorial
      • Quick Start
        • Flow Diagram
        • Hello World Example
        • Form Handling Example
      • Handler Mapping
        • BeanNameUrlHandlerMapping
        • ControllerClassNameHandlerMapping
        • SimpleUrlHandlerMapping
      • Validation & Exception Handling
        • Validation+Annotations
        • Validation+ResourceBundle
        • @ExceptionHandler
        • @ControllerAdvice
        • Custom Exception Handling
      • Form Tag Library
        • Textbox Example
        • TextArea Example
        • Password Example
        • Dropdown Box Example
        • Checkboxes Example
        • Radiobuttons Example
        • HiddenValue Example
      • Misc
        • Change Config file name
    • Spring Boot Tutorial
  • Hibernate Tutorial
  • REST Tutorial
    • JAX-RS REST @PathParam Example
    • JAX-RS REST @QueryParam Example
    • JAX-RS REST @DefaultValue Example
    • JAX-RS REST @Context Example
    • JAX-RS REST @MatrixParam Example
    • JAX-RS REST @FormParam Example
    • JAX-RS REST @Produces Example
    • JAX-RS REST @Consumes Example
    • JAX-RS REST @Produces both XML and JSON Example
    • JAX-RS REST @Consumes both XML and JSON Example
  • Miscellaneous
    • JSON Parser
      • Read a JSON file
      • Write JSON object to File
      • Read / Write JSON using GSON
      • Java Object to JSON using JAXB
    • CSV Parser
      • Read / Write CSV file
      • Read/Parse/Write CSV File – OpenCSV
      • Export data into a CSV File
      • CsvToBean and BeanToCsv – OpenCSV

JavaInterviewPoint

Java Development Tutorials

How to Write Excel File in Java using POI

January 24, 2017 by javainterviewpoint Leave a Comment

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

Write Excel File in Java

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:

Write Excel File in Java using POI

Other interesting articles which you may like …

  • Jackson 2 JSON Parser – Convert JSON to/from Java Object
  • How to Convert JSON to / from Java Map using JACKSON
  • Jackson Tree Model Example – JsonNode
  • Jackson Streaming API Example | Read and Write JSON
  • Jackson JSON Example | ObjectMapper and @JSONView
  • How to Parse JSON to/from Java Object using Boon JSON Parser
  • How to Read and Write JSON using GSON
  • How to write JSON object to File in Java?
  • How to read JSON file in Java – JSONObject and JSONArray
  • Jersey Jackson JSON Tutorial
  • Spring REST Hello World Example – JSON and XML responses
  • How to Convert Java Object to JSON using JAXB
  • JAX-RS REST @Consumes both XML and JSON Example
  • JAX-RS REST @Produces both XML and JSON Example

Filed Under: J2EE, Java Tagged With: Write Excel File in Java

Leave a Reply Cancel 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.

Java Basics

  • JVM Architecture
  • Object in Java
  • Class in Java
  • How to Set Classpath for Java in Windows
  • Components of JDK
  • Decompiling a class file
  • Use of Class.forName in java
  • Use Class.forName in SQL JDBC

Oops Concepts

  • Inheritance in Java
  • Types of Inheritance in Java
  • Single Inheritance in Java
  • Multiple Inheritance in Java
  • Multilevel Inheritance in Java
  • Hierarchical Inheritance in Java
  • Hybrid Inheritance in Java
  • Polymorphism in Java – Method Overloading and Overriding
  • Types of Polymorphism in java
  • Method Overriding in Java
  • Can we Overload static methods in Java
  • Can we Override static methods in Java
  • Java Constructor Overloading
  • Java Method Overloading Example
  • Encapsulation in Java with Example
  • Constructor in Java
  • Constructor in an Interface?
  • Parameterized Constructor in Java
  • Constructor Chaining with example
  • What is the use of a Private Constructors in Java
  • Interface in Java
  • What is Marker Interface
  • Abstract Class in Java

Java Keywords

  • Java this keyword
  • Java super keyword
  • Final Keyword in Java
  • static Keyword in Java
  • Static Import
  • Transient Keyword

Miscellaneous

  • newInstance() method
  • How does Hashmap works internally in Java
  • Java Ternary operator
  • How System.out.println() really work?
  • Autoboxing and Unboxing Examples
  • Serialization and Deserialization in Java with Example
  • Generate SerialVersionUID in Java
  • How to make a class Immutable in Java
  • Differences betwen HashMap and Hashtable
  • Difference between Enumeration and Iterator ?
  • Difference between fail-fast and fail-safe Iterator
  • Difference Between Interface and Abstract Class in Java
  • Difference between equals() and ==
  • Sort Objects in a ArrayList using Java Comparable Interface
  • Sort Objects in a ArrayList using Java Comparator

Follow

  • Coding Utils

Useful Links

  • Spring 4.1.x Documentation
  • Spring 3.2.x Documentation
  • Spring 2.5.x Documentation
  • Java 6 API
  • Java 7 API
  • Java 8 API
  • Java EE 5 Tutorial
  • Java EE 6 Tutorial
  • Java EE 7 Tutorial
  • Maven Repository
  • Hibernate ORM

About JavaInterviewPoint

javainterviewpoint.com is a tech blog dedicated to all Java/J2EE developers and Web Developers. We publish useful tutorials on Java, J2EE and all latest frameworks.

All examples and tutorials posted here are very well tested in our development environment.

Connect with us on Facebook | Privacy Policy | Sitemap

Copyright ©2023 · Java Interview Point - All Rights Are Reserved ·