由于目前項目多個地方需要導出excel功能,  而之前別人寫的代碼可重用性很低, 重復代碼太多, 上周六閑著沒事, 就寫了一個通用的導出代碼. 只需要一個properties配置文件, 然后直接調用就可以了. 代碼如下:

package com.founder.cms.core.util;

import Java.io.Serializable;
import Java.lang.reflect.Field;

public class FieldTitle implements Serializable {

 private static final long serialVersionUID = 8641298907642008247L;
 
 private Field field; //對象屬性
 
 private String title; //excel中的列標題

 public FieldTitle(Field field, String title) {
  this.field = field;
  this.title = title;
 }

 public Field getField() {
  return field;
 }
 
 public void setField(Field field) {
  this.field = field;
 }
 
 public String getTitle() {
  return title;
 }
 
 public void setTitle(String title) {
  this.title = title;
 }
}
FieldTitle類用作記錄需要導出的列對應與java對象中的屬性和excel中的列標題信息

package com.founder.cms.core.util;

import Java.io.IOException;
import Java.io.InputStream;
import Java.lang.reflect.Field;
import Java.util.ArrayList;
import Java.util.List;
import Java.util.Properties;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

public class ExportConfigHelper {
 private static Logger logger = Logger.getLogger(ExportConfigHelper.class);
 
 private static final String CONFIGURATION_FILE_PREFIX = "export/";
 
 /**
  * 根據配置文件獲取需要導出的字段名以及列標題
  * @param clz
  * @param configFile
  * @return
  */
 public List<FieldTitle> getFieldTitles(Class clz, String configFile) {
  String url = CONFIGURATION_FILE_PREFIX + configFile + ".properties";
  List<FieldTitle> result = new ArrayList<FieldTitle>();
  InputStream is = ExportConfigHelper.class.getClassLoader().getResourceAsStream(url);
  if (is == null) {
   throw new RuntimeException("Cannot find Configuration file " + url);
  }
  
  Properties properties = new Properties();
  Field[] fields = clz.getDeclaredFields();
  try {
   properties.load(is);
   for (Field field : fields) {
    String title = properties.getProperty(field.getName());
    if (StringUtils.isNotEmpty(title)) {
     result.add(new FieldTitle(field, title));
    }
   }
  } catch (IOException e) {
   logger.error("Read configuration file " + url, e);
   throw new RuntimeException("Read configuration file " + url, e);
  }
  
  return result;
 }
 
 
}

ExportConfigHelper 類用作根據屬性文件獲取需要導出的字段名以及對應的列標題.

package com.founder.cms.core.util;

import Java.io.IOException;
import Java.io.OutputStream;
import Java.io.UnsupportedEncodingException;
import Java.text.SimpleDateFormat;
import Java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelUtil {

 private ExcelUtil() {}
 
 /**
     * 設置頭信息
     *
     * @param response HttpServletResponse
     * @param fileName 默認的文件名稱
     */
    public static void setExcelContentType(HttpServletResponse response, String fileName) {
        try {
            fileName = new String(fileName.getBytes("MS932"), "ISO-8859-1");
        } catch (UnsupportedEncodingException e) {
            // should no happen
        }
        response.reset();
        response.setContentType("application/msexcel;charset=MS932");
        response.setHeader("Content-disposition", "attachment;filename= " + fileName);
    }
 

    public static void write(HttpServletResponse response, List<Object> objects, Class clz, String propertiesFileName) {
     setExcelContentType(response, getFileName());
     ExportConfigHelper helper = new ExportConfigHelper();
     
     //根據properties文件獲取需要導出的字段名以及在excel中的標題名稱
  List<FieldTitle> result = helper.getFieldTitles(clz, propertiesFileName);
  
  WritableWorkbook wwbook =  null;
  OutputStream os = null;
  try {
   os = response.getOutputStream();
   wwbook = jxl.Workbook.createWorkbook(os);
   WritableSheet wsheet = wwbook.createSheet("sheet1", 0);// set sheet
   for (int i = 0; i < result.size(); i++) { //set header title
    jxl.write.Label titleCell = new jxl.write.Label(i, 0, result.get(i).getTitle());
    wsheet.addCell(titleCell);
   }
   for (int i = 1; i <= objects.size(); i++) { // set value
    Object obj = objects.get(i-1);
    for(int j = 0; j < result.size(); j++) {
     result.get(j).getField().setAccessible(true);
     Object value = result.get(j).getField().get(obj);
     jxl.write.Label valueCell = new jxl.write.Label(j, i, ( value != null)? value.toString() : "");
     wsheet.addCell(valueCell);
    }
   }
   wwbook.write();
  } catch (IOException e) {
   e.printStackTrace();
  } catch (RowsExceededException e) {
   e.printStackTrace();
  } catch (WriteException e) {
   e.printStackTrace();
  } catch (IllegalArgumentException e) {
   e.printStackTrace();
  } catch (IllegalAccessException e) {
   e.printStackTrace();
  }finally {
   try {
    wwbook.close();
    os.close();
   } catch (IOException ie) {
    ie.printStackTrace();
   } catch (WriteException e) {
    e.printStackTrace();
   }
  }
    }
   
    public static String getFileName() {
     SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmm");
        StringBuilder sb = new StringBuilder();
        sb.append(sf.format(System.currentTimeMillis()));
        sb.append(".xls");
        return sb.toString();
    }
}