excel - Dev Shed

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

February 19th, 2002, 07:00 AM

sgaucho

Member

Join Date: Jan 2002

Posts: 26

Time spent in forums: 1 m 28 sec
Reputation Power: 0

excel

Hi,

I need to open an Excel (csv file) in an excel spreadsheet and shown in a browser when clicked on a link..

can anyone pl provide suggestions??

TIA,

sands

February 19th, 2002, 07:03 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 7,711

Time spent in forums: 2 Months 2 Weeks 6 Days 4 h 53 m 59 sec
Reputation Power: 259

Use a jsp to set proper MIME type and it will open Excel as a plugin.
Take a look at this php example http://www.judas-price.de/scripts/mysql2xls.dhtml

__________________
My blog about OpenSource Databases
PDF tutorials about OSS databases, DBMonster ...
Please contribute to Open Source Development, fill bug reports!!!
Developer Shed eSupport
Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins

February 19th, 2002, 07:07 AM

sgaucho

Member

Join Date: Jan 2002

Posts: 26

Time spent in forums: 1 m 28 sec
Reputation Power: 0

hi ,

i tried response.setContentType("application/vnd.ms-excel");

and when I dump some text within the file it does open it in an excel spreadsheet but am unable to make it work with a file stored in the oracle DB..

any suggestions??

TIA,

gaucho

February 19th, 2002, 07:13 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,711

Time spent in forums: 2 Months 2 Weeks 6 Days 4 h 53 m 59 sec
Reputation Power: 259

Why didn't you look at the example provided?
After setting mime type you have to read the file and send it to browser.

February 19th, 2002, 08:12 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,711

Time spent in forums: 2 Months 2 Weeks 6 Days 4 h 53 m 59 sec
Reputation Power: 259

You can find a java example here http://www.jguru.com/faq/view.jsp?EID=502939

full of errors, at least

public class DownloadServlet extends HttpServlet
{

public void doGet(HttpServletRequest request, HttpServletResponse response)

February 19th, 2002, 11:13 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,711

Time spent in forums: 2 Months 2 Weeks 6 Days 4 h 53 m 59 sec
Reputation Power: 259

This works, even if not perfectly, on my Win98se+Tomcat+JDK1.3.1_2 PC
[java]
import java.io.*;
import javax.servlet.http.*;
import javax.servlet.*;

public class DownloadServlet extends HttpServlet
{

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
int length;
byte[] buf = new byte[1024];
BufferedInputStream in =
new BufferedInputStream(new FileInputStream("C:\\LPServer\\jakarta-tomcat-3.2.3\\webapps\\ROOT\\test.csv"));

ServletOutputStream out =
response.getOutputStream();

response.setContentType("x-application/vnd.ms-excel");
//response.setContentLength(size);
// see http://www.faqs.org/rfcs/rfc1806.html
response.addHeader("Content-Disposition", "inline; filename=\" test.csv "\"");

// copy data
while ((in != null) && ((length = in.read(buf)) != -1))
{
out.write(buf, 0, length);
}
}
}
[/java]
Adapted from the example before.
I'll work out a better option later!

February 19th, 2002, 12:01 PM

sgaucho

Member

Join Date: Jan 2002

Posts: 26

Time spent in forums: 1 m 28 sec
Reputation Power: 0

got it to work finally..

<%

String a ="Content-type: ";

response.setContentType(contype);
long index = 1;

while(index < doc.length()){
response.getOutputStream().write(doc.getBytes(index,100));
index+=100;

}

%>

PHP is far easier and simpler... and outputting BLOB is pretty different too!!

February 21st, 2002, 07:42 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 7,711

Time spent in forums: 2 Months 2 Weeks 6 Days 4 h 53 m 59 sec
Reputation Power: 259

OK, I'm just playing around with your original question, and came out with this, using SQL should be more flexible:

Quote:

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

public class DBServlet extends HttpServlet {

private static final String DB_DRIVER = "org.relique.jdbc.csv.CsvDriver";
private static final String DB_URL = "jdbc:relique:csv:c:\\WINDOWS\\Desktop\\excelread";

public void init(ServletConfig config) throws ServletException {
try {
Class.forName(DB_DRIVER);
} catch (ClassNotFoundException ex) {
throw new UnavailableException("JDBC Driver " + DB_DRIVER + " not found.");
}
}

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException{
try {
//Properties props = new Properties();
//props.put("separator",";");
// create a connection. The first command line parameter is assumed to
// be the directory in which the .csv files are held
//Connection conn = DriverManager.getConnection("jdbc:relique:csv:c:\\WINDOWS\\Desktop\\excelread",props);
Connection conn = DriverManager.getConnection("jdbc:relique:csv:c:\\WINDOWS\\Desktop\\excelread");
// execute statements (and process results)
String query = "select * from test";
Statement st = conn.createStatement();
ResultSet myResultSet = st.executeQuery(query);
//output to excel
ServletOutputStream out = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
//response.setContentLength(size);
// see http://www.faqs.org/rfcs/rfc1806.html
response.addHeader("Content-Disposition", "inline; filename=\"test.csv\" ");
if (myResultSet != null) {
while (myResultSet.next()) {
// specify the field name
String name = myResultSet.getString("pippo");
out.println(name);
}
}
conn.close();
}
catch (SQLException ex) {
// handle exception
// Printout root SQLException
System.err.println("An SQL exception occurred: " + ex);

// Get all chained SQLExceptions
while((ex = ex.getNextException()) != null)
{
System.err.println("Contained reason: " + ex);
}

}
}

}

It reads a file like
pippo,pluto,minnie,topolino
we,12,12,12
12,we,12,12
where first row is like column names.
Your opinion?
Thanks in advance

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: