Excel文件导出以及压缩下载

1、生成Excel文件

        /***
	 * 生成excel文件
	 * @param dateList:封装成map对象的list数据,map的value数据顺序和excel导出数据一致 不然会出现列数据错乱
	 * @param headerArr:excel title显示值数组
	 * @param sheetName:工作簿名称
	 * @return HSSFWorkbook:对象
	 */
	public static HSSFWorkbook down(List<Map<String,Object>> dateList, String[] headerArr,String sheetName){
		HSSFWorkbook wb = new HSSFWorkbook();
		final int maxrownum = 65535;
		int sheetnum = dateList.size()/maxrownum+1;
		for(int num=1; num<sheetnum+1; num++){
			HSSFSheet sheet = wb.createSheet(sheetName+num);
			sheet.setDefaultColumnWidth((short) 15);
			HSSFRow row = sheet.createRow((int) 0);
			HSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
			HSSFCell cell = null;
			//画出title
			for (int i = 0; i < headerArr.length; i++) {
				cell = row.createCell((short) i);
				cell.setCellValue(headerArr[i]);
				cell.setCellStyle(style);
			}
			//画出数据
			int maxdatanum = dateList.size()-(num-1)*maxrownum;
			for(int i=0; i<maxrownum&&i<maxdatanum; i++){
				row = sheet.createRow((int) i + 1);
				Map<String,Object> map = dateList.get((num-1)*maxrownum+i);
				int j = 0;
				for (Object obj : map.values()) {
					String value = (obj == null)?"":String.valueOf(obj);
						row.createCell((short) j).setCellValue(value);
					j++;
				}
			}
		}
		return wb;
	}

 

2、导出Excel文件

        /*
	 * 导出Excel
	 * @Param filePath 路径
	 * @Param fileName 文件名
	 * */
	public void export(HSSFWorkbook wb,String filePath,String fileName) throws IOException{		
			//全路径
			String filefull=filePath+File.separator+fileName;
			
			File floder=new File(filePath);
			if  (!floder.exists() && !floder.isDirectory())      
			{       
			    floder.mkdirs();    
			} 
			FileOutputStream fout = null;
		try {
			fout = new FileOutputStream(filefull);
			wb.write(fout);
			fout.flush();
			fout.close();
		}
		catch (Exception e)
		{
			fout.close();
			e.printStackTrace();
		}
	}

3、下载Excel文件

        /*
	 * 下载
	 * @Param filePath 路径
	 * @Param fileName 文件名
	 * */
	public void download(HttpServletResponse response, String filePath, String fileName) throws IOException{
            FileInputStream fis = null;
            OutputStream os = null;
           
         try{
            //全路径
	    String filefull=filePath+File.separator+fileName;	
            File file = new File(filefull);
            if (!file.exists()) {
             System.out.println("文件下载失败：文件或路径错误");
             
            }
            fis = new FileInputStream(file);
            // 取得输出流
            os = response.getOutputStream();   
            // 清空输出流
            response.reset();  
            // 设定输出文件头
            response.setHeader("Content-disposition", "attachment; filename=" + new String( fileName.getBytes("GBK"), "ISO8859_1" ));  
            response.setContentType("application/x-download");
            byte[] mybyte = new byte[8192];
            int len = 0;
            while ((len = fis.read(mybyte)) != -1) {
                os.write(mybyte, 0, len);
            }
            fis.close();
            os.close();
        }
	catch (Exception e){
	    fis.close();
	    os.close();
	    e.printStackTrace();
	}
		
   }

4、压缩Excel文件下载

压缩帮助类

import org.apache.commons.io.IOUtils;
import org.apache.tools.zip.ZipEntry;
import org.apache.tools.zip.ZipFile;
import org.apache.tools.zip.ZipOutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Enumeration;

public class ZipHelper {
	private ZipFile zipFile; 
    private ZipOutputStream zipOut; 
    private int bufSize; 
    private byte[] buf; 
    private int readedBytes; 
    private String zipDirectory = null;
    protected HttpServletRequest request = null;
	protected HttpServletResponse response = null;
    
    public ZipHelper(HttpServletRequest request, HttpServletResponse response){
        this(512); 
        this.request = request;
		this.response = response;
    } 

    public ZipHelper(int bufSize){ 
        this.bufSize = bufSize; 
        this.buf = new byte[this.bufSize]; 
    } 
     
    public void doZip(String dirOrFile){ 
    	this.zipDirectory = dirOrFile;
        File zipDir = new File(dirOrFile); 
        String zipFileName = zipDir.getPath() + ".zip"; 
        try{ 
            this.zipOut = new ZipOutputStream(new BufferedOutputStream(new FileOutputStream(zipFileName)));
            handleDir(zipDir , this.zipOut); 
            this.zipOut.close(); 
        }catch(IOException ioe){ 
        	ioe.printStackTrace();
        } 
    }

    public void doZip(String dirOrFile, String targetFullZipName){
    	this.zipDirectory = dirOrFile;
        File zipDir = new File(dirOrFile); 
        try{ 
            this.zipOut = new ZipOutputStream(new BufferedOutputStream(new FileOutputStream(targetFullZipName)));
            handleDir(zipDir , this.zipOut); 
            this.zipOut.close(); 
        }catch(IOException ioe){ 
        	ioe.printStackTrace();
        } 
    }
    
