从数据库导出数据到Excel-优快云博客

本文介绍了一个使用Java实现从数据库查询数据并生成Excel文件的方法。通过示例代码展示了如何连接数据库、执行SQL查询，并将结果导出到指定路径的Excel文件中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >



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

}