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

最新推荐文章于 2024-06-16 09:46:17 发布

bosschen

最新推荐文章于 2024-06-16 09:46:17 发布

阅读量2k

点赞数 1

文章标签： java excel servlet

本文链接：https://blog.csdn.net/bosschen/article/details/129415213

版权

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

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

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

具体代码如下：

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

 

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

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

 

具体代码如下：

Java代码  收藏代码
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("文件名编码异常");  
        }  
    }  
}