Java Create Excel File

In Java, read excel file and write excel file is a bit tricky because excel worksheet has cells to store data. Java does not provide direct API to read or write Microsoft Excel or Word documents. We have to rely on the third-party library that is Apache POI. In this section, we will learn how to create an excel file in Java and how to write or insert data in the excel file using the Apache POI Java library.

Java Apache POI Library

Apache POI (Poor Obfuscation Implementation) is a Java API for reading and writing Microsoft Documents. It contains classes and interfaces. The Apache POI library provides two implementations for reading or writing excel files:

HSSF (Horrible SpreadSheet Format) Implementation:It denotes an API that is working with Excel 2003 or earlier versions.
XSSF (XML SpreadSheet Format) Implementation:It denotes an API that is working with Excel 2007 or later versions.

Throughout this section, we have used HSSF implementation.

Create Excel File in Java

Follow the steps given below to create an excel file in Java.

Step 1: Create a Java project in eclipse. We have created a Java project with the name CreateExcelEile.

Step 2: Create a class with the name CreateExcelFileExample1 and write the code that we have written in CreateExcelFileExample1.java file.

Step 3: Download the Apache POI library (poi-3.17.jar).

Step 4: Add the Apache POI to the project. Right-click on the project -> Build Path -> Configure Build Path. It opens the Properties window for the current project.

Step 5: In the Properties window, click on the Add External JARs button.

Step 6: Go to the path where the poi-3.17.jar file is located. Select the JAR file and click on the Open button. It adds the JAR file to the project. After that, click on the Apply and Close button to apply the changes.

When we have done with all the above steps, the project structure looks like the following:

Let's understand the following code.

In the following program, we have used the Apache POI library to create an excel file. The library provides the class named HSSFWorkbook defined in the org.apache.poi.hssf.usermodel package.

CreateExcelFileExample1.java

import java.io.*; 
import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
import org.apache.poi.ss.usermodel.Workbook; 
public class CreateExcelFileExample1 
{ 
public static void main(String[] args) throws FileNotFoundException, IOException 
{ 
//creating an instance of Workbook class 
Workbook wb = new HSSFWorkbook(); 
//creates an excel file at the specified location
OutputStream fileOut = new FileOutputStream("C:\\demo\\BankStatement.xlsx"); 
System.out.println("Excel File has been created successfully."); 
wb.write(fileOut); 
} 
}

Output:

It creates a blank excel file at the specified location.

Let's create another Java program that creates an excel file.

CreateExcelFileExample2.java

import java.io.*;
public class CreateExcelFileExample2
{
public static void main(String[]args) 
{
try 
{
String filename = "C:\\Users\\Anubhav\\Desktop\\CustomersDetail.xlsx";
FileOutputStream fileOut = new FileOutputStream(filename);
fileOut.close();
System.out.println("Excel file has been generated successfully.");
} 
catch (Exception e) 
{
e.printStackTrace();
}
}
}

Output:

It creates a blank excel file at the specified location.

Insert Data in the Excel File

Still, we have created a blank excel file. But it is not always necessary that create a blank excel file only. Some times we need to insert some data in the worksheet. Let's see how to insert data in an excel file through a Java program.

CreateExcelFileExample3.java

import  java.io.*;
import  org.apache.poi.hssf.usermodel.HSSFSheet;
import  org.apache.poi.hssf.usermodel.HSSFWorkbook;
import  org.apache.poi.hssf.usermodel.HSSFRow;
public class CreateExcelFileExample3
{
public static void main(String[]args) 
{
try 
{
//declare file name to be create 
String filename = "F:\\Demo Data\\Balance.xlsx";
//creating an instance of HSSFWorkbook class
HSSFWorkbook workbook = new HSSFWorkbook();
//invoking creatSheet() method and passing the name of the sheet to be created 
HSSFSheet sheet = workbook.createSheet("January"); 
//creating the 0th row using the createRow() method
HSSFRow rowhead = sheet.createRow((short)0);
//creating cell by using the createCell() method and setting the values to the cell by using the setCellValue() method
rowhead.createCell(0).setCellValue("S.No.");
rowhead.createCell(1).setCellValue("Customer Name");
rowhead.createCell(2).setCellValue("Account Number");
rowhead.createCell(3).setCellValue("e-mail");
rowhead.createCell(4).setCellValue("Balance");
//creating the 1st row
HSSFRow row = sheet.createRow((short)1);
//inserting data in the first row
row.createCell(0).setCellValue("1");
row.createCell(1).setCellValue("John William");
row.createCell(2).setCellValue("9999999");
row.createCell(3).setCellValue("william.john@gmail.com");
row.createCell(4).setCellValue("700000.00");
//creating the 2nd row
HSSFRow row1 = sheet.createRow((short)2);
//inserting data in the second row
row1.createCell(0).setCellValue("2");
row1.createCell(1).setCellValue("Mathew Parker");
row1.createCell(2).setCellValue("22222222");
row1.createCell(3).setCellValue("parker.mathew@gmail.com");
row1.createCell(4).setCellValue("200000.00");
FileOutputStream fileOut = new FileOutputStream(filename);
workbook.write(fileOut);
//closing the Stream
fileOut.close();
//closing the workbook
workbook.close();
//prints the message on the console
System.out.println("Excel file has been generated successfully.");
} 
catch (Exception e) 
{
e.printStackTrace();
}
}
}

Output: