Poi的一个实例——用SQL动态导出Excel文件
很久以前。。。自己写的一个通过SQL语句导出Excel文件下载的程序，其中用到了POI库。程序并不完整比如需要传入一个Connection，总的来说还是比较值得回忆的吧。http://www.agoit.com/images/smiles/icon_arrow.gif
By the way，我并没有写测试的main函数。http://www.agoit.com/images/smiles/icon_biggrin.gif
package com.yxhc.util.execl ;import java.io.IOException ;import java.io.OutputStream ;import java.sql.Connection ;import java.sql.PreparedStatement ;import java.sql.ResultSet ;import java.sql.ResultSetMetaData ;import java.sql.SQLException ;import java.util.Date ;import javax.servlet.http.HttpServletResponse ;import org.apache.commons.logging.Log ;import org.apache.commons.logging.LogFactory ;import org.apache.poi.hssf.usermodel.HSSFCell ;import org.apache.poi.hssf.usermodel.HSSFCellStyle ;import org.apache.poi.hssf.usermodel.HSSFRow ;import org.apache.poi.hssf.usermodel.HSSFSheet ;import org.apache.poi.hssf.usermodel.HSSFWorkbook ;import com.yxhc.util.dbUtil.ConnectionUtil ;/** * @author Great nomandia */public class ExcelUtil { private static Log log = LogFactory.getLog( ExcelUtil.class ) ; private static String shtName = "" ; private static String[] cNames = null ; private static String[] cLabels = null ; private static int rpp = 200 ; private static HSSFCellStyle style = null ; /** * 通过给定的Sql导出Excel文件到Response输出流，需要指定Connection * @param response HttpServletResponse Response * @param conn Connection 指定的数据库连接 * @param sqlStr String 查询的Sql语句 * @param sheetName String 导出的Excel Sheet名称 * @param columnNames String[] 导出的 Excel 列名称 * @param rowPerPage int 每一个Sheet页的行数 * @throws SQLException */ public static void export( HttpServletResponse response , Connection conn , String sqlStr , String sheetName , String columnNames[] , int rowPerPage ) throws SQLException { PreparedStatement ps = null ; ResultSet rs = null ; ps = conn.prepareStatement( sqlStr ) ; rs = ps.executeQuery() ; ResultSetMetaData rsmd = rs.getMetaData() ; if ( rowPerPage <= 10000 && rowPerPage >= 1 ) { rpp = rowPerPage ; } if ( !"".equals( sheetName ) && null != sheetName ) { shtName = sheetName ; } else { shtName = rsmd.getTableName( 0 ) ; } cNames = getColumnNames( rsmd ) ; if ( null != columnNames ) { cLabels = columnNames ; //compare( columnNames ) ; } else { cLabels = cNames ; } HSSFWorkbook wb = new HSSFWorkbook() ; style = wb.createCellStyle() ; style.setAlignment( HSSFCellStyle.ALIGN_CENTER ) ; HSSFSheet sheet = createSheet( wb , 1 ) ; setSheetColumnTitle( sheet ) ; int rowCnt = 0 ; int sheetNum = 2 ; while ( rs.next() ) { if ( rowCnt == rpp ) { sheet = createSheet( wb , sheetNum ) ; setSheetColumnTitle( sheet ) ; rowCnt = 0 ; sheetNum++ ; } HSSFRow row = sheet.createRow( rowCnt + 1 ) ; for ( int i = 0 ; i < cNames.length ; i++ ) { HSSFCell cell = row.createCell( ( short ) i ) ; cell.setEncoding( HSSFCell.ENCODING_UTF_16 ) ; String val = rs.getString( cNames[ i ] ) ; if ( null == val ) { val = "" ; } cell.setCellValue( val.toUpperCase() ) ; } rowCnt++ ; } try { OutputStream os = response.getOutputStream() ; response.reset() ; response.setContentType( "application/vnd.ms-excel" ) ; response.setHeader( "Content-disposition" , "attachment; filename=" + getFileName( shtName ) ) ; wb.write( os ) ; if ( conn != null ) { conn.close() ; } } catch ( IOException ex ) { log.info( "Export Excel file error ! " + ex.getMessage() ) ; } } /** * 设置Sheet页的列属性 * @param sht HSSFSheet */ private static void setSheetColumnTitle( HSSFSheet sht ) { HSSFRow row = sht.createRow( 0 ) ; for ( int i = 0 ; i < cLabels.length ; i++ ) { HSSFCell cell = row.createCell( ( short ) ( i ) ) ; cell.setEncoding( HSSFCell.ENCODING_COMPRESSED_UNICODE) ; cell.setCellValue( cLabels[ i ] ) ; cell.setCellStyle( style ) ; } } /** * 获得源数据中的列名称 * @param rsmd ResultSetMetaData * @return String[] */ private static String[] getColumnNames( ResultSetMetaData rsmd ) { try { StringBuffer result = new StringBuffer("") ; for ( int i = 1 ; i <= rsmd.getColumnCount() ; i++ ) { result.append(rsmd.getColumnLabel( i )).append(","); } if ( result.length()>0 ) { return result.substring( 0 , result.length() - 1 ).toString().split( "," ) ; } } catch ( Exception e ) { return null ; } return null ; } /** * 创建一个Sheet页并返回该对象 * @param wb HSSFWorkbook * @param seq int * @return HSSFSheet */ private static HSSFSheet createSheet( HSSFWorkbook wb , int seq ) { int sup = seq * rpp ; int sub = ( seq - 1 ) * rpp + 1 ; if ( sub < 1 ) { sub = 1 ; } return wb.createSheet( shtName + "(" + sub + "-" + sup + ")" ) ; } /** * 获得导出的文件全名 * @param tableName String * @return String */ private static String getFileName( String tableName ) { return tableName + new Date().getTime() + ".xls" ; }}
页:
[1]