• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

import HTML rows to Excel

 
Rashmi Trivedi
Ranch Hand
Posts: 60
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Team
I have a issue with my project which i am currently working on.

I have JSP page which retrieve rows from the database.

I want to put an option on the JSP page which enable users to export the rows to excel spread sheet.

I am not able to find any good information to do this.

Please help me to start this up. I am really struggling to get my head aroudn this. any example you may have or some documents would be really good help.

Thanks in advance.

regards,
Rashmi Trivedi
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Refer to JSP And Excel
 
Rashmi Trivedi
Ranch Hand
Posts: 60
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Addel
Thank you for the reply. The code which you suggest to use is fine howerver i have a situation where i display the results first in HTML rows in JSP. I would like to have a button which says "IMPORT TO EXCEL". If users click on it, it triggers the code which you suggest to use.

The questions is HOW DO I TRIGGER THE CODE WHEN USER CLICKS ON THE BUTTON?

IS IT A JSP FORM ACTION or "IMPORT TO EXCEL" onClick event?

Could you please guide me here mate..

Thanks a lot.

regards,
Rashmi Trivedi
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe its better to make a request to server, get all the data needed in any of the supported format. Few are listed below:

Beans
Using Tags (jXLS)
Collections
JDBC ResultSet

You can use your chosen library to generate the excel sheet by passing data in any of these format.

You can either write your excel file on the disk and forward the request to that or use outputstream to send the file to client.
 
Prabhu Venkatachalam
Ranch Hand
Posts: 502
Java jQuery Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fetch data from database and write it to excel file to some default location in the server.

If you are using struts,

In your JSP, when user clicks on JSP,call JavaScript function some thing like this,

And map your action class to /download in struts-config.xml

And in your action class, do some thing like this,




If you not using struts, then instead of Action class map URL to a servlet in web.xml and do the same.

HTH,
 
Rashmi Trivedi
Ranch Hand
Posts: 60
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Prabhu
Thank you for the response.

It is not fetching data from Database and write it to Excel.

I am displaying data in HTML table/rows on the IE browser and i would like to have an option for users to export that rows to excel.

I hope i am explaining my self. Please let me know if this is confusing.

BTW: I am not using Java Strust. I am only using tomcat with Java.

regards,
Rashmi Trivedi
 
ganesh narayana
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

it is very simple to do so, when you click on download to excel button, you submit this request to some jsp or servlet. in that server page set content type to application/vnd.ms-excel, then the generated output will be opened with an excel file.
i am giving code for a servlet
ExcelServlet.java

---
---

public void doPost(HttpServletRequest req,HttpServletResponse res)
{
res.setContentType("application/vnd.ms-excel");
PrintWriter out=res.getWriter();
out.println("<table>");
out.println("<tr bgcolor=lightblue><td>Hello </td><td>James</td></tr>");
out.close();
}
}

what ever you are giving in td will be a cell content.

so solve your problem easily and keep smiling.

 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here you are.
Learn to Read and Write Microsoft Excel Documents with Jakarta's POI
[ November 13, 2006: Message edited by: Adeel Ansari ]
 
Madhura Phansalkar
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi

i have tried out one option ..just thought of sharing it with you

you can use xl template file .
simplest way is to use jxls api
fetch data rows from DB, put them in a bean
use transform function to generate xl file filled up with the data..


Configuration cf = new Configuration();
XLSTransformer transformer = new XLSTransformer(cf);
transformer.transformXLS("samplexltemplate.xls", beans,"target.xls");




beans is a map containing required data..
target.xls will be your result xls...

just try this, you can serch on it in details on net


all the best !
 
nilesh kamani
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
Below is a simple example to create xlsheet.

html_display.jsp
---------------------------------------------------------------
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Tabular Display in HTML</title>
</head>
<body>
<table>
<tr>
<td>Nilesh</td>
<td>Kamani</td>
</tr>
<tr>
<td>Prateek</td>
<td>Kumar</td>
</tr>
<tr>
<td>Madhuri</td>
<td>Joshi</td>
</tr>
<tr>
<td>Prasanna</td>
<td>Ramchandran</td>
</tr>
</table>
</body>
<form method="post" action="DownloadAsExcel">
<input type="submit" name="submit" value="Download as Excel" />
</form>
</html>



----------------------------------------------------------------------
Below is the servlet mapped to DownloadAsExcel
----------------------------------------------------------------------

/**
*
*/
package com.example.excel;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

/**
* @author kamanini
*
*/
public class DownloadAsExcel extends HttpServlet {
public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
res.setContentType("application/vnd.ms-excel");
PrintWriter out=res.getWriter();

// USING TABLE
//out.println("<table>");
//out.println("<tr>");
//out.println("<td>Nilesh</td>");
//out.println("<td>Kamani</td>");
//out.println("</tr>");
//out.println("<tr>");
//out.println("<td>Prateek</td>");
//out.println("<td>Kumar</td>");
//out.println("</tr>");
//out.println("<tr>");
//out.println("<td>Madhuri</td>");
//out.println("<td>Joshi</td>");
//out.println("</tr>");
//out.println("<tr>");
//out.println("<td>Prasanna</td>");
//out.println("<td>Ramchandran</td>");
//out.println("</tr>");
//out.println("</table>");

// TAB SEPARATED
out.println("Nilesh\tKamani");
out.println("Prateek\tKumar");
out.println("Madhuri\tJoshi");
out.println("Prasanna\tRamchandran");


out.close();
}


}
-----------------------------------------------------------------------
I hope this will help you.

Regards
Nilesh Kamani
 
Rashmi Trivedi
Ranch Hand
Posts: 60
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks you all..i truely appreciate that you are taking time out to help me out..

Once again thank you all.

I think Nilesh's solution is the best suits for what i want to do so i will try that out..

Thanks mate..nice work.

thank you all and keep in touch.

regards,
Rashmi Trivedi
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic