java从页面导出数据的excl文件

注：此代码仅做参考用，可能有语句不对的地方需要自己去修改

Java实现excl导出:
自我感觉代码注释已经足够详细，此处不再做过多的说明

首先创建数据库链接：代码如下

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
* jdbc工具类，提供了
* 获取数据库连接，关闭连接等方法。
*
*/
public class DBUtil {
private static String driver = ConfigUtil.getValue(“driver”);
private static String url = ConfigUtil.getValue(“url”);
private static String user = ConfigUtil.getValue(“user”);
private static String pwd = ConfigUtil.getValue(“pwd”);
private static ThreadLocal
connectionHoders = new ThreadLocal();
public static synchronized Connection getConnection2() throws Exception{
//先从线程局部变量(看成是一个容器)中取
Connection conn = connectionHoders.get();
if(conn == null){
conn = getConnection();
//以当前线程对象作为key,以conn作为
//value,放到了一个HashMap里面。
connectionHoders.set(conn);
}
return conn;
}
public static synchronized void close2(){
//以当前线程对象作为key,从HashMap
//中取对应的value
Connection conn = connectionHoders.get();
if(conn != null){
try {
conn.close();
connectionHoders.set(null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}

/**
* 获得一个连接
*/
public static Connection getConnection()
throws Exception{
Class.forName(driver);
Connection conn =
DriverManager.getConnection(url,user,pwd);
return conn;
}

/**
* 执行增删改操作
* @throws Exception
* */
public static int executeUpdate(String sql,Object[] params) throws Exception
{
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
int count=0;
con=getConnection();
System.out.println(“执行sql语句—–&amp;gt;”+sql);
pstmt=con.prepareStatement(sql);
setParams(pstmt, params);
System.out.println();
count=pstmt.executeUpdate();

//关闭连接
if(rs != null)
{
rs.close();
rs=null;
}

if(pstmt != null)
{
pstmt.close();
pstmt=null;
}

if(con != null)
{
con.close();
con=null;
}

return count;
}
/**
* xunianchi
* 处理参数对象
* @throws SQLException
* */
private static void setParams(PreparedStatement pstmt, Object[] params) throws SQLException
{
if(params==null)return;

for(int i=0;i&amp;lt;params.length;i++)
{
pstmt.setObject(i+1, params[i]);
}
}
/**
* 关闭连接
*/
public static void close(Connection conn){
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public static void close(ResultSet rst){
if(rst != null){
try {
rst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public static void close(Statement stat){
if(stat != null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public static void close(ResultSet rst,
Statement stat,Connection conn){
close(rst);
close(stat);
close(conn);
}

public static void close(Statement stat,
Connection conn){
close(stat);
close(conn);
}

}

//然后创建一个对照表头
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.TreeMap;

import util.DBUtil;

public class map_StuService {
public static Map getAllByDb(String sql) {
ResultSetMetaData rsmd = null;
Connection conn = null;
// 创建预编译语句对象，一般都是用这个而不用Statement
PreparedStatement ps = null;
// 创建一个结果集对象
ResultSet rs = null;
Map map = new LinkedHashMap() ;
try {
conn = DBUtil.getConnection();
// String sql = “select trim(VNAME),UPPER(trim(KKEY)) from cwzk_jycg_dz where xz=’通用’ or xz =’企业’ ORDER BY ID”;
ps = conn.prepareStatement(sql);// 实例化预编译语句
rs = ps.executeQuery();
rsmd = rs.getMetaData();
int c = rsmd.getColumnCount();
System.out.println(c);
int j=1;
while (rs.next()) {
map.put(rs.getObject(2), rs.getObject(1));
j++;
}

} catch ( Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.close(rs, ps, conn);
}
return map;
}
}
//然后创建执行文件

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import util.DBUtil;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class map_sql2excel {

public void createXLS(HttpServletResponse resp,String sqltype,String years) {
Connection conn = null;
// 创建预编译语句对象，一般都是用这个而不用Statement
PreparedStatement pre = null;
// 创建一个结果集对象
ResultSet result = null;
ResultSetMetaData rsmd = null;
try {
conn = DBUtil.getConnection();
Date now = new Date();
SimpleDateFormat df = new SimpleDateFormat(“yyyyMMddHHmmss”);
String nowdate = df.format(now);
// // 打开文件
// WritableWorkbook book = Workbook.createWorkbook(new File(nowdate + “.xls”));
// System.out.println(book);
// System.out.println(nowdate + “.xls”);
// // 生成名为”第一页”的工作表，参数0表示这是第一
// WritableSheet sheet = book.createSheet(“第一页”, 0);
//
// // 设置字体为宋体,16号字,加粗,颜色为黑色
// WritableFont font1 = new WritableFont(WritableFont.createFont(“宋体”), 12, WritableFont.BOLD);
// font1.setColour(Colour.BLACK);
// WritableCellFormat format1 = new WritableCellFormat(font1);
// format1.setAlignment(jxl.format.Alignment.CENTRE);
// format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
//String sqltype =””;//定义类型,定义sql对象
String[] v_sql=new String[10];
String[] sqlcon=new String[10];
String[] sheetname=new String[10];
//对sql进行判断

if(“qkxq”.equals(sqltype))
{ //期刊详情

v_sql[0]=”select deptno,dname,loc from dept”;
sqlcon[0]=”SELECT trim(VNAME),UPPER(trim(KKEY)) FROM cwzk_jycg_dz WHERE xz=’dept’ ORDER BY ID”;
sheetname[0]=”测试”;

}

//创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
System.out.println(v_sql.length-1);
for(int x=0;x&amp;lt;v_sql.length-1;x++){ //循环执行数组中的sql
if(v_sql[x] != null){
//打印得到的对象
System.out.println(“第”+x+”次执行”);
System.out.println(“拿到的第”+x+”个表头名：”+sheetname[x]);
System.out.println(“执行sql：”+v_sql[x]);
System.out.println(“执行对照sql：”+sqlcon[x]);
//2.创建工作表

XSSFSheet sheet = workbook.createSheet(sheetname[x]);
// workbook.setSheetName(x, sheetname[x]);
//设置默认列宽
sheet.setDefaultColumnWidth(25);
// XSSFRow row2 = sheet.createRow(0);

pre = conn.prepareStatement(v_sql[x]);// 实例化预编译语句
result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
rsmd = result.getMetaData();
int c = rsmd.getColumnCount();
XSSFRow row2 = sheet.createRow(0);
// 查询数据库中所有的数据
Map map = map_StuService.getAllByDb(sqlcon[x]);
int p=-1;
int j=0;
int h = 1;
// System.out.println(“————————“+list.toString());
while (result.next()) {
p=p+1;
XSSFRow row3 = sheet.createRow(h);
for(int i=1;i&amp;lt;c;i++){
if(p==0){
String bak =(String) map.get(rsmd.getColumnName(i));

if(bak != null){
XSSFCell cell2 = row2.createCell(j);
cell2.setCellValue(bak);
j=j+1;
}
}

Object bak_1 = result.getObject(rsmd.getColumnName(i));
if(bak_1 == null){
bak_1 =” “;
}
// System.out.println(result.getObject(“DWBM”));
// System.out.println(rsmd.getColumnName(1));
// System.out.println(result.getObject(rsmd.getColumnName(1)));
// System.out.println(bak_1);

String bak =bak_1.toString();
XSSFCell cell3 = row3.createCell(i-1);
cell3.setCellValue(bak);

}

h=h+1;
}
}else
{
System.out.println(“没有数据了，跳出循环”);
break;
}

}
// 写入数据并关闭文件
// book.write();
// book.close();
//自动判断文件类型
resp.setContentType(“multipart/form-data”);
//文件传输给前台
OutputStream out = resp.getOutputStream();
workbook.write(out);
out.close();
workbook.close();
System.out.println(“创建并上传文件成功!”);

} catch (Exception e) {
System.out.println(e);
e.printStackTrace();
}finally {
DBUtil.close(result, pre, conn);
}
}

private String String(int i) {
// TODO Auto-generated method stub
return null;
}

public static void main(String[] args) {

//new map_sql2excel().createXLS();
}
}

此处涉及到的表结构
–自建参照表
create table CWZK_JYCG_DZ
(
vname CHAR(100),–键
kkey CHAR(100),–值
xz CHAR(10),–标识
id NUMBER,–编号，排序用
fields VARCHAR2(100)—预留字段
)

–系统测试表
create table SCOTT.DEPT
(
deptno NUMBER(2) not null,
dname VARCHAR2(14),
loc VARCHAR2(13)
)