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(); }}至此,大功告成。