QuestionAsk Questions?
View Answers
June 12, 2009 at 5:18 PM
Hi Friend,
Please send your code so that we can help you.
Thanks
Please send your code so that we can help you.
Thanks
June 15, 2009 at 11:24 AM
Hi
I am using 3 jsp files.Here is my code.
Browsefile.jsp:
<%@page language="java" import="java.sql.*"%>
<html>
<body bgcolor=#EFF1FB>
<form name="browsetable">
<script type="text/javascript">
var xmlHttp,table,xmlHttp1,optr,i=1,j=1,query,path,filepath;
function showxlsFileTable(str)
{
path=str;
//alert(str);
xmlHttp=GetXmlHttpObject();
if (xmlHttp==null)
{
alert ("Your browser does not support AJAX!");
return;
}
var url="GetxlsFileTable.jsp";
url=url+"?q="+str;
url=url+"&sid="+Math.random();
xmlHttp.onreadystatechange=stateChanged1;
xmlHttp.open("GET",url,true);
xmlHttp.send(null);
// alert("Im in function="+str);
}
function showxlsFileColumn(str)
{
xmlHttp=GetXmlHttpObject();
if (xmlHttp==null)
{
alert ("Your browser does not support AJAX!");
return;
}
var url="GetxlsFileColumn.jsp";
//url=url+"?q="+str;
url=url+"?q="+str+ "&q1=" +path;
url=url+"&sid="+Math.random();
xmlHttp.onreadystatechange=stateChanged2;
xmlHttp.open("GET",url,true);
xmlHttp.send(null);
// alert("Im in function="+str);
}
function stateChanged()
{
if (xmlHttp.readyState==4)
{
document.getElementById("txtHint").innerHTML=xmlHttp.responseText;
}
}
function stateChanged1()
{
if (xmlHttp.readyState==4)
{
document.getElementById("txtHint1").innerHTML=xmlHttp.responseText;
}
}
function stateChanged2()
{
if (xmlHttp.readyState==4)
{
document.getElementById("txtHint2").innerHTML=xmlHttp.responseText;
}
}
function GetXmlHttpObject()
{
var xmlHttp=null;
try
{
// Firefox, Opera 8.0+, Safari
xmlHttp=new XMLHttpRequest();
}
catch (e)
{
// Internet Explorer
try
{
xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
//alert("Your browser broke!");
}
catch (e)
{
xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
}
}
return xmlHttp;
}
function showTableValue(str)
{
//alert("2 "+filepath);
filetable=str;
fpath=filepath.substring(filepath.length-3,filepath.length);
if(fpath=="xls")
showxlsFileColumn(str)
//alert(document.addmytbl.datafile.value);
}
function readFile(fileBrowser)
{
try {
netscape.security.PrivilegeManager.enablePrivilege("UniversalXPConnect");
} catch (e) {
alert('Unable to access local files due to browser security settings. To overcome this, follow these steps: (1) Enter "about:config" in the URL field; (2) Right click and select New->Boolean; (3) Enter "signed.applets.codebase_principal_support" (without the quotes) as a new preference name; (4) Click OK and try loading the file again.');
return;
}
filepath=fileBrowser.value;
fpath=filepath.substring(filepath.length-3,filepath.length);
// alert(filepath);
if(fpath=="xls")
showxlsFileTable(filepath)
var file = Components.classes["@mozilla.org/file/local;1"]
.createInstance(Components.interfaces.nsILocalFile);
try
{
// Back slashes for windows
file.initWithPath( fileName.replace(/\//g, "\\\\") );
}
catch(e)
{
if (e.result!=Components.results.NS_ERROR_FILE_UNRECOGNIZED_PATH) throw e;
alert("File '" + fileName + "' cannot be loaded: relative paths are not allowed. Please provide an absolute path to this file.");
return;
}
if ( file.exists() == false ) {
alert("File '" + fileName + "' not found.");
return;
}
}
</script>
<br><br>
Browse to select a file <input type="file" name="fileBrowser" size="40" onchange="readFile(this)">
<p><div id="txtHint1"><b></b></div></p>
<p><div id="txtHint"><b></b></div></p>
<p><div id="txtHint2"><b></b></div></p>
</form>
</body>
</html>
_________________________________________________________________________
For Getting the tablenames am using next file.
GetxlsFileTable.jsp:
<%@page language="java" import="java.sql.*"%>
<html>
<%
String filepath=request.getParameter("q");
Connection cn;
ResultSet rs;
Statement st;
String tableName="";
//out.println("<br><br><table width=400 border><td>Table Column Name</td>");
%>
<h4>Select Table:                     <select name=sheet2 onchange='showTableValue(this.value)'></h4>
<option value="-">-
<%
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException se)
{
se.printStackTrace();
}
try
{
String database = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ="+filepath.trim();
//System.out.println("tableName:"+tableName);
cn = DriverManager.getConnection(database,"","");
DatabaseMetaData dbmd = cn.getMetaData();
// String[] types = {"TABLE"};
rs = dbmd.getTables(null, null, "%", null);
while (rs.next())
{
tableName = rs.getString(3);
//System.out.println("tableName1111:"+tableName);
if(tableName.endsWith("$"))
{
tableName=tableName.substring(0,tableName.length()-1);
// tableName.addElement(tableName);
}
System.out.println("tableName:"+tableName);
%>
<option value<%=tableName%>><%=tableName%></option>
<%
}
%>
</select>
<%
}
catch (SQLException e)
{
}
%>
</html>
_______________________________________________________________________
For getting the table columns, am using this file.
GetxlsFileColumn.jsp:
<%@page language="java" import="java.sql.*"%>
<html>
<%
String sel1=request.getParameter("q");
//System.out.println("sel1=:"+sel1);
String filepath=request.getParameter("q1");
//System.out.println("filepath:"+filepath);
Connection cn;
ResultSet rs;
Statement st;
String columnName="";
//out.println("<br><br><table width=400 border><td>Table Column Name</td>");
%>
<h4>Column in importing table:<select name=sheet2 size=4 onchange='showValue1(this.value)'></h4>
<!--<option value="-">-->
<%
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException se)
{
se.printStackTrace();
}
try
{
//String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
// database+= filepath.trim() + ";";
String database = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ="+filepath.trim();
cn = DriverManager.getConnection(database,"","");
DatabaseMetaData dbmd = cn.getMetaData();
rs = dbmd.getColumns(null, null, sel1+"$", null);
while (rs.next())
{
columnName = rs.getString("COLUMN_NAME");
//System.out.println("columnName:"+columnName);
%>
<option value<%=columnName%>><%=columnName%></option>
<%
}
%>
</select>
<%
}
catch (SQLException e)
{
}
%>
</html>
_________________________________________________________________________
I am using 3 jsp files.Here is my code.
Browsefile.jsp:
<%@page language="java" import="java.sql.*"%>
<html>
<body bgcolor=#EFF1FB>
<form name="browsetable">
<script type="text/javascript">
var xmlHttp,table,xmlHttp1,optr,i=1,j=1,query,path,filepath;
function showxlsFileTable(str)
{
path=str;
//alert(str);
xmlHttp=GetXmlHttpObject();
if (xmlHttp==null)
{
alert ("Your browser does not support AJAX!");
return;
}
var url="GetxlsFileTable.jsp";
url=url+"?q="+str;
url=url+"&sid="+Math.random();
xmlHttp.onreadystatechange=stateChanged1;
xmlHttp.open("GET",url,true);
xmlHttp.send(null);
// alert("Im in function="+str);
}
function showxlsFileColumn(str)
{
xmlHttp=GetXmlHttpObject();
if (xmlHttp==null)
{
alert ("Your browser does not support AJAX!");
return;
}
var url="GetxlsFileColumn.jsp";
//url=url+"?q="+str;
url=url+"?q="+str+ "&q1=" +path;
url=url+"&sid="+Math.random();
xmlHttp.onreadystatechange=stateChanged2;
xmlHttp.open("GET",url,true);
xmlHttp.send(null);
// alert("Im in function="+str);
}
function stateChanged()
{
if (xmlHttp.readyState==4)
{
document.getElementById("txtHint").innerHTML=xmlHttp.responseText;
}
}
function stateChanged1()
{
if (xmlHttp.readyState==4)
{
document.getElementById("txtHint1").innerHTML=xmlHttp.responseText;
}
}
function stateChanged2()
{
if (xmlHttp.readyState==4)
{
document.getElementById("txtHint2").innerHTML=xmlHttp.responseText;
}
}
function GetXmlHttpObject()
{
var xmlHttp=null;
try
{
// Firefox, Opera 8.0+, Safari
xmlHttp=new XMLHttpRequest();
}
catch (e)
{
// Internet Explorer
try
{
xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
//alert("Your browser broke!");
}
catch (e)
{
xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
}
}
return xmlHttp;
}
function showTableValue(str)
{
//alert("2 "+filepath);
filetable=str;
fpath=filepath.substring(filepath.length-3,filepath.length);
if(fpath=="xls")
showxlsFileColumn(str)
//alert(document.addmytbl.datafile.value);
}
function readFile(fileBrowser)
{
try {
netscape.security.PrivilegeManager.enablePrivilege("UniversalXPConnect");
} catch (e) {
alert('Unable to access local files due to browser security settings. To overcome this, follow these steps: (1) Enter "about:config" in the URL field; (2) Right click and select New->Boolean; (3) Enter "signed.applets.codebase_principal_support" (without the quotes) as a new preference name; (4) Click OK and try loading the file again.');
return;
}
filepath=fileBrowser.value;
fpath=filepath.substring(filepath.length-3,filepath.length);
// alert(filepath);
if(fpath=="xls")
showxlsFileTable(filepath)
var file = Components.classes["@mozilla.org/file/local;1"]
.createInstance(Components.interfaces.nsILocalFile);
try
{
// Back slashes for windows
file.initWithPath( fileName.replace(/\//g, "\\\\") );
}
catch(e)
{
if (e.result!=Components.results.NS_ERROR_FILE_UNRECOGNIZED_PATH) throw e;
alert("File '" + fileName + "' cannot be loaded: relative paths are not allowed. Please provide an absolute path to this file.");
return;
}
if ( file.exists() == false ) {
alert("File '" + fileName + "' not found.");
return;
}
}
</script>
<br><br>
Browse to select a file <input type="file" name="fileBrowser" size="40" onchange="readFile(this)">
<p><div id="txtHint1"><b></b></div></p>
<p><div id="txtHint"><b></b></div></p>
<p><div id="txtHint2"><b></b></div></p>
</form>
</body>
</html>
_________________________________________________________________________
For Getting the tablenames am using next file.
GetxlsFileTable.jsp:
<%@page language="java" import="java.sql.*"%>
<html>
<%
String filepath=request.getParameter("q");
Connection cn;
ResultSet rs;
Statement st;
String tableName="";
//out.println("<br><br><table width=400 border><td>Table Column Name</td>");
%>
<h4>Select Table:                     <select name=sheet2 onchange='showTableValue(this.value)'></h4>
<option value="-">-
<%
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException se)
{
se.printStackTrace();
}
try
{
String database = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ="+filepath.trim();
//System.out.println("tableName:"+tableName);
cn = DriverManager.getConnection(database,"","");
DatabaseMetaData dbmd = cn.getMetaData();
// String[] types = {"TABLE"};
rs = dbmd.getTables(null, null, "%", null);
while (rs.next())
{
tableName = rs.getString(3);
//System.out.println("tableName1111:"+tableName);
if(tableName.endsWith("$"))
{
tableName=tableName.substring(0,tableName.length()-1);
// tableName.addElement(tableName);
}
System.out.println("tableName:"+tableName);
%>
<option value<%=tableName%>><%=tableName%></option>
<%
}
%>
</select>
<%
}
catch (SQLException e)
{
}
%>
</html>
_______________________________________________________________________
For getting the table columns, am using this file.
GetxlsFileColumn.jsp:
<%@page language="java" import="java.sql.*"%>
<html>
<%
String sel1=request.getParameter("q");
//System.out.println("sel1=:"+sel1);
String filepath=request.getParameter("q1");
//System.out.println("filepath:"+filepath);
Connection cn;
ResultSet rs;
Statement st;
String columnName="";
//out.println("<br><br><table width=400 border><td>Table Column Name</td>");
%>
<h4>Column in importing table:<select name=sheet2 size=4 onchange='showValue1(this.value)'></h4>
<!--<option value="-">-->
<%
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException se)
{
se.printStackTrace();
}
try
{
//String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
// database+= filepath.trim() + ";";
String database = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ="+filepath.trim();
cn = DriverManager.getConnection(database,"","");
DatabaseMetaData dbmd = cn.getMetaData();
rs = dbmd.getColumns(null, null, sel1+"$", null);
while (rs.next())
{
columnName = rs.getString("COLUMN_NAME");
//System.out.println("columnName:"+columnName);
%>
<option value<%=columnName%>><%=columnName%></option>
<%
}
%>
</select>
<%
}
catch (SQLException e)
{
}
%>
</html>
_________________________________________________________________________
Related Pages:
If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.
Ask your questions, our development team will try to give answers to your questions.
Tutorial Topics
