2 wuhj2011 wuhj2011 于 2014.10.12 11:26 提问

在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());
%>


Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!