jsp+servlet+poi导出数据库中的数据

jsp+servlet+poi导出数据库中的数据

index.jsp:

<a href="POIout">简单导出数据</a>

<a href="POIoutTemplate">根据模板导出数据</a>

package com.wp.poi;

import java.sql.Connection;
import java.sql.DriverManager;
//连接数据库类
public class DbUtil { private String dbUrl = "jdbc:mysql://localhost:3306/db_easyui";
private String dbUserName = "root";
private String dbPassword = "root";
private String jdbcName = "com.mysql.jdbc.Driver"; public Connection getCon() throws Exception {
Class.forName(jdbcName);
Connection con = DriverManager.getConnection(dbUrl, dbUserName,
dbPassword);
return con;
} public void closeCon(Connection con) throws Exception {
if (con != null) {
con.close();
}
}
}
package com.wp.poi;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet; import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook; /**
* 普通导出数据
*
* @author admin
*
*/
public class POIout extends HttpServlet { public POIout() {
super();
} public void init() throws ServletException {
} public void destroy() {
super.destroy();
} public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
} public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException { response.setContentType("text/html;charset=utf-8");
DbUtil dbUtil = new DbUtil();
Connection conn = null;
try {
conn = dbUtil.getCon();// 获得数据库连接
Workbook wb = new HSSFWorkbook();
String headers[] = { "编号", "姓名", "电话", "Email", "QQ" };// 标题
ResultSet rs = userList(conn);// 得到结果集
fillExcelData(rs, wb, headers);
export(response, wb, "导出数据.xls");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
dbUtil.closeCon(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
} /**
* 查询数据库
*
* @param con
* @return ResultSet 返回结果集
* @throws Exception
*/
public ResultSet userList(Connection con) throws Exception {
StringBuffer sb = new StringBuffer("select * from t_user");
PreparedStatement pstmt = con.prepareStatement(sb.toString());
return pstmt.executeQuery();
} /**
* 导出用户
*
* @throws Exception
*/
public void fillExcelData(ResultSet rs, Workbook wb, String[] headers)
throws Exception {
int rowIndex = 0; // 第一行
Sheet sheet = wb.createSheet(); // 创建sheet页
Row row = sheet.createRow(rowIndex++);
// 创建标题
for (int i = 0; i < headers.length; i++) {
row.createCell(i).setCellValue(headers[i]);
}
// 导出数据库中的数据
while (rs.next()) {
row = sheet.createRow(rowIndex++);
for (int i = 0; i < headers.length; i++) {
row.createCell(i).setCellValue(rs.getObject(i + 1).toString());
//rs.getObject(i + 1)得到一个对象,即数据库中一行的结果,每一列就是属性凑成一行变成对象。因为id是从1开始,所以要+1。
}
}
} /**
* 把数据放入到.xls文件中并下载到本地
*
* @param response
* @param wb
* @param fileName
* @throws Exception
*/
public void export(HttpServletResponse response, Workbook wb,
String fileName) throws Exception {
response.setHeader("Content-Disposition", "attachment;filename="
+ new String(fileName.getBytes("utf-8"), "iso8859-1"));// 设置头信息
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out = response.getOutputStream();
wb.write(out);// 进行输出,下载到本地
out.flush();
out.close();
}
}
package com.wp.poi;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet; import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook; /**
* 利用模板导出数据
*
* @author admin
*
*/
public class POIoutTemplate extends HttpServlet { public POIoutTemplate() {
super();
} public void init() throws ServletException {
} public void destroy() {
super.destroy();
} public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
} public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException { response.setContentType("text/html;charset=utf-8");
DbUtil dbUtil = new DbUtil();
Connection conn = null;
try {
conn = dbUtil.getCon();
Workbook wb = fillExcelDataWithTemplate(userList(conn),
"Template.xls");
export(response, wb, "利用模板导出的数据.xls");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
dbUtil.closeCon(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
} /**
* 查询数据库
*
* @param con
* @return
* @throws Exception
*/
public ResultSet userList(Connection con) throws Exception {
StringBuffer sb = new StringBuffer("select * from t_user");
PreparedStatement pstmt = con.prepareStatement(sb.toString());
return pstmt.executeQuery();
} /**
* 根据模板导出用户
*
* @throws Exception
*/
public Workbook fillExcelDataWithTemplate(ResultSet rs,
String templateFileName) throws Exception {
InputStream in = POIoutTemplate.class
.getResourceAsStream("/com/wp/poi/" + templateFileName);
POIFSFileSystem fs = new POIFSFileSystem(in);// 解析Excel文件
Workbook wb = new HSSFWorkbook(fs);// 以解析的excel文件格式进行创建
Sheet sheet = wb.getSheetAt(0);
// 获取列数
int cellNums = sheet.getRow(0).getLastCellNum(); int rowIndex = 1;// 从1开始的原因,模板的第一行固定了,所以从下一行开始
while (rs.next()) {
Row row = sheet.createRow(rowIndex++);
for (int i = 0; i < cellNums; i++) {
row.createCell(i).setCellValue(rs.getObject(i + 1).toString());
}
}
return wb;
} /**
* 导出
*
* @param response
* @param wb
* @param fileName
* @throws Exception
*/
public static void export(HttpServletResponse response, Workbook wb,
String fileName) throws Exception {
response.setHeader("Content-Disposition", "attachment;filename="
+ new String(fileName.getBytes("utf-8"), "iso8859-1"));
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
}

模板:

jsp+servlet+poi导出数据库中的数据

导出后:

jsp+servlet+poi导出数据库中的数据

Java小生店铺:

Pc端:http://shop125970977.taobao.com/index.htm

手机端:搜索 java小生店铺

希望店铺的资料能帮助到你!!!

jsp+servlet+poi导出数据库中的数据

上一篇:3.1、Ubuntu系统中jmeter的安装和目录解析


下一篇:windows系统下载地址大全&大白菜下载和教程