Poi的一个实例——用SQL动态导出Excel文件

很久以前。。。自己写的一个通过SQL语句导出Excel文件下载的程序，其中用到了POI库。

程序并不完整比如需要传入一个Connection，总的来说还是比较值得回忆的吧。

By the way，我并没有写测试的main函数。

 

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" ;
    }
}