Java导出excel并下载功能解析

public  void exportToExcel(List<PortalContactVO> data) throws Exception   
    {   
        this.setEnableAccessRequest(true); 
        this.setEnableAccessResponse(true); 
        HttpServletRequest request = this.getRequest(); 
        HttpServletResponse response = this.getResponse(); 
        String randomNumber = request.getParameter("randomNumber");// session名称 
        try { 
            session = request.getSession(); 
            session.setAttribute(randomNumber, new Double(1)); 
            // 导出的EXCEL文件名 
            String exportFileName = "addressBook.xlsx"; 
            response.reset(); 
            response.setContentType("octets/stream"); 
            // response.setHeader("Content-Disposition","attachment;filename="+exportFileName); 
            response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(exportFileName.getBytes("UTF-8"), "iso8859-1") + "\""); 
 
            // 导出的EXCEL列属性 
            List<String> columnListName = new ArrayList<String>(); 
            columnListName.add("userName&姓名"); 
            columnListName.add("mobile&手机"); 
            columnListName.add("shopTel&分店电话"); 
            columnListName.add("postName&职位"); 
            columnListName.add("email&邮箱"); 
            columnListName.add("shopAddress&分店地址"); 
            Bean2ExcelConversionUtils.beans2excelFile07(columnListName, data, response.getOutputStream()); 
            session.setAttribute(randomNumber, new Double(100)); 
 
        } catch (Exception e) { 
            e.printStackTrace(); 
            session.setAttribute(randomNumber, new Double(100)); 
        } catch (Throwable e) { 
            e.printStackTrace(); 
            session.setAttribute(randomNumber, new Double(100)); 
        } 
    }

   <form id="exportForm">     
       <input class="btnStyle" type="submit" id="inputExport" value="导出" onclick="exportToExcel()" /> 
   </form>

function exportToExcel() { 
            var randomNumber=new Date().getTime(); 
            top.$.jBox.tip("正在导出...", 'loading'); 
            var exportDate = "${ctx}/xxxAction.do?method=export&randomNumber="+randomNumber; 
            $("#exportForm").attr("action", exportDate); 
            $("#exportForm").attr("method","post"); 
            $("#exportForm").submit(); 
        }

import java.beans.Introspector; 
import java.beans.PropertyDescriptor; 
import java.io.File; 
import java.io.FileOutputStream; 
import java.io.IOException; 
import java.io.OutputStream; 
import java.text.SimpleDateFormat; 
import java.util.ArrayList; 
import java.util.Date; 
import java.util.HashMap; 
import java.util.List; 
 
import javax.servlet.http.HttpServletRequest; 
import javax.servlet.http.HttpServletResponse; 
 
import org.apache.poi.hssf.usermodel.HSSFCell; 
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.Cell; 
import org.apache.poi.ss.usermodel.CellStyle; 
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.xssf.streaming.SXSSFSheet; 
import org.apache.poi.xssf.streaming.SXSSFWorkbook; 
 
/** 
 * @author Tan Jiangyong 
 * @date 2013-9-3 下午3:36:43 
 * @version V1.0 
 */ 
@SuppressWarnings("all") 
public class Bean2ExcelConversionUtils { 
    private static final String PATTERN="yyyy-MM-dd HH:mm:ss";    //excel日期格式，默认配置 
    private static final String DATE_PATTERN="yyyy-MM-dd";        //excel日期格式 
    private static final String DATE_HH_PATTERN="HH:mm:ss";        //excel时间格式 
    private static final int TOTAL_SIZE=40000;                    //每个excel写入多少数据（默认配置） 
    private static final int MAX_SHEET_SIZE=10000;                //每一个sheet的大小（默认配置） 
    private static final int COLUMN_WIDTH_WORD=25;                //列宽，默认汉字个数为25个 
    private static final int FLUSH_ROWS=100;                    //每生成excel行数，内存中缓存记录数清空（目的，避免零时文件过大） 
     
