import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import psp.com.admin.dao.CopyOfReadData;
import psp.com.admin.dao.OperateExcel;
/**
* SQL Server数据库驱动
* */
public class DBSqlServer {
public static Connection con;
private String DatabaseDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private String DbIp="localhost";
private String DbName="jygl";//数据库名称
//&zeroDateTimeBehavior=convertToNull设置这个属性的原因是 当数据库中日期为‘0000-00-00’时不抛出异常
private String DatabaseConnUser = "sa";//
private String DatabaseConnPwd = "key";
public DBSqlServer(){
try {
Class.forName(DatabaseDriver);
con=DriverManager.getConnection("jdbc:sqlserver://"+DbIp+":1433;DatabaseName="+DbName,DatabaseConnUser,DatabaseConnPwd);
System.out.println("连接成功");
} catch (java.lang.ClassNotFoundException e) {
System.out.println("加载驱动器有错误:" + e.getMessage());
} catch (SQLException e) {
System.out.println("连接数据库有错误:" + e.getMessage());
}
}
/**
* SQL Server数据库存到Excel中
* @param sqlStr:数据库查询语句
* @param savePath:excel存放地址
* @param fileName:excel文件的名称
* @param sheetName:sheet标签名
* */
public void db2excel(String sqlStr,String savePath,String fileName,String sheetName) throws Exception{
//String sql="select id,入所编号,姓名,别名绰号,性别,出生日期,身份证号,重刑犯 from 基本信息";
Statement st = con.createStatement();
ResultSet rs=null;
rs=st.executeQuery(sqlStr);
//---------------写excel-----------------------------------------------------
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();
}
OperateExcel excel=new OperateExcel();
excel.writeToExcel(savePath+"/"+fileName,sheetName,fieldTitle,notes,sheetName);
System.out.println("导出完毕");
}
/**如果文件存在 删除掉*/
public void delExistFile(String fileName){
File file = new File(fileName);
if(file.exists()){
file.delete();//如果存在该文件则删除
System.out.println("文件删除成功!");
}
}
public static void main(String[] args){
String sql1="select id,人员编号,姓名,重刑犯,关押期限,涉嫌罪名,监室号 from 基本信息";//基本信息表
String sql2="select id,人员编号,人员姓名,会见时间 from 家属会见记录";//家属会见记录表
String sql3="select id,人员编号,奖惩日期,奖惩形式,奖惩原因 FROM 奖惩记录";
String sql4="select id,人员编号,原关押期限,处理日期,加减型期,关押期限 FROM 加减刑记录";
String savePath="e:/";
String fileName="监狱管理表.xls";
String sheetName1="基本信息";
String sheetName2="会见记录信息";
String sheetName3="奖惩记录";
String sheetName4="加减刑记录";
try {
DBSqlServer dbsql=new DBSqlServer();
dbsql.delExistFile(savePath+fileName);
dbsql.db2excel(sql1,savePath,fileName,sheetName1);
dbsql.db2excel(sql2,savePath,fileName,sheetName2);
dbsql.db2excel(sql3,savePath,fileName,sheetName3);
dbsql.db2excel(sql4,savePath,fileName,sheetName4);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
下面的代码是db2excel函数中调用的函数
package psp.com.admin.dao;
import java.io.*;
import java.util.*;
import jxl.*;
import jxl.format.*;
import jxl.read.biff.BiffException;
import jxl.write.*;
public class OperateExcel {
public OperateExcel() {
}
/**
* 写入Excel
* @param filePath
* @param header
* @param fieldTitle
* @param notes
* @param sheetName:sheet的名称,用于存放各个数据表
*/
public void writeToExcel(String filePath, String header,
String[] fieldTitle, List notes,String sheetName) {
// 在指定路径创建文件
File tempFile = new File(filePath);
// 创建工作薄
WritableWorkbook writbook = null;
if(tempFile.exists()){
System.out.println("文件存在");
try {
Workbook workbook = Workbook.getWorkbook(tempFile);
writbook = Workbook.createWorkbook(tempFile,workbook);
} catch (Exception e) {
System.out.println("在创建工作薄时抛出异常,内容如下:");
e.printStackTrace();
}
}else {
System.out.println("文件不存在");
try {
writbook = Workbook.createWorkbook(tempFile);
} catch (IOException e) {
System.out.println("在创建工作薄时抛出异常,内容如下:");
e.printStackTrace();
}
}
// 创建工作表并指定名称和索引位置
WritableSheet sheet = writbook.createSheet(sheetName, 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();
}
}
}
jxl类包网上比较多