Java 压缩Excel文件生成.zip文件

bosschen

浏览: 197982 次
性别:
来自: 北京

最近访客更多访客>>

冰~水

hiroada

cunzhangok

zincal8

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (143)

社区版块

存档分类

以下第一种方法转自：https://www.iteye.com/blog/zhouhaitao-1864743

首先创建文件目录，然后生成Excel文件到创建的目录下，

通过IO流压缩Excel文件成zip文件到指定目录，最后删除指定目录下所有的Excel文件。

具体代码如下：

package pack.java.io.demo;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * zip压缩文件实例
 * add by 周海涛
 * @author Administrator
 *
 */
public class ZipDemo {

	/**
	 * @param args
	 * @throws IOException 
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	public static void main(String[] args) throws RowsExceededException, WriteException, IOException {
		String path = "C:/document/excel";
		//创建文件夹;
		createFile(path);
		//创建Excel文件;
		createExcelFile(path);
		//生成.zip文件;
		craeteZipPath(path);
		//删除目录下所有的文件;
		File file = new File(path);
		//删除文件;
		deleteExcelPath(file);
		//重新创建文件;
		file.mkdirs();
	}
	
	/**
	 * 创建文件夹;
	 * @param path
	 * @return
	 */
	public static String createFile(String path){
		File file = new File(path);
		//判断文件是否存在;
		if(!file.exists()){
			//创建文件;
			boolean bol = file.mkdirs();
			if(bol){
				System.out.println(path+" 路径创建成功!");
			}else{
				System.out.println(path+" 路径创建失败!");
			}
		}else{
			System.out.println(path+" 文件已经存在!");
		}
		return path;
	}
	
	/**
	 * 在指定目录下创建Excel文件;
	 * @param path
	 * @throws IOException 
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	public static void createExcelFile(String path) throws IOException, RowsExceededException, WriteException{
		for(int i =0;i<3;i++){
			//创建Excel;
			WritableWorkbook workbook = Workbook.createWorkbook(new File(path+"/" + new SimpleDateFormat("yyyyMMddHHmmsss").format(new Date() )+"_"+(i+1)+".xls"));
			//创建第一个sheet文件;
			WritableSheet sheet = workbook.createSheet("导出Excel文件", 0);
			//设置默认宽度;
			sheet.getSettings().setDefaultColumnWidth(30);
			
			//设置字体;
			WritableFont font1 = new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);

			WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
			//设置背景颜色;
			cellFormat1.setBackground(Colour.BLUE_GREY);
			//设置边框;
			cellFormat1.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
			//设置自动换行;
			cellFormat1.setWrap(true);
			//设置文字居中对齐方式;
			cellFormat1.setAlignment(Alignment.CENTRE);
			//设置垂直居中;
			cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
			//创建单元格
			Label label1 = new Label(0, 0, "第一行第一个单元格(测试是否自动换行!)",cellFormat1);
			Label label2 = new Label(1, 0, "第一行第二个单元格",cellFormat1);
			Label label3 = new Label(2, 0, "第一行第三个单元格",cellFormat1);
			Label label4 = new Label(3, 0, "第一行第四个单元格",cellFormat1);
			//添加到行中;
			sheet.addCell(label1);
			sheet.addCell(label2);
			sheet.addCell(label3);
			sheet.addCell(label4);
			
			//给第二行设置背景、字体颜色、对齐方式等等;
			WritableFont font2 = new WritableFont(WritableFont.ARIAL,14,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLUE2);
			WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
			cellFormat2.setAlignment(Alignment.CENTRE);
			cellFormat2.setBackground(Colour.PINK);
			cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
			cellFormat2.setWrap(true);

			//创建单元格;
			Label label11= new Label(0,  1, "第二行第一个单元格(测试是否自动换行!)",cellFormat2);
			Label label22 = new Label(1, 1, "第二行第二个单元格",cellFormat2);
			Label label33 = new Label(2, 1, "第二行第三个单元格",cellFormat2);
			Label label44 = new Label(3, 1, "第二行第四个单元格",cellFormat2);

			sheet.addCell(label11);
			sheet.addCell(label22);
			sheet.addCell(label33);
			sheet.addCell(label44);

			//写入Excel表格中;
			workbook.write();
			//关闭流;
			workbook.close();
		}
	}
	
	/**
	 * 生成.zip文件;
	 * @param path
	 * @throws IOException 
	 */
	public static void craeteZipPath(String path) throws IOException{
		ZipOutputStream zipOutputStream = null;
		File file = new File(path+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".zip");
		zipOutputStream = new ZipOutputStream(new BufferedOutputStream(new FileOutputStream(file)));
		File[] files = new File(path).listFiles();
		FileInputStream fileInputStream = null;
		byte[] buf = new byte[1024];
		int len = 0;
		if(files!=null && files.length > 0){
			for(File excelFile:files){
				String fileName = excelFile.getName();
				fileInputStream = new FileInputStream(excelFile);
				//放入压缩zip包中;
				zipOutputStream.putNextEntry(new ZipEntry(path + "/"+fileName));
				
				//读取文件;
				while((len=fileInputStream.read(buf)) >0){
					zipOutputStream.write(buf, 0, len);
				}
				//关闭;
				zipOutputStream.closeEntry();
				if(fileInputStream != null){
					fileInputStream.close();
				}
			}
		}
		
		if(zipOutputStream !=null){
			zipOutputStream.close();
		}
	}
	
