Thread: excel

    #1
  1. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    26
    Rep 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
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  4. #3
  5. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    26
    Rep 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
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  8. #5
  9. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  10. #6
  11. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    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!
  12. #7
  13. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    26
    Rep 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!!
  14. #8
  15. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    OK, I'm just playing around with your original question, and came out with this, using SQL should be more flexible:
    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

IMN logo majestic logo threadwatch logo seochat tools logo