How to export data from Oracle Table to Excel sheet in Java Script

You have searched over the internet about how to export oracle table to excel using java script. I am posting this article to give you complete source code to export data from oracle to excel.

Follow the below mentioned steps and use the below mentioned code.

Step-1: In body section, first you have to define format for exporting data to excel.

Step-2: Set database connection string to connect with oracle database.

Step-3: Load table data to web page using for and while loop.


Step-4: Then click on “Export to Excel” to export data into excel.

How to send an E-Mail from 10g Oracle Database


<%@ page language="java" import="java.sql.*" 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=UTF-8">
<title>JSP Page</title>
<style type="text/css">
table.myTable { border-collapse:collapse; }
table.myTable td, table.myTable th { border:1px solid black;padding:5px; }
</style>

</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");
}
%>
<div id="MyExportTable">
<table class="myTable">

<%
Connection conn = null;
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","user_name", "user_password");
out.println("Oracle Connected.");
ResultSetMetaData rsmd;
ResultSet rs1 = null;
ResultSet rsnew=null;
PreparedStatement ps1=null;
String st="SELECT EMPID,EMPNAME,ADDRESS || ' ' CITY from EMP";
try{%>
<tr><%
ps1=conn.prepareStatement(st);
rsnew=ps1.executeQuery();
rs1=rsnew;
rsmd=rs1.getMetaData();
int cou=rsmd.getColumnCount();
for(int i=1;i<=cou;i++){
%>

<td><b>
<%=rsmd.getColumnName(i)%>
<%
}
%> </td></b></tr>
<%
while(rs1.next())
{
%>
<tr><td><%=rs1.getString(1)%></td><td><%=rs1.getString(2)%></td><td><%=rs1.getString(3)%></td></tr>
<%
}
}
catch(Exception e)
{
out.println(e);
}
%>
</table> </div>
<%
if (exportToExcel == null) {
%>
<a href="grid.jsp?exportToExcel=YES">Export to Excel</a>
<%
}
%>
</body>
</html>

What is JQuery and JQuery Hide & Show method examples

I hope this will be helpful to export data into excel very easily.

If you have any other solution or any query about this topic, feel free to comment below this post.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)