Dynamic generation of database and Excel reports

Dynamic generation of database and Excel reports
I. Dynamic generation of database and Excel reports

(1) Reading database data to dynamically generate Excel reports, which is a common problem in JSP applications. The basic methods used in this section are as follows:

In the Excel worksheet, create a report template in the first worksheet, read data from the database, and copy the template worksheet using the POI component.

A new worksheet is generated, and the query data is filled in the new worksheet.

(2) instance analysis

Write a Servlet program to query the titles and sales tables of the pubs database, and enter the query result set data in the report shown in Figure 6-14,

The procedure is as follows:

Step 2: Create a Servlet class named "DBExcelServlet"
Step 2: 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. *; public class DBExcelServlet extends HttpServlet {Connection con = null; Statement st = null; ResultSet rs = null; protected void doGet (HttpServletRequest request, HttpServletResponse response) throws ServletException, java. io. iow.t Ion {ServletContext application = getServletContext (); ServletConfig config = getServletConfig (); response. setContentType ("application/vnd. ms-excel "); // defines that the excel Data File ServletOutputStream out = response is returned to the client. getOutputStream (); HttpSession session = request. getSession (); request. setCharacterEncoding ("gb2312"); String inputFileName = "c: \ tomcat \ webapps \ ROOT \ bookSales.xls"; // HSSFWorkbook sal E = new HSSFWorkbook (new FileInputStream (inputFileName); // open a template. HSSFSheet sheet = null; // reference to a worksheet. HSSFRow row = null; // HSSFCell cell = null is referenced by a data airline object; // A cell Object int rowNumber = 3; // record the number of rows in the current data Row double sum = 0; // calculate the amount. try {Class. forName ("com. microsoft. sqlserver. jdbc. SQLServerDriver "); String url =" jdbc: sqlserver: // localhost: 1433; databaseName = pubs; user = sa; password = "; con = DriverManager. getConnection (u Rl); String SQL = "select. title_id, title, price, qty from titles a join sales B on. title_id = B. title_id "; st = con. createStatement (); rs1_st.exe cuteQuery (SQL); sheet = sale. cloneSheet (0); // clone the template worksheet in the workbook to get a new worksheet while (rs. next () // traverse the loop {row = sheet. getRow (rowNumber); // obtain the object 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 entering the worksheet, delete the template worksheet in the workbook, which is the first worksheet sale. write (out); // write the workbook to the response Buffer and return it to the client browser to display the out. close ();} protected void doPost (HttpServletRequest request, HttpServletResponse response) throws ServletException, java. io. IOException {doGet (request, response );}}

(3) I have seen many of my original posts in many forums recently. I hope that you can indicate the source when you repost them. This is a great affirmation of my efforts! Thank you for your support!