问题描述
- 在jsp中使用jxl.jar包来将mysql搜索语句sql结果集导出到excel文件
-
整体构思是：连接数据库由DBManager.java，创建excel样式由Excel.java；最后在excel.jsp实现！但现在数据库数据并不能显示，求指教，我是java小白！
Excel.java代码package com;
import java.io.*;
import java.sql.*;import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;public class Excel {
private static Connection conn=null;
private static PreparedStatement pstmt=null;
private static ResultSet rs=null;
private static CachedRowSet crs;
@SuppressWarnings("static-access")
public CachedRowSet GetResult(String sql)throws SQLException{
try {
conn=DBManager.getDBManager().connection;
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
crs=new CachedRowSetImpl();
crs.populate(rs);
System.out.println(null==crs);
return crs;
} catch (Exception e) {
// TODO: handle exception
return null;
}}
public void getExcelResult(String sql, OutputStream os)
throws SQLException, IOException, WriteException {// 首先获取结果集
// 这里获取RowSet的方法
CachedRowSet crs = this.GetResult(sql);// 然后将结果集转化为Excel输出
// 初始化工作
System.out.println(null==crs);
WritableWorkbook wwb = null;try {
wwb = Workbook.createWorkbook(os);// 创建工作表 jxl.write.WritableSheet ws = wwb.createSheet("Sheet1", 0); // 逐行添加数据 Label l1=new Label(0,0,"开始时间"); Label l2=new Label(1,0,"时间"); Label l3=new Label(2,0,"最高电压1"); Label l4=new Label(3,0,"电池号"); Label l5=new Label(4,0,"最高电压2"); Label l6=new Label(5,0,"电池号"); Label l7=new Label(6,0,"最高电压3"); Label l8=new Label(7,0,"电池号"); ws.addCell(l1); ws.addCell(l2); ws.addCell(l3); ws.addCell(l4); ws.addCell(l5); ws.addCell(l6); ws.addCell(l7); ws.addCell(l8); int i = 1; while (crs.next()) { for (int j = 1; j <= crs.getMetaData().getColumnCount(); j++) { String s = crs.getString(j); Label labelC = new Label(j-1, i, s); ws.addCell(labelC); } i++; }} catch (Exception e) {
//logger.error("export excel error:" + e);
e.printStackTrace();
} finally {
if (wwb != null) {
wwb.write();
wwb.close();
}
}}
}DBManager.java代码
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;public class DBManager {
private String driver="com.mysql.jdbc.Driver";
private String url="jdbc:mysql://localhost:3306/hwglxt?useUnicode=true&characterEncoding=gb2312";
private String username="root";
private String password="root";
public static Connection connection =null;
private static DBManager dbManager=null;
private DBManager()
{
try
{
Class.forName(driver);
connection=DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
System.out.println("1");
} catch (SQLException e) {
System.out.println("2");
}} public static DBManager getDBManager() { if (null==dbManager) { dbManager=new DBManager(); } return dbManager; }}
jsp页面文件
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="com.util.Excel" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">Insert title here
<% out.print(request.getParameter("sql"));
Excel excel=new Excel();
response.reset();
response.setContentType("application/vnd.ms-excel");// String sql="select * from tbit_links ";
String sql=request.getParameter("sql");
excel.getExcelResult(sql,response.getOutputStream());
%>