Browse Excel File Problem

View Answers

June 12, 2009 at 5:18 PM

Hi Friend,

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:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp<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>

<%
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>

_________________________________________________________________________

Browse Excel File Problem

Questions by Category