How to write Excel files in java using Apache POI

Table of Contents

Some basics about Apache POI:
Dependency:
write excel file using poi:
Java Program:

In this post, we will see how to write excel in java using Apache POI example.

The Apache POI Project’s mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java.

Some basics about Apache POI:

There are two prefixes which you encounter while reading/writing excel in java

HSSF: Used for dealing with files excel 2003 or earlier(.xls). Some of classes with HSSF prefix are HSSFWorkbook , HSSFSheet , HSSFRow and HSSFCell.

XSSF: Used for dealing with files excel 2007 or later(.xlsx). Some of classes with XSSF prefix are XSSFWorkbook , XSSFSheet , XSSFRow and XSSFCell.

Here are few classes which you need to aware of.

Workbook : This is high level class for representing excel workbook.
Sheet : This is high level class for representing excel sheet.
Row : This is high level class for representing excel row. It has methods which are related to row.
Cell: This is high level class for representing individual excel cell. It has methods which are related to cell for example : getDataType().

Dependency:

If you are using maven, then you need to add below dependency in pom.xml.


   org.apache.poi
   poi
   3.13

   org.apache.poi
   poi-ooxml
   3.13

org.apache.poi

poi

3.13

org.apache.poi

poi-ooxml

3.13

If you are not using maven, then you need to add below jars in classpath.

poi-3.13.jar
commons-codec-1.9.jar
poi-ooxml-3.13.jar
poi-ooxml-schemas-3.13.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar

write excel file using poi:

Create a blank workbook

XSSFWorkbook workbook = new XSSFWorkbook();

Create a sheet and pass name of the sheet

XSSFSheet sheet = workbook.createSheet(“Country”);

Create row

Row row = sheet.createRow(rownum++);

Create cells, set its value and add cell to above row

Cell cell = row.createCell(cellnum++); if(obj instanceof String) cell.setCellValue((String)obj); else if(obj instanceof Double) cell.setCellValue((Double)obj); else if(obj instanceof Integer) cell.setCellValue((Integer)obj);

Repeat 3 and 4 until you have data

Java Program:

We are going to write excel file named “CountriesDetails.xlsx” Create WriteExcelMain.java as below


package org.arpit.java2blog;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;

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 WriteExcelMain {

 public static void main(String[] args) throws IOException {
  writeFileUsingPOI();
 }

 public static void writeFileUsingPOI() throws IOException
 {
  //create blank workbook
  XSSFWorkbook workbook = new XSSFWorkbook();

  //Create a blank sheet
  XSSFSheet sheet = workbook.createSheet("Country");

  ArrayList<Object[]> data=new ArrayList<Object[]>();
  data.add(new String[]{"Country","Capital","Population"});
  data.add(new Object[]{"India","Delhi",10000});
  data.add(new Object[]{"France","Paris",40000});
  data.add(new Object[]{"Germany","Berlin",20000});
  data.add(new Object[]{"England","London",30000});

  //Iterate over data and write to sheet
  int rownum = 0;
  for (Object[] countries : data)
  {
   Row row = sheet.createRow(rownum++);

   int cellnum = 0;
   for (Object obj : countries)
   {
    Cell cell = row.createCell(cellnum++);
    if(obj instanceof String)
     cell.setCellValue((String)obj);
    else if(obj instanceof Double)
     cell.setCellValue((Double)obj);
    else if(obj instanceof Integer)
     cell.setCellValue((Integer)obj);
   }
  }
  try
  {
   //Write the workbook in file system
   FileOutputStream out = new FileOutputStream(new File("CountriesDetails.xlsx"));
   workbook.write(out);
   out.close();
   System.out.println("CountriesDetails.xlsx has been created successfully");
  }
  catch (Exception e)
  {
   e.printStackTrace();
  }
  finally {
   workbook.close();
  }
 }

}

package org.arpit.java2blog;

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.ArrayList;

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 WriteExcelMain {

public static void main(String[] args) throws IOException {

writeFileUsingPOI();

}

public static void writeFileUsingPOI() throws IOException

{

//create blank workbook

XSSFWorkbook workbook = new XSSFWorkbook();

//Create a blank sheet

XSSFSheet sheet = workbook.createSheet("Country");

ArrayList<Object[]> data=new ArrayList<Object[]>();

data.add(new String[]{"Country","Capital","Population"});

data.add(new Object[]{"India","Delhi",10000});

data.add(new Object[]{"France","Paris",40000});

data.add(new Object[]{"Germany","Berlin",20000});

data.add(new Object[]{"England","London",30000});

//Iterate over data and write to sheet

int rownum = 0;

for (Object[] countries : data)

{

Row row = sheet.createRow(rownum++);

int cellnum = 0;

for (Object obj : countries)

{

Cell cell = row.createCell(cellnum++);

if(obj instanceof String)

cell.setCellValue((String)obj);

else if(obj instanceof Double)

cell.setCellValue((Double)obj);

else if(obj instanceof Integer)

cell.setCellValue((Integer)obj);

}

try

{

//Write the workbook in file system

FileOutputStream out = new FileOutputStream(new File("CountriesDetails.xlsx"));

workbook.write(out);

out.close();

System.out.println("CountriesDetails.xlsx has been created successfully");

}

catch (Exception e)

{

e.printStackTrace();

}

finally {

workbook.close();

}

When you run above program, you will get following output:


CountriesDetails.xlsx has been created successfully

CountriesDetails.xlsx has been created successfully

Lets see content of CountriesDetails.xlsx now.

Was this post helpful?

Let us know if this post was helpful. Feedbacks are monitored on daily basis. Please do provide feedback as that\'s the only way to improve.

How to write Excel files in java using Apache POI

Some basics about Apache POI:

Dependency:

write excel file using poi:

Java Program:

Was this post helpful?

Author

Comments

Leave a Reply Cancel reply

Categories

Popular Posts

Let’s be Friends

Some basics about Apache POI:

Dependency:

write excel file using poi:

Java Program:

Was this post helpful?

Related posts:

Share this

Author

Comments

Leave a Reply Cancel reply

Let’s be Friends