java导出excel工具

package com.rsclouds.ole.mgmt.utils; import java.beans.IntrospectionException; import java.beans.PropertyDescriptor; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; /**  * description:excel工具类  * @author Lishun  */ public class ExcelTools {  /**    * 把list集合转换成 List<Map<String, Object>> by Lishun   * @param listObj   * @return 返回 List<Map<String, Object>>   */  public static <T> List<Map<String, Object>> toListMap(List<T> listObj)    throws IntrospectionException, IllegalAccessException,    IllegalArgumentException, InvocationTargetException,    InstantiationException {   if (listObj.size() > 0) {    // 若参数原本是Map就直接返回    if (listObj.get(0) instanceof Map) {     return (List<Map<String, Object>>) listObj;    }    List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();    for (T t : listObj) {     Map<String, Object> temp = new HashMap<String, Object>();     Class clazz = t.getClass();     Field[] fields = clazz.getDeclaredFields();     for (Field field : fields) {      PropertyDescriptor pd = new PropertyDescriptor(        field.getName(), clazz);      Object val = pd.getReadMethod().invoke(t);      if(val==null){       val="";      }      temp.put(field.getName(), val);     }     listMap.add(temp);    }    return listMap;   }   return null;  }  /**   * 获取导出excel工作表格对象 by Lishun   * @param list：数据列表   * @param headerList：标题 [map.put("titele", "标题");]   * @param excelName：导出excel名称   * @param isChildList：是否存在子列表   * @param childListName：子列表名称   * @return   */  public static <T> HSSFWorkbook exportListExcel(List<T> list,Map<String, Object> headerList,    String excelName,Boolean isChildList,String childListName) throws Exception {   List<Map<String, Object>> dataList=toListMap(list);   // 创建excel文档对象   HSSFWorkbook wb = new HSSFWorkbook();   // 创建excel一个工作表格（sheet）：   HSSFSheet sheet = wb.createSheet(excelName);   // 创建excel的行(首行标题)   HSSFRow row = sheet.createRow((int) 0);   // excel的格式   HSSFCellStyle style = wb.createCellStyle();   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);   // 设置标题   if(headerList!=null&&headerList.size()>0){    Set<String> keyHeader = headerList.keySet();    int j = 0;    for (String keys : keyHeader) {     HSSFCell cell = row.createCell(j);     cell.setCellValue(String.valueOf(headerList.get(keys)));     cell.setCellStyle(style);     // 设置每列的宽度     sheet.setColumnWidth(j, 7500);     headerList.put(keys, j);     j++;    }   }   if (isChildList) {// 有isChildList,    return (HSSFWorkbook) exportChildListExcel(wb, dataList, headerList,      excelName,childListName);   }   if(dataList!=null){    // 计算行数    Integer rowIndex = 1;    //列数    Integer cellIndex=0;    String cellVal="";    // 设置内容    for (Map<String, Object> mapTemp : dataList) {     row = sheet.createRow(rowIndex);     Set<String> keyVal = headerList.keySet();     for (String key : keyVal) {      cellIndex=Integer.valueOf(String.valueOf(headerList.get(key)));      if(mapTemp.get(key)!=null){       cellVal=String.valueOf(mapTemp.get(key));      }else{       cellVal="";       System.out.println("无法匹配"+key+",检查集合元素类型是否含有属性");      }      row.createCell(cellIndex).setCellValue(cellVal);     }     rowIndex++;    }   }   return wb;  }  /**   * 导出含有子列表的excel by Lishun   * @param list：数据列表   * @param headerList：标题 [map.put("titele", "标题");]   * @param excelName：导出excel名称   * @param childListName：子列表名称   * @return   */  private static Workbook exportChildListExcel(Workbook workbook,    List<Map<String, Object>> list, Map<String, Object> headerList    ,String excelName,String childListName) throws Exception {   Sheet sheet = workbook.getSheetAt(0);   Row row = sheet.getRow(0);   Integer rowIndex = 1; // 计算行数   List<Map<String, Object>> childList = new ArrayList<Map<String, Object>>();   Set<String> childKey=null;   for (Map<String, Object> map : list) {    childList = (List) map.get(childListName);    if(childList!=null){     childList=toListMap(childList);     for (Map<String, Object> mapChild : childList) {      childKey=mapChild.keySet();      row = sheet.createRow(rowIndex);      //列数      Integer cellIndex=0;      String cellVal="";      for (String key : headerList.keySet()) {       cellIndex=Integer.valueOf(String.valueOf(headerList.get(key)));       if(map.get(key)!=null){        cellVal=String.valueOf(map.get(key));       }else{        if(mapChild.get(key)!=null){         cellVal=String.valueOf(mapChild.get(key));        }else{         cellVal="";         System.out.println("无法匹配"+key+",检查集合元素类型是否含有属性");        }       }       row.createCell(cellIndex).setCellValue(cellVal);      }      rowIndex++;     }     // 若存在多个数据，就合并成一行     if (rowIndex > 1) {      if(childKey!=null){       for (String key : headerList.keySet()) {        //对不存在子列表的key值进行合并        if(!childKey.contains(key)){         int cellIndex=Integer.valueOf(String.valueOf(headerList.get(key)));         // firstRow:从第几行开始; lastRow:到第几行结束; firstCol:从第几列开始;lastCol:到第几列结束         sheet.addMergedRegion(           new CellRangeAddress(rowIndex- childList.size(), rowIndex - 1, cellIndex,cellIndex));        }       }      }     }    }else{     //不存在子列表     return exportListExcel(list, headerList, excelName, false, "");    }   }   return workbook;  } } 

主要是exportListExcel(.......)方法导出excel

测试

@RequiresPermissions("user:lishun:view") @RequestMapping(value = "index/test", method = RequestMethod.GET) public String test(HttpServletResponse response) throws Exception {  News n1=new News();  n1.setTitele("你好1");  n1.setCount(1);  n1.setCreateDate(new Date());  News n2=new News();  n2.setTitele("你好2");  n2.setCount(2);  n2.setCreateDate(new Date());  News n3=new News();  n3.setTitele("你好3");  n3.setCount(3);  n3.setCreateDate(new Date());  List<News> list=new ArrayList<News>();  list.add(n3);  list.add(n2);  List<News> list1=new ArrayList<News>();  list1.add(n1);  //ExcelTools  Map<String, Object> headerList=new LinkedHashMap<String, Object>();  headerList.put("name", "名称");  headerList.put("age", "年龄");  headerList.put("titele", "标题");  headerList.put("count", "统计");  List<Map<String,Object>> listMap=new ArrayList<Map<String,Object>>();  Map<String,Object> map1=new HashMap<String, Object>();  map1.put("name", "你好1");  map1.put("age", "1");  //map1.put("childList", list1);//添加子列表  Map<String,Object> map2=new HashMap<String, Object>();  map2.put("name", "你好2");  map2.put("age", "2");  //map2.put("childList", list);//添加子列表  listMap.add(map1);  listMap.add(map2);  Workbook wb = ExcelTools.exportListExcel(listMap, headerList,"用户列表",false,"true");  //Workbook wb = ExcelTools.exportListExcel(listMap, headerList,"用户列表",true,"childList");  response.setContentType("application/vnd.ms-excel");  response.setHeader("Content-disposition", "attachment;filename="    + URLEncoder.encode("用户列表", "UTF-8") + ".xls");  OutputStream ouputStream = response.getOutputStream();  wb.write(ouputStream);  wb.close();  ouputStream.flush();  ouputStream.close();  return "index/index"; } 

不含子列表的结果

含有子列表