数据导出-excel，excel压缩包

①action层调用

        // 设置返回
        LinkedHashMap<String,String> excelHead = new LinkedHashMap<String, String>();
        excelHead.put("createTime", "转入时间");
        excelHead.put("loginName", "用户名");
        excelHead.put("transInAmount", "转入(元)");
        excelHead.put("transOutAmount", "申请转出(元)");
        List<MAPP> excelData = (List<MAPP>) pagination.getRows();//获得前台数据
        ExportExcelUtil<MAPP> export= new ExportExcelUtil<MAPP>(); 
        export.exportExcel(excelData, excelHead, ServletActionContext.getResponse());

②service层导出excel方法

public void exportExcel(List<T> data, LinkedHashMap<String, String> head,
            HttpServletResponse response) {
        int sheetRows = 80000;
        int sheetTotal = data.size() / sheetRows;
        BufferedOutputStream out = null;
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        for (int page = 0; page <= sheetTotal; page++) {
            int realRows = sheetRows;
            if (page == sheetTotal) {
                realRows = data.size() - page * sheetRows;
            }

            // 生成一个表格

            XSSFSheet sheet = workbook.createSheet("sheet" + (page + 1));
            // 生成一个样式
            XSSFCellStyle style = workbook.createCellStyle();
            // 设置这些样式
            /*
             * style.setFillForegroundColor(XSSFColor.SKY_BLUE.index);
             * style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
             */
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            // 生成一个字体
            XSSFFont font = workbook.createFont();
            /* font.setColor(XSSFColor.VIOLET.index); */
            font.setFontHeightInPoints((short) 12);
            font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
            // 把字体应用到当前的样式
            style.setFont(font);
            // 生成并设置另一个样式
            XSSFCellStyle style2 = workbook.createCellStyle();
            /*
             * style2.setFillForegroundColor(XSSFColor.LIGHT_YELLOW.index);
             * style2.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
             */
            style2.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            style2.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            style2.setBorderRight(XSSFCellStyle.BORDER_THIN);
            style2.setBorderTop(XSSFCellStyle.BORDER_THIN);
            style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            // 生成另一个字体
            XSSFFont font2 = workbook.createFont();
            font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
            // 把字体应用到当前的样式
            style2.setFont(font2);
            // 产生表格标题行
            XSSFRow row = sheet.createRow(0);
            int k = 0;
            for (String title : head.values()) {
                XSSFCell cell = row.createCell(k);
                cell.setCellStyle(style);
                XSSFRichTextString text = new XSSFRichTextString(title);
                cell.setCellValue(text);
                k++;
            }

            // 遍历集合数据，产生数据行
            for (int index = 0; index < realRows; index++) {
                row = sheet.createRow(index + 1);
                T t = (T) data.get(page * sheetRows + index);
                int n = 0;
                for (String fieldName : head.keySet()) {
                    XSSFCell cell = row.createCell(n);
                    cell.setCellStyle(style2);
                    String methodName = "get"
                            + fieldName.substring(0, 1).toUpperCase()
                            + fieldName.substring(1);
                    try {

                        Method method = t.getClass().getMethod(methodName,
                                new Class[] {});
                        Object value = method.invoke(t, new Object[] {});
                        if (value == null) {
                            value = "";
                        }
                        // 判断值的类型后进行强制类型转换
                        String textValue = "";
                        if (value instanceof Date) {
                            Date date = (Date) value;
                            SimpleDateFormat sdf = new SimpleDateFormat(
                                    "yyyy-MM-dd HH:mm:ss");
                            textValue = sdf.format(date);
                            cell.setCellValue(textValue);
                        } else {
                            XSSFRichTextString richString = new XSSFRichTextString(
                                    ExportExcelUtil
                                            .fromNumberToMoneyString(value));
                            cell.setCellValue(richString);
                        }
                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    n++;
                }

            }

        }
        try {
            // 设置response的Header
            response.setHeader("Content-Disposition", "attachment; filename="
                    + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())
                    + ".xlsx");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            out = new BufferedOutputStream(response.getOutputStream());
            workbook.write(out);
            // response.setContentType("application/excel;charset=utf-8");
            out.flush();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {

                if (out != null) {
                    out.close();
                }

            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

public static String fromNumberToMoneyString(Object ob) {
        try {
            String moneyString = String.format("%.2f", ob);
            String reg = "^-?\\d+\\.\\d+$";
            StringBuffer sb = new StringBuffer();
            /*
             * Pattern p=Pattern.compile(reg); Matcher matcher=
             * p.matcher(moneyString); if(matcher.matches()){
             */
            int point = moneyString.indexOf(".");
            String n1 = "", n2 = "";
            n1 = moneyString.substring(0, point);
            n2 = moneyString.substring(point + 1);
            int step = 0;
            for (int i = n1.length() - 1; i >= 0; i--) {
                char v = n1.charAt(i);
                if (step != 0 && step % 3 == 0 && v != '-') {
                    sb.append(",");
                    sb.append(v);
                } else {
                    sb.append(v);
                }
                step++;
            }
            /*
             * if(n2.length()==1){ n2= n2+"0"; } if(n2.length()>2){ int k=1;
             * for(int j=0;j<n2.length()-2;j++){ k=k*10; }
             * n2=String.valueOf(Math.round(Double.valueOf(n2)/k)); }
             */
            return sb.reverse().toString() + "." + n2;
            /* }; */
        } catch (Exception e) {
            // TODO Auto-generated catch block
            return String.valueOf(ob);
        }
    }

压缩包

 /**
         * 
         * 导出EXCEL压缩包
         * @param List<T> data
         * @param LinkedHashMap<String,String> head
         * @param HttpServletResponse response
         *
         */
         @SuppressWarnings("unchecked")
           public void exportExcelZip(List<T> data, LinkedHashMap<String,String> head,HttpServletResponse response,int rows) {
             final int MAX_ROW=30000;
             if(data==null||data.isEmpty()){
                 throw new RuntimeException("导出数据不能为空");
             }
             if(head==null||head.isEmpty()){
                 throw new RuntimeException("导出标题头不能为空"); 
             }
            if(rows<=0){
                 throw new RuntimeException("导出条数不能小于等于零"); 
            }
            if(rows> MAX_ROW){
                 throw new RuntimeException("导出条数不能大于"+MAX_ROW); 
            }
             int pageTotal=0;
             if(data.size()%rows==0){
                 pageTotal=data.size()/rows-1;
             } else {
                 pageTotal=data.size()/rows;
             }

             BufferedOutputStream out=null;
             ZipOutputStream zos=null;
             int len = 0;
              byte[] buff = new byte[2048];
              try {
                // 设置response的Header  
              response.setHeader("Content-Disposition", "attachment; filename="+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".zip");  
              response.setContentType("application/octet-stream;charset=UTF-8");  
              out=  new BufferedOutputStream(response.getOutputStream());
              zos=new ZipOutputStream(out); 
            } catch (IOException e1) {
                e1.printStackTrace();
            }

        for (int page = 0; page <= pageTotal; page++) {
            ByteArrayOutputStream     bao=new  ByteArrayOutputStream();
            int realRows = 0;
            if (page == pageTotal) {
                realRows = data.size() - page * rows;
            } else {
                realRows = rows;
            }
               // 声明一个工作薄
             XSSFWorkbook workbook = new  XSSFWorkbook();
            // 生成一个表格
             XSSFSheet sheet = workbook.createSheet();
              // 生成一个样式
             XSSFCellStyle style = workbook.createCellStyle();
              // 设置这些样式
              style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
              style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
              style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
              style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
              style.setBorderRight(XSSFCellStyle.BORDER_THIN);
              style.setBorderTop(XSSFCellStyle.BORDER_THIN);
              style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
              // 生成一个字体
              XSSFFont font = workbook.createFont();
             /* font.setColor(XSSFColor.VIOLET.index);*/
              font.setFontHeightInPoints((short) 12);
              font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
              // 把字体应用到当前的样式
              style.setFont(font);
              // 生成并设置另一个样式
              XSSFCellStyle style2 = workbook.createCellStyle();
             /* style2.setFillForegroundColor(XSSFColor.LIGHT_YELLOW.index);
              style2.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);*/
              style2.setBorderBottom(XSSFCellStyle.BORDER_THIN);
              style2.setBorderLeft(XSSFCellStyle.BORDER_THIN);
              style2.setBorderRight(XSSFCellStyle.BORDER_THIN);
              style2.setBorderTop(XSSFCellStyle.BORDER_THIN);
              style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
              style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
              // 生成另一个字体
              XSSFFont font2 = workbook.createFont();
              font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
              // 把字体应用到当前的样式
              style2.setFont(font2);    
              //产生表格标题行
              XSSFRow row = sheet.createRow(0);
              int k=0;
               for (String title:head.values()) {
                 XSSFCell cell = row.createCell(k);
                 cell.setCellStyle(style);
                 XSSFRichTextString text = new XSSFRichTextString(title);
                 cell.setCellValue(text);
                 k++;
              }
              //遍历集合数据，产生数据行
             for(int index=0;index<realRows;index++) {
                 row = sheet.createRow(index+1);
                 T t = (T) data.get(page*rows+index);
                 int n=0;
                 for (String fieldName:head.keySet()) {
                     XSSFCell cell = row.createCell(n);
                    cell.setCellStyle(style2);
                    String methodName = "get"
                           + fieldName.substring(0, 1).toUpperCase()
                           + fieldName.substring(1);
                    try {

                        Method method = t.getClass().getMethod(methodName, new Class[] {});
                        Object value = method.invoke(t, new Object[] {});
                        if(value==null){
                            value="";
                        }
                        //判断值的类型后进行强制类型转换
                        String textValue ="";
                        if (value instanceof java.sql.Date) {
                            java.sql.Date date = (java.sql.Date) value;
                               SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                textValue = sdf.format(date);
                                cell.setCellValue(textValue);
                            } else  if (value instanceof Date) {
                                   Date dateTime = (Date) value;
                                   SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    textValue = sdf.format(dateTime);
                                    cell.setCellValue(textValue);
                                } else {    
                                      XSSFRichTextString richString = new XSSFRichTextString(ExportExcelUtil.fromNumberToMoneyString(value));
                                      cell.setCellValue(richString);
                                }

                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    } 
                    n++;
                 }
              }
             try {  
                    workbook.write(bao);
                      InputStream  is=new ByteArrayInputStream(bao.toByteArray());
                     ZipEntry ze=new ZipEntry(String.valueOf(java.util.UUID.randomUUID())+"_"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".xlsx");  
                     zos.putNextEntry(ze);  
                     while ((len=is.read(buff))!=-1) {  
                            zos.write(buff, 0, len);  
                        }  
                    zos.closeEntry();
                    zos.flush();
                    if(is!=null){
                        is.close();
                    }
                    if(bao!=null){
                        bao.close();
                    }
              } catch (Exception e) {
                 e.printStackTrace();
              }
            }
              try {  
                  out.flush();
              } catch (Exception e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
              } finally {
                  try{    
                        if( zos!=null){
                              zos.close();
                        }

                    }catch(Exception e){
                        e.printStackTrace();
                    }
                    try{    
                        if(out!=null){
                            out.close();
                        }

                    }catch(Exception e){
                        e.printStackTrace();
                    }
                 } 
           }