machha

how to export data from jsp to excel

View Answers

January 5, 2011 at 4:54 PM

Hi Friend,

Follow these steps:

1)Go to the start<<Control Panel<<Administrative Tools<< data sources.

2)Click Add button and select the Drive do Microsoft Excel(*.xls).

3)After selecting the driver, click finish button.

4)Then give Data Source Name,select drive, directory and workbook and click ok button.

5)Your DSN will get created.

6)Select the driver.Click Configure then click on Options and then uncheck the Read Only option.

7) Click ok button.

8)Then run the following code:

form.jsp:

<html>
<form method="post" action="insertToExcel.jsp">
<table>
<tr><td>Name:</td><td><input type="text" name="name"></td></tr>
<tr><td>Address:</td><td><input type="text" name="address"></td></tr>
<tr><td>Contact No:</td><td><input type="text" name="contact"></td></tr>
<tr><td>Email:</td><td><input type="text" name="email"></td></tr>
<tr><td></td><td><input type="submit" value="Submit"></td></tr>
</table>
</form>
</html>

insertToExcel.jsp:

<%@ page import="java.sql.*"%>
<% 
String name=request.getParameter("name");
String address=request.getParameter("address");
String contact=request.getParameter("contact");
String email=request.getParameter("email");

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
    Connection con= DriverManager.getConnection("jdbc:odbc:excelfiles");
    Statement stmt = con.createStatement();
    String excelQuery = "insert into [sheet1$]([Name], [Address],[Contact No], [Email]) values('"+name+"', '"+address+"','"+contact+"','"+email+"')";
    stmt.executeUpdate(excelQuery);
    con.close();
    out.println("Data is inserted into excel file");
    %>

Thanks