以下第一种方法转自: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("文件名编码异常");
}
}
}