POI导出报表 ExcelUtil

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;

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.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.awt.Font;
import java.beans.*;

//import com.yxhc.util.dbUtil.ConnectionUtil ;  

/**
 * 23. *
 * 
 * @author Great nomandia 24.
 */
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 List cColumns=null;
 //每页显示行数
 private static int rpp = 5000;

 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());
  }
 }
 
 
 public static void export(HttpServletResponse response, List list,
    String sheetName, Class cls,Map columnNames,
   int rowPerPage)  {

  if (rowPerPage <= 10000 && rowPerPage >= 1) {
   rpp = rowPerPage;
  }
  if (!"".equals(sheetName) && null != sheetName) {
   shtName = sheetName;
  } else {
   shtName = "sheet";
  }
  

  HSSFWorkbook wb = new HSSFWorkbook();
  style = wb.createCellStyle();
  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

  HSSFSheet sheet = createSheet(wb, 1);
  
  int rowCnt = 0;
  int sheetNum = 2;
  Iterator it=list.iterator();
  Iterator columnNamesKey= columnNames.keySet().iterator();
  cLabels=new String[columnNames.size()];
  cNames=new String[columnNames.size()];
  int i=0;
  while(columnNamesKey.hasNext()){
   String columnName=String.valueOf(columnNamesKey.next());
   System.out.println("columnName="+columnName);
   cNames[i]=columnName;
   cLabels[i]=(String)columnNames.get(columnName);
   i++;
  }
  setSheetColumnTitle(sheet);
  while (it.hasNext()) {
   if (rowCnt == rpp) {
    sheet = createSheet(wb, sheetNum);
    setSheetColumnTitle(sheet);
    rowCnt = 0;
    sheetNum++;
   }
   HSSFRow row = sheet.createRow(rowCnt + 1);
   Object entity=it.next();
    
   for(int j=0;j<cNames.length;j++) {
    //String propertyName=(String)columnNamesKey.next();
    HSSFCell cell = row.createCell((short) j);
    cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    String val = (String)getPropertyValue(entity,cNames[j]);
    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);
    
  } catch (IOException ex) {
   log.info("Export Excel file error ! " + ex.getMessage());
  }
 }
 
 
