利用jxl实现通用的导出excel

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();
    }
}


ExcelUtil 类是最终实现写excel文件功能.

属性文件配置如下:
id=ID
name=Name
age=Age
#email=Email

以上能够实现简单的导出，可以运行。但是现在一般的情况下导出是使用模板的形式导出，上

面的导出在某些方面存在很大的局限性，配置文件太多，虽然也是可以把所有的导出标题配置

一个配置文件中。

原创地址：http://www.blogjava.net/rain1102/archive/2009/06/22/283579.html