首页 > 代码库 > Java实现Oracle导出数据到Excel
Java实现Oracle导出数据到Excel
1.导入相应的jar包（jxl.jar 和 数据库连接的jar包）
2.写数据库连接的工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Dbutil {
/*
* 功能：编写一个静态方法用于与数据库建立连接 输入参数：无 返回值：数据库连接对象
*/
public static Connection getConnection() {
// 定义一个连接对象
Connection conn = null;
// 定义连接数据库的URL资源
String url = "jdbc:Oracle:thin:@localhost:1521:orcl";
// 定义连接数据库的用户名称与密码
String username = "root";
String password = "root";
// 加载数据库连接驱动
String className = "oracle.jdbc.driver.OracleDriver";
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 获取数据库的连接对象
try {
conn = DriverManager.getConnection(url, username, password);
System.out.println("数据库连接建立成功...");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 返回连接对象
return conn;
}
public static void close(Connection c) {
if (c != null) {
try {
c.close();
} catch (Throwable e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement c) {
if (c != null) {
try {
c.close();
} catch (Throwable e) {
e.printStackTrace();
}
}
}
}
3.写实体类
public class StuEntity {
private String id;
private String office_id;
private String name;
private String enname;
private String role_type;
private String data_scope;
private String is_sys;
private String useable;
private String create_by;
private String create_date;
private String update_by;
private String update_date;
private String remarks;
private String del_flag;
public StuEntity() {
super();
}
public StuEntity(String id, String office_id, String name, String enname, String role_type, String data_scope,
String is_sys, String useable, String create_by, String create_date, String update_by, String update_date,
String remarks, String del_flag) {
super();
this.id = id;
this.office_id = office_id;
this.name = name;
this.enname = enname;
this.role_type = role_type;
this.data_scope = data_scope;
this.is_sys = is_sys;
this.useable = useable;
this.create_by = create_by;
this.create_date = create_date;
this.update_by = update_by;
this.update_date = update_date;
this.remarks = remarks;
this.del_flag = del_flag;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getOffice_id() {
return office_id;
}
public void setOffice_id(String office_id) {
this.office_id = office_id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEnname() {
return enname;
}
public void setEnname(String enname) {
this.enname = enname;
}
public String getRole_type() {
return role_type;
}
public void setRole_type(String role_type) {
this.role_type = role_type;
}
public String getData_scope() {
return data_scope;
}
public void setData_scope(String data_scope) {
this.data_scope = data_scope;
}
public String getIs_sys() {
return is_sys;
}
public void setIs_sys(String is_sys) {
this.is_sys = is_sys;
}
public String getUseable() {
return useable;
}
public void setUseable(String useable) {
this.useable = useable;
}
public String getCreate_by() {
return create_by;
}
public void setCreate_by(String create_by) {
this.create_by = create_by;
}
public String getCreate_date() {
return create_date;
}
public void setCreate_date(String create_date) {
this.create_date = create_date;
}
public String getUpdate_by() {
return update_by;
}
public void setUpdate_by(String update_by) {
this.update_by = update_by;
}
public String getUpdate_date() {
return update_date;
}
public void setUpdate_date(String update_date) {
this.update_date = update_date;
}
public String getRemarks() {
return remarks;
}
public void setRemarks(String remarks) {
this.remarks = remarks;
}
public String getDel_flag() {
return del_flag;
}
public void setDel_flag(String del_flag) {
this.del_flag = del_flag;
}
@Override
public String toString() {
return "StuEntity [id=" + id + ", office_id=" + office_id + ", name=" + name + ", enname=" + enname
+ ", role_type=" + role_type + ", data_scope=" + data_scope + ", is_sys=" + is_sys + ", useable="
+ useable + ", create_by=" + create_by + ", create_date=" + create_date + ", update_by=" + update_by
+ ", update_date=" + update_date + ", remarks=" + remarks + ", del_flag=" + del_flag + "]";
}
}
4.获取数据库表中的数据
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.ninemax.util.Dbutil;
public class StuService {
/**
* @return
*/
public static List<StuEntity> getAllByDb() {
List<StuEntity> list = new ArrayList<StuEntity>();
try {
Connection conn = null;
conn = Dbutil.getConnection();
// 创建预编译语句对象，一般都是用这个而不用Statement
PreparedStatement pre = null;
// 创建一个结果集对象
ResultSet result = null;
String sql = "select * from SYS_ROLE";
pre = conn.prepareStatement(sql);// 实例化预编译语句
result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
while (result.next()) {
String id = result.getString("id");
String office_id = result.getString("office_id");
String name = result.getString("name");
String enname = result.getString("enname");
String role_type = result.getString("role_type");
String data_scope = result.getString("data_scope");
String is_sys = result.getString("is_sys");
String useable = result.getString("useable");
String create_by = result.getString("create_by");
String create_date = result.getString("create_date");
String update_by = result.getString("update_by");
String update_date = result.getString("update_date");
String remarks = result.getString("remarks");
String del_flag = result.getString("del_flag");
list.add(new StuEntity(id, office_id, name,enname,role_type,data_scope,is_sys,useable,create_by,create_date,update_by,update_date,remarks,del_flag));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
5.导入数据到excel表中，并以当前时间命名文件
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import com.ninemax.util.Dbutil;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class sql2excel {
public void createXLS() {
Connection conn = null;
try {
conn = Dbutil.getConnection();
Date now = new Date();
SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
String nowdate = df.format(now);
// 打开文件
WritableWorkbook book = Workbook.createWorkbook(new File(nowdate + ".xls"));
System.out.println(book);
System.out.println(nowdate + ".xls");
// 生成名为"第一页"的工作表，参数0表示这是第一
WritableSheet sheet = book.createSheet("第一页", 0);
// 设置字体为宋体,16号字,加粗,颜色为黑色
WritableFont font1 = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD);
font1.setColour(Colour.BLACK);
WritableCellFormat format1 = new WritableCellFormat(font1);
format1.setAlignment(jxl.format.Alignment.CENTRE);
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
// Label labelA = new Label(0, 0, "CALL_GUID", format1);
// Label labelB = new Label(1, 0, "RELATIONID", format1);
// Label labelC = new Label(2, 0, "ANI", format1);
// Label labelD = new Label(3, 0, "DNIS", format1);
// Label labelE = new Label(4, 0, "STAFF_ID", format1);
// Label labelF = new Label(5, 0, "CALLSTARTTIME", format1);
// Label labelG = new Label(6, 0, "CALLENDTIME", format1);
// Label labelH = new Label(7, 0, "CALLRESULT", format1);
// Label labelI = new Label(8, 0, "CALLRESULTREASON_ID", format1);
// Label labelJ = new Label(9, 0, "CALLREMARK", format1);
// Label labelK = new Label(10, 0, "EVENT_GUID", format1);
Label labelA = new Label(0, 0, "id", format1);
Label labelB = new Label(1, 0, "office_id", format1);
Label labelC = new Label(2, 0, "name", format1);
Label labelD = new Label(3, 0, "enname", format1);
Label labelE = new Label(4, 0, "role_type", format1);
Label labelF = new Label(5, 0, "data_scope", format1);
Label labelG = new Label(6, 0, "is_sys", format1);
Label labelH = new Label(7, 0, "useable", format1);
Label labelI = new Label(8, 0, "create_by", format1);
Label labelJ = new Label(9, 0, "create_date", format1);
Label labelK = new Label(10, 0, "update_by", format1);
Label labelL = new Label(11, 0, "update_date", format1);
Label labelM = new Label(12, 0, "remarks", format1);
Label labelN = new Label(13, 0, "del_flag", format1);
// 将定义好的单元格添加到工作表中
// sheet.addCell(labelA);
// sheet.addCell(labelB);
// sheet.addCell(labelC);
// sheet.addCell(labelD);
// sheet.addCell(labelE);
// sheet.addCell(labelF);
// sheet.addCell(labelG);
// sheet.addCell(labelH);
// sheet.addCell(labelI);
// sheet.addCell(labelJ);
// sheet.addCell(labelK);
sheet.addCell(labelA);
sheet.addCell(labelB);
sheet.addCell(labelC);
sheet.addCell(labelD);
sheet.addCell(labelE);
sheet.addCell(labelF);
sheet.addCell(labelG);
sheet.addCell(labelH);
sheet.addCell(labelI);
sheet.addCell(labelJ);
sheet.addCell(labelK);
sheet.addCell(labelL);
sheet.addCell(labelM);
sheet.addCell(labelN);
// 创建预编译语句对象，一般都是用这个而不用Statement
PreparedStatement pre = null;
// 创建一个结果集对象
ResultSet result = null;
String sql = "select * from SYS_ROLE";// 预编译语句
pre = conn.prepareStatement(sql);// 实例化预编译语句
result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
// 查询数据库中所有的数据
List<StuEntity> list = StuService.getAllByDb();
System.out.println(list.size());
// System.out.println("------------------------"+list.toString());
while (result.next()) {
for (int i = 0; i < list.size(); i++) {
Label labelAi = new Label(0, i + 1, list.get(i).getId());
System.out.println("----------------------"+labelAi.toString());
Label labelBi = new Label(1, i + 1, list.get(i).getOffice_id());
Label labelCi = new Label(2, i + 1, list.get(i).getName());
Label labelDi = new Label(3, i + 1, list.get(i).getEnname());
Label labelEi = new Label(4, i + 1, list.get(i).getRole_type());
Label labelFi = new Label(5, i + 1, list.get(i).getData_scope());
Label labelGi = new Label(6, i + 1, list.get(i).getIs_sys());
Label labelHi = new Label(7, i + 1, list.get(i).getUseable());
Label labelIi = new Label(8, i + 1, list.get(i).getCreate_by());
Label labelJi = new Label(9, i + 1, list.get(i).getCreate_date());
Label labelKi = new Label(10, i + 1, list.get(i).getUpdate_by());
Label labelLi = new Label(11, i + 1, list.get(i).getUpdate_date());
Label labelMi = new Label(12, i + 1, list.get(i).getRemarks());
Label labelNi = new Label(13, i + 1, list.get(i).getDel_flag());
sheet.addCell(labelAi);
sheet.addCell(labelBi);
sheet.addCell(labelCi);
sheet.addCell(labelDi);
sheet.addCell(labelEi);
sheet.addCell(labelFi);
sheet.addCell(labelGi);
sheet.addCell(labelHi);
sheet.addCell(labelIi);
sheet.addCell(labelJi);
sheet.addCell(labelKi);
sheet.addCell(labelLi);
sheet.addCell(labelMi);
sheet.addCell(labelNi);
}
System.out.println(sheet.toString());
}
// 写入数据并关闭文件
book.write();
book.close();
System.out.println("创建文件成功!");
} catch (Exception e) {
System.out.println(e);
}
}
public static void main(String[] args) {
new sql2excel().createXLS();
}
}
至此，大功告成。
Java实现Oracle导出数据到Excel