    /** 
     * 07、10办公版EXCEL导出（数据直接写到服务器的EXCEL里，以下载的形式，下载导出的数据） 
     * @param listName        列表头名称 
     * @param beans         实体集合 
     * @param result        数字字典Map集 
     * @param filePath        服务器存放文件路径 
     * @param fileName         文件名称 
     * @param totalSize     EXCEL条数量 
     * @param maxSheetSize     sheet页条数量 
     * @return              文件集合 
     * @throws Exception     
     */ 
    public static <T> List<File> beans2excelFile07(List<String> listName,List<T> beans,HashMap<String,HashMap<String,String>> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throws Exception{ 
        if(totalSize==null || totalSize<=0) 
            totalSize=TOTAL_SIZE; 
        if(maxSheetSize==null || maxSheetSize<=0) 
            maxSheetSize=MAX_SHEET_SIZE; 
        if(fileName==null) 
            fileName=""; 
        return beans2excelFile2007(listName, beans, result, filePath, fileName,totalSize,maxSheetSize); 
    } 
    /** 
     * 07、10办公版EXCEL导出（数据直接写到服务器的EXCEL里，以下载的形式，下载导出的数据） 
     * @param listName        列表头名称 
     * @param beans         实体集合 
     * @param result        数字字典Map集 
     * @param filePath        服务器存放文件路径 
     * @param fileName         文件名称 
     * @param totalSize     EXCEL条数量 
     * @param maxSheetSize     sheet页条数量 
     * @param request        客户端请求对象 
     * @param response        客户端响应对象 
     * @throws Exception 
     */ 
    public static <T> void beans2excelFile07(List<String> listName,List<T> beans,HashMap<String,HashMap<String,String>> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize,HttpServletRequest request,HttpServletResponse response) throws Exception{ 
        if(totalSize==null || totalSize<=0) 
            totalSize=TOTAL_SIZE; 
        if(maxSheetSize==null || maxSheetSize<=0) 
            maxSheetSize=MAX_SHEET_SIZE; 
        if(fileName==null) 
            fileName=""; 
        List<File> files = beans2excelFile2007(listName, beans, result, filePath, fileName,totalSize,maxSheetSize); 
        DownLoadUtils.downLoadFiles(files, filePath, request, response); 
    } 
     
    /** 
     * 07、10办公版EXCEL导出，每个EXCEL组织数据 
     * @param listName         列表头名称 
     * @param beans             实体集合 
     * @param result         数字字典Map集 
     * @param filePath         服务器存放文件路径 
     * @param fileName         文件名称 
     * @param totalSize      EXCEL条数量 
     * @param maxSheetSize  sheet页条数量 
     * @return                文件集合 
     * @throws Exception 
     */ 
    private static <T> List<File> beans2excelFile2007(List<String> listName,List<T> beans,HashMap<String,HashMap<String,String>> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throws Exception{ 
        if ((listName == null) || (listName.size() == 0)) { 
            throw new Exception("listName is null when create excel document"); 
        } 
        List<File> listFile=new ArrayList<File>();//返回的文件集合 
         
        int size=beans==null?0:beans.size(); 
        String fileSuffixName=".xlsx";//后缀 
        String path="";//文件路径 
        Integer startIdx=0;//数据读取的起始行 
        Integer endIdx=0;//数据读取的结束行 
        (new File(filePath)).mkdirs(); //没有该目录创建目录 
        if(size==0){ 
            startIdx=0; 
            endIdx=(totalSize)>size?size:(totalSize); 
            String name=fileName+"_第0-0条数据"; 
            path=filePath+File.separatorChar+name+fileSuffixName; 
            Workbook wb =new SXSSFWorkbook(); 
            buildExcelDocument2007(wb, listName, beans,result,startIdx,endIdx,maxSheetSize); 
            //没有文件，创建文件 
            File file = new File(path); 
            if (!file.exists()){   
                file.createNewFile();   
            } 
            FileOutputStream out=new FileOutputStream(file); 
            wb.write(out); 
            out.close(); 
            return listFile; 
        } 
        for (int i = 0; i < size;i++) { 
            int remainder=i%totalSize; 
            if(size==0 || remainder==0){ 
                startIdx=i; 
                endIdx=(i+totalSize)>size?size:(i+totalSize); 
                String name=fileName+"_第"+(startIdx+1)+"-"+(endIdx)+"条数据"; 
                path=filePath+"/"+name+fileSuffixName; 
                Workbook wb =new SXSSFWorkbook(); 
                buildExcelDocument2007(wb, listName, beans,result,startIdx,endIdx,maxSheetSize); 
                //没有文件，创建文件 
                File file = new File(path); 
                if (!file.exists()){   
                    file.createNewFile();   
                } 
                FileOutputStream out=new FileOutputStream(file); 
                wb.write(out); 
                out.close(); 
                listFile.add(file); 
            }else if((size-i)<totalSize && i>endIdx){//最后，不满一万条 
                startIdx=i; 
                endIdx=i+totalSize; 
                String name=fileName+"_第"+(startIdx+1)+"-"+(endIdx)+"条数据"; 
                path=filePath+name+"."+fileSuffixName;//没有文件，创建文件 
                Workbook wb =new SXSSFWorkbook(); 
                buildExcelDocument2007(wb, listName, beans, result,startIdx,endIdx,maxSheetSize); 
                //没有文件，创建文件 
                File file = new File(path); 
                if (!file.exists()){   
                    file.createNewFile();   
                } 
                FileOutputStream out=new FileOutputStream(file); 
                wb.write(out); 
                out.close(); 
                listFile.add(file); 
            } 
        } 
        return listFile; 
    } 
     
    /** 
     * 07、10办公版EXCEL导出，每个EXCEL写入数据 
     * @param wb            EXCEL工作薄 
     * @param listName        列表头名称 
     * @param beans            实体集合 
     * @param result        数字字典Map集 
     * @param startIdx        数据集合，开始行 
     * @param endIdx        数据集合，结束始行 
     * @param maxSheetSize    SHEET页条数 
     * @throws Exception  
     */ 
    private static <T> void buildExcelDocument2007(Workbook wb, List<String> listName, List<T> beans,HashMap<String,HashMap<String,String>> result,Integer startIdx,Integer endIdx,Integer maxSheetSize) throws Exception 
    { 
        int totalSize=endIdx-startIdx;//总共条数 
        try 
        { 
            CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null); 
            List titles = new ArrayList(); 
            List beanAttrNames = new ArrayList(); 
            boolean flagListExists=false; 
            List flagList=new ArrayList(); 
            List widthList=new ArrayList(); 
            HashMap<String,String> dateMap=new HashMap<String, String>(); 
            String[] header = new String[listName.size()]; 
            int rows_max = 0;//标题占多少列 
            for (int i=0;i<listName.size();i++) 
            { 
                String[] str=listName.get(i).split("&"); 
                String en_name=str[0]; 
                String zh_name=str[1]; 
                beanAttrNames.add(i,en_name); 
                titles.add(i, zh_name); 
                header[i]=zh_name; 
                if (zh_name.split("_").length > rows_max) { 
                    rows_max = zh_name.split("_").length; 
                } 
                if(str.length>2){ 
                    String flag=str[2]; 
                    flagList.add(i,flag); 
                    if(!flagListExists) 
                        flagListExists=true; 
                } 
                if(str.length>3){ 
                    widthList.add(str[3]); 
                } 
                if(str.length>4){ 
                    dateMap.put(en_name, str[4]); 
                } 
            } 
 
            PropertyDescriptor[] props = null; 
 
            int size=endIdx-startIdx; 
            Sheet sheet=null; 
             
            //如果没有数据，导出表头 
            if(size==0){ 
                sheet=ExcelHeadUtils.getExcelHead2007(wb, header, "Sheet1"); 
                sheet.setDefaultRowHeight((short)350);//高度 
                setColumnWidth2007(widthList, sheet,beanAttrNames.size()); 
                return ; 
            } 
            int u=1;//用来创建每个sheet的行 
            int h=0;//用来标注每个sheet也得名字：多少行-多少行 
            for (int i = startIdx; i < endIdx ; i++) { 
                int remainder=h%maxSheetSize; 
                if(size==0 || i==startIdx || remainder==0){ 
                    u=1; 
                    int section=(h+maxSheetSize)>totalSize?totalSize:(h+maxSheetSize); 
                    sheet=ExcelHeadUtils.getExcelHead2007(wb, header,"第"+(h+1)+"-"+section+"条"); 
                    sheet.createFreezePane( 1, rows_max, 1, rows_max); 
                    sheet.setDefaultRowHeight((short)350);//高度 
                    setColumnWidth2007(widthList, sheet,beanAttrNames.size()); 
                } 
                if(props==null) 
                    props=Introspector.getBeanInfo(beans.get(0).getClass()).getPropertyDescriptors(); 
                Object bean = beans.get(i); 
                Row row = sheet.createRow(u+rows_max-1); 
                u++; 
                h++; 
                for (int j = 0; j < beanAttrNames.size(); j++) { 
                    String beanAttrName = (String)beanAttrNames.get(j); 
                    String flag=""; 
                    if(flagListExists) 
                        flag=(String)flagList.get(j); 
                    for (int k = 0; k < props.length; k++) { 
                        String propName = props[k].getName(); 
                        if (propName.equals(beanAttrName)) 
                        { 
                            String pattern=dateMap.get(beanAttrName); 
                            Cell cell = row.createCell((short)j); 
 
                            Object cellValue = callGetter(bean, props[k],pattern); 
                            if("true".equalsIgnoreCase(flag)){ 
                                if(result!=null){ 
                                    HashMap<String,String> hash=result.get(beanAttrName); 
                                    if(hash!=null) 
                                        cellValue=hash.get(cellValue); 
                                } 
                            } 
                            if (cellValue == null) { 
                                cellValue = ""; 
                            } 
                            setExcelCellText2007(cell, cellValue.toString(),cellStyle); 
                        } 
                    } 
                } 
                 //每当行数达到设置的值就刷新数据到硬盘,以清理内存 
                if(i%FLUSH_ROWS==0){ 
                   ((SXSSFSheet)sheet).flushRows(); 
                } 
            } 
        } catch (Exception e) { 
            throw new Exception(e); 
        } 
    } 
 
    /** 
     * 07、10办公版EXCEL导出（直接以流的方式，写到客户端，导出的EXCEL文件只有一个） 
     * @param listName        列表头名称 
     * @param beans            实体集合 
     * @param maxSheetSize    SHEET页的条数 
     * @param outputStream    客户端输出流 
     * @throws Exception 
     */ 
    public static <T> void beans2excelFile07(List<String> listName,List<T> beans, OutputStream outputStream) throws Exception{ 
        if ((listName == null) || (listName.size() == 0)) { 
            throw new Exception("listName is null when create excel document"); 
        } 
        if (outputStream == null) { 
            throw new Exception("outputStream is null when create excel document"); 
        } 
        Workbook wb =new SXSSFWorkbook(); 
        beans2excelFile07(listName, beans, null, null, MAX_SHEET_SIZE, outputStream); 
        try { 
            wb.write(outputStream); 
            outputStream.close(); 
        } catch (IOException e) { 
            throw new Exception(e); 
        }  
    } 
    /** 
     * 07、10办公版EXCEL导出（直接以流的方式，写到客户端，导出的EXCEL文件只有一个） 
     * @param listName        列表头名称 
     * @param beans            实体集合 
     * @param maxSheetSize    SHEET页的条数 
     * @param outputStream    客户端输出流 
     * @throws Exception 
     */ 
    public static <T> void beans2excelFile07(List<String> listName,List<T> beans,HashMap<String,HashMap<String,String>> result,String sheetName,Integer maxSheetSize, OutputStream outputStream) throws Exception{ 
        if ((listName == null) || (listName.size() == 0)) { 
            throw new Exception("listName is null when create excel document"); 
        } 
        if (outputStream == null) { 
            throw new Exception("outputStream is null when create excel document"); 
        } 
        if(maxSheetSize==null || maxSheetSize<=0){ 
            maxSheetSize=MAX_SHEET_SIZE; 
        } 
        if(sheetName==null || "".equals(sheetName.trim())){ 
            sheetName="Sheet"; 
        } 
        Workbook wb =new SXSSFWorkbook(); 
        if(maxSheetSize==null || maxSheetSize<=0){ 
            maxSheetSize=MAX_SHEET_SIZE; 
        } 
        buildExcelDocument2007(wb, listName, beans,result,sheetName,maxSheetSize); 
        try { 
            wb.write(outputStream); 
            outputStream.close(); 
        } catch (IOException e) { 
            throw new Exception(e); 
        }  
    } 
    /** 
     *  
     * @param listName 
     * @param beans 
     * @param response 
     * @param fileName 导出的文件名称 
     * @throws Exception 
     */ 
    public static <T> void beans2excelFile07(List<String> listName, List<T> beans, HttpServletResponse response,String fileName) throws Exception { 
        response.reset(); 
        response.setContentType("octets/stream"); 
        response.setHeader("Content-Disposition", "attachment;filename="+java.net.URLEncoder.encode(fileName, "UTF-8")); 
        if ((listName == null) || (listName.size() == 0)) { 
            throw new Exception("listName is null when create excel document"); 
        } 
        if (response.getOutputStream() == null) { 
            throw new Exception("outputStream is null when create excel document"); 
        } 
        beans2excelFile07(listName, beans, null, null, MAX_SHEET_SIZE, response.getOutputStream()); 
    } 
    /** 
     * 07、10办公版EXCEL导出，EXCEL写入数据 
     * @param wb            EXCEL工作薄 
     * @param listName        列表头名称 
     * @param beans            实体集合 
     * @param maxSheetSize    SHEET页的条数 
     * @throws Exception  
     */ 
    private static <T> void buildExcelDocument2007(Workbook wb, List<String> listName, List<T> beans,HashMap<String,HashMap<String,String>> result,String sheetName,Integer maxSheetSize) throws Exception 
    { 
        try 
        { 
            CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null); 
            List titles = new ArrayList(); 
            List beanAttrNames = new ArrayList(); 
            List widthList = new ArrayList(); 
            HashMap<String,String> dateMap=new HashMap<String, String>(); 
            String[] header = new String[listName.size()]; 
            int rows_max = 0;//标题占多少列 
            List flagList=new ArrayList(); 
            boolean flagListExists=false; 
            for (int i=0;i<listName.size();i++) 
            { 
                String[] str=listName.get(i).split("&"); 
                String en_name=str[0]; 
                String zh_name=str[1]; 
                beanAttrNames.add(i,en_name); 
                titles.add(i, zh_name); 
                header[i]=zh_name; 
                if (zh_name.split("_").length > rows_max) { 
                    rows_max = zh_name.split("_").length; 
                } 
                if(str.length>2){ 
                    String flag=str[2]; 
                    flagList.add(i,flag); 
                    if(!flagListExists) 
                        flagListExists=true; 
                } 
                if(str.length>3){ 
                    widthList.add(str[3]); 
                } 
                if(str.length>4){ 
                    dateMap.put(en_name, str[4]); 
                } 
            } 
 
            PropertyDescriptor[] props = null; 
 
            int size=beans==null?0:beans.size(); 
            Sheet sheet=null; 
             
            //如果没有数据，导出表头 
            if(size==0){ 
                sheet=ExcelHeadUtils.getExcelHead2007(wb, header, sheetName); 
                sheet.setDefaultRowHeight((short)350);//高度 
                setColumnWidth2007(widthList, sheet,beanAttrNames.size()); 
                return ; 
            } 
             
            for (int i = 0; i < size ; i++) { 
                int remainder=i%maxSheetSize; 
                if(size==0 || i==0 || remainder==0){ 
                    sheet=ExcelHeadUtils.getExcelHead2007(wb, header,sheetName+(i/maxSheetSize)); 
                    sheet.createFreezePane( 1, rows_max, 1, rows_max); 
                    sheet.setDefaultRowHeight((short)350);//高度 
                    setColumnWidth2007(widthList, sheet,beanAttrNames.size()); 
                } 
                if(props==null) 
                    props=Introspector.getBeanInfo(beans.get(0).getClass()).getPropertyDescriptors(); 
                Object bean = beans.get(i); 
                Row row = sheet.createRow(remainder+rows_max); 
                for (int j = 0; j < beanAttrNames.size(); j++) { 
                    String beanAttrName = (String)beanAttrNames.get(j); 
                    String flag=""; 
                    if(flagListExists) 
                        flag=(String)flagList.get(j); 
                    for (int k = 0; k < props.length; k++) { 
                        String propName = props[k].getName(); 
                        if (propName.equals(beanAttrName)) 
                        { 
                            String pattern=dateMap.get(beanAttrName); 
                            Cell cell = row.createCell((short)j); 
 
                            Object cellValue = callGetter(bean, props[k],pattern); 
                            if("true".equalsIgnoreCase(flag)){ 
                                if(result!=null){ 
                                    HashMap<String,String> hash=result.get(beanAttrName); 
                                    if(hash!=null) 
                                        cellValue=hash.get(cellValue); 
                                } 
                            } 
                            if (cellValue == null) { 
                                cellValue = ""; 
                            } 
                            setExcelCellText2007(cell, cellValue.toString(),cellStyle); 
                        } 
                    } 
                } 
                 //每当行数达到设置的值就刷新数据到硬盘,以清理内存 
                if(i%FLUSH_ROWS==0){ 
                   ((SXSSFSheet)sheet).flushRows(); 
                } 
            } 
        } catch (Exception e) { 
            throw new Exception(e); 
        } 
    } 
    /** 
     * 07、10办公版EXCEL导出（直接以流的方式，写到客户端，导出的EXCEL文件只有一个） 
     * @param listName        列表头名称 
     * @param beans            实体集合 
     * @param maxSheetSize    SHEET页的条数 
     * @param outputStream    客户端输出流 
     * @throws Exception 
     */ 
    public static <T> void beans2excelFile07List(List<ArrayList<String>> listColumnName,List<List> list2beans,HashMap<String,HashMap<String,HashMap<String,String>>> result,List<String> listSheetName, OutputStream outputStream) throws Exception{ 
        if ((listColumnName == null) || (listColumnName.size() == 0)) { 
            throw new Exception("listColumnName is null when create excel document"); 
        } 
        if (list2beans.size() != listColumnName.size()) { 
            throw new Exception("list2beans and listColumnName size Unequal"); 
        } 
        if (outputStream == null) { 
            throw new Exception("outputStream is null when create excel document"); 
        } 
        Workbook wb =new SXSSFWorkbook(); 
        buildExcelDocument2007List(wb, listColumnName, list2beans, result, listSheetName); 
        try { 
            wb.write(outputStream); 
            outputStream.close(); 
        } catch (IOException e) { 
            throw new Exception(e); 
        }  
    } 
    /** 
     * 07、10办公版EXCEL导出，EXCEL写入数据 
     * @param wb            EXCEL工作薄 
     * @param listName        列表头名称 
     * @param beans            实体集合 
     * @param maxSheetSize    SHEET页的条数 
     * @throws Exception  
     */ 
    private static <T> void buildExcelDocument2007List(Workbook wb, List<ArrayList<String>> listColumnName,List<List> list2beans,HashMap<String,HashMap<String,HashMap<String,String>>> resultMap,List<String> listSheetName) throws Exception 
    { 
        try 
        { 
            int sheets=listColumnName.size(); 
            boolean sheetNameIsNullFlag=false; 
            if(listSheetName==null || listSheetName.size()!=sheets){ 
                sheetNameIsNullFlag=true; 
            } 
            for (int s = 0; s < sheets; s++) { 
                String sheetName="Sheet"+s; 
                if(!sheetNameIsNullFlag){ 
                    sheetName=listSheetName.get(s); 
                } 
                List<String> listName=listColumnName.get(s); 
                CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null); 
                List titles = new ArrayList(); 
                List beanAttrNames = new ArrayList(); 
                List widthList = new ArrayList(); 
                HashMap<String,String> dateMap=new HashMap<String, String>(); 
                String[] header = new String[listName.size()]; 
                int rows_max = 0;//标题占多少列 
                List flagList=new ArrayList(); 
                boolean flagListExists=false; 
                for (int i=0;i<listName.size();i++) 
                { 
                    String[] str=listName.get(i).split("&"); 
                    String en_name=str[0]; 
                    String zh_name=str[1]; 
                    beanAttrNames.add(i,en_name); 
                    titles.add(i, zh_name); 
                    header[i]=zh_name; 
                    if (zh_name.split("_").length > rows_max) { 
                        rows_max = zh_name.split("_").length; 
                    } 
                    if(str.length>2){ 
                        String flag=str[2]; 
                        flagList.add(i,flag); 
                        if(!flagListExists) 
                            flagListExists=true; 
                    } 
                    if(str.length>3){ 
                        widthList.add(str[3]); 
                    } 
                    if(str.length>4){ 
                        dateMap.put(en_name, str[4]); 
                    } 
                } 
     
                PropertyDescriptor[] props = null; 
                ArrayList<T> beans=(ArrayList<T>)list2beans.get(s); 
                int size=beans==null?0:beans.size(); 
                Sheet sheet=null; 
                 
                //如果没有数据，导出表头 
                if(size==0){ 
                    sheet=ExcelHeadUtils.getExcelHead2007(wb, header, sheetName); 
                    sheet.setDefaultRowHeight((short)350);//高度 
                    setColumnWidth2007(widthList, sheet,beanAttrNames.size()); 
                    return ; 
                } 
                 
                HashMap<String,HashMap<String,String>> result=null; 
                if(resultMap!=null){ 
                    result=resultMap.get(sheetName); 
                } 
                sheet=ExcelHeadUtils.getExcelHead2007(wb, header,sheetName); 
                sheet.createFreezePane( 1, rows_max, 1, rows_max); 
                sheet.setDefaultRowHeight((short)350);//高度 
                setColumnWidth2007(widthList, sheet,beanAttrNames.size()); 
                for (int i = 0; i < size ; i++) { 
                    if(props==null) 
                        props=Introspector.getBeanInfo(beans.get(0).getClass()).getPropertyDescriptors(); 
                    Object bean = beans.get(i); 
                    Row row = sheet.createRow(rows_max+i); 
                    for (int j = 0; j < beanAttrNames.size(); j++) { 
                        String beanAttrName = (String)beanAttrNames.get(j); 
                        String flag=""; 
                        if(flagListExists) 
                            flag=(String)flagList.get(j); 
                        for (int k = 0; k < props.length; k++) { 
                            String propName = props[k].getName(); 
                            if (propName.equals(beanAttrName)) 
                            { 
                                String pattern=dateMap.get(beanAttrName); 
                                Cell cell = row.createCell((short)j); 
     
                                Object cellValue = callGetter(bean, props[k],pattern); 
                                if("true".equalsIgnoreCase(flag)){ 
                                    if(result!=null){ 
                                        HashMap<String,String> hash=result.get(beanAttrName); 
                                        if(hash!=null) 
                                            cellValue=hash.get(cellValue); 
                                    } 
                                } 
                                if (cellValue == null) { 
                                    cellValue = ""; 
                                } 
                                setExcelCellText2007(cell, cellValue.toString(),cellStyle); 
                            } 
                        } 
                    } 
                     //每当行数达到设置的值就刷新数据到硬盘,以清理内存 
                    if(i%FLUSH_ROWS==0){ 
                       ((SXSSFSheet)sheet).flushRows(); 
                    } 
                } 
            } 
        } catch (Exception e) { 
            throw new Exception(e); 
        } 
    } 
    /** 
     * 07、10办公版EXCEL导出，单元格设置 
     * @param cell        单元格对象 
     * @param text        单元格文本内容 
     * @param cellStyle    单元格格式 
     */ 
    private static void setExcelCellText2007(Cell cell, Object text,CellStyle cellStyle) 
    { 
        cell.setCellValue(text.toString()); 
        cell.setCellType(1);//单元格类型 
        cell.setCellStyle(cellStyle); 
    } 
     
    /** 
     * 07、10办公版EXCEL导出，单元格宽度设置 
     * @param widthList        列宽集合 
     * @param sheet            sheet对象 
     * @param allSize        总列数 
     */ 
    private static void setColumnWidth2007(List widthList,Sheet sheet,int allSize){ 
        if(widthList!=null && widthList.size()>0){ 
            int size=widthList.size(); 
            for (int i = 0; i < size; i++) { 
                try { 
                    Integer width=Integer.parseInt((String) widthList.get(i)); 
                    sheet.setColumnWidth((short) i,width*256); 
                } catch (NumberFormatException e) { 
                    continue; 
                } 
            } 
        }else{ 
            for (int i = 0; i < allSize; i++) { 
                try { 
                    sheet.setColumnWidth((short) i,COLUMN_WIDTH_WORD*256); 
                } catch (NumberFormatException e) { 
                    continue; 
                } 
            } 
         
        } 
    } 
     
    /** 
     * 03、WPS：EXCEL导出（数据直接写到服务器的EXCEL里，以下载的形式，下载导出的数据） 
     * @param listName            列表头名称 
     * @param beans                实体集合 
     * @param result            数字字典Map集 
     * @param filePath            服务器存放文件路径 
     * @param fileName            文件名称 
     * @param totalSize            EXCEL条数量 
     * @param maxSheetSize        sheet页条数量 
     * @return List<File>        文件集合 
     * @throws Exception 
     */ 
    public static <T> List<File> beans2excelFile03(List<String> listName,List<T> beans,HashMap<String,HashMap<String,String>> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throws Exception{ 
        if(totalSize==null || totalSize<=0) 
            totalSize=TOTAL_SIZE; 
        if(maxSheetSize==null || maxSheetSize<=0) 
            maxSheetSize=MAX_SHEET_SIZE; 
        if(fileName==null) 
            fileName=""; 
        return beans2excelFile2003(listName, beans, result, filePath, fileName,totalSize,maxSheetSize); 
    } 
    /** 
     * 03、WPS：EXCEL导出（数据直接写到服务器的EXCEL里，以下载的形式，下载导出的数据） 
     * @param listName            列表头名称 
     * @param beans                实体集合 
     * @param result            数字字典Map集 
     * @param filePath            服务器存放文件路径 
     * @param fileName            文件名称 
     * @param totalSize            EXCEL条数量 
     * @param maxSheetSize        sheet页条数量 
     * @param request        客户端请求对象 
     * @param response        客户端响应对象 
     * @throws Exception 
     */ 
    public static <T> void beans2excelFile03(List<String> listName,List<T> beans,HashMap<String,HashMap<String,String>> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize,HttpServletRequest request,HttpServletResponse response) throws Exception{ 
        if(totalSize==null || totalSize<=0) 
            totalSize=TOTAL_SIZE; 
        if(maxSheetSize==null || maxSheetSize<=0) 
            maxSheetSize=MAX_SHEET_SIZE; 
        if(fileName==null) 
            fileName=""; 
        List<File> files=beans2excelFile2003(listName, beans, result, filePath, fileName,totalSize,maxSheetSize); 
        DownLoadUtils.downLoadFiles(files, filePath, request, response); 
    } 
    /** 
     *  03、WPS：EXCEL导出，每个EXCEL组织数据 
     * @param listName        列表头名称 
     * @param beans            实体集合 
     * @param result        数字字典Map集 
     * @param filePath        服务器存放文件路径 
     * @param fileName        文件名称 
     * @param totalSize        EXCEL条数量 
     * @param maxSheetSize    sheet页条数量 
     * @return                文件集合 
     * @throws Exception 
     */ 
    private static <T> List<File> beans2excelFile2003(List<String> listName,List<T> beans,HashMap<String,HashMap<String,String>> result,String filePath,String fileName,Integer totalSize,Integer maxSheetSize) throws Exception{ 
        if ((listName == null) || (listName.size() == 0)) { 
            throw new Exception("listName is null when create excel document"); 
        } 
        List<File> listFile=new ArrayList<File>();//返回的文件集合 
         
        int size=beans==null?0:beans.size(); 
        String fileSuffixName=".xls";//后缀 
        String path="";//文件路径 
        Integer startIdx=0;//数据读取的起始行 
        Integer endIdx=0;//数据读取的结束行 
        (new File(filePath)).mkdirs(); //没有该目录创建目录 
        if(size==0){ 
            startIdx=0; 
            endIdx=(totalSize)>size?size:(totalSize); 
            String name=fileName+"_第0-0条数据"; 
            path=filePath+File.separatorChar+name+fileSuffixName; 
            HSSFWorkbook wb =new HSSFWorkbook(); 
            buildExcelDocument2003(wb, listName, beans,result,startIdx,endIdx,maxSheetSize); 
            //没有文件，创建文件 
            File file = new File(path); 
            if (!file.exists()){   
                file.createNewFile();   
            } 
            FileOutputStream out=new FileOutputStream(file); 
            wb.write(out); 
            out.close(); 
            return listFile; 
        } 
        for (int i = 0; i < size;i++) { 
            int remainder=i%totalSize; 
            if(size==0 || remainder==0){ 
                startIdx=i; 
                endIdx=(i+totalSize)>size?size:(i+totalSize); 
                String name=fileName+"_第"+(startIdx+1)+"-"+(endIdx)+"条数据"; 
                path=filePath+"/"+name+fileSuffixName; 
                HSSFWorkbook wb =new HSSFWorkbook(); 
                buildExcelDocument2003(wb, listName, beans,result,startIdx,endIdx,maxSheetSize); 
                //没有文件，创建文件 
                File file = new File(path); 
                if (!file.exists()){   
                    file.createNewFile();   
                } 
                FileOutputStream out=new FileOutputStream(file); 
                wb.write(out); 
                out.close(); 
                listFile.add(file); 
            }else if((size-i)<totalSize && i>endIdx){//最后，不满一万条 
                startIdx=i; 
                endIdx=i+totalSize; 
                String name=fileName+"_第"+(startIdx+1)+"-"+(endIdx)+"条数据"; 
                path=filePath+name+"."+fileSuffixName;//没有文件，创建文件 
                HSSFWorkbook wb =new HSSFWorkbook(); 
                buildExcelDocument2003(wb, listName, beans, result,startIdx,endIdx,maxSheetSize); 
                //没有文件，创建文件 
                File file = new File(path); 
                if (!file.exists()){   
                    file.createNewFile();   
                } 
                FileOutputStream out=new FileOutputStream(file); 
                wb.write(out); 
                out.close(); 
                listFile.add(file); 
            } 
        } 
        return listFile; 
    } 
     
    /** 
     * 03,WPS：EXCEL导出，每个EXCEL写入数据 
     * @param wb            EXCEL工作薄 
     * @param listName        列表头名称 
     * @param beans            实体集合 
     * @param result        数字字典Map集 
     * @param startIdx        数据集合，开始行 
     * @param endIdx        数据集合，结束始行 
     * @param maxSheetSize    SHEET页条数 
     * @throws Exception  
     */ 
    private static <T> void buildExcelDocument2003(HSSFWorkbook wb, List<String> listName, List<T> beans,HashMap<String,HashMap<String,String>> result,Integer startIdx,Integer endIdx,Integer maxSheetSize) throws Exception 
    { 
        int totalSize=endIdx-startIdx;//总共条数 
        try 
        { 
            CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null); 
            List titles = new ArrayList(); 
            List beanAttrNames = new ArrayList(); 
            List widthList=new ArrayList(); 
            String[] header = new String[listName.size()]; 
            List flagList=new ArrayList(); 
            boolean flagListExists=false; 
            int rows_max = 0;//标题占多少列 
            HashMap<String,String> dateMap=new HashMap<String, String>(); 
            for (int i=0;i<listName.size();i++) 
            { 
                String[] str=listName.get(i).split("&"); 
                String en_name=str[0]; 
                String zh_name=str[1]; 
                beanAttrNames.add(i,en_name); 
                titles.add(i, zh_name); 
                header[i]=zh_name; 
                if (zh_name.split("_").length > rows_max) { 
                    rows_max = zh_name.split("_").length; 
                } 
                if(str.length>2){ 
                    String flag=str[2]; 
                    flagList.add(i,flag); 
                    if(!flagListExists) 
                        flagListExists=true; 
                } 
                if(str.length>3){ 
                    widthList.add(str[3]); 
                } 
                if(str.length>4){ 
                    dateMap.put(en_name, str[4]); 
                } 
            } 
 
            PropertyDescriptor[] props = null; 
 
            int size=endIdx-startIdx; 
            HSSFSheet sheet=null; 
             
            //如果没有数据，导出表头 
            if(size==0){ 
                sheet=ExcelHeadUtils.getExcelHead2003(wb, header, "Sheet1"); 
                sheet.setDefaultRowHeight((short)350);//高度 
                setColumnWidth2003(widthList, sheet,beanAttrNames.size()); 
                return ; 
            } 
            int u=1;//用来创建每个sheet的行 
            int h=0;//用来标注每个sheet也得名字：多少行-多少行 
            for (int i = startIdx; i < endIdx ; i++) { 
                int remainder=h%maxSheetSize; 
                if(size==0 || i==startIdx || remainder==0){ 
                    u=1; 
                    int section=(h+maxSheetSize)>totalSize?totalSize:(h+maxSheetSize); 
                    sheet=ExcelHeadUtils.getExcelHead2003(wb, header, "第"+(h+1)+"-"+section+"条"); 
                    sheet.createFreezePane( 1, rows_max, 1, rows_max); 
                    sheet.setDefaultRowHeight((short)350);//高度 
                    setColumnWidth2003(widthList, sheet,beanAttrNames.size()); 
                } 
                if(props==null) 
                    props=Introspector.getBeanInfo(beans.get(0).getClass()).getPropertyDescriptors(); 
                Object bean = beans.get(i); 
                HSSFRow row = sheet.createRow(u+rows_max-1); 
                u++; 
                h++; 
                for (int j = 0; j < beanAttrNames.size(); j++) { 
                    String beanAttrName = (String)beanAttrNames.get(j); 
                    String flag=null; 
                    if(flagListExists) 
                        flag=(String)flagList.get(j); 
                    for (int k = 0; k < props.length; k++) { 
                        String propName = props[k].getName(); 
                        if (propName.equals(beanAttrName)) 
                        { 
                            String pattern=dateMap.get(beanAttrName); 
                            HSSFCell cell = row.createCell((short)j); 
                            Object cellValue = callGetter(bean, props[k],pattern); 
                            if("true".equalsIgnoreCase(flag)){ 
                                if(result!=null){ 
                                    HashMap<String,String> hash=result.get(beanAttrName); 
                                    if(hash!=null) 
                                        cellValue=hash.get(cellValue); 
                                } 
                            } 
                            if (cellValue == null) { 
                                cellValue = ""; 
                            } 
                            setExcelCellText2003(cell, cellValue.toString(),cellStyle); 
                        } 
                    } 
                } 
            } 
        } catch (Exception e) { 
            throw new Exception(e); 
        } 
    } 
     
    /** 
     * 03,WPS：EXCEL导出（直接以流的方式，写到客户端，导出的EXCEL文件只有一个） 
     * @param listName        列表头名称 
     * @param beans            实体集合 
     * @param maxSheetSize    sheet页条数量 
     * @param outputStream    客户端输出流 
     * @throws Exception 
     */ 
    public static <T> void beans2excelFile03(List<String> listName,List<T> beans,HashMap<String,HashMap<String,String>> result,String sheetName,Integer maxSheetSize, OutputStream outputStream) throws Exception{ 
        if ((listName == null) || (listName.size() == 0)) { 
            throw new Exception("listName is null when create excel document"); 
        } 
        if(maxSheetSize==null || maxSheetSize<=0){ 
            maxSheetSize=MAX_SHEET_SIZE; 
        } 
        if(sheetName==null || "".equals(sheetName.trim())){ 
            sheetName="Sheet"; 
        } 
        HSSFWorkbook wb =new HSSFWorkbook(); 
        if(maxSheetSize==null || maxSheetSize<=0) 
            maxSheetSize=MAX_SHEET_SIZE; 
        buildExcelDocument2003(wb, listName, beans,result,sheetName,maxSheetSize); 
        try { 
            wb.write(outputStream); 
            outputStream.close(); 
        } catch (IOException e) { 
            throw new Exception(e); 
        }  
    } 
 
    /** 
     * 03,WPS：EXCEL导出，EXCEL写入数据 
     * @param wb            EXCEL工作薄 
     * @param listName        列表头名称 
     * @param beans            实体集合 
     * @param maxSheetSize    sheet页条数量 
     * @throws Exception  
     */ 
    private static <T> void buildExcelDocument2003(HSSFWorkbook wb, List<String> listName, List<T> beans,HashMap<String,HashMap<String,String>> result,String sheetName,Integer maxSheetSize) throws Exception 
    { 
        try 
        { 
            CellStyle cellStyle=POIUtils.getCellStyleFont(wb,null); 
            List titles = new ArrayList(); 
            List beanAttrNames = new ArrayList(); 
            List widthList = new ArrayList(); 
            HashMap<String,String> dateMap=new HashMap<String, String>(); 
            String[] header = new String[listName.size()]; 
            int rows_max = 0;//标题占多少列 
            List flagList=new ArrayList(); 
            boolean flagListExists=false; 
            for (int i=0;i<listName.size();i++) 
            { 
                String[] str=listName.get(i).split("&"); 
                String en_name=str[0]; 
                String zh_name=str[1]; 
                beanAttrNames.add(i,en_name); 
                titles.add(i, zh_name);     
                header[i]=zh_name; 
                if (zh_name.split("_").length > rows_max) { 
                    rows_max = zh_name.split("_").length; 
                } 
                if(str.length>2){ 
                    String flag=str[2]; 
                    flagList.add(i,flag); 
                    if(!flagListExists) 
                        flagListExists=true; 
                } 
                if(str.length>3){ 
                    widthList.add(str[3]); 
                } 
                if(str.length>4){ 
                    dateMap.put(en_name, str[4]); 
                } 
            } 
            PropertyDescriptor[] props =null; 
 
            int size=beans==null?0:beans.size(); 
            HSSFSheet sheet=null; 
            //如果没有数据，导出表头 
            if(size==0){ 
                sheet=ExcelHeadUtils.getExcelHead2003(wb, header, sheetName); 
                setColumnWidth2003(widthList, sheet,beanAttrNames.size()); 
                sheet.setDefaultRowHeight((short)350);//高度 
                return ; 
            } 
            for (int i = 0; i < size ; i++) { 
                int remainder=i%maxSheetSize; 
                if(size==0 || i==0 || remainder==0){ 
                    sheet=ExcelHeadUtils.getExcelHead2003(wb, header, sheetName+(i/maxSheetSize)); 
                    sheet.createFreezePane( 1, rows_max, 1, rows_max); 
                    setColumnWidth2003(widthList, sheet,beanAttrNames.size()); 
                    sheet.setDefaultRowHeight((short)350);//高度 
                } 
                if(props==null) 
                    props= Introspector.getBeanInfo(beans.get(0).getClass()).getPropertyDescriptors(); 
                Object bean = beans.get(i); 
                HSSFRow row = sheet.createRow(remainder+rows_max); 
                for (int j = 0; j < beanAttrNames.size(); j++) { 
 
                    String beanAttrName = (String)beanAttrNames.get(j); 
                    String flag=null; 
                    if(flagListExists) 
                        flag=(String)flagList.get(j); 
                    for (int k = 0; k < props.length; k++) { 
                        String propName = props[k].getName(); 
                        if (propName.equals(beanAttrName)) 
                        { 
                            String pattern=dateMap.get(beanAttrName); 
                            HSSFCell cell = row.createCell((short)j); 
                            Object cellValue = callGetter(bean, props[k],pattern); 
                            if("true".equalsIgnoreCase(flag)){ 
                                if(result!=null){ 
                                    HashMap<String,String> hash=result.get(beanAttrName); 
                                    if(hash!=null) 
                                        cellValue=hash.get(cellValue); 
                                } 
                            } 
                            if (cellValue == null) { 
                                cellValue = ""; 
                            } 
                            setExcelCellText2003(cell, cellValue.toString(),cellStyle); 
                        } 
                    } 
                } 
            } 
        } catch (Exception e) { 
            throw new Exception(e); 
        } 
    } 
 
    /** 
     * 03,WPS：EXCEL导出，单元格设置 
     * @param cell        单元格对象 
     * @param text        单元格文本内容 
     * @param cellStyle    单元格格式 
     */ 
    private static void setExcelCellText2003(HSSFCell cell, Object text,CellStyle cellStyle) 
    { 
        cell.setCellValue(text.toString()); 
        cell.setCellType(1);//单元格类型 
        cell.setCellStyle(cellStyle); 
    } 
 
    /** 
     * 03,WPS：EXCEL导出，单元格宽度设置 
     * @param widthList        列宽集合 
     * @param sheet            sheet对象 
     * @param allSize        总列数 
     */ 
    private static void setColumnWidth2003(List widthList,HSSFSheet sheet,int allSize){ 
        if(widthList!=null && widthList.size()>0){ 
            int size=widthList.size(); 
            for (int i = 0; i < size; i++) { 
                try { 
                    Integer width=Integer.parseInt((String) widthList.get(i)); 
                    sheet.setColumnWidth((short) i,width*256); 
                } catch (NumberFormatException e) { 
                    continue; 
                } 
            } 
        }else{ 
            for (int i = 0; i < allSize; i++) { 
                try { 
                    sheet.setColumnWidth((short) i,COLUMN_WIDTH_WORD*256); 
                } catch (NumberFormatException e) { 
                    continue; 
                } 
            } 
        } 
    } 
 
    /** 
     * 根据反射，获取实体属性的值 
     * @param target    实体属性 
     * @param prop        反射调用类 
     * @param pattern    日期格式 
     * @return             
     */ 
    private static Object callGetter(Object target, PropertyDescriptor prop,String pattern) { 
        Object o = null; 
        if (prop.getReadMethod() != null) { 
            try { 
                o = prop.getReadMethod().invoke(target, null); 
                if (Date.class.equals(prop.getPropertyType())) { 
                    if(pattern!=null && !"".equals(pattern)){ 
                        try { 
                            o = new SimpleDateFormat(pattern).format(o); 
                        } catch (Exception e) { 
                            o = new SimpleDateFormat(PATTERN).format(o); 
                        } 
                    }else{ 
                        o = formatDate(o); 
                    } 
                } 
            } catch (Exception e) { 
                o = null; 
            } 
        } 
        return o; 
    } 
     
    /** 
     * 日期转换 
     * @param date 
     * @return 字符串的日期 
     */ 
    private static String formatDate(Object date) { 
        if(date==null) 
            return ""; 
        String dateStr = new SimpleDateFormat(DATE_HH_PATTERN).format(date); 
        if("00:00:00".equals(dateStr)){ 
            return new SimpleDateFormat(DATE_PATTERN).format(date); 
        } 
        return new SimpleDateFormat(PATTERN).format(date); 
    } 
}