将SQL Server数据表导出到Excel中

《将SQL Server数据表导出到Excel中》2007-09-13 09:14:47|  分类： JSP应用与控制 |  标签： |字号大中小 订阅 .

  在开发应用软件的过程中，有时候需要将将SQL Server数据表导出到Excel中，但并不需要在JSP页面显示，而是直接生成Excel文件。运行程序之前我们先来看一下项目要用到的表tb_record的表结构及其模拟的数据。如下图所示。



表tb_record的表结构及其模拟的数据



  运行程序，在页面中填写数据库名称，要导出的数据表的表名和欲导出的位置作息。如下图所示。



页面运行效果



单击页面中的“导出到Excel”按钮，将显示下图所示的页面提示信息。





其执行效果如下图所示。



导出后生成的Excel文件





技术要点

  在实例《利用Java Excel访问Excel》一文中是将导出的数据显示到JSP页面，那么怎样通过Java Excel将导出的数据保存到指定的Excel文件呢？

  类Workbook的静态方法createWorkbook()既可以接收JSP页面的输出流对象作为参数，又可以接收文件的绝对路径作为参数，代码如下：

File tempFile = new File(filePath);

WritableWorkbook writbook = null;

writbook = Workbook.createWorkbook(tempFile);

  其中参数filePath为保存Excel文件的绝对路径。

注意：如果在指定路径存在同名的文件，则将原文件覆盖，反之则创建该文件。



实现过程

（1）创建OperateDatabase类，OperateDatabase.java代码如下：

package mrgf;

//引入连接数据库用的包
import java.sql.*;

public class OperateDatabase {
    //定义连接数据库用的全局属性
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    int num=0;
    public OperateDatabase() {
    }

    //通过静态块加载数据库驱动
    static {
        String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        try {
            Class.forName(driverClass).newInstance();
        } catch (Exception ex) {
            System.out.println("------在加载数据库驱动时抛出异常，内容如下：");
            ex.printStackTrace();
        }
    }

    //获得数据库连接
    public void conndb(String dbName, String username, String password) {
        String url =
                "jdbc:sqlserver://localhost:1433;DatabaseName=" +
                dbName;
        try {
            conn = DriverManager.getConnection(url, username, password);
            stmt = conn.createStatement();
        } catch (SQLException ex) {
            System.out.println("------在建立数据库连接时抛出异常，内容如下：");
            ex.printStackTrace();
        }
    }

    //关闭数据库连接,释放资源
    public void closedb() {
        //先判断欲关闭对象是否为空,如果为空则跳过
        if (rs != null) {
            try {
                rs.close();
                rs = null;
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
                stmt = null;
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
                conn = null;
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    //插入记录
    public void insert(String sql) {
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            System.out.println("------在插入记录时抛出异常，内容如下：");
            ex.printStackTrace();
        }
    }

    //修改记录
    public void update(String sql) {
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            System.out.println("------在修改记录时抛出异常，内容如下：");
            ex.printStackTrace();
        }
    }

    //删除记录
    public void delete(String sql) {
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            System.out.println("------在删除记录时抛出异常，内容如下：");
            ex.printStackTrace();
        }
    }

    //查询记录，返回结果集
    public ResultSet select(String sql) {
        try {
            rs = stmt.executeQuery(sql);
        } catch (SQLException ex) {
            System.out.println("------在查询记录时抛出异常，内容如下：");
            ex.printStackTrace();
        }
        return rs;
    }

}




（2）创建OperateExcel类，OperateExcel.java代码如下：

 

package mrgf;

import java.io.*;
import java.util.*;
import jxl.*;
import jxl.format.*;
import jxl.write.*;
import jxl.write.DateTime;

public class OperateExcel {
    public OperateExcel() {
    }

    /**
     * 读取Excel
     * @param filePath
     * @param header
     * @param fieldTitle
     * @param notes
     */
    public void writeToExcel(String filePath, String header,
                             String[] fieldTitle, List notes) {

        // 在指定路径创建文件
        File tempFile = new File(filePath);
        // 创建工作薄
        WritableWorkbook writbook = null;
        try {
            writbook = Workbook.createWorkbook(tempFile);
        } catch (IOException e) {
            System.out.println("在创建工作薄时抛出异常，内容如下：");
            e.printStackTrace();
        }

        // 创建工作表并指定名称和索引位置
        WritableSheet sheet = writbook.createSheet("Sheet1", 0);

        // 设置合并单元格
        try {
            sheet.mergeCells(0, 0, fieldTitle.length - 1, 0);
            sheet.mergeCells(0, 1, fieldTitle.length - 1, 1);
            sheet.mergeCells(0, 2, fieldTitle.length - 1, 2);
        } catch (WriteException e) {
            System.out.println("在合并单元格时抛出异常，内容如下：");
            e.printStackTrace();
        }

        //预定义一些字体和格式

        // 定义表标题字体
        WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 16,
                WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                jxl.format.Colour.BLUE);
        WritableCellFormat headerFormat = new WritableCellFormat(headerFont);
        // 设置居中显示
        try {
            headerFormat.setAlignment(jxl.format.Alignment.CENTRE);
        } catch (WriteException e) {
            System.out.println("在设置居中显示时抛出异常，内容如下：");
            e.printStackTrace();
        }

