/**
* poi导出多个excel文件并压缩层.zip
* 参数 startnum 开始编号
* endnum 结束编号
* 通过输入的编号范围查询出需要导出的数据
* */
@RequestMapping(value = "/exportFixByMonths", method = RequestMethod.GET)
public void exportFixByMonths(ActivitiesBean bean,HttpServletResponse response,HttpServletRequest request,
@RequestParam String startnum,@RequestParam String endnum) throws IOException {
int STARTNUM= Integer.parseInt(startnum);
int ENDNUM=Integer.parseInt(endnum);
List<File> srcfile = new ArrayList<File>(); //声明一个集合，用来存放多个Excel文件路径及名称
for(int i=STARTNUM;i<=ENDNUM;i++){
bean.setCOMMITTEE(i);
// 文件模板路径
String rootpath = ((HttpServletRequest) request).getSession().getServletContext().getRealPath("/muban");
File file=new File(rootpath + File.separator + "tongjis.xlsx");
// 新的文件名
String filename="测试表" + new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date()) + ".xlsx";
File newFile = ExcelUtil.createNewFile(filename,file);
// 新文件写入数据，并下载*****************************************************
InputStream is = null;
XSSFWorkbook workbook = null;
//HSSFWorkbook workbook=null;
XSSFSheet sheet = null;
try {
is = new FileInputStream(newFile);// 将excel文件转为输入流
workbook = new XSSFWorkbook(is);// 创建个workbook，
//workbook = new HSSFWorkbook(is);// 创建个workbook，
// 获取第一个sheet
sheet = workbook.getSheetAt(0);
} catch (Exception e1) {
e1.printStackTrace();
}
if (sheet != null) {
try {
// 写数据
FileOutputStream fos = new FileOutputStream(newFile);
XSSFRow row = sheet.getRow(1);
if (row == null) {
row = sheet.createRow(1);
}
XSSFCell cell = row.getCell(0);
if (cell == null) {
cell = row.createCell(0);
}
//根据id查询数据
TestBean testBean = activitiesService.queryceshi(bean);
if(testBean!=null && !"".equals(testBean)){
//姓名
String committeename=testBean.getCOMMITTEENAME()!= null? testBean.getCOMMITTEENAME()+" ":" ";
//
String cppccsession=testBean.getCPPCCSESSION()!= null? testBean.getCPPCCSESSION()+" ":" ";
//职位
String positions=testBean.getPOSITIONS()!= null? testBean.getPOSITIONS()+" ":" ";
cell = sheet.getRow(1).getCell(0);
cell.setCellValue(committeename);
cell = sheet.getRow(1).getCell(1);
cell.setCellValue(cppccsession);
cell = sheet.getRow(1).getCell(2);
cell.setCellValue(i);
cell = sheet.getRow(2).getCell(3);
cell.setCellValue(positions);
}
workbook.write(fos);
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != is) {
is.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
srcfile.add(newFile);
}
}
//指定磁盘目录
String strpath=SysParamInit.zipPath+File.separator;//例如D://file
//指定.zip格式和名称
String pathname=new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date()) + ".zip";
File zipfile = new File(strpath+pathname);
//压缩多个excel文件为.zip格式并删除
ExcelUtil.zipFiles(srcfile,zipfile);
ExcelUtil.deleteFiles(srcfile);
//下载.zip格式文件并删除
ExcelUtil.downFile(response,strpath,pathname);
ExcelUtil.deleteZip(zipfile);
}
/**
* 将多个Excel打包成zip文件
* @param srcfile
* @param zipfile
*/
public static void zipFiles(List<File> srcfile, File zipfile) {
byte[] buf = new byte[1024];
try {
// Create the ZIP file
ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));
// Compress the files
for (int i = 0; i < srcfile.size(); i++) {
File file = srcfile.get(i);
FileInputStream in = new FileInputStream(file);
// Add ZIP entry to output stream.
out.putNextEntry(new ZipEntry(file.getName()));
// Transfer bytes from the file to the ZIP file
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
// Complete the entry
out.closeEntry();
in.close();
}
// Complete the ZIP file
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 删除多个文件方法
*
* @param srcfile
*/
public static void deleteFiles(List<File> srcfile) {
for (File file : srcfile) {
if (file.exists()) {
file.delete();
}
}
}
public static void downFile(HttpServletResponse response,String serverPath, String str) {
try {
String path = serverPath + str;
File file = new File(path);
if (file.exists()) {
InputStream ins = new FileInputStream(path);
BufferedInputStream bins = new BufferedInputStream(ins);// 放到缓冲流里面
OutputStream outs = response.getOutputStream();// 获取文件输出IO流
BufferedOutputStream bouts = new BufferedOutputStream(outs);
response.setContentType("application/x-download");// 设置response内容的类型
response.setHeader(
"Content-disposition",
"attachment;filename="
+ URLEncoder.encode(str, "GBK"));// 设置头部信息
int bytesRead = 0;
byte[] buffer = new byte[8192];
//开始向网络传输文件流
while ((bytesRead = bins.read(buffer, 0, 8192)) != -1) {
bouts.write(buffer, 0, bytesRead);
}
bouts.flush();// 这里一定要调用flush()方法
ins.close();
bins.close();
outs.close();
bouts.close();
} else {
response.sendRedirect("../error.jsp");
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 删除zip
*/
public static void deleteZip(File path) {
if (path.exists()) {
path.delete();
}
}
