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

Source: Internet
Author: User
Tags date now

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 ;}}

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.