Dynamic generation of database and Excel reports

Source: Internet
Author: User

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!


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.