In my last post Read excel using java API, I talked about reading the excel file using java API. In this post, I am going to explain how to create an excel file using java.

A Brief Idea

We are going to use Apache POI HSSF for creating excel file. Here I am going to explain how to create and write data into excel file.Apart from that, I will write a simple servlet to download excel file from server using client(browser).

Moving Ahead

I am going to do the following things here:

Read data from database table

Create an excel file and write tables' data into it

Download the excel file

Here is my table employee:

Here is my ExcelCreator.java class.
In this class, I am getting all four rows of the above table and pumping it into excel file.

package com.sarf.excel.model;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

publicclass ExcelCreator {

@SuppressWarnings("deprecation")

public String downloadExcel(ServletOutputStream out){

int nRow = 1;

String strQuery = null;

Connection con = null;

HSSFRow row;

HSSFCell cell;

try {

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("Employee");

/* Getting connection here for mysql database */

Class.forName("com.mysql.jdbc.Driver").newInstance();

con = DriverManager.getConnection

("jdbc:mysql://localhost:3306/test","sarfuser","password");

if(con==null)

return"Connection Failed";

/* Database Query */

strQuery = "select * from employee";

Statement stmt=con.createStatement();

ResultSet rs=stmt.executeQuery(strQuery);

/* Setting Font Style for Header Row */

sheet.setColumnWidth(0, 5000);

sheet.setColumnWidth(1, 7000);

sheet.setColumnWidth(3, 5000);

sheet.setColumnWidth(4, 5000);

/* Creating the Font Style here */

HSSFFont boldFont = wb.createFont();

boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

boldFont.setColor(HSSFFont.COLOR_RED);

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setFont(boldFont);

// Create a row for header

row = sheet.createRow( (short)0);

cell = row.createCell( (short)0);

cell.setCellValue("Employee Id");

cell.setCellStyle(cellStyle);

cell = row.createCell( (short)1);

cell.setCellValue("Employee Name");

cell.setCellStyle(cellStyle);

cell = row.createCell( (short)2);

cell.setCellValue("Location");

cell.setCellStyle(cellStyle);

cell = row.createCell( (short)3);

cell.setCellValue("Salary");

cell.setCellStyle(cellStyle);

// Reading one row of table at a time and

putting the values into excel cell

while(rs.next()){

row = sheet.createRow( (short)nRow);

// Create a cell and put a value in it.

cell = row.createCell( (short)0);

cell.setCellValue(rs.getString(1));

cell = row.createCell( (short)1);

cell.setCellValue(rs.getString(2));

cell = row.createCell( (short)2);

cell.setCellValue(rs.getString(3));

cell = row.createCell( (short)3);

cell.setCellValue(rs.getString(4));

cell = row.createCell( (short)4);

nRow++;

}

wb.write(out);

return"File downloaded successfully";

}

catch (Exception e) {

return e.getMessage();

}

}

}

Here we will see a servlet definition which will help us to download the created excel file.This servlet will call the downloadExcel() method and get the excel file.

Here is our DownloadExcelController.java.

package com.sarf.excel.controller;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.sarf.excel.model.ExcelCreator;

publicclass DownloadExcelController extends HttpServlet {

privatestaticfinallongserialVersionUID = 1L;

public DownloadExcelController() {

}

protectedvoid doGet(HttpServletRequest request,

HttpServletResponse response)

throws ServletException, IOException {

}

protectedvoid doPost(HttpServletRequest request,

HttpServletResponse response)

throws ServletException, IOException {

String strMessage="";

try

{

response.reset();

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-Disposition",

"attachment;filename=Data.xls");

ExcelCreator objEDH=new ExcelCreator();

/* Calling method downloadExcel */

strMessage=objEDH.downloadExcel(response.getOutputStream());

request.setAttribute("Message",strMessage);

}catch (Exception e)

{

e.getMessage();

}

}

}

Here is our web.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<web-appid="WebApp_ID"version="2.4"

xmlns="http://java.sun.com/xml/ns/j2ee"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee

http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">

<display-name>DownloadExcel</display-name>

<servlet>

<description></description>

<display-name>DownloadExcelController</display-name>

<servlet-name>DownloadExcelController</servlet-name>

<servlet-class>

com.sarf.excel.controller.DownloadExcelController

</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>DownloadExcelController</servlet-name>

<url-pattern>/DownloadExcelController</url-pattern>

</servlet-mapping>

<welcome-file-list>

<welcome-file>index.html</welcome-file>

</welcome-file-list>

</web-app>

Here is export.html file content.

<html>

<head>

<title>Download File</title>

</head>

<body>

<FORMname="filesForm1"action="./DownloadExcelController"

method="post">

<inputtype="submit"value="Download Excel">

</FORM>

</body>

</html>

You have to put these compiled class files into web application folder structure and deploy into a web server or application server.