java 导出excel poi_java poi导出Excel

由于ajax请求不能下载，故先利用ajax将请求数据提交到后台，存入redis，并返回一个uuid，然后在ajax请求成功之后用window.location.href进行下载。

另外因为需求原因，Excel列数和列title是动态的，所以利用反射创建工作簿。

HSSFWorkbook wb = CreateExcelByReflect.createWb(data);//创建工作簿

String fileName ="任务表" +new Date().getTime() +".xls";

OutputStream out =null;

try {

out = response.getOutputStream();

response.reset();

response.setHeader("Content-Disposition","attachment;filename="

.concat(String.valueOf(URLEncoder.encode(fileName,"UTF-8"))));

response.setContentType("application/ms-excel;charset=UTF-8");

wb.write(out);

out.flush();

out.close();

}catch (IOException e) {

e.printStackTrace();

}

public class CreateExcelByReflect {

private static HSSFSheetsheet;//表格对象

private static HSSFWorkbookwb;//工作簿

private static LinkedHashMapheader;//表头

//初始化

public static void init(String sheetName,JSONArray titleArr) {

wb =new HSSFWorkbook();

sheet =wb.createSheet(sheetName);

header =new LinkedHashMap();

for(Object title : titleArr){

JSONObject obj = (JSONObject) JSON.toJSON(title);

header.put(obj.getString("key"), obj.getString("title"));

}

}

/**

* 创建各列表头

*/

public static void createHeadRow(){

HSSFRow head =sheet.createRow(0);//创建表格第一行对象，为表头行

Iterator> headTitle =header.entrySet().iterator();//循环输出表头

for(int i=0;headTitle.hasNext();i++){

HSSFCell cell = head.createCell(i);

cell.setCellValue(headTitle.next().getValue());

}

}

/**

* 创建数据行

*/

public static void createRows(List data){

int rowCount = data.size();//根据数据集设置行数

for(int i=0;i

HSSFRow row =sheet.createRow(i+1);//创建行，表头是第0行

//转换数据，将每一个DO映射为属性名与FieldsEntity的Map

Map map = DataConvertUtil.convertObjectToMap(data.get(i));

Iterator> head =header.entrySet().iterator();

//创建每行的单元格并填充值

for(int col =0;col

HSSFCell cell = row.createCell(col);

//设置表头的迭代器

Map.Entry enty = (Map.Entry)head.next();

String name = enty.getKey();

Object value = map.get(name);

if(null != value){

cell.setCellValue(map.get(name).getValue().toString());//填充属性值

}

}

}

}

public static HSSFWorkbook createWb(String sheetName,JSONArray titleArr, List data){

init(sheetName, titleArr);

createHeadRow();

createRows(data);

return wb;

}

}

public class DataConvertUtil {

/**

* 将对象的属性名称与值映射为MAP

* @param o 对象

* @return Mapkey为属性名称，value为名称、类型和值组成的对象

*/

public static Map convertObjectToMap(Object o){

Class oClass = o.getClass();

// Field[] fields = oClass.getDeclaredFields(); //获取类中的所有声明的属性

Field[] fields =getBeanFields(oClass, oClass.getDeclaredFields());

Map map =new HashMap();

try{

for(int i=0;i

// 不对序列化ID进行映射

if(fields[i].getName().equals("serialVersionUID")){

continue;

}

Object valueObject =getFieldValue(o,fields[i].getName());

map.put(fields[i].getName(),new FieldsEntity(fields[i].getName(), valueObject, fields[i].getType()));

}

return map;

}catch(Exception e){

e.printStackTrace();

}

return map;

}

public static Field[] getBeanFields(Class cls,Field[] fs){

fs = (Field[]) ArrayUtils.addAll(fs, cls.getDeclaredFields());

if(cls.getSuperclass()!=null){

Class clsSup = cls.getSuperclass();

fs =getBeanFields(clsSup,fs);

}

return fs;

}

/**

* 通过对象的getter方法获取属性值

* @param o 对象

* @param name 属性名称

* @return 相应属性的值

*/

public static Object getFieldValue(Object o,String name)throws SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException, InvocationTargetException {

Class owner = o.getClass();

Method mothed = owner.getMethod(createGetter(name));

Object object = mothed.invoke(o);

return object;

}

/**

* 通过属性名称拼凑getter方法

* @param fieldName

* @return

*/

public static String createGetter(String fieldName){

if(fieldName ==null || fieldName.length() ==0 ){

return null;

}

StringBuffer sb =new StringBuffer("get");

sb.append(fieldName.substring(0,1).toUpperCase()).append(fieldName.substring(1));

return sb.toString();

}

}

public class FieldsEntity {

private StringattributeName;//属性变量名称

private Objectvalue;//属性变量值

private ClassclassType;//属性类型

public String getAttributeName() {

return attributeName;

}

public void setAttributeName(String attributeName) {

this.attributeName = attributeName;

}

public Object getValue() {

return value;

}

public void setValue(Object value) {

this.value = value;

}

public Class getClassType() {

return classType;

}

public void setClassType(Class classType) {

this.classType = classType;

}

public FieldsEntity(String fieldName, Object o, Class classType){

this.attributeName = fieldName;

this.value = o;

this.classType = classType;

}

}