整体构思是:连接数据库由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());
%>