package com.yaofang;
import java.io.*;
import java.sql.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import jxl.*;
import jxl.write.*;
public class Excel {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
/*
* ======================================================== ===
* 函数功能:生成excel文件 === 参 数 === title:字段名称 === sql: sql字符串 === path:文件生成路径
*
* ==========================================================
*/
public void createEXCEL(String[] title, String sql, String path) {
int i = 0;
int j = 0;
try {
// 建立与数据库的连接
conn = DBConnection.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
// 构建Workbook对象, 只读Workbook对象
// :创建可写入的Excel工作薄
WritableWorkbook wwb = Workbook
.createWorkbook(new FileOutputStream(path));
WritableSheet ws = wwb.createSheet("sheet1", 0);
for (i = 0; i < title.length; i++) {
WritableFont wf = new WritableFont(WritableFont.TIMES, 12,
WritableFont.BOLD, false);
WritableCellFormat wcfF = new WritableCellFormat(wf);
Label labelCF = new Label(i, 0, title[i], wcfF);
ws.addCell(labelCF);
}
i = 1;
while (rs.next()) {
for (j = 1; j <= title.length; j++) {
Label labelC = new Label(j - 1, i, rs.getString(j));
ws.addCell(labelC);
}
i++;
}
// 写入Exel工作表
wwb.write();
// 关闭Excel工作薄对象
wwb.close();
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
System.out.println(e.toString());
}
}
// //测试函数
public static void main(String args[]) {
String[] title = { "", "", "", "", "", "", "" };
String sql = "select * from drugs";
Excel Myexcel = new Excel();
Myexcel.createEXCEL(title, sql, "d://dzl.csv");
}
}