Excel export tool made with poi3.9

First, take a look at the generated files

Second, prepare

This article requires six jar packages:

Dom4j-1.6.1.jar
Ojdbc14.jar
Poi-3.9-20121203.jar
Poi-ooxml-3.9-20121203.jar
Poi-ooxml-schemas-3.9-20121203.jar
Xmlbeans-2.3.0.jar

Except that Ojdbc14.jar is used to access the database, the other is the POI-related jar package that is needed to export Excel.

Note: This article is written in poi3.9 version, the use of Sxssfworkbook this workbook, this can be written in batches to prevent memory overflow. This class is only available in versions 3.8 and 3.8.

Third, write a database connection tool

It's easy to write with an Oracle database

The code is as follows:

Package Com.utils;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.sqlexception;public Class Dbutil {private static string driver= "Oracle.jdbc.driver.OracleDriver";p rivate static string Url= "Jdbc:oracle: Thin:@127.0.0.1:1521:xe ";p rivate static string user=" test ";p rivate static string password=" test ";p ublic static Connection getconnection () {Connection conn = null;try {class.forname (driver); conn = drivermanager.getconnection (URL, User,password);} catch (ClassNotFoundException e) {e.printstacktrace ();} catch (SQLException e) {e.printstacktrace ();} Return conn;} public static void Main (string[] args) {System.out.println (dbutil.getconnection ());//Test Connection}}

Iv. Writing and exporting tool classes

The code is as follows:

Package Com.utils;import Java.io.fileoutputstream;import Java.sql.connection;import java.sql.resultset;import Java.sql.sqlexception;import Java.sql.statement;import Org.apache.poi.ss.usermodel.cell;import Org.apache.poi.ss.usermodel.cellstyle;import Org.apache.poi.ss.usermodel.font;import Org.apache.poi.ss.usermodel.row;import Org.apache.poi.ss.usermodel.sheet;import Org.apache.poi.ss.usermodel.workbook;import Org.apache.poi.ss.util.cellrangeaddress;import Org.apache.poi.xssf.streaming.sxssfworkbook;public class Poiexport {/** * generates a workbook * @param SQL query data based on the parameters passed in * @p Aram columns SQL column name string, comma-delimited, case-insensitive * @param headers header string array, if it is a multi-header, write "null" where required to merge, such as: Test1,test2,null,null,test3. * This means that Test2 will account for three columns. If there is no null below test2, it is a row of three columns, with n null, and a row of n rows of 3 columns * @param the splitstr of the split header string * @return */public static Workbook export (String sql, String columns,string[] headers, string splitstr) {Sxssfworkbook wb = new Sxssfworkbook (1000);//Create an Excel document, keep 1000 data in memory, Avoid memory overflow font font = Wb.createfont ();//Font font.seTboldweight (Font.boldweight_bold),//bold CellStyle Cellstylehead = Wb.createcellstyle (),//Header style Cellstylehead.setfont ( fonts);//Set Font style cellstylehead.setalignment (cellstyle.align_center);//Horizontal Alignment cellstylehead.setverticalalignment ( Cellstyle.vertical_center);//Vertical alignment Cellstylehead.setwraptext (true);//Wrap Line//Set Border Cellstylehead.setbordertop ( Cellstyle.border_thin); Cellstylehead.setborderright (Cellstyle.border_thin); Cellstylehead.setborderbottom ( Cellstyle.border_thin); Cellstylehead.setborderleft (Cellstyle.border_thin);//table body style CellStyle CellStyleBody = Wb.createcellstyle ();//table body cell style cellstylebody.setalignment (cellstyle.align_left);// Horizontal alignment cellstylebody.setverticalalignment (cellstyle.vertical_center);//Vertical alignment Cellstylebody.setwraptext (true);//Wrap Line Set Border Cellstylebody.setbordertop (Cellstyle.border_thin); Cellstylebody.setborderright (CellStyle.BORDER_THIN); Cellstylebody.setborderbottom (Cellstyle.border_thin); Cellstylebody.setborderleft (CellStyle.BORDER_THIN); Sheet Sheet = Wb.createsheet ("Sheet1");//Create a Sheetsheet.setdefaUltcolumnwidth (15);//Set default column width//write table header CreateHeader (WB,SHEET,CELLSTYLEHEAD,HEADERS,SPLITSTR);//write table body int beginrownumber = headers.length;//table body start line string[] cols = Columns.split (",");//Shard SQL column name int cellsize = cols.length;//column number Connection conn = null ; Statement stmt = null; ResultSet rs = Null;conn = Dbutil.getconnection (); try {stmt = Conn.createstatement (); rs = stmt.executequery (sql); int Coun t = 0;//Record line number while (Rs.next ()) {Row row = Sheet.createrow (Count+beginrownumber); row.setheightinpoints (14);//Set row height for ( int j=0;j<cellsize;j++) {Cell cell = Row.createcell (j); Object obj = Rs.getobject (cols[j]); String CV = obj==null? "": O Bj.tostring ();//Get the value in the corresponding column cell.setcellvalue (CV);//Set the value of the cell Cell.setcellstyle (cellstylebody);//Set style}count++;} System.out.println ("Write Data:" +count+ "Bar");} catch (SQLException e) {e.printstacktrace ();} finally {try {if (rs!=null) {Rs.close ()}} catch (SQLException e) { E.printstacktrace ();} try {if (stmt!=null) {stmt.close ();}} catch (SQLException e) {e.printstacktrace ();} try {if (conn!=null) {conn.close ();}}catch (SQLException e) {e.printstacktrace ();}} return WB;} /** * Create Excel Header * @param wb Excel Workbook * @param sheet Excel sheet * @param cellstylehead Excel Style * @param headers header character String array * @param splitstr table header string cutter */private static void CreateHeader (Sxssfworkbook wb, Sheet sheet,cellstyle Cellstylehead, St Ring[] headers, String splitstr) {//Traverse create cell for (int i=0;iV. If there are any bugs, please criticize them.
I have packaged the source code, if you do not want to waste 2 points, you can directly copy the above codes. If you do not want to copy, click here to download:
http://download.csdn.net/detail/yunsyz/8345949

Attached file directory diagram:



Excel export tool made with poi3.9