日期:2014-05-20 浏览次数:20765 次
<%@ page language="java" contentType="text/html;charset=UTF-8" import="java.util.*,java.io.*,java.sql.*,org.apache.poi.hssf.usermodel.*" pageEncoding="UTF-8"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>数据库导出成Excel文件测试</title> </head> <body> <br><br><br><br> <center> <% String driverName = "com.mysql.jdbc.Driver"; // 驱动名称 String userName = "root"; // 用户名 String password = "318404"; // 密码 String dbName = "test"; // 数据库名字 String tableName = "userinfo"; // 表名 String url="jdbc:mysql://localhost/"+dbName+"?user="+userName+"&password="+password; Class.forName(driverName).newInstance(); Connection connection = DriverManager.getConnection(url); Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery("SELECT userName,passWord,age,email,address FROM userinfo "); %> <% HSSFWorkbook workbook = new HSSFWorkbook(); //创建新的Excel工作薄 HSSFSheet sheet = workbook.createSheet("userInfo"); //在Excel工作薄中建工作表,名为缺省 HSSFRow row = sheet.createRow((short)0); //在索引0的位置建行(最顶端的行) // HSSFCell cell = row.createCell((short)0); //在索引0的位置建单元格 // cell.setEncoding(HSSFCell.ENCODING_UTF_16); //定义单元格为字符串类型 cell.setCellValue("编号"); //在单元格输入一些内容 cell = row.createCell((short)1); cell.setCellValue("姓名"); //在单元格输入一些内容 cell = row.createCell((short)2); cell.setCellValue("密码"); cell = row.createCell((short)3); cell.setCellValue("年龄"); cell = row.createCell((short)4); cell.setCellValue("邮箱"); cell = row.createCell((short)5); cell.setCellValue("地址"); int i = 1; while(rs.next()){ row = sheet.createRow((short)i); //在索引1的位置创建行(最顶端的行) cell = row.createCell((short)0); //在索引0的位置创建单元格(左上端) cell.setCellValue(i); //在单元格输入一些内容 cell = row.createCell((short)1); cell.setCellValue(rs.getString(1)); //在单元格输入一些内容 cell = row.createCell((short)2); cell.setCellValue(rs.getString(2)); cell = row.createCell((short)3); cell.setCellValue(rs.getString(3)); cell = row.createCell((short)4); cell.setCellValue(rs.getString(4)); cell = row.createCell((short)5); cell.setCellValue(rs.getString(5)); i++; } String filename = application.getRealPath("/") + "test.xls"; //filename是工作薄的存放位置,存放在当前应用程序的根目录下 FileOutputStream fOut = new FileOutputStream(filename); //新建输出文件流 workbook.write(fOut); //把相应的Excel工作薄存盘 fOut.flush(); fOut.close(); //操作结束,关闭文件 out.println("excel文件已经生成,存放在 <font color=red>" + filename + "</font>"); %> <fieldset style="height:auto;width: 80%;"> <legend>数据库导出成Excel文件测试</legend> <table align="center" border="1" width="80%"> <tr align="center"> <td colspan="6">纯JSP页面的分页测试数据</td> </tr> <tr align="center"> <td>编号</td> <td>姓名</td> <td>密码</td> <td>年龄</td> <td>邮箱</td> <td>地址</td> </tr> <% ResultSet result = statement.executeQuery("SELECT userName,passWord,age,email,address FROM userinfo ORDER BY id DESC"); int j = 1; while (result.next()) { // int bil = j + (PageNo-1)*PageSize; %> <tr align="center"> <td><%=j %></td> <td><%=result.getString(1)%></td> <td><%=result.getString(2) %></td> <td><%=result.getString(3) %></td> <td><%=result.getString(4) %></td> <td><%=result.getString(5) %></td> </tr> <% j++; } rs.close(); result.close(); statement.close(); connection.close(); %> </fieldset> </center> </body> </html>