    public void handleDir(File dirOrFile, ZipOutputStream zipOut)throws IOException{
        FileInputStream fileIn; 
        if (dirOrFile.isFile()){
            fileIn = new FileInputStream(dirOrFile); 
            String fileName = dirOrFile.getName();
            this.zipOut.putNextEntry(new ZipEntry(fileName));
            while((this.readedBytes = fileIn.read(this.buf))>0){ 
                this.zipOut.write(this.buf ,0,this.readedBytes); 
            } 
            this.zipOut.closeEntry(); 
        }else{
            File[] files = dirOrFile.listFiles(); 
            if(files.length == 0){ 
                this.zipOut.putNextEntry(new ZipEntry(dirOrFile.toString() + "/")); 
                this.zipOut.closeEntry(); 
            } 
            else{ 
                for(File file : files){ 
                    if(file.isDirectory()){ 
                        handleDir(file , this.zipOut); 
                    } 
                    else{
                        if (!file.getName().endsWith(".zip")) {
                            fileIn = new FileInputStream(file);
                            String fileName = file.getName();
                                    //file.getPath().substring(this.zipDirectory.length()+1);
                            System.out.println("file.getName="+file.getName());
                            System.out.println("fileName="+file.getPath().substring(this.zipDirectory.length()+1));
                            this.zipOut.putNextEntry(new ZipEntry(fileName));
                            while((this.readedBytes = fileIn.read(this.buf))>0){
                                this.zipOut.write(this.buf ,0,this.readedBytes);
                            }
                            this.zipOut.closeEntry();
                        }
                    } 
                } 
            }         	
        }
    } 

    @SuppressWarnings("rawtypes")
    public void unZip(String zipfileName,String destinationDir){
    	destinationDir = destinationDir.replaceAll("\\\\", "/");
    	if (!destinationDir.endsWith("/")){
    		destinationDir = destinationDir + "/";
    	}
        FileOutputStream fileOut; 
        InputStream inputStream; 
        try{ 
        	File pathFile = new File(destinationDir); 
            if(!pathFile.exists()){ 
                 pathFile.mkdirs(); 
            } 
            
            this.zipFile = new ZipFile(zipfileName); 
            Enumeration entries = this.zipFile.getEntries();
            while(entries.hasMoreElements()){
                ZipEntry entry = (ZipEntry)entries.nextElement(); 
                String zipEntryName = entry.getName(); 
                inputStream = zipFile.getInputStream(entry); 
                String outPath = (destinationDir+zipEntryName).replaceAll("\\\\", "/");
                
                if (entry.isDirectory()){
                	continue;
                }
                
                File outFile = new File(outPath);
                File parentFile = outFile.getParentFile();
                if (!parentFile.exists()){
                	parentFile.mkdirs();
                }
                if (!outFile.exists()){
                	outFile.createNewFile();
                }
                fileOut = new FileOutputStream(outPath); 
                while(( this.readedBytes = inputStream.read(this.buf) ) > 0){
                     fileOut.write(this.buf ,0,this.readedBytes ); 
                } 
                fileOut.close(); 
                inputStream.close(); 
            }
            
            this.zipFile.close(); 
        }catch(IOException ioe){ 
        	ioe.printStackTrace();
        } 
    }
    
    public void downZip(String dirOrFile,String targetFullZipName) throws IOException{
        File file = new File(dirOrFile);
        FileInputStream fis;
        OutputStream outputStream;
        try {
            fis = new FileInputStream(file);
            outputStream = new BufferedOutputStream(response.getOutputStream());
            response.reset();
            targetFullZipName = targetFullZipName + ".zip";
            targetFullZipName = new String( targetFullZipName.getBytes("GBK"), "ISO8859_1" );
            response.addHeader("Content-Disposition", "attachment;filename=" + targetFullZipName);
            response.addHeader("Content-Length", "" + file.length());
            response.setContentType("application/octet-stream");
            byte [] buffer = new byte[8192];
            int len = 0;
            while ((len = fis.read(buffer)) != -1) {
                outputStream.write(buffer,0 , len);
            }
            fis.close();
            outputStream.flush();
            outputStream.close();
            IOUtils.closeQuietly(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }
    
    public void deleteZip(String dirOrFile){ 
    	this.zipDirectory = dirOrFile;
        File file = new File(dirOrFile); 
        if(file.exists()){
        	deleteFile(file);
        }
    }
    private void deleteFile(File file){
        if(file.isDirectory()){
            File[] files = file.listFiles();
            for(int i=0; i<files.length; i++){
                deleteFile(files[i]);
            }
        }
	    file.delete();
    }
}

调用文件压缩下载

//压缩文件名
String zipName = "video.xls";
//存放待压缩Excel文件的文件夹路径
String dirOrFile = tempExcelPath;
ZipHelper zipHelper = new ZipHelper(request, response);
//生成压缩文件全路径
String targetFullZipName = dirOrFile + File.separator + zipName + ".zip";
//压缩文件
zipHelper.doZip(dirOrFile, targetFullZipName);
//下载压缩文件
zipHelper.downZip(targetFullZipName, zipName);