Java exports data from the database to Excel for data backup

Invocation part:

Package Com.otdrmsys.action;import Com.otdrmsys.util.resultsettoexcel;public class Excelexport {public static void Main (string[] args) {//string filename = "OTDR";//filename, without path, without. xls suffix//string [] coloumitems = {"OTDR number", "OTDR site ID", " Target fiber Segment id "};//string sql =" SELECT * from _OTDR "; String filename = "substation";//filename, without path, without. xls suffix//string [] coloumitems = {"Site ID", "Site name", "Site level", "latitude", "Longitude value", "Lat value"};//str ing sql = "SELECT * from _station"; String filename = "fiber segment";//file name, without path, without. xls suffix//string[] coloumitems = {"Fibre segment ID", "Fiber segment name", "Start", "End", "owner", "owner" phone number};// String sql = "SELECT ID, Name, ("//+ "select name from _station s where s.id = f.startpoint_id), ("//+ "select name from _station s where s.id=f.endpoint_id), ("//+" select name from _repairer R where r.id=f.repairer_id), ("//+" Select Phon ENum from _repairer R where r.id=f.repairer_id) '//+ ' from _fiberline F; /string filename = "Operation record";//file name, without path, without. xls suffix//string[] coloumitems = {"Action record ID", "Operation Time", "Action object", "Action type", "Action content"};//string sql = "SELECT ID, Operation_date, object, type, content from _operation;";  String filename = "owner";//filename, without path, without. xls suffix//string[] coloumitems = {"Owner id", "name", "Phone number"};//string sql = "SELECT ID, Name, phonenum from _repairer; "; /string filename = "Historical fault information statistics";//file name, without path, without. xls suffix//string[] coloumitems = {"Statistic id", "Fiber segment name", "Number of failures", "Total failures"};//string sq L = "SELECT ID, Fiberline_name, errortimes, totalduration from _statistic;"; /string filename = "website user";//file name, without path, without. xls suffix//string[] coloumitems = {"User id", "User name", "User Role", "real name"};//string sql = "s Elect ID, user_name, role_id, realname from user; "; String filename = "fault record";//file name, without path, without. xls suffix string[] coloumitems = {"Fault record ID", "Fiber segment name", "Fault longitude", "Fault latitude", "Fault Time", "Repair Time", " Fault duration "," whether repaired "}; String sql = "SELECT ID, Fiberline_name, Error_lon, Error_lat, Error_date, Recover_date, duration, fixstatus from _errorre Cord; ";          Resultsettoexcel.writeexcel (FileName, Coloumitems, SQL); }}

Functional Encapsulation Section:

First, export the Excel function encapsulation

Package Com.otdrmsys.util;import Java.io.file;import Java.io.filenotfoundexception;import Java.io.FileOutputStream ; Import Java.io.ioexception;import Java.sql.connection;import java.sql.preparedstatement;import Java.sql.ResultSet ; Import Java.sql.sqlexception;import Org.apache.poi.hssf.usermodel.hssfcell;import Org.apache.poi.hssf.usermodel.hssfrow;import Org.apache.poi.hssf.usermodel.hssfsheet;import org.apache.poi.hssf.usermodel.hssfworkbook;/*** * Export data from database to Excel Description: This class is implemented in Apache POI component * User provided to Writeexcel method file name only , column names, and a SQL query statement can export data to an Excel file * */public class Resultsettoexcel {/** * Write Excel Operation * * @param filename * filename, but does not need To suffix * @param coloumitems * Field name, which is the name of each column in the table * @param SQL * Database query statement */public static String Backuppath = "d:/Website Data backup directory _ do not delete/";p ublic static void Writeexcel (String fileName, string[] coloumitems,string sql) {File BackupFolder = n EW File (backuppath), if (!backupfolder.exists ()) {Backupfolder.mkdir ();} Connection conn = Db.createconn (); PreparedstAtement PS = db.prepare (conn, SQL); ResultSet rs = null;try {rs = ps.executequery (sql);} catch (SQLException e) {e.printstacktrace ();} FileOutputStream FileOutputStream = null;try {fileoutputstream = new FileOutputStream (backuppath + fileName+ ". xls"); Hssfworkbook workbook = new Hssfworkbook (); Hssfsheet sheet = workbook.createsheet () createtag (coloumitems, sheet);//write table column name CreateValue (RS, sheet);//Get Data set, Then get the data, write the file Workbook.write (FileOutputStream); Fileoutputstream.close ();} catch (FileNotFoundException e) {e.printstacktrace ();} catch (IOException e) {e.printstacktrace ();} finally {if ( FileOutputStream! = null) {try {fileoutputstream.close ();} catch (IOException e) {e.printstacktrace ()}}} Db.close (PS);D b.close (conn);} /** * CREATE TABLE header * * @param tags * @param s */private static void Createtag (string[] tags, hssfsheet s) {Hssfrow row = S.crea Terow (0); Hssfcell cell = null;for (int i = 0; i < tags.length; i++) {cell = Row.createcell (i); Cell.setcellvalue (Tags[i]);}} /** * Set Table contents * * @param res * @param s */private static void CreateValue (Java.sql.ResultSet res, hssfsheet s) {try {int flag = 1;int Count = Res.getmet AData (). getColumnCount (); Hssfrow row = null; Hssfcell cell = Null;while (Res.next ()) {row = S.createrow (flag); for (int i = 1; I <= count; i++) {cell = Row.createcel L (i-1); Object obj = Res.getobject (i); Cell.setcellvalue (obj + "");} flag++;}} catch (SQLException e) {e.printstacktrace ();}}}

Second, the database operation package

Package Com.otdrmsys.util;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.sql.sqlexception;import java.sql.Statement; public class DB {public static Connection Createconn () {Connection conn = null;try {class.forname ("Com.mysql.jdbc.Driver" ); conn = Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/mydatabasename", "root", "123456");} catch (ClassNotFoundException e) {e.printstacktrace ();} catch (SQLException e) {e.printstacktrace ();} Return conn;} public static PreparedStatement prepare (Connection conn, String sql) {PreparedStatement PS = null;try {PS = conn.preparest Atement (SQL);} catch (SQLException e) {e.printstacktrace ();} return PS;} public static void Close (Connection conn) {try {conn.close (); conn = null;} catch (SQLException e) {e.printstacktrace ();}} public static void Close (Statement stmt) {try {stmt.close (); stmt = null;} catch (SQLException e) {e.printstacktrace ();}} public static void Close (ResulTset Rs) {try {rs.close (); rs = null;} catch (SQLException e) {e.printstacktrace ();}}}