// 3
 public static void export(HttpServletResponse response, List list,
   String fileName, String sheetName, String[] columnNames, Map columnLabels,
   int rowPerPage)  {

  if (rowPerPage <= 10000 && rowPerPage >= 1) {
   rpp = rowPerPage;
  }
  if (!"".equals(sheetName) && null != sheetName) {
   shtName = sheetName;
  } else {
   shtName = "sheet";
  }
   
  cNames=columnNames;
  cLabels=new String[columnNames.length];
  
  for(int i=0;i< columnNames.length;i++){
    
   cLabels[i]=String.valueOf(columnLabels.get(columnNames[i]));
    
  }
  
  HSSFWorkbook wb = new HSSFWorkbook();
  style = wb.createCellStyle();
  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

  HSSFSheet sheet = createSheet(wb, 1);
  
  int rowCnt = 0;
  int sheetNum = 2;
  Iterator it=list.iterator();
  //Iterator columnNamesKey= columnNames.keySet().iterator();
  
  setSheetColumnTitle(sheet);
  while (it.hasNext()) {
   if (rowCnt == rpp) {
    sheet = createSheet(wb, sheetNum);
    setSheetColumnTitle(sheet);
    rowCnt = 0;
    sheetNum++;
   }
   HSSFRow row = sheet.createRow(rowCnt + 1);
   Map entity=(Map)it.next();
   for(int j=0;j<cNames.length;j++) {
    //String propertyName=(String)columnNamesKey.next();
    HSSFCell cell = row.createCell((short) j);
    cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    String val = (String)entity.get(cNames[j]);
    if (null == val) {
     val = "";
    }
    cell.setCellValue(val);
   }
   rowCnt++;
  }
  try {
   OutputStream os = response.getOutputStream();
   response.reset();
   response.setContentType("application/vnd.ms-excel");
   response.setHeader("Content-disposition", "attachment; filename="
     + getFileName(fileName));
   wb.write(os);
    
  } catch (IOException ex) {
   log.info("Export Excel file error ! " + ex.getMessage());
  }
 }
 

 /**4
  * @param response
  *            HttpServletResponse Response
  * @param list 数据集
  * @param 
  *      String fileName 导出的Excel 文件名称
  * @param sheetName
  *            String 导出的Excel Sheet名称
  * @param columns
  *            List<Column> 导出的 Excel列
  * @param rowPerPage
  *            int 每一个Sheet页的行数
  */
 public static void export(HttpServletResponse response, List list,
   String fileName, String sheetName, List columns, int rowPerPage)  {

  if (rowPerPage <= 10000 && rowPerPage >= 1) {
   rpp = rowPerPage;
  }
  if (!"".equals(sheetName) && null != sheetName) {
   shtName = sheetName;
  } else {
   shtName = "sheet";
  }
  cColumns=columns;
  cNames=new String[columns.size()];
  cLabels=new String[columns.size()];
  for(int i=0;i<cColumns.size();i++){
   Column column=(Column)columns.get(i);
   cNames[i]=column.getName();
   cLabels[i]=column.getLabel();
  }
  HSSFWorkbook wb = new HSSFWorkbook();
  style = wb.createCellStyle();
  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

  HSSFSheet sheet = createSheet(wb, 1,list.size());
  
  int rowCnt = 0;
  int sheetNum = 2;
  Iterator it=list.iterator();
  //Iterator columnNamesKey= columnNames.keySet().iterator();
  setSheetColumnTitle2(sheet);
  while (it.hasNext()) {
   if (rowCnt == rpp) {
    sheet = createSheet(wb, sheetNum,list.size());
    setSheetColumnTitle2(sheet);
    rowCnt = 0;
    sheetNum++;
   }
   HSSFRow row = sheet.createRow(rowCnt + 1);
   Map entity=(Map)it.next();
   for(int j=0;j<cNames.length;j++) {
    //String propertyName=(String)columnNamesKey.next();
    HSSFCell cell = row.createCell((short) j);
    cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    String val = (String)entity.get(cNames[j]);
    if (null == val) {
     val = "";
    }
    cell.setCellValue(val);
   }
   rowCnt++;
  }
  try {
   OutputStream os = response.getOutputStream();
   response.reset();
   response.setContentType("application/vnd.ms-excel");
   response.setHeader("Content-disposition", "attachment; filename="
     + getFileName(fileName));
   wb.write(os);
    
  } catch (IOException ex) {
   log.info("Export Excel file error ! " + ex.getMessage());
  }
 }
  
 /**
  * 设置Sheet页的列属性 123. *
  * 
  * @param sht
  *            HSSFSheet 124.
  */
 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_UTF_16);
   cell.setCellValue(cLabels[i]);
   cell.setCellStyle(style);
   //sht.autoSizeColumn(i,true);
   
  }
 }
 private static void setSheetColumnTitle2(HSSFSheet sht) {
  
  HSSFRow row = sht.createRow(0);
  for (int i = 0; i < cColumns.size(); i++) {
   Column column=(Column)cColumns.get(i);
   HSSFCell cell = row.createCell((short) (i));
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell.setCellValue(cLabels[i]);
   cell.setCellStyle(style);
   //sht.autoSizeColumn(i,true);
   sht.setColumnWidth((short) i,  column.getWidth());
   
  }
 }

 /**
  * 获得源数据中的列名称
  * 
  * @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;
  }
  HSSFSheet sheet=wb.createSheet( );
  shtName=shtName + "(" + sub + "-" + sup + ")";
   
  wb.setSheetName(seq-1,shtName,HSSFWorkbook.ENCODING_UTF_16);   
   
  return sheet;
 }
 
 private static HSSFSheet createSheet(HSSFWorkbook wb, int seq,int totalSize) {
  
  int sup = seq * rpp < totalSize ? seq * rpp : totalSize;
  int sub = (seq - 1) * rpp + 1;
  if (sub < 1) {
   sub = 1;
  }
  HSSFSheet sheet=wb.createSheet( );
   
  wb.setSheetName(seq-1,shtName + "(" + sub + "-" + sup + ")",HSSFWorkbook.ENCODING_UTF_16);   
   
  return sheet;
 }

 /**
  * 获得导出的文件全名
  * 
  * @param tableName
  *            String
  * @return String
  */
 private static String getFileName(String fileName) {
   fileName+= new Date().getTime() + ".xls";
  try {
   return URLEncoder.encode(fileName, "UTF-8");
  } catch (UnsupportedEncodingException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return null;
   
 }
 
 public static Object getPropertyValue(Object entity,String propertyName){
  Object result=null;
  if(methods==null){
   try {
    methods=dumMethod(entity.getClass());
   } catch (IntrospectionException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
  Method method=(Method)methods.get(propertyName);
  try {
     result= method.invoke(entity, null);
  } catch (IllegalArgumentException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IllegalAccessException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (InvocationTargetException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return result;
  
 }
 
 public static Map dumMethod(Class cls) throws IntrospectionException{
  BeanInfo bean=Introspector.getBeanInfo(cls ,cls.getSuperclass());
  PropertyDescriptor[] propertyDescriptors= bean.getPropertyDescriptors();
  Map map=new HashMap();
  for(int i=0;i<propertyDescriptors.length;i++){
   PropertyDescriptor pd=propertyDescriptors[i];
   System.out.println("属性名="+pd.getName());
   map.put(pd.getName(), pd.getReadMethod());
  }
  return map;
  
 }
 
 static Map methods=null;
 
 
  
 

}

Column.java

package com.sinovatech.bms.common.excel;

public class Column {
 
 private String name;
 private String label;
 private short width=5000;
 
 public Column(){}
 
 public Column(String name,String label,short width){
  this.name=name;
  this.label=label;
  this.width=width;
 }
 public Column(String name,String label ){
  this.name=name;
  this.label=label;
  this.width=5000;
 }
 public String getLabel() {
  return label;
 }
 public void setLabel(String label) {
  this.label = label;
 }
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 public short getWidth() {
  return width;
 }
 public void setWidth(short width) {
  this.width = width;
 }

}