excelexportimpl.java

这是一个用java三层框架做的ISS学员管理系统

package org.pontifex.commons.exchange;

import org.apache.poi.hssf.usermodel.*;
import org.pontifex.web.mapping.PageInfo;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
import org.springframework.beans.BeansException;


import javax.servlet.http.HttpServletResponse;
import java.util.*;
import java.io.*;
import java.math.BigDecimal;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;


/**
 * excel导出的实现类
 * Created by IntelliJ IDEA.
 * User: Songzou
 * Date: 2007-05-10
 * Time: 11:57:43
 */
public class ExcelExportImpl implements ExcelExport {
  private Map container = null;
  private HSSFCellStyle dataStyle = null;
  private HSSFWorkbook workbook = null;
  private final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  private final DecimalFormat doubleFormat = new DecimalFormat("####.0000");
  private final DecimalFormat longFormat = new DecimalFormat("####");
  private HttpServletResponse response;

  public ExcelExportImpl() {
    init(null);
  }

  public ExcelExportImpl(HttpServletResponse response) {
    init(response);
  }

  public void init(HttpServletResponse response) {
    container = new HashMap();
    workbook = new HSSFWorkbook();
    dataStyle = workbook.createCellStyle();
    HSSFDataFormat format=workbook.createDataFormat();
    dataStyle.setDataFormat(format.getFormat("yyyy-mm-dd hh:mm:ss"));
    this.response=response;
  }

  public void setSheet(String sheetName, List resultList) throws ExcelExportException {
    if (container.containsKey(sheetName)) {
      throw new ExcelExportException("\"" + sheetName + "\"名称已经设置，工作表名称不能重复！");
    }
    if (resultList != null && resultList.size() > 65535) {
      throw new ExcelExportException("\"" + sheetName + "\"工作表的的记录集超过了excel工作表存储的最大行数！");
    }
    workbook.createSheet(sheetName);
    container.put(sheetName,resultList);
  }

  public void setSheet(String sheetName) throws ExcelExportException{
    if (container.containsKey(sheetName)) {
      throw new ExcelExportException("\"" + sheetName + "\"名称已经设置，工作表名称不能重复！");
    }
    workbook.createSheet(sheetName);
  }

  private void fillSheetWithMapping(HSSFSheet sheet, Class objectClass ,List resultList) {
    List filedList = new ArrayList();
    HSSFRow row = sheet.createRow(0);
    Field field = null;
    HSSFCell cell = null;
    String fieldName=null;
    BeanWrapper beanWrapper=new BeanWrapperImpl(objectClass);
    while (!objectClass.getName().equals(PageInfo.class.getName())) {
      int len = objectClass.getDeclaredFields().length;
      for (int i = 0; i < len; i++) {
        field = objectClass.getDeclaredFields()[i];
        fieldName = field.getName();
        if(beanWrapper.isWritableProperty(fieldName)){
          cell = row.createCell((short)filedList.size());
          setCellValue(cell, fieldName);
          filedList.add(fieldName);
        }
      }
      objectClass = objectClass.getSuperclass();
    }
    Object value= null;
    Object object=null;
    String filedName=null;
    for(int i=0;i<resultList.size();i++){
      object = resultList.get(i);
      row=sheet.createRow(i+1);
      for(short j=0;j<filedList.size();j++){
        cell = row.createCell(j);
        filedName=(String) filedList.get(j);
        beanWrapper=new BeanWrapperImpl(object);
        value=beanWrapper.getPropertyValue(filedName);
        setCellValue(cell, value);
      }
    }
  }

  private void fillSheetWithMap(HSSFSheet sheet, Set keySet ,List resultList){
    List filedList =new ArrayList();
    HSSFRow row = sheet.createRow(0);
    Iterator iterator=keySet.iterator();
    HSSFCell cell=null;
    String key=null;
    while(iterator.hasNext()){
      key =  (String)iterator.next();
      cell = row.createCell((short)filedList.size());
      setCellValue(cell, key);
      filedList.add(key);
    }
    Object value= null;
    Map map=null;
    for(int i=0;i<resultList.size();i++){
      map=(Map)resultList.get(i);
      row=sheet.createRow(i+1);
      for(short j=0;j<filedList.size();j++){
        cell = row.createCell(j);
        key=(String) filedList.get(j);
        value=map.get(key);
        setCellValue(cell, value);
      }
    }
  }

