Java exports data from the database to Excel for data backup

Source: Internet
Author: User

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



Related Article

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.