Pages Navigation Menu

Coding is much easier than you think

Export Grid View to Excel in Servlet and Jsp

Posted by on Sep 21, 2013 in J2EE, Java, Java Excel, Servlet | 0 comments

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.
 
Export to Excel
 

Project Structure

 
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

Running
 
On clicking “Export To Excel” button the following page with download file options get appears

Export to excel
 

Advertisements
Read More

Java code to convert xlsx & xls to csv

Posted by on Mar 16, 2013 in CSV, Java, Java Excel | 3 comments

images (1)
 

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

 

dwd2
Download It – ExcelToCSV

 

Read More

Java Code to Convert XLSX to CSV Files

Posted by on Mar 16, 2013 in CSV, Java, Java Excel | 19 comments

 

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

Posted by on Mar 16, 2013 in CSV, Java, Java Excel | 14 comments

 

 
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

Posted by on Mar 10, 2013 in Java Excel | 18 comments

java excel 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 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 More

Read Excel with Java (Jexcel-api)

Posted by on Feb 17, 2013 in Java, Java Excel | 0 comments

 
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.
 

dwd2
Download – Java Excel library

 

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