Java Tips - How to view database data in XLS format using servlets

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
