Java to export data from an oracle table to an excel table

Java to export data from an oracle table to an excel table

1. Database Connection class: Dbutilpackage sql2excel; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. SQL. SQLException; public class Dbutil {/** function: Write a static method to establish a Connection with the database. input parameter: no return value: database Connection object */public static Connection getConnection () {// define a Connection object Connection conn = null; // create a pre-compiled statement object. This is generally used instead of StatementPreparedState. Ment pre = null; // create a result set object ResultSet result = null; // define the URL resource String url to connect to the database = "jdbc: oracle: thin: @ 10.20.56.52: 1521: orcl "; // define the user name and password for database connection String username =" crm "; String password =" crm "; // load the database connection driver String className =" oracle. jdbc. driver. oracleDriver "; try {Class. forName (className);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke. printStackTrace ();} // obtain the database connection object try {conn = DriverManager. getConnection (url, username, password); System. out. println ("database connection established successfully... ");} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}// return the Connection object 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 () ;}}} 2. Master Program sql2excelpackage sql2excel; import java. io. file; import java. SQL. connection; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. text. simpleDateFormat; import java. util. date; import java. util. list; 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; PreparedStatement pstmt = null; try {conn = Dbutil. getConnection (); Date now = new Date (); SimpleDateFormat df = new SimpleDateFormat ("yyyyMMddHHmmss"); String nowdate = df. format (now); // open the WritableWorkbook bo File OK = Workbook. createWorkbook (new File (nowdate + ". (xls "); // generate a worksheet named" first page ". The parameter 0 indicates that this is the first WritableSheet sheet = book. createSheet ("first page", 0); // set the font to, the 16 character, bold, and the color to black WritableFont font1 = new WritableFont (WritableFont. createFont (""), 10, WritableFont. BOLD); font1.setColour (Colour. BLACK); WritableCellFormat format1 = new WritableCellFormat (font1); format1.setAlignment (jxl. format. alignment. CENTRE); format1.setVert IcalAlignment (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 = ne W 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, "NAME", format1 ); label labelB = new Label (1, 0, "addh", format1); Label labelC = new Label (2, 0, "AGE", format1); // Add the defined cells to the worksheet // 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); // creates a pre-compiled statement object, which is usually used Do not use StatementPreparedStatement pre = null; // create a result set object ResultSet result = null; String SQL = "select * from student"; // pre = conn. prepareStatement (SQL); // instantiate the precompiled statement result = pre.exe cuteQuery (); // execute the query, note that no parameters need to be added to the brackets. // You can query all the data in the database List <StuEntity> list = StuService. getAllByDb (); while (result. next () {for (int I = 0; I <list. size (); I ++) {Label labelAi = new Label (0, I + 1, list. get (I ). getName (); Label LabelBi = new Label (1, I + 1, list. get (I ). getadsid (); Label labelCi = new Label (2, I + 1, list. get (I ). getAge (); sheet. addCell (labelAi); sheet. addCell (labelBi); sheet. addCell (labelCi) ;}// write data and close the file book. write (); book. close (); System. out. println ("File Created successfully! ");} Catch (Exception e) {System. out. println (e) ;}} public static void main (String [] args) {new sql2excel (). createXLS () ;}} 3. StuEntitypackage sql2excel; public class StuEntity {/*** @ author liuhaijun **/private String name; private String age; private String adsid; public StuEntity (String name, String adsid, String age) {this. age = age; this. name = name; this. adsid = adsid;} public String getName () {return name;} public void setName (String name) {this. name = name;} public String getaddred () {return addred;} public void setaddred (String addred) {this. adsid = adsid;} public String getAge () {return age;} public void setAge (String age) {this. age = age ;}} 4. StuServicepackage sql2excel; import java. io. file; 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 jxl. sheet; import jxl. workbook;/*** @ author liuhaijun ***/public class StuService {/*** @ return */public static List <StuEntity> getAllByDb () {List <StuEntity> list = new ArrayList <StuEntity> (); try {Connection conn = null; conn = Dbutil. getConnection (); // create a pre-compiled statement object, which is usually used instead of StatementPreparedStatement pre = null; // create a result set object ResultSet result = null; string SQL = "select * from student"; pre = conn. prepareStatement (SQL); // instantiate the precompiled statement result = pre.exe cuteQuery (); // execute the query. Note that you do not need to add the parameter while (result. next () {String name = result. getString ("name"); String addh = result. getString ("adsid"); String age = result. getString ("age"); list. add (new StuEntity (name, adsid, age) ;}} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} return list ;}}