java left logo
java middle logo
java right logo
 

Home arrow Java EE Tips
 
 
Main Menu
Home
Java Tutorials
Book Reviews
Java SE Tips
Java ME Tips
Java EE Tips
Other API Tips
Java Applications
Java Libraries
Java Games
Java Network
Java Forums
Java Blog




Most Visited Tips
Java SE Tips
Java ME Tips
Java EE Tips
Other API Tips
Java Applications
Java Libraries
Java Games
Book Reviews
Top Rated Tips
Java SE Tips
Java ME Tips
Java EE Tips
Other API Tips
Java Applications
Java Libraries
Java Games
Book Reviews


Statistics
Registered Users: 4094
Java SE Tips: 614
Java ME Tips: 202
Java EE Tips: 183
Other API Tips: 779
Java Applications: 298
Java Libraries: 209
Java Games: 16
Book Reviews:
 
 
 
How to view database data in XLS format using servlets E-mail
User Rating: / 16
PoorBest 

The example below extracts data from SQL server and outputs it into a web browser in the form of a XLS sheet using Java Servlets.

import java.io.IOException;
import java.io.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import jxl.write.*;
import jxl.*;
import java.util.*;

public class viewDataIntoXLS extends HttpServlet {
    
    public void doGet(HttpServletRequest request, 
            HttpServletResponse response
            throws ServletException, IOException {
        
        DB3 db = new DB3();
        Connection conn=db.dbConnect(
                "jdbc:jtds:sqlserver://localhost:1433/ntconnect","sa","");
        
        java.util.List details = db.getDetails(conn);
        try {
            response.setContentType("application/vnd.ms-excel");
            WritableWorkbook w = Workbook.createWorkbook(
                    response.getOutputStream());
            
            WritableSheet s = w.createSheet("Demo"0);
            WritableFont wf = new WritableFont(
                    WritableFont.ARIAL, 10, WritableFont.BOLD);
            
            WritableCellFormat cf = new WritableCellFormat(wf);
            cf.setWrap(true);
            Label l;
            Iterator i = details.iterator();
            int column =0;
            int row = 0;
            while(i.hasNext()) {
                l= new Label(column,row,(String)i.next(),cf);
                s.addCell(l);
                column++;
            }
            w.write();
            w.close();
            
        catch (Exception e) {
            throw new ServletException("Exception in XLS  Servlet", e);
        }
    }
    
    public void doPost(HttpServletRequest request,
            HttpServletResponse response)
            throws IOException, ServletException {
        doGet(request, response);
    }
}

class DB3 {
    public DB3() {}
    
    public Connection dbConnect(String db_connect_string,
            String db_userid, String db_password) {
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            Connection conn = DriverManager.getConnection(
                    db_connect_string, db_userid, db_password);
            System.out.println("connected");
            return conn;
            
        catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    
    public java.util.List getDetails(Connection conn) {
        
        java.util.List items = null;
        String query;
        try {
            query = 
               "SELECT col_User, col_Password, col_Date, col_Domain, "+
               "col_LastName, col_FirstName, col_Occupation FROM "+
               "[tbl_Current-usr] WHERE (col_User LIKE '%JLOGAN%')";
            
            Statement state = conn.createStatement();
            ResultSet rs = state.executeQuery(query);
            items = new ArrayList();
            
            while (rs.next()) {
                items.add(rs.getString("col_User"));
                items.add(rs.getString("col_password"));
                items.add(rs.getString("col_date"));
                items.add(rs.getString("col_domain"));
                items.add(rs.getString("col_lastname"));
                items.add(rs.getString("col_firstname"));
                items.add(rs.getString("col_ocupation"));
            }
            
        catch (Exception e) {
            e.printStackTrace();
        }
        return items;
    }
};


 Related Tips

 
< Prev   Next >

Page 1 of 0 ( 0 comments )

You can share your information about this topic using the form below!

Please do not post your questions with this form! Thanks.


Name (required)


E-Mail (required)

Your email will not be displayed on the site - only to our administrator
Homepage(optional)



Comment Enable HTML code : Yes No



 
       
         
     
 
 
 
   
 
 
java bottom left
java bottom middle
java bottom right
RSS 0.91 FeedRSS 1.0 FeedRSS 2.0 FeedATOM FeedOPML Feed

Home - About Us - Privacy Policy
Copyright 2005 - 2008 www.java-tips.org
Java is a trademark of Sun Microsystems, Inc.