Export Grid View to Excel in Servlet and Jsp
Exporting contents to excel spreadsheet is a much required functionality for almost every data driven website. In this article I am going to explain in detail on how to export gridview contents to an excel sheet via a java web application.
Project Structure
Servlet
package servlet;
import java.io.IOException;
import java.util.ArrayList;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import model.Student;
public class ExportToExcel extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
ArrayList<Student> students = new ArrayList<Student>();
students.add(new Student("Mohaideen", "CSE", 17));
students.add(new Student("Nilafar", "IT", 16));
students.add(new Student("Thasleema", "CSE", 16));
request.setAttribute("students", students);
RequestDispatcher rd = request.getRequestDispatcher("report.jsp");
rd.forward(request, response);
}
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
ArrayList<Student> students = new ArrayList<Student>();
students.add(new Student("Mohaideen", "CSE", 17));
students.add(new Student("Nilafar", "IT", 16));
students.add(new Student("Thasleema", "CSE", 16));
request.setAttribute("students", students);
RequestDispatcher rd = request.getRequestDispatcher("exportExcel.jsp");
rd.forward(request, response);
}
}
Model
package model;
public class Student {
private String name;
private String department;
private int age;
public Student(String name, String department, int age) {
this.name = name;
this.department = department;
this.age = age;
}
public String getName() {
return name;
}
public String getDepartment() {
return department;
}
public int getAge() {
return age;
}
public void setName(String name) {
this.name = name;
}
public void setDepartment(String department) {
this.department = department;
}
public void setAge(int age) {
this.age = age;
}
}
JSP Page
File : index.jsp – Used to navigate to exporttoexcel servlet action
<html> <head> <META HTTP-EQUIV="Refresh" CONTENT="0;URL=exporttoexcel"> </head> <body> </body> </html>
File : report.jsp – This page used to display gridview filled with data of student table from server side.
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.util.List"%>
<%@ page import="model.Student"%>
<html>
<head>
<title>Export to Excel</title>
</head>
<body>
<h3>Export to Excel Example</h3>
<form action="exporttoexcel" method="post">
<table cellpadding="1" cellspacing="1" border="1" bordercolor="gray">
<tr>
<td><b>Name</b></td>
<td><b>Department</b></td>
<td><b>Age</b></td>
</tr>
<%
List<Student> students = (List<Student>) request
.getAttribute("students");
for (Student std : students) {
%>
<tr>
<td><%=std.getName()%></td>
<td><%=std.getDepartment()%></td>
<td><%=std.getAge()%></td>
</tr>
<%
}
%>
</table>
<BR /> <input type="submit" value="Export To Excel" />
</form>
</body>
</html>
File : exportExcel.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.util.List"%>
<%@ page import="model.Student"%>
<html>
<head>
<title>Export to Excel Example</title>
</head>
<body>
<h3>Export to Excel Example</h3>
<table cellpadding="1" cellspacing="1" border="1" bordercolor="gray">
<tr>
<td>Name</td>
<td>Department</td>
<td>Age</td>
</tr>
<%
List<Student> students = (List<Student>) request.getAttribute("students");
if (students != null) {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "inline; filename="
+ "StudentReport.xls");
}
for (Student std : students) {
%>
<tr>
<td><%=std.getName()%></td>
<td><%=std.getDepartment()%></td>
<td><%=std.getAge()%></td>
</tr>
<%
}
%>
</table>
</body>
</html>
web.xml
<web-app .. version="3.0">
<display-name>ExportToExcel</display-name>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>ExportToExcel</servlet-name>
<servlet-class>servlet.ExportToExcel</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ExportToExcel</servlet-name>
<url-pattern>/exporttoexcel</url-pattern>
</servlet-mapping>
</web-app>
Demo
On running the application
On clicking “Export To Excel” button the following page with download file options get appears
Java code to convert xlsx & xls to csv
Note : This Program uses XSSFWorkbook for xlsx and HSSFWorkbook for xlx.
package com.simplecode.excel;
import java.io.*;
import java.util.Iterator;
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;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
class ExcelToCSV {
static void convertToXlsx(File inputFile, File outputFile)
{
// For storing data into CSV files
StringBuffer cellValue = new StringBuffer();
try
{
FileOutputStream fos = new FileOutputStream(outputFile);
// Get the workbook instance for XLSX file
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));
// Get first sheet from the workbook
XSSFSheet sheet = wb.getSheetAt(0);
Row row;
Cell cell;
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
cell = cellIterator.next();
switch (cell.getCellType())
{
case Cell.CELL_TYPE_BOOLEAN:
cellValue.append(cell.getBooleanCellValue() + ",");
break;
case Cell.CELL_TYPE_NUMERIC:
cellValue.append(cell.getNumericCellValue() + ",");
break;
case Cell.CELL_TYPE_STRING:
cellValue.append(cell.getStringCellValue() + ",");
break;
case Cell.CELL_TYPE_BLANK:
cellValue.append("" + ",");
break;
default:
cellValue.append(cell + ",");
}
}
}
fos.write(cellValue.toString().getBytes());
fos.close();
}
catch (Exception e)
{
System.err.println("Exception :" + e.getMessage());
}
}
static void convertToXls(File inputFile, File outputFile)
{
// For storing data into CSV files
StringBuffer cellDData = new StringBuffer();
try
{
FileOutputStream fos = new FileOutputStream(outputFile);
// Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
// Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
Cell cell;
Row row;
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
cell = cellIterator.next();
switch (cell.getCellType())
{
case Cell.CELL_TYPE_BOOLEAN:
cellDData.append(cell.getBooleanCellValue() + ",");
break;
case Cell.CELL_TYPE_NUMERIC:
cellDData.append(cell.getNumericCellValue() + ",");
break;
case Cell.CELL_TYPE_STRING:
cellDData.append(cell.getStringCellValue() + ",");
break;
case Cell.CELL_TYPE_BLANK:
cellDData.append("" + ",");
break;
default:
cellDData.append(cell + ",");
}
}
}
fos.write(cellDData.toString().getBytes());
fos.close();
}
catch (FileNotFoundException e)
{
System.err.println("Exception" + e.getMessage());
}
catch (IOException e)
{
System.err.println("Exception" + e.getMessage());
}
}
public static void main(String[] args)
{
File inputFile = new File("C:\input.xls");
File outputFile = new File("C:\output1.csv");
File inputFile2 = new File("C:\input.xlsx");
File outputFile2 = new File("C:\output2.csv");
convertToXls(inputFile, outputFile);
convertToXlsx(inputFile2, outputFile2);
}
}
To execute the above code you must have the following libraries
- dom4j-1.1.jar
- poi-3.7-20101029.jar
- poi-ooxml-3.7-20101029.jar
- poi-ooxml-schemas-3.7-20101029.jar
- xmlbeans-2.3.0.jar
 |