        // 定义字段标题字体
        WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10,
                                                  WritableFont.NO_BOLD, false,
                                                  UnderlineStyle.NO_UNDERLINE,
                                                  jxl.format.Colour.RED);
        WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
        // 设置居中显示
        try {
            titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
        } catch (WriteException e) {
            System.out.println("在设置居中显示时抛出异常，内容如下：");
            e.printStackTrace();
        }

        // 定义记录字体
        WritableFont noteFont = new WritableFont(WritableFont.ARIAL, 10,
                                                 WritableFont.NO_BOLD, false,
                                                 UnderlineStyle.NO_UNDERLINE,
                                                 jxl.format.Colour.BLACK);
        WritableCellFormat noteFormat = new WritableCellFormat(noteFont);
        // 设置居中显示
        try {
            noteFormat.setAlignment(jxl.format.Alignment.CENTRE);
        } catch (WriteException e) {
            System.out.println("在设置居中显示时抛出异常，内容如下：");
            e.printStackTrace();
        }

        // 一些临时变量，用于写到excel中
        Label lable = null;
        jxl.write.Number num = null;
        jxl.write.DateTime date = null;

        // 填写工作表
        try {
            // 填写表名
            lable = new Label(0, 1, header, headerFormat);
            sheet.addCell(lable);
            // 填写字段名
            for (int i = 0; i < fieldTitle.length; i++) {
                lable = new Label(i, 3, fieldTitle[i], titleFormat);
                sheet.addCell(lable);
            }

            // 填写记录
            int row = 4;
            int column = 0;
            Iterator itNotes = notes.iterator();
            while (itNotes.hasNext()) {
                ArrayList note = (ArrayList) itNotes.next();
                Iterator itNote = note.iterator();
                while (itNote.hasNext()) {
                    String content = (String) itNote.next();
                    lable = new Label(column, row, content, noteFormat);
                    sheet.addCell(lable);
                    column = column + 1;
                }
                row = row + 1;
                column = 0;
            }
            // 写入文件
            writbook.write();
            // 关闭工作薄对象，释放内存空间
            writbook.close();
        } catch (Exception e) {
            System.out.println("在填写工作表内容时抛出异常，内容如下：");
            e.printStackTrace();
        }
    }

}




（3）创建首页面index.jsp，代码如下：

 

<%@ page contentType="text/html; charset=GBK" %>
<html>
<head>
<title>
导出到Excel数据库中
</title>
</head>
<body bgcolor="#ffffff">
<table width="400"  border="0" cellspacing="0" cellpadding="4" background="bg.gif">
  <tr>
    <td width="50">&nbsp;</td>
    <td width="300">&nbsp;</td>
    <td width="50">&nbsp;</td>
  </tr>
<form action="dispose.jsp">
  <tr>
    <td colspan="3"><div align="center">将SQL Server数据库表导出到Excel </div></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>数据库名：<input type="text" name="database" value="db_database16"></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>数据表名：<input type="text" name="table" value="tb_record"></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>导出位置：<input type="text" name="address" value="e:/record.xls"></td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td><div align="right"><input type="submit" name="Submit" value="导出到Excel"></div></td>
    <td>&nbsp;</td>
  </tr>
</form>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</body>
</html>




（4）创建页面dispose.jsp，代码如下：

 

<%@ page contentType="text/html; charset=GBK" %>
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="jxl.*" %>
<%@ page import="mrgf.*" %>
<html>
<head>
<title>
导出到Excel数据库中
</title>
</head>
<body>
<%
String database=request.getParameter("database");
String table=request.getParameter("table");
String address=request.getParameter("address");
OperateDatabase db=new OperateDatabase();
db.conndb(database,"admin","123456");
String sql="select * from "+table;
ResultSet rs=db.select(sql);
String[] fieldTitle=null;
List notes=new ArrayList();
try {
  ResultSetMetaData rsmd=rs.getMetaData();
  int columnCount=rsmd.getColumnCount();
  fieldTitle=new String[columnCount-1];
  for(int i=2;i<=columnCount;i++){
    fieldTitle[i-2]=rsmd.getColumnName(i);
  }
  while(rs.next()){
    List note=new ArrayList();
    for(int i=2;i<=columnCount;i++){
      note.add(rs.getString(i));
    }
    notes.add(note);
  }
} catch (Exception ex) {
  System.out.println("在查询数据时抛出异常，内容如下：");
  ex.printStackTrace();
}
db.closedb();
OperateExcel excel=new OperateExcel();
excel.writeToExcel(address,"未命名",fieldTitle,notes);
%>
<table width="400"  border="0" cellspacing="0" cellpadding="4" background="bg.gif">
  <tr>
    <td width="50">&nbsp;</td>
    <td width="300">&nbsp;</td>
    <td width="50">&nbsp;</td>
  </tr>
  <tr>
    <td colspan="3"><div align="center">将SQL Server数据库表导出到Excel </div></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>数据导出结束，请到<%=address %>查看导出结果！！！</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
<form action="index.jsp">
  <tr>
    <td>&nbsp;</td>
    <td><div align="right"><input type="submit" name="Submit" value="返回"></div></td>
    <td>&nbsp;</td>
  </tr>
</form>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
</body>
</html>