从数据库导出数据到Excel

来源：互联网发布：淘宝买刀剑犯法吗编辑：程序博客网时间：2024/05/01 01:12

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");
}

}