1、2003的Excel(HSSFWorkbook)升级为2007版本(SXSSFWorkbook)的
2、多文件生成临时文件批量暂存服务器,然后再压缩文件生成zip包下载.
3、下载后文件命名兼容火狐浏览器,解决了乱码问题
ZipFileUtil工具类
package cn.tools;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Base64Utils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import static cn.tools.DateHelper.getNowDate;
@SuppressWarnings("restriction")
public class ZipFileUtil {
private static final Logger logger = LoggerFactory.getLogger(ZipFileUtil.class);
/**
* 编译下载的文件名
* @param fileName
* @param agent
* @return
* @throws IOException
*/
public static String encodeDownloadFilename(String fileName, String agent)throws IOException {
if (agent.contains("Firefox")) { // 火狐浏览器
fileName = "=?UTF-8?B?" + (new String(Base64Utils.encodeToString(fileName.getBytes("UTF-8")))) + "?=";
} else { // IE及其他浏览器
fileName = URLEncoder.encode(fileName, "utf-8");
fileName = fileName.replace("+"," ");
}
return fileName;
}
/**
* 创建文件夹;
* @param path
*/
public static void createFile(String path) {
File file = new File(path);
//判断文件是否存在;
if (!file.exists()) {
//创建文件;
file.mkdirs();
}
}
/**
* 生成.zip文件;
* @param path
* @throws IOException
*/
public static ZipOutputStream craeteZipPath(String path) throws IOException{
ZipOutputStream zipOutputStream = null;
File file = new File(path+getNowDate(DateHelper.FMT_DATE_YYYY_MM_DD)+".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();
}
}
}
return zipOutputStream;
}
/**
* //压缩文件
* @param srcfile 要压缩的文件数组
* @param zipfile 生成的zip文件对象
*/
public static void ZipFiles(java.io.File[] srcfile, File zipfile) throws Exception {
byte[] buf = new byte[1024];
FileOutputStream fos = new FileOutputStream(zipfile);
ZipOutputStream out = new ZipOutputStream(fos);
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();
fos.flush();
fos.close();
}
/**
* 删除文件夹及文件夹下所有文件
* @param dir
* @return
*/
public static boolean deleteDir(File dir) {
if (dir == null || !dir.exists()){
return true;
}
if (dir.isDirectory()) {
String[] children = dir.list();
//递归删除目录中的子目录下
for (int i=0; i<children.length; i++) {
boolean success = deleteDir(new File(dir, children[i]));
if (!success) {
return false;
}
}
}
// 目录此时为空，可以删除
return dir.delete();
}
/**
* 生成html
* @param msg
* @return
* @author zgd
* @time 2018年6月25日11:47:07
*/
public static String getErrorHtml(String msg) {
StringBuffer sb = new StringBuffer();
sb.append("<html>");
sb.append("<head>");
sb.append("<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>");
sb.append("</head>");
sb.append("<body>");
sb.append("<div id='errorInfo'> ");
sb.append("</div>");
sb.append("<script>alert('"+msg+"')</script>");
sb.append("</body>");
sb.append("</html>");
return sb.toString();
}
/**
* 设置下载excel的响应头信息
* @param response
* @param request
* @param fileName
* @throws IOException
* @author zgd
* @time 2018年6月25日11:47:07
*/
public static void setExcelHeadInfo(HttpServletResponse response, HttpServletRequest request, String fileName) {
try {
// 获取客户端浏览器的类型
String agent = request.getHeader("User-Agent");
// 对文件名重新编码
String encodingFileName = ZipFileUtil.encodeDownloadFilename(fileName, agent);
// 告诉客户端允许断点续传多线程连接下载
response.setHeader("Accept-Ranges", "bytes");
//文件后缀
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment; filename=" + encodingFileName);
} catch (IOException e) {
logger.error(Thread.currentThread().getStackTrace()[1].getMethodName() +"发生的异常是: ",e);
throw new RuntimeException(e);
}
}
/**
* 设置下载zip的响应头信息
* @param response
* @param fileName 文件名
* @param request
* @throws IOException
* @author zgd
* @time 2018年6月25日11:47:07
*/
public static void setZipDownLoadHeadInfo(HttpServletResponse response, HttpServletRequest request, String fileName) throws IOException {
// 获取客户端浏览器的类型
String agent = request.getHeader("User-Agent");
response.setContentType("application/octet-stream ");
// 表示不能用浏览器直接打开
response.setHeader("Connection", "close");
// 告诉客户端允许断点续传多线程连接下载
response.setHeader("Accept-Ranges", "bytes");
// 对文件名重新编码
String encodingFileName = ZipFileUtil.encodeDownloadFilename(fileName, agent);
response.setHeader("Content-Disposition", "attachment; filename=" + encodingFileName);
}
/**
* 将批量文件打包下载成zip
* @param request
* @param response
* @param zipName 下载的zip名
* @param files 要打包的批量文件
* @param zipPath 生成的zip路径
* @throws Exception
*/
public static void downloadZip(HttpServletRequest request, HttpServletResponse response, String zipName, List<File> files, String zipPath)throws Exception {
File srcfile[] = new File[files.size()];
File zip = new File(zipPath);
for (int i = 0; i < files.size(); i++) {
srcfile[i] = files.get(i);
}
//生成.zip文件;
FileInputStream inStream = null;
ServletOutputStream os = null;
try {
//设置下载zip的头信息
ZipFileUtil.setZipDownLoadHeadInfo(response, request, zipName);
os = response.getOutputStream();
ZipFileUtil.ZipFiles(srcfile, zip);
inStream = new FileInputStream(zip);
byte[] buf = new byte[4096];
int readLength;
while (((readLength = inStream.read(buf)) != -1)) {
os.write(buf, 0, readLength);
}
} finally {
if (inStream != null) {
inStream.close();
}
if (os != null) {
os.flush();
os.close();
}
}
}
/**
* 将数据转成多个excel文件放在项目中
*
* @param tempDir
* @throws Exception
* @author chengboying
* @time 2020年02月25日11:47:07
*/
public static List<File> getStoreOrderExcels(Map<String,SXSSFWorkbook> workbook, String tempDir) throws Exception {
//excel文件个数
ZipFileUtil.createFile(tempDir);
List<File> files = new ArrayList<File>(); //声明一个集合，用来存放多个Excel文件路径及名称
for (Map.Entry<String,SXSSFWorkbook> entry : workbook.entrySet()){
SXSSFWorkbook wb = entry.getValue();
//生成一个excel
String path = tempDir + "-" + entry.getKey() + ".xlsx";
generateExcelToPath(wb, path);
//excel添加到files中
files.add(new File(path));
}
return files;
}
/**
* 生成excel到指定路径
* @param wb
* @param path
* @throws Exception
*/
private static void generateExcelToPath(SXSSFWorkbook wb, String path) throws Exception {
FileOutputStream fos = null;
try {
fos = new FileOutputStream(path);
wb.write(fos);
} finally {
if (fos != null) {
fos.flush();
fos.close();
}
}
}
}
这里是controller层的方法
public void toStatisticExcel() throws Exception{
String beginDate = getFromRequestParameter("beginDate");
String endDate = getFromRequestParameter("endDate");
String websiteId = getFromRequestParameter("websiteId");
String options = getFromRequestParameter("options");
//用Map来存储要导出的多个SXSSFWorkbook对象，String为Excel要显示的表名
Map<String,SXSSFWorkbook> hssw = statisticExcel.statisticExcel(beginDate, endDate,websiteId,options);
AreaBean areaBean = dataAreaDao.getAreaById(areaId);
//单个Excel表不需要压缩、直接导出就好
if (hssw.size() == 1){
String keyName = getKeyOrNull(hssw);
super.downloadFile(hssw.get(keyName), areaBean.getName()+"人民政府-智能问答统计报表-"+keyName+"-"+beginDate+"～"+endDate, "xlsx");
}else {
super.poizip(hssw, areaBean.getName()+"人民政府-智能问答统计报表-"+beginDate+"～"+endDate, "zip");
}
}
这里是单个Excel下载的调用方法
/**
*
* @Title: downloadFile
* @author chengboying
* @Description: 下载文件
* @date 2020年1月25日 上午10:30:38
*/
protected void downloadFile(SXSSFWorkbook hwb, String fileName,
String extension) throws Exception {
// 获取客户端浏览器的类型
String agent = request.getHeader("User-Agent");
String downloadName = ZipFileUtil.encodeDownloadFilename(fileName,agent);
response.reset();
response.setContentType("application/x-msdownload");
response.setHeader(
"Content-Disposition",
"attachment; filename="
+ downloadName
+ "." + extension);
OutputStream out = response.getOutputStream();
hwb.write(out);
out.close();
}
这里是多个Excel压缩打包下载的调用方法
public void poizip(Map<String,SXSSFWorkbook> sxssfWorkbook, String fileName, String extension) throws IOException {
try {
String realPath = request.getSession().getServletContext().getRealPath("WEB-INF");
//创建临时文件夹保存excel
String tempDir = realPath + "/tempDir/" + fileName;
List<File> files = ZipFileUtil.getStoreOrderExcels(sxssfWorkbook,tempDir);
fileName = fileName + ".zip";
String zipPath = tempDir + "\\" + fileName;
//下载zip
ZipFileUtil.downloadZip(request, response, fileName, files, zipPath);
//删除tempDir文件夹和其中的excel和zip文件
boolean b = ZipFileUtil.deleteDir(new File(realPath + "\\tempDir"));
if (!b) {
throw new RuntimeException("tempDir文件夹及其中的临时Excel和zip文件删除失败");
}
}catch (Exception e) {
try {
if (!response.isCommitted()) {
response.setContentType("text/html;charset=utf-8");
response.setHeader("Content-Disposition", "");
String html = ZipFileUtil.getErrorHtml("下载失败");
response.getOutputStream().write(html.getBytes("UTF-8"));
}
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
Excel的SXSSFWorkbook对象数据内容生成根据自己的业务规则完成