|
Read More
Java Code to Convert XLSX to CSV Files
Note : xlsx file should contain only data in cells. No image and any other media element. This program has been developed only for cell data
package com.simplecode.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
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;
class XlsxtoCSV {
static void xlsx(File inputFile, File outputFile) {
// For storing data into CSV files
StringBuffer data = new StringBuffer();
try {
FileOutputStream fos = new FileOutputStream(outputFile);
// Get the workbook object for XLSX file
XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));
// Get first sheet from the workbook
XSSFSheet sheet = wBook.getSheetAt(0);
Row row;
Cell cell;
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
data.append(cell.getBooleanCellValue() + ",");
break;
case Cell.CELL_TYPE_NUMERIC:
data.append(cell.getNumericCellValue() + ",");
break;
case Cell.CELL_TYPE_STRING:
data.append(cell.getStringCellValue() + ",");
break;
case Cell.CELL_TYPE_BLANK:
data.append("" + ",");
break;
default:
data.append(cell + ",");
}
}
}
fos.write(data.toString().getBytes());
fos.close();
} catch (Exception ioe) {
ioe.printStackTrace();
}
}
public static void main(String[] args) {
File inputFile = new File("C:\test.xlsx");
File outputFile = new File("C:\output.csv");
xlsx(inputFile, outputFile);
}
}
To execute the above code you must have the following libraries
- dom4j-1.1.jar
- poi-3.7-20101029.jar
- poi-ooxml-3.7-20101029.jar
- poi-ooxml-schemas-3.7-20101029.jar
- xmlbeans-2.3.0.jar
Let me know if you face any issues. Read More
Converting XLS to CSV files Using Java
package com.simplecode.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
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;
class XlstoCSV
{
static void xls(File inputFile, File outputFile)
{
// For storing data into CSV files
StringBuffer data = new StringBuffer();
try
{
FileOutputStream fos = new FileOutputStream(outputFile);
// Get the workbook object for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
// Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
Cell cell;
Row row;
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
cell = cellIterator.next();
switch (cell.getCellType())
{
case Cell.CELL_TYPE_BOOLEAN:
data.append(cell.getBooleanCellValue() + ",");
break;
case Cell.CELL_TYPE_NUMERIC:
data.append(cell.getNumericCellValue() + ",");
break;
case Cell.CELL_TYPE_STRING:
data.append(cell.getStringCellValue() + ",");
break;
case Cell.CELL_TYPE_BLANK:
data.append("" + ",");
break;
default:
data.append(cell + ",");
}
data.append('\n');
}
}
fos.write(data.toString().getBytes());
fos.close();
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}
public static void main(String[] args)
{
File inputFile = new File("C:\test.xls");
File outputFile = new File("C:\output.csv");
xls(inputFile, outputFile);
}
}
To execute the above code you must have the following libraries
- dom4j-1.1.jar
- poi-3.7-20101029.jar
- poi-ooxml-3.7-20101029.jar
- poi-ooxml-schemas-3.7-20101029.jar
- xmlbeans-2.3.0.jar
Read More
Java Code to Read XLS and XLSX Files
Note : This Program uses XSSFWorkbook for xlsx and HSSFWorkbook for xlx.
package com.simplecode.excel;
import java.io.*;
import java.util.Iterator;
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;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
class ExcelReader
{
static void readXlsx(File inputFile)
{
try
{
// Get the workbook instance for XLSX file
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));
// Get first sheet from the workbook
XSSFSheet sheet = wb.getSheetAt(0);
Row row;
Cell cell;
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
cell = cellIterator.next();
switch (cell.getCellType())
{
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BLANK:
System.out.println(" ");
break;
default:
System.out.println(cell);
}
}
}
}
catch (Exception e)
{
System.err.println("Exception :" + e.getMessage());
}
}
static void readXls(File inputFile)
{
try
{
// Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
// Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
Cell cell;
Row row;
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
cell = cellIterator.next();
switch (cell.getCellType())
{
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BLANK:
System.out.println(" ");
break;
default:
System.out.println(cell);
}
}
}
}
catch (FileNotFoundException e)
{
System.err.println("Exception" + e.getMessage());
}
catch (IOException e)
{
System.err.println("Exception" + e.getMessage());
}
}
public static void main(String[] args)
{
File inputFile = new File("C:\input.xls");
File inputFile2 = new File("C:\input.xlsx");
readXls(inputFile);
readXlsx(inputFile2);
}
}
To execute the above code you must have the following libraries
- dom4j-1.1.jar
- poi-3.7-20101029.jar
- poi-ooxml-3.7-20101029.jar
- poi-ooxml-schemas-3.7-20101029.jar
- xmlbeans-2.3.0.jar
Read Excel with Java (Jexcel-api)
In our previous article we have In our next article I have implemented Write Excel in Java using Jexcel-api in this article demonstrate how to read Excel files with the Java Excel API.
Since I have explained about Jexcel api in my previous article, so I’m not going to explain here again.
|
|
package com.simplecode.reader;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class ReadExcel {
public static void main(String[] args) throws IOException {
ReadExcel test = new ReadExcel();
test.setInputFile("c:/temp/output.xls");
test.read();
}
private String inputFile;
public void setInputFile(String inputFile) {
this.inputFile = inputFile;
}
public void read() throws IOException {
File inputWorkbook = new File(inputFile);
Workbook w;
try {
w = Workbook.getWorkbook(inputWorkbook);
// Get the first sheet
Sheet sheet = w.getSheet(0);
// Loop over first 10 column and lines
for (int j = 0; j < sheet.getColumns(); j++) {
for (int i = 0; i < sheet.getRows(); i++) {
Cell cell = sheet.getCell(j, i);
CellType type = cell.getType();
if (type == CellType.LABEL) {
System.out.println("I got a label "
+ cell.getContents());
}
if (type == CellType.NUMBER) {
System.out.println("I got a number "
+ cell.getContents());
}
}
}
} catch (BiffException e) {
e.printStackTrace();
}
}
}
Read More