多excel打包成zip是利用servlet进行的:
1.在jsp页面:首先我们要调用servlet后台:
window.location = ApBase.contextPath+'/servlet/ReportServlet?reportType=loginReport&start_date='+$("#startDate").val()+'&end_date='+$("#endDate").val()+'&lan_id='+$("#lan_id").val();
以上代码是交互servlet后台(web.xml)自己配一下
2.ReportServlet:
package com.hyz.appportal.inf.report.servlet;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import com.hyz.appportal.config.AppPortalConst;
import com.hyz.appportal.inf.report.model.LoginReportRequest;
import com.hyz.appportal.inf.report.service.LoginReportService;
import com.hyz.appportal.inf.statistics.model.StatisticsRequest;
import com.hyz.framework.service.ServiceFactory;
import com.hyz.util.ZipUtil;
public class ReportServlet extends HttpServlet{
/**
* 日志
*/
private Logger log = LogManager.getLogger(ReportServlet.class);
private String fileName;
private String reportType;
private String start_date="";
private String end_date="";
private String lan_id = "";
private static int REPORTPAGECOUNT = 100000;
public ReportServlet() {
super();
}
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("GBK");
response.setContentType("text/html; charset=GBK");
//得到生成的是什么报表
reportType = request.getParameter("reportType");
//得到开始时间
start_date = request.getParameter("start_date");
end_date = request.getParameter("end_date");
lan_id = request.getParameter("lan_id");
// 文件名获取
Date date = new Date();
SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
String f = reportType + format.format(date);
this.fileName = f;
setResponseHeader(response);
OutputStream out = null;
try {
out = response.getOutputStream();
if(!reportType.equals("orderReport")){
toLoginExcel(request,REPORTPAGECOUNT,f,out);
}else{
toOrderExcel(request, REPORTPAGECOUNT, f, out);
}
} catch (Exception e1) {
e1.printStackTrace();
} finally {
try {
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/** 设置响应头 */
public void setResponseHeader(HttpServletResponse response) {
try {
response.setContentType("application/octet-stream;charset=GBK");
response.setHeader("Content-Disposition", "attachment;filename="
+ java.net.URLEncoder.encode(this.fileName, "GBK")
+ ".zip");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
public void init() throws ServletException {
// Put your code here
}
public void toLoginExcel( HttpServletRequest request,
int length, String f, OutputStream out) throws Exception {
try {
log.debug("登录量报表生产成");
List<String> fileNames = new ArrayList<String>();// 用于存放生成的文件名称s
File zip = new File(AppPortalConst.REPORT_PATH + "/" + f + ".zip");// 压缩文件
for(int loginCount=0;loginCount<3;loginCount++){
LoginReportRequest loginRequest = new LoginReportRequest();
loginRequest.setStart_date(start_date);
loginRequest.setEnd_date(end_date);
loginRequest.setLan_id(lan_id);
List<Map<String, Object>> list;
String filename;
if(loginCount == 0){
//查询所有的lan
list = ServiceFactory.invokeService("loginReportDao", "loginReportByAllLan",loginRequest);
filename = "登录报表-本地网报表";
}else if(loginCount == 1){
//查询相关的市县
list = ServiceFactory.invokeService("loginReportDao", "loginReportByRegion",loginRequest);
filename = "登录报表-市县报表";
}else{
//查询相关的用户
list = ServiceFactory.invokeService("loginReportDao", "loginReportByCustDetail",loginRequest);
filename = "登录报表-用户信息报表";
}
// 生成excel
for (int j = 0, n = list.size() / length + 1; j < n; j++) {
Workbook book = new HSSFWorkbook();
Sheet sheet = book.createSheet(filename+"-"+n);
File dir = new File(AppPortalConst.REPORT_PATH);
if (!dir.exists()) {
dir.mkdirs();
}
if (!dir.isDirectory()) {
throw new IOException("dest dir (" + dir + ") is not a folder");
}
String file = AppPortalConst.REPORT_PATH + "/" + filename +"-" +f+ "-" + j
+ ".xls";
fileNames.add(file);
FileOutputStream o = null;
try {
o = new FileOutputStream(file);
Row row = sheet.createRow(0);
if(loginCount == 0){
row.createCell(0).setCellValue("本地网");
row.createCell(1).setCellValue("登录量");
}else if(loginCount == 1){
row.createCell(0).setCellValue("市县");
row.createCell(1).setCellValue("登录量");
}else{
row.createCell(0).setCellValue("本地网");
row.createCell(1).setCellValue("市县");
row.createCell(2).setCellValue("店中商名称");
row.createCell(3).setCellValue("工号");
row.createCell(4).setCellValue("姓名");
row.createCell(5).setCellValue("手机号");
row.createCell(6).setCellValue("登录时间");
}
int m = 1;
for (int i = 1, min = (list.size() - j * length + 1) > (length + 1) ? (length + 1)
: (list.size() - j * length + 1); i < min; i++) {
m++;
Map<String, Object> map = list.get(length * (j) + i - 1);
row = sheet.createRow(i);
//填充数据
if(loginCount == 0){
row.createCell(0).setCellValue(map.get("lan_name").toString());
row.createCell(1).setCellValue(map.get("count").toString());
}else if(loginCount == 1){
row.createCell(0).setCellValue(map.get("region_name").toString());
row.createCell(1).setCellValue(map.get("count").toString());
}else{
row.createCell(0).setCellValue(map.get("lan_name").toString());
row.createCell(1).setCellValue(map.get("region_name").toString());
row.createCell(2).setCellValue(map.get("org_name").toString());
row.createCell(3).setCellValue(map.get("staff_code").toString());
row.createCell(4).setCellValue(map.get("staff_name").toString());
row.createCell(5).setCellValue(map.get("attr_value").toString());
row.createCell(6).setCellValue(map.get("request_time").toString());
}
}
CellStyle cellStyle2 = book.createCellStyle();
cellStyle2.setAlignment(CellStyle.ALIGN_CENTER);
sheet.addMergedRegion(new CellRangeAddress(m, m, 0, 3));
} catch (Exception e) {
e.printStackTrace();
}
try {
book.write(o);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
o.flush();
o.close();
}
}
}
File srcfile[] = new File[fileNames.size()];
for (int i = 0, n = fileNames.size(); i < n; i++) {
srcfile[i] = new File(fileNames.get(i));
}
ZipUtil.ZipFiles(srcfile, zip);
//ZipUtil.zip(request.getServletContext().getRealPath("/reportFiles") + "/" + f + ".zip", srcfile);
FileInputStream inStream = new FileInputStream(zip);
byte[] buf = new byte[4096];
int readLength;
while (((readLength = inStream.read(buf)) != -1)) {
out.write(buf, 0, readLength);
}
inStream.close();
//删除服务器上的文件
File dir = new File(AppPortalConst.REPORT_PATH);
File[] fileList = dir.listFiles();
for(int i = 0;i<fileList.length;i++){
fileList[i].delete();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
3.以下是zipUtil的代码(建议压缩引用apache的,可以解决中文乱码)
package com.hyz.util;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Enumeration;
import java.util.regex.Pattern;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.tools.zip.ZipEntry;
import org.apache.tools.zip.ZipFile;
import org.apache.tools.zip.ZipOutputStream;
public class ZipUtil {
private static final Log log = LogFactory.getLog(ZipUtil.class);
private static int BUF_SIZE = 1024;
private static String ZIP_ENCODEING = "GBK";
/**
* 压缩文件或文件夹
*
* @param zipFileName
* @param inputFile
* @throws Exception
*/
public void zip(String zipFileName, String inputFile) throws Exception {
zip(zipFileName, new File(inputFile));
}
/**
* 压缩文件或文件夹
*
* @param zipFileName
* @param inputFile
* @throws Exception
*/
public static void zip(String zipFileName, File inputFile) throws Exception {
// 未指定压缩文件名,默认为"ZipFile"
if (zipFileName == null || zipFileName.equals(""))
zipFileName = "ZipFile";
// 添加".zip"后缀
if (!zipFileName.endsWith(".zip"))
zipFileName += ".zip";
// 创建文件夹
String path = Pattern.compile("[\\/]").matcher(zipFileName).replaceAll(File.separator);
int endIndex = path.lastIndexOf(File.separator);
path = path.substring(0, endIndex);
File f = new File(path);
f.mkdirs();
// 开始压缩
{
ZipOutputStream zos = new ZipOutputStream(new BufferedOutputStream(new FileOutputStream(zipFileName)));
zos.setEncoding(ZIP_ENCODEING);
compress(zos, inputFile, "");
log.debug("zip done");
zos.close();
}
}
/**
* 解压缩zip压缩文件到指定目录
*
* @param unZipFileName
* @param outputDirectory
* @throws Exception
*/
public static void unZip(String unZipFileName, String outputDirectory) throws Exception {
// 创建输出文件夹对象
File outDirFile = new File(outputDirectory);
outDirFile.mkdirs();
// 打开压缩文件文件夹
ZipFile zipFile = new ZipFile(unZipFileName, ZIP_ENCODEING);
for (Enumeration entries = zipFile.getEntries(); entries.hasMoreElements();) {
ZipEntry ze = (ZipEntry) entries.nextElement();
File file = new File(outDirFile, ze.getName());
if (ze.isDirectory()) {// 是目录,则创建之
file.mkdirs();
log.debug("mkdir " + file.getAbsolutePath());
} else {
File parent = file.getParentFile();
if (parent != null && !parent.exists()) {
parent.mkdirs();
}
log.debug("unziping " + ze.getName());
file.createNewFile();
FileOutputStream fos = new FileOutputStream(file);
InputStream is = zipFile.getInputStream(ze);
inStream2outStream(is, fos);
fos.close();
is.close();
}
}
zipFile.close();
}
/**
* 压缩一个文件夹或文件对象到已经打开的zip输出流 <b>不建议直接调用该方法</b>
*
* @param zos
* @param f
* @param fileName
* @throws Exception
*/
public static void compress(ZipOutputStream zos, File f, String fileName) throws Exception {
log.debug("Zipping " + f.getName());
if (f.isDirectory()) {
// 压缩文件夹
File[] fl = f.listFiles();
zos.putNextEntry(new ZipEntry(fileName + "/"));
fileName = fileName.length() == 0 ? "" : fileName + "/";
for (int i = 0; i < fl.length; i++) {
compress(zos, fl[i], fileName + fl[i].getName());
}
} else {
// 压缩文件
zos.putNextEntry(new ZipEntry(fileName));
FileInputStream fis = new FileInputStream(f);
inStream2outStream(fis, zos);
fis.close();
zos.closeEntry();
}
}
private static void inStream2outStream(InputStream is, OutputStream os) throws IOException {
BufferedInputStream bis = new BufferedInputStream(is);
BufferedOutputStream bos = new BufferedOutputStream(os);
int bytesRead = 0;
for (byte[] buffer = new byte[BUF_SIZE]; ((bytesRead = bis.read(buffer, 0, BUF_SIZE)) != -1);) {
bos.write(buffer, 0, bytesRead); // 将流写入
}
}
/**
*
* @param srcfile 文件名数组
* @param zipfile 压缩后文件
*/
public static void ZipFiles(java.io.File[] srcfile, java.io.File zipfile) {
byte[] buf = new byte[1024];
try {
ZipOutputStream out = new ZipOutputStream(new FileOutputStream(
zipfile));
out.setEncoding("GBK");
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();
} catch (IOException e) {
e.printStackTrace();
}
}
}