How to Export Web Page to Excel (in JSP)

In this tutorial we will see how to export web page to excel in JSP. We will add Export to Excel functionality to an existing JSP.

Many times it is convenient for users to have an ability to export the contents of the page they are viewing to an excel. Common scenario would be to export a table containing a lot of data like reports, numbers etc. By exporting the data to excel, the end user will also get an ability to use the various analytical functionalities provided by excel, which would be difficult to implement in your Java based application.

Consider this as your existing JSP:

Export web page to excel

And here is the corresponding source of this JSP (Export to Excel link not yet added). Its a simple JSP with some data in table:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Export to Excel - Demo</title>
</head>
<body>
	<table align="center" border="2">
		<thead>
			<tr bgcolor="lightgreen">
				<th>Sr. No.</th>
				<th>Text Data</th>
				<th>Number Data</th>
			</tr>
		</thead>
		<tbody>
			<%
				for (int i = 0; i < 10; i++) {
			%>
			<tr bgcolor="lightblue">
				<td align="center"><%=i%></td>
				<td align="center">This is text data <%=i%></td>
				<td align="center"><%=i * i%></td>
			</tr>
			<%
				}
			%>
		</tbody>
	</table>
</body>
</html>

We will add a link “Export to Excel” which will export the contents of the page to an excel file. The page will look something like this:

Export web page to excel

Here is the new version code of the jsp we listed above. This version adds “Export to Excel” link and its corresponding functionality:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Export to Excel - Demo</title>
</head>
<body>
	<%
		String exportToExcel = request.getParameter("exportToExcel");
		if (exportToExcel != null
				&& exportToExcel.toString().equalsIgnoreCase("YES")) {
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "inline; filename="
					+ "excel.xls");

		}
	%>
	<table align="left" border="2">
		<thead>
			<tr bgcolor="lightgreen">
				<th>Sr. No.</th>
				<th>Text Data</th>
				<th>Number Data</th>
			</tr>
		</thead>
		<tbody>
			<%
				for (int i = 0; i < 10; i++) {
			%>
			<tr bgcolor="lightblue">
				<td align="center"><%=i + 1%></td>
				<td align="center">This is text data <%=i%></td>
				<td align="center"><%=i * i%></td>
			</tr>
			<%
				}
			%>
		</tbody>
	</table>
	<br><br><br><br><br><br><br><br><br><br><br><br><br><br><br> 
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
	<%
		if (exportToExcel == null) {
	%>
	<a href="excel.jsp?exportToExcel=YES">Export to Excel</a>
	<%
		}
	%>
</body>
</html>

Export web page to excel code explanation:

1) In this version of JSP, when you click on the “Export to Excel” link, the request is sent to the same page (excel.jsp) but with a URL parameter exportToExcel=YES.

Around The World Flights - Promo Code WORLD15
	<a href="excel.jsp?exportToExcel=YES">Export to Excel</a>

2) The JSP checks for this parameter right at the beginning. If this parameter is found with value YES, we set the content type of the response to excel and specify the filename of the excel which will open on the user’s computer.

		String exportToExcel = request.getParameter("exportToExcel");
		if (exportToExcel != null
				&& exportToExcel.toString().equalsIgnoreCase("YES")) {
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "inline; filename="
					+ "excel.xls");

		} 

3) All the contents on the page are exported when you click on the “Export to Excel” link. However we might not want the “Export to Excel” link itself to appear in the exported excel. To prevent this from appearing, we add an if condition to check whether exportToExcel parameter is present or not. If it is, then it means that the contents are to be exported to excel so we don’t include that link. If the parameter is not present, then it means we are just loading the JSP page on browser, so the link is printed on page.

	<%
		if (exportToExcel == null) {
	%>
	<a href="excel.jsp?exportToExcel=YES">Export to Excel</a>
	<%
		}
	%>

Output of export web page to excel:

When you click the link, you should get a popup asking you to Open or Save the file. Click open, you should see the file as below:

Export web page to excel

As you can see, this export also preserves the formatting which was there on the page.

In this next tutorial we will see how to export web page to a word file.

You can download the eclipse project for the code explained in this tutorial:

JavaExcelExport.zip (5KB)