  private void fillSheet(){
    Iterator iterator= container.keySet().iterator();
    List resultList = null;
    HSSFSheet sheet = null;
    while (iterator.hasNext()) {
      String key =  (String)iterator.next();
      resultList = (List)container.get(key);
      if(resultList!=null&&resultList.size()>0){
        Object object = resultList.get(0);
        sheet = workbook.getSheet(key);
        if (object instanceof Map) {
          fillSheetWithMap(sheet,((Map)object).keySet(),resultList);
        }
        else if(object instanceof PageInfo){
          fillSheetWithMapping(sheet,object.getClass(),resultList);
        }
      }
    }
  }

  public void exportWorkbook(String fileName, HttpServletResponse response) throws IOException, UnsupportedEncodingException {
    fillSheet();
    response.reset();
    response.setContentType("application/vnd.ms-excel;charset=GBK");
    String downloadName = URLEncoder.encode(fileName+".xls", "utf-8");
    response.setHeader("Content-Disposition", "attachment;filename=\""+downloadName+"\"");
    OutputStream ouputStream=null;
    try {
      ouputStream = response.getOutputStream();
      workbook.write(ouputStream);
      ouputStream.flush();
    }
    catch (IOException e) {
      System.out.println(e.getMessage());
      throw e;
    }
    finally {
      if (ouputStream != null) ouputStream.close();
    }
  }

  public void exportWorkbook(String fileName) throws IOException, UnsupportedEncodingException {
    exportWorkbook(fileName, this.response);
  }

  public void setCellValue(String sheetName, int rowIndex, int colIndex, Object value) throws ExcelExportException {
    HSSFSheet sheet = workbook.getSheet(sheetName);
    HSSFRow row = null;
    HSSFCell cell = null;
    if(sheet==null){
      throw new ExcelExportException("没有找到名称为\""+sheetName+"\"的工作表！");
    }
    row = sheet.getRow(rowIndex);
    if(row==null) {
      row = sheet.createRow(rowIndex);
    }
    cell = row.getCell((short)colIndex);
    if(cell==null){
      cell = row.createCell((short)colIndex);
    }
    setCellValue(cell,value);
  }

  private void setCellValue(HSSFCell cell, Object value){
    if(value!=null){
      String valueType = value.getClass().getName();
      cell.setCellType(HSSFCell.CELL_TYPE_STRING);
      if(valueType.equals("java.lang.String")){
        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue((String)value);
      }
      else if(valueType.equals("java.lang.Integer")){
        cell.setCellValue(longFormat.format(value));
      }
      else if(valueType.equals("java.lang.Long")){
        cell.setCellValue(longFormat.format(value));
      }
      else if(valueType.equals("java.lang.Float")){
        cell.setCellValue(doubleFormat.format(value));
      }
      else if(valueType.equals("java.lang.Double")){
        cell.setCellValue(doubleFormat.format(value));
      }
      else if(valueType.equals("java.math.BigDecimal")){
        cell.setCellValue(doubleFormat.format(value));
      }
      else if(valueType.equals("java.util.Date")){
        cell.setCellValue(dateFormat.format(value));
      }
      else if(valueType.equals("java.sql.Date")){
        cell.setCellValue(dateFormat.format(value));
      }
      else if(valueType.equals("java.lang.Boolean")){
        cell.setCellValue(value.toString());
      }
      else if(valueType.equals("java.lang.Byte")){
        cell.setCellValue(value.toString());
      }
      else if(valueType.equals("java.lang.Short")){
        cell.setCellValue(value.toString());
      }
    }
  }
  
  /**
   * 获得工作薄
   * @return HSSFWorkbook
   */
  public HSSFWorkbook getWorkbook() {
    return this.workbook;
  }
  /**
   * 获得指定名称的工作表
   * @param sheetName
   * @return HSSFSheet
   */
  public HSSFSheet getSheet(String sheetName) {
    return workbook.getSheet(sheetName);
  }
  /**
   * 获得指定索引的工作表
   * @param sheetIndex
   * @return HSSFSheet
   */
  public HSSFSheet getSheet(int sheetIndex) {
    return workbook.getSheetAt(sheetIndex);
  }

}