	/**
	 * 删除目录下所有的文件;
	 * @param path
	 */
	public static boolean deleteExcelPath(File file){
		String[] files = null;
		if(file != null){
			files = file.list();
		}
		
		if(file.isDirectory()){
			for(int i =0;i<files.length;i++){
				boolean bol = deleteExcelPath(new File(file,files[i]));
				if(bol){
					System.out.println("删除成功!");
				}else{
					System.out.println("删除失败!");
				}
			}
		}
		return file.delete();
	}
}

另一种方法：

//测试  http://localhost:8080/admin/test/test/poizip
    @RequestMapping(value = "/poizip")
    public void poizip(HttpServletResponse response) throws IOException {
        //response 输出流
        ServletOutputStream out = response.getOutputStream();
        //压缩输出流
        ZipOutputStream zipOutputStream = new ZipOutputStream(out);
        try {
            for (int i = 0; i < 6; i++) {
                //创建工作簿
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet = wb.createSheet("sheet" + i);
                HSSFRow row = sheet.createRow(0);
                HSSFCell cell = row.createCell(0);
                cell.setCellValue("内容" + i);
                response.setContentType("application/octet-stream; charset=utf-8");
                response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode("测试.zip"));
                //重点开始,创建压缩文件
                ZipEntry z = new ZipEntry(i + ".xls");
                zipOutputStream.putNextEntry(z);
                //写入一个压缩文件
                wb.write(zipOutputStream);
            }
            zipOutputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //注意关闭顺序，否则可能文件错误
            if (zipOutputStream != null) {
                zipOutputStream.close();
            }
            if (out != null) {
                out.close();
            }
        }
    }

第三种方法：

@ResponseBody
    @RequestMapping(value = "/xxx")
    public BaseResp unDirectExport(Date date, HttpServletResponse response) throws Exception {
        String fileName = xxx;
        ResponseUtil.setMultipartHeader(response, fileName);
        List<T> list = xxxService.query(xx);
      
        //按某个条件分组
        Map<String, List<T>> map = list.stream().collect(Collectors.groupingBy(T::getXXX));
        exportExcel(response, map);
        return BaseResp.succResp();
    }

    private void exportExcel(HttpServletResponse response, Map<String, List<T>> map) throws IOException {
        //Excel sheet 标题
        String[] headers = {""};
        ServletOutputStream outputStream = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream);
        try {
            map.forEach((k, v) -> {
                //新建一个Excel 并设置下sheet头
                HSSFWorkbook workbook = createExcelAndSetHeaders(headers, k);
                //向sheet中 继续填充对象的数据
                setSheetCellValue(workbook.getSheet(k), v);
                try {
                    //重点开始，创建压缩文件
                    ZipEntry zipEntry = new ZipEntry(k + ".xls");
                    zipOutputStream.putNextEntry(zipEntry);
                } catch (IOException e) {
                    logger.error("向XXX压缩包中添加Excel失败");
                    throw new Exception("向XXX压缩包中添加Excel失败");
                }
                try {
                    //写入一个压缩文件
                    workbook.write(zipOutputStream);
                } catch (IOException e) {
                    logger.error("向zipOutputStream中写入流数据失败");
                    throw new Exception("向zipOutputStream中写入流数据失败");
                }
            });
            zipOutputStream.flush();
        } catch (Exception e) {
            //重复丢出异常，有点多余，但是为了套上try catch finally 关闭数据流
            logger.error("导XXX失败,原因" + e.getErrorCode());
            throw new Exception("导出XXX结算数据失败，原因：" + e.getErrorCode());
        } finally {
            //关闭数据流，注意关闭的顺序
            zipOutputStream.close();
            outputStream.close();
        }
    }

    private HSSFWorkbook createExcelAndSetHeaders(String[] headers, String sheetName) {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet hssfSheet = hssfWorkbook.createSheet(sheetName);
        HSSFRow row0 = hssfSheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cellHeader = row0.createCell(i);
            cellHeader.setCellValue(headers[i]);
        }
        return hssfWorkbook;
    }

    private void setSheetCellValue(HSSFSheet hssfSheet, List<T> dtos) {
        for (T dto : dtos) {
            //从当前sheet页的最后一行后新增一行，开始填充数据
            HSSFRow row = hssfSheet.createRow(hssfSheet.getLastRowNum() + 1);
            int count = -1;
            row.createCell(++count).setCellValue(dto.getXXX);
            row.createCell(++count).setCellValue(dto.getXXX);
            row.createCell(++count).setCellValue(dto.getXXX);
            row.createCell(++count).setCellValue(dto.getXXX);
            row.createCell(++count).setCellValuedto.getXXX);
        }
    }

import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

/**
 * @author zhaojq
 * @email zhaojq@tsintergy.com
 * @date 2019 2019/10/14 17:11
 */
public class ResponseUtil {

    private ResponseUtil() {
    }
    public static void setMultipartHeader(HttpServletResponse response, String fileName)    {
        try {
            response.setCharacterEncoding("utf-8");
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName, "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            throw new BusinessException("文件名编码异常");
        }
    }
}

分享到：