Create Excel file using java API
 |
| Create Excel File |
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;
public class 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;
public class DownloadExcelController extends HttpServlet {
private static final long serialVersionUID = 1L;
public DownloadExcelController() {
}
protected void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
}
protected void 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();
}
}
}
<?xml version="1.0" encoding="UTF-8"?>
<web-app id="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>
<FORM name="filesForm1" action="./DownloadExcelController"
method="post">
<input type="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.
Download and copy these jar files into your lib folder.
You can use latest version of these apis.
My eclipse dynamic project directory structure will look like this :
posted by Sarf K. @ January 15, 2011
