Java操作POI批量导出多个excle，打压缩包

1、前端js

function exportRecord(){  
    var browseUrl=$("#browseUrl").val();  
      if(browseUrl == null || browseUrl == ""){  
          $.messager.show({  
                title : '提示',  
                msg : '浏览资源ID,不能为空！'  
            });  
            return ;  
      }else{  
      var data = $.toJSON(getCustomerSearch());  
      window.location.href="statResourceBrowseAction!exportStatResourceBrowse?queryJson="+data+"&excelTatol="+excelTatol;  
      }  
}  

二、Java请求处理

 public void exportStatResourceBrowse() {  
        ExportToExcelUtil<StatResourceBrowse> excelUtil = new ExportToExcelUtil<StatResourceBrowse>();  
        // 导出总记录数  
        excelTatol = request.getParameter("excelTatol") == null ? 10 : Integer.parseInt(request.getParameter("excelTatol"));  
        OutputStream out = null;  
        try {  
            out = response.getOutputStream();  
            excelUtil.setResponseHeader(response,"Excel表表名");  
            String[] headers = { "列1",  "列2", "列3", "列4","列5","列6","列7"};  
            String[] columns = { "Id", "Title","areas", "province","hospital","Level","Source"};  
  
            List<StatResourceBrowse> dataset = service.getList(getQueryJsonKeywordObject());  
            excelUtil.exportExcel( headers, columns, dataset, out, request, "");  
        } catch (Exception e1) {  
            e1.printStackTrace();  
        } finally {  
            try {  
                out.flush();  
                out.close();  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
        }  
    }  

三、poi导出Excel工具类

import java.io.File;  
import java.io.FileInputStream;  
import java.io.FileOutputStream;  
import java.io.IOException;  
import java.io.OutputStream;  
import java.lang.reflect.Method;  
import java.text.SimpleDateFormat;  
import java.util.ArrayList;  
import java.util.Collection;  
import java.util.Date;  
import java.util.List;  
import java.util.regex.Matcher;  
import java.util.regex.Pattern;  
import java.util.zip.ZipEntry;  
import java.util.zip.ZipOutputStream;  
  
import javax.servlet.http.HttpServletRequest;  
import javax.servlet.http.HttpServletResponse;  
  
import org.apache.poi.hssf.usermodel.HSSFCell;  
import org.apache.poi.hssf.usermodel.HSSFCellStyle;  
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;  
import org.apache.poi.hssf.usermodel.HSSFFont;  
import org.apache.poi.hssf.usermodel.HSSFPatriarch;  
import org.apache.poi.hssf.usermodel.HSSFRichTextString;  
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.hssf.util.HSSFColor;  
import org.apache.poi.hssf.util.Region;  
import org.apache.poi.ss.usermodel.Workbook;  
import org.compass.core.util.CollectionUtils;  
  
/**  
* 2015-4-29  
* DES:POI导出Excel 
* author:JiBaoLe  
*/   
public class ExportToExcelUtil<T> {    
    //每次设置导出数量  
    public static int  NUM=5000;  
    public static String title="";  
     
    /**  
     * 导出Excel的方法  
     * @param title excel中的sheet名称  
     * @param headers 表头  
     * @param result 结果集  
     * @param out 输出流  
     * @param pattern 时间格式  
     * @throws Exception  
     */     
    public void exportExcel( String[] headers,String[] columns, List<T> result, OutputStream out,HttpServletRequest request, String pattern) throws Exception{     
          
        File zip = new File(request.getRealPath("/files") + "/" +getFileName() + ".zip");// 压缩文件  
          
        int n=0;  
        if (!CollectionUtils.isEmpty(result)) {  
            if (result.size() % NUM == 0) {  
                n = result.size() / NUM;  
            } else {  
                n = result.size() / NUM + 1;  
            }  
        }else{  
            n=1;  
        }  
        List<String> fileNames = new ArrayList();// 用于存放生成的文件名称s  
        //文件流用于转存文件  
          
        for (int j = 0; j < n; j++) {  
            Collection<T> result1=null;  
        //切取每5000为一个导出单位，存储一个文件  
        //对不足5000做处理；  
            if (!CollectionUtils.isEmpty(result)) {  
                if (j == n - 1) {  
                    if (result.size() % NUM == 0) {  
                        result1 = result.subList(5000 * j, 5000 * (j + 1));  
                    } else {  
                        result1 = result.subList(5000 * j,  
                                5000 * j + result.size() % NUM);  
                    }  
                } else {  
                    result1 = result.subList(5000 * j, 5000 * (j + 1));  
                }  
            }  
        // 声明一个工作薄     
            Workbook workbook = new HSSFWorkbook();  
        // 生成一个表格     
        HSSFSheet sheet = (HSSFSheet) workbook.createSheet(title);     
        // 设置表格默认列宽度为18个字节     
        sheet.setDefaultColumnWidth((short)18);     
             
          
        String file = request.getRealPath("/files") + "/" + getFileName() + "-" +j+ ".xls";  
  
        fileNames.add(file);  
          
        FileOutputStream o = new FileOutputStream(file);  
             
        // 生成一个样式     
        HSSFCellStyle style = (HSSFCellStyle) workbook.createCellStyle();     
        // 设置这些样式     
        style.setFillForegroundColor(HSSFColor.GOLD.index);     
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);     
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);     
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);     
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);     
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);     
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);     
        // 生成一个字体     
        HSSFFont font = (HSSFFont) workbook.createFont();     
        font.setColor(HSSFColor.VIOLET.index);     
        //font.setFontHeightInPoints((short) 12);     
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);     
        // 把字体应用到当前的样式     
        style.setFont(font);     
             
        // 指定当单元格内容显示不下时自动换行     
        style.setWrapText(true);     
           
        // 声明一个画图的顶级管理器    
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();   
        
        // 产生表格标题行     
        //表头的样式   
        HSSFCellStyle titleStyle = (HSSFCellStyle) workbook.createCellStyle();// 创建样式对象   
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中   
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中   
        // 设置字体   
        HSSFFont titleFont = (HSSFFont) workbook.createFont(); // 创建字体对象   
        titleFont.setFontHeightInPoints((short) 15); // 设置字体大小   
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体   
      //  titleFont.setFontName("黑体"); // 设置为黑体字   
        titleStyle.setFont(titleFont);   
        sheet.addMergedRegion(new Region(0,(short)0,0,(short)(headers.length-1)));//指定合并区域    
        HSSFRow rowHeader = sheet.createRow(0);     
        HSSFCell cellHeader = rowHeader.createCell((short)0);   //只能往第一格子写数据，然后应用样式，就可以水平垂直居中   
        HSSFRichTextString textHeader = new HSSFRichTextString(title);     
        cellHeader.setCellStyle(titleStyle);   
        cellHeader.setCellValue(textHeader);   
           
        HSSFRow row = sheet.createRow(1);     
        for (int i = 0; i < headers.length; i++) {     
            HSSFCell cell = row.createCell((short)i);     
            cell.setCellStyle(style);     
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);     
            cell.setCellValue(text);     
         }     
         // 遍历集合数据，产生数据行     
         if(result1 != null){     
             int index = 2;     
             for(T t:result1){    
                 row = sheet.createRow(index);     
                 index++;   
                 for(short i = 0; i < columns.length; i++) {   
                     HSSFCell cell = row.createCell(i);   
                     String fieldName = columns[i];   
                     String getMethodName = "get"   
                         + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);   
                     Class tCls = t.getClass();   
                     Method getMethod = tCls.getMethod(getMethodName, new Class[]{});   
                     Object value = getMethod.invoke(t, new Class[]{});   
                     String textValue = null;   
                     if(value == null) {   
                         textValue = "";   
                     }else if (value instanceof Date) {   
                         Date date = (Date) value;   
                         SimpleDateFormat sdf = new SimpleDateFormat(pattern);   
                          textValue = sdf.format(date);   
                      }  else if (value instanceof byte[]) {   
                         // 有图片时，设置行高为60px;   
                         row.setHeightInPoints(60);   
                         // 设置图片所在列宽度为80px,注意这里单位的一个换算   
                         sheet.setColumnWidth(i, (short) (35.7 * 80));   
                         byte[] bsValue = (byte[]) value;   
                         HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,   
                               1023, 255, (short) 6, index, (short) 6, index);   
                         anchor.setAnchorType(2);   
                         patriarch.createPicture(anchor, workbook.addPicture(   
                               bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));   
                      } else{   
                         //其它数据类型都当作字符串简单处理   
                         textValue = value.toString();   
                      }   
                        
                     if(textValue!= null){   
                         Pattern p = Pattern.compile("^//d+(//.//d+)?$");     
                         Matcher matcher = p.matcher(textValue);   
                         if(matcher.matches()){   
                            //是数字当作double处理   
                            cell.setCellValue(Double.parseDouble(textValue));   
                         }else{   
                            HSSFRichTextString richString = new HSSFRichTextString(textValue);   
                            cell.setCellValue(richString);   
                         }   
                      }   
                 }   
             }        
         }     
         workbook.write(o);    
         File srcfile[] = new File[fileNames.size()];  
        for (int i = 0, n1 = fileNames.size(); i < n1; i++) {  
            srcfile[i] = new File(fileNames.get(i));  
        }  
        ZipFiles(srcfile, zip);  
        FileInputStream inStream = new FileInputStream(zip);  
        byte[] buf = new byte[4096];  
        int readLength;  
        while (((readLength = inStream.read(buf)) != -1)) {  
            out.write(buf, 0, readLength);  
        }  
        inStream.close();  
        }  
     }     
    //获取文件名字  
    public static String getFileName(){  
        // 文件名获取  
        Date date = new Date();  
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");  
        String f = title + format.format(date);  
        return f;  
    }  
    //压缩文件  
    public static void ZipFiles(java.io.File[] srcfile, java.io.File zipfile) {  
        byte[] buf = new byte[1024];  
        try {  
            ZipOutputStream out = new ZipOutputStream(new FileOutputStream(  
                    zipfile));  
            for (int i = 0; i < srcfile.length; i++) {  
                FileInputStream in = new FileInputStream(srcfile[i]);  
                out.putNextEntry(new ZipEntry(srcfile[i].getName()));  
                int len;  
                while ((len = in.read(buf)) > 0) {  
                    out.write(buf, 0, len);  
                }  
                out.closeEntry();  
                in.close();  
            }  
            out.close();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
      
    /** 设置响应头 */  
    public void setResponseHeader(HttpServletResponse response,String fileName) {  
        try {  
            this.title=fileName;  
            response.reset();// 清空输出流  
            response.setContentType("application/octet-stream;charset=UTF-8");  
            response.setHeader("Content-Disposition", "attachment;filename="  
                    +new String(this.title.getBytes("GB2312"), "8859_1")  
                    + ".zip");  
            response.addHeader("Pargam", "no-cache");  
            response.addHeader("Cache-Control", "no-cache");  
        } catch (Exception ex) {  
            ex.printStackTrace();  
        }  
    }  
 }     

至于所导出的Excel的样式，可以在poi中进行调整，获得自己想要的Excel表格！