你的位置:首页 > Java教程

[Java教程]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";  }

不含子列表的结果

含有子列表