Java implementation exports data from Oracle tables to the Excel table

1. Database Connection class: Dbutil

Package 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 used to establish connection with database input parameters: No return Value: Database Connection object

*/

public static Connection getconnection () {

Define a Connection object

Connection conn = null;

Create precompiled statement objects, usually with this instead of statement

PreparedStatement pre = NULL;

Create a result set object

ResultSet result = null;

To define a URL resource for a connection database

String url = "JDBC:ORACLE:THIN:@10.20.56.52:1521:ORCL";

Define the user name and password to connect to the database

String username = "CRM";

String Password = "CRM";

Load Database Connection Driver

String className = "Oracle.jdbc.driver.OracleDriver";

try {

Class.forName (ClassName);

} catch (ClassNotFoundException e) {

TODO auto-generated Catch block

E.printstacktrace ();

}

Gets the connection object for the database

try {

conn = drivermanager.getconnection (URL, username, password);

SYSTEM.OUT.PRINTLN ("Database connection established successfully ...");

} catch (SQLException e) {

TODO auto-generated Catch block

E.printstacktrace ();

}

Return 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, the main program Sql2excel

Package 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 File

Writableworkbook book = Workbook.createworkbook (New File (nowdate+ ". xls"));

Generate a worksheet named "first page", with parameter 0 indicating that this is the first

Writablesheet sheet = book.createsheet ("First page", 0);

Set font to Arial, 16th, bold, black color

Writablefont font1 = new Writablefont (

Writablefont.createfont ("Song Body"), 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 (ten, 0, "Event_guid", FORMAT1);

Label Labela = new label (0, 0, "NAME", FORMAT1);

Label Labelb = new label (1, 0, "Addre", FORMAT1);

Label LABELC = new label (2, 0, "age", FORMAT1);

Add a defined cell to a 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);

Create precompiled statement objects, usually with this instead of statement

PreparedStatement pre = NULL;

Create a result set object

ResultSet result = null;

String sql = "SELECT * from student";//Precompiled statement

Pre = conn.preparestatement (SQL);//instantiation of precompiled statements

result = Pre.executequery ();//Execute query, note that no additional arguments are required in parentheses

Querying 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). Getaddre ());

Label Labelci = new label (2, i + 1, list.get (i). Getage ());

Sheet.addcell (Labelai);

Sheet.addcell (LABELBI);

Sheet.addcell (LABELCI);

}

}

Writing data and closing files

Book.write ();

Book.close ();

System.out.println ("Create file successfully!");

} catch (Exception e) {

System.out.println (e);

}

}

public static void Main (string[] args) {

New Sql2excel (). Createxls ();

}

}

3, Stuentity

Package sql2excel;

public class Stuentity {

/**

* @author Liuhaijun

*

*/

private String name;

Private String age;

Private String Addre;

Public stuentity (string name, String Addre, String age) {

This.age = age;

THIS.name = name;

This.addre = Addre;

}

Public String GetName () {

return name;

}

public void SetName (String name) {

THIS.name = name;

}

Public String Getaddre () {

return addre;

}

public void Setaddre (String addre) {

This.addre = Addre;

}

Public String Getage () {

return age;

}

public void Setage (String age) {

This.age = age;

}

}

4, Stuservice

Package 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 precompiled statement objects, usually with this instead of statement

PreparedStatement pre = NULL;

Create a result set object

ResultSet result = null;

String sql = "SELECT * from student";

Pre = conn.preparestatement (SQL);//instantiation of precompiled statements

result = Pre.executequery ();//Execute query, note that no additional arguments are required in parentheses

while (Result.next ()) {

String name = result.getstring ("name");

String Addre = result.getstring ("Addre");

String age = result.getstring ("Age");

List.add (New stuentity (name, Addre, age));

}

} catch (SQLException e) {

TODO auto-generated Catch block

E.printstacktrace ();

}

return list;

}

}

This article is from the "Database Siege Lion" blog, please make sure to keep this source http://liu16.blog.51cto.com/6494747/1720706

Java implementation exports data from Oracle tables to the Excel table