Dynamic generation of databases and Excel reports

I. Dynamic generation of database and Excel reports

(1) Read the database data dynamically generated Excel report, this is a common problem in JSP application, the basic method used in this section is:

In an Excel workbook, make a report template on worksheet one, read the data from the database, and use the POI component to copy the template sheet

Instead, you get a new worksheet that fills in the query data into a new worksheet.

(2) Example analysis

Write a servlet program, query the titles table and sales table of the pubs database, fill in the query result set data into the report in Figure 6-14,

The procedure is as follows:

1th Step: Create a new servlet class named "Dbexcelservlet"
2nd step: Compile the Dbexcelservlet program.

Package My;import Javax.servlet.*;import Javax.servlet.http.*;import java.io.*;import java.util.*;import Org.apache.poi.hssf.usermodel.*;import java.sql.*;import javax.sql.*;p ublic class Dbexcelservlet extends HttpServlet  {Connection con=null;  Statement St=null;    ResultSet Rs=null; protected void doget (HttpServletRequest request,httpservletresponse response) throws Servletexce      ption,java.io.ioexception {ServletContext application=getservletcontext ();           ServletConfig Config=getservletconfig (); Response.setcontenttype ("application/vnd.ms-excel");      The definition returned to the client is an Excel data file Servletoutputstream out=response.getoutputstream ();      HttpSession session =request.getsession ();            Request.setcharacterencoding ("gb2312"); String inputfilename= "C:\\tomcat\\webapps\\root\\booksales.xls";   The path to the report file Hssfworkbook sale = new Hssfworkbook (new FileInputStream (InputFileName)); Open a template working thin hssfsheet sheet=null;A worksheet reference Hssfrow Row=null;  A data navigation object reference Hssfcell cell=null;//a Cell object int rownumber=3;   Records the number of rows in the current data double sum=0;   Statistic amount try {class.forname ("com.microsoft.sqlserver.jdbc.SQLServerDriver");   String url= "jdbc:sqlserver://localhost:1433;databasename=pubs;user=sa;password=";      con = drivermanager.getconnection (URL);   String sql= "Select A.title_id,title,price,qty from titles a joins sales B on a.title_id=b.title_id";   St=con.createstatement ();    Rs=st.executequery (SQL); Sheet=sale.clonesheet (0);  The template sheet in the clone workbook gets a new worksheet while (Rs.next ())//Traversal loop {Row=sheet.getrow (rowNumber);//Get Data Row object to fill String Title_id=rs.getstring (1); Cell=row.getcell ((short) 0);  Cell.setcellvalue (title_id); String title=rs.getstring (2); Cell=row.getcell ((short) 1);  Cell.setcellvalue (title); Double price=rs.getdouble (3); Cell=row.getcell ((short) 4);  Cell.setcellvalue (price); int Qty=rs.getint (4); Cell=row.getcell ((short) 5);  Cell.setcellvalue (qty); Double S=price*qty; CEll=row.getcell ((short) 6);  Cell.setcellvalue (s);  Sum=sum+s; rownumber++;  if (rownumber==8) {Row=sheet.getrow (8); Cell=row.getcell ((short) 6); Cell.setcellvalue (sum); sum=0; Sheet=sale.clonesheet (0); rownumber=3; Row=sheet.getrow (RowNumber);   }} if (rownumber<8) {Row=sheet.getrow (8); Cell=row.getcell ((short) 6); Cell.setcellvalue (sum);   } rs.close ();   St.close (); Con.close ();}  catch (Exception e) {out.print (E.getmessage ());  } finally {try {if (rs!=null) rs.close (); if (st!=null) st.close (); if (con!=null) con.close ();  }catch (Exception em) {System.out.println (EM);}  } sale.removesheetat (0); After completing the worksheet, delete the template sheet from the workbook, which is the first sheet sale.write (out);   Writes the workbook to the response buffer and returns the Out.close () to the client browser display; } protected void DoPost (httpservletrequest request,httpservletresponse response) th   Rows servletexception,java.io.ioexception {doget (request,response); } }

(3) Because recently in a lot of forums to see their own many original posts, I hope everyone in the reprint when the source, is the greater affirmation of my efforts! Thank you for your support!

Dynamic generation of databases and Excel reports