Please consider following our blog (via Email, Twitter, Facebook and/or Google+ – See the right sidebar) for more interesting posts.

23 responses to “How to Export Web Page to Excel (in JSP)”

  1. How to Export Web Page to Word (in JSP) - QuicklyJava

    [...] previous post we saw how we can export the content displayed on a web page to an excel file. In this [...]

  2. cer_pa

    rock on! i love this stuff.

  3. lista de email

    i like reading this article…thanks.

  4. Sruthi

    So great & useful information! Explained so beautifully that even people who are new to this can understand.
    Many thanks!

  5. Srk

    Could you write about Physics so I can pass Science class?

  6. Greg

    This works fine for a jsp that has static data and can be called directly. Do you have an example that first calls a struts2 action class and then dynamically builds a jsp that can then be saved by the user.
    I have tried several methods including using result type of stream and nothing works. It looks like every method wants to have a file on the file system verses dynamic content.
    I really would prefer not to have to save a jsp to file system to have a link to it that allows it to be saved. Extra clicks and total waste of server space.
    One last comment. Unfortunately are default and only browser is IE.

    Any ideas would be greatly appreaciated.

    1. Dhandabani

      Hi, i am facing the problem what you faced .. give me solution that how you solved that problem..

  7. pree

    This is working for excel 2003, but whenever i am trying to open in excel 2007 it shows a blank file.

  8. jayesh

    does it working completely

  9. AVetal

    Hello!
    How to convert url encoded string to the Excel file by jsp?

    For example:
    by jsp i got parameter, which looks in the page like “?3″ ;
    by using jsp “encodeForUrl” , this parameter will look like “%D0%A41″

    by javasrcipt this string is normal in page
    ————————————————————————————–

    document.write(decodeURIComponent(“”));

    ————————————————————————————–
    and how to convert this string to the Excel?

    I used next strings with “UTF-8″, “ISO-8859-1″, “Cp1251″ coding
    ————————————————————————————————
    response.setContentType(“application/vnd.ms-excel”);
    response.setHeader(“Content-Disposition”, “inline; filename=” + “excel.xls”);
    ————————————————————————————————

    using “URLDecoder.decode” and “URLEncoder.encode” with different and same codes did not solve the problem

  10. AVetal

    thanks
    I have solved the problem.

    just use in the jsp file string in the head

  11. Saurabh

    For excel 2007 and above, the mime/content types have changed. Refer this article: http://technet.microsoft.com/en-us/library/ee309278%28office.12%29.aspx
    For excel 2007 the format is: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

  12. arvind

    can u tell me how to create project that can display the content of xls file to web interface in jsp

  13. Anita

    Gr8! It works fine! Thank U

  14. shan

    Hi, I used above code. I have used external css in my jsp page, so after downloading when i open excel file, it gives error, c:\abc.css is missing. How to resolve this? thanks

  15. Lakshman

    I downloaded the project. But I am getting error ” ServletResponse refers to the missing
    type String”
    Please help me

  16. Lakshman

    what are the changes we need to do to export to excel to get 3 sheets in excel? please suggest

  17. lakshman

    In my JSP page , I have header and footer .While exporting to excel header and footer are also exporting to excel.Please suggest the solution

  18. Priya

    Address

            

    Export to Excel

  19. srinu

    hi
    during my project i need to export jsp into excel. my issue is
    my jsp page contain table plus some content. i only want to export only table of jsp to excel.
    can you give any idea to proceed

  20. Ico

    very nice and easy to learn tutorial.
    i have a jsp page somewhat similar to your example but to be
    implemented to Excel 2007 and above (or .xlsx extension).
    i tried replacing
    –> response.setContentType(“application/vnd.ms-excel”);
    with
    –> response.setContentType(“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”);
    but i’m not getting the correct result.
    Thanks you in advance.

  21. mkumarpisl

    This works fine for a jsp that has static data and can be called directly. Do you have an example that first calls a struts2 action class and then dynamically builds a jsp that can then be saved by the user.
    I have tried several methods including using result type of stream and nothing works. It looks like every method wants to have a file on the file system verses dynamic content.

    1. mkumarpisl

      This works fine for a jsp that has static data and can be called directly
      For dynamic page it is not working. . Can any one help on this.

      Thanks,
      Kumar

Leave a Reply