Poi3.9 excel export tool, poi3.9excel
1. first look at the generated file
2. Preparation
This document 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, others are the poi jar packages required for exporting excel files.
Note: This article is written in poi3.9 and uses the Workbook SXSSFWorkbook, which can be written in batches to prevent memory overflow. This class is available only in versions 3.8 and 3.8.
3. Write a Database Connection Tool
It is easy to write. It uses 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 "; private static String url =" jdbc: oracle: thin: @ 127.0.0.1: 1521: xe "; private static String user =" test "; private static String password = "test"; public 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 tools
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. wor Kbook; import org. apache. poi. ss. util. cellRangeAddress; import org. apache. poi. xssf. streaming. SXSSFWorkbook; public class POIExport {/*** generate a column name string in Workbook * @ param SQL data query SQL * @ param columns SQL based on the input parameters, separated by commas, case-insensitive * @ param headers: A String Array of headers. If multiple headers exist, write "null" in the place where they need to be merged, such as test1, test2, null, null, and test3. * This indicates that test2 occupies three columns. If there is no null under test2, there are n null columns in one row, 3 columns in n rows * @ param splitStr delimiter used to separate the header String * @ return */public static Workbook export (String SQL, String columns, String [] headers, String splitStr) {SXSSFWorkbook wb = new SXSSFWorkbook (1000); // create an excel document and keep 1000 pieces of data in the memory to avoid memory overflow Font font = wb. createFont (); // font. setBoldweight (Font. BOLDWEIGHT_BOLD); // bold CellStyle cellStylehead = wb. createCellStyle (); // Header style cellStylehead. setFont (fo Nt); // set the font style cellStylehead. setAlignment (CellStyle. ALIGN_CENTER); // horizontally aligned cellStylehead. setverticalignment (CellStyle. VERTICAL_CENTER); // vertically aligned cellStylehead. setWrapText (true); // wrap automatically // set the border cellStylehead. setBorderTop (CellStyle. BORDER_THIN); cellStylehead. setBorderRight (CellStyle. BORDER_THIN); cellStylehead. setBorderBottom (CellStyle. BORDER_THIN); cellStylehead. setBorderLeft (CellStyle. BORDER_THIN); // table body style CellSty Le cellStyleBody = wb. createCellStyle (); // table body cell style cellStyleBody. setAlignment (CellStyle. ALIGN_LEFT); // horizontally aligned cellStyleBody. setverticalignment (CellStyle. VERTICAL_CENTER); // vertically aligned cellStyleBody. setWrapText (true); // wrap automatically // set the border cellStyleBody. setBorderTop (CellStyle. BORDER_THIN); cellStyleBody. setBorderRight (CellStyle. BORDER_THIN); cellStyleBody. setBorderBottom (CellStyle. BORDER_THIN); cellStyleBody. setBorderLeft (C EllStyle. BORDER_THIN); Sheet sheet = wb. createSheet ("sheet1"); // create a sheetsheet. setdefacolumcolumnwidth (15); // set the default column width // write the header createHeader (wb, sheet, cellStylehead, headers, splitStr); // write the table body int beginRowNumber = headers. length; // start line of the table body String [] cols = columns. split (","); // split the SQL column name int cellSize = cols. length; // Number of columns Connection conn = null; Statement stmt = null; ResultSet rs = null; conn = DBUtil. getConnection (); try {stmt = Conn. createStatement (); rs = stmt.exe cuteQuery (SQL); int count = 0; // record row number while (rs. next () {Row row = sheet. createRow (count + beginRowNumber); row. setHeightInPoints (14); // set the row Height for (int j = 0; j <cellSize; j ++) {Cell cell = row. createCell (j); Object obj = rs. getObject (cols [j]); String cv = obj = null? "": Obj. toString (); // obtain the cell value in the corresponding column. setCellValue (cv); // set the cell value. setCellStyle (cellStyleBody); // set the style} count ++;} System. out. println ("data written in total:" + count + "items");} 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 an excel header * @ param wb excel workbook * @ param sheet excel sheet * @ param cellStylehead excel Style * @ param headers header string array * @ param splitStr header Header string delimiter */private static void createHeader (SXSSFWorkbook wb, sheet sheet, CellStyle cellStylehead, String [] headers, String splitStr) {// traverse the created cell for (int I = 0; I 5. criticize and correct any bugs.
I packed the source code. If you don't want to waste 2 points, you can directly copy the above Code. If you do not want to copy, click here to download:
Http://download.csdn.net/detail/yunsyz/8345949
Attached file directory: