java poi导出Excel
2018-03-21 本文已影响0人
嘿小逗比就是你
由于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;
}
}
