在浏览器端点击下载,会下载一个zip压缩包,里面包含多个Excel文件
我暂且把从程序中下载压缩包分为三种类型,即三步下载,两步下载,一步下载。三步下载是指第一步先从数据库读取数据、写成文件,然后把文件们下载到本地磁盘;第二步是把文件们打成压缩包;第三步是把压缩包读取到程序中然后响应到浏览器。两步下载是指从数据库读取数据、写成文件再打成压缩包,然后把压缩包下载到本地磁盘,这是第一步;第二步是把压缩包读取到程序中然后响应到浏览器。一步下载是指程序从数据库读取数据、写成文件、转成流和响应到浏览器,都不用写到本地磁盘,只在内存中,一步输出压缩包。
本次先以多Excel文件打成Zip压缩包为例,其他文件格式后续发表。
1.三步下载
特点:要把文件下载到本地;要把文件打成压缩包放到本地;把压缩包响应到浏览器
优点:操作简单,可以分步骤完成
难点:对WorkBook的理解
第一步:把文件们下载到本地磁盘,第一个红色高亮部分(有标注)是把数据库读到的数据写到workbook中,创建workbook文件;第二个红色高两部分是创建excel的过程;第三个红色高亮部分是把workbook写到本地磁盘;第四个红色高亮部分是写文件;
第二步:把下载到本地的excel文件们打成压缩包,第五个红色高亮部分;第六个红色高亮部分是打压缩包过程;
第三部:把压缩包响应到浏览器,第七个红色高亮部分。
这样三步下载就完成了。
private String ldpf(List<CasPayLog> payLogList,List<String> fileNameList,String filePath,HttpServletResponse response) throws Exception{
long sumAmt = 0;
int num=0;
int num2=0;
int count=0;
int count2=0;
FileInputStream fis = null;
FileOutputStream fos = null;
Workbook workbookTemp = null;
List<CasPayLog> tempLogList = new ArrayList<CasPayLog>();
List<CasPayLog> tempLogList2 = new ArrayList<CasPayLog>();
String fileNo = seqService.updateAndGetSequence(SeqServiceImpl.TRANSFER_FILE_SEQ_DAY, 4);
String fileNo2 = seqService.updateAndGetSequence(SeqServiceImpl.TRANSFER_FILE_SEQ_DAY, 4);
//银行对公文件名
String fileName = DateUtils.getCurrentDate("yyyyMMdd").substring(2)+fileNo+".xls";
//银行对私文件名
String fileName2 =DateUtils.getCurrentDate("yyyyMMdd").substring(2)+fileNo2+".xls";
for(int i=0;i<payLogList.size();i++){
CasPayLog payLog = payLogList.get(i);
//更新付款记录
CasPayLog tempPayLog = new CasPayLog();
tempPayLog.setPayDt(DateUtils.getCurrentDate("yyyyMMdd"));
tempPayLog.setChnlPaySt(1);
tempPayLog.setPayBatchNo(payLog.getPayBatchNo());
if(payLog.getOutAcntTp()==0){
tempPayLog.setPayFileNm(fileName);
}else if(payLog.getOutAcntTp()==1){
tempPayLog.setPayFileNm(fileName2);
}
tempPayLog.setRecUpdTs(new Date());
if(1!=casPayLogMapper.updateByPrimaryKeySelective(tempPayLog)){
throw new CHException("付款信息更新失败");
}
sumAmt+=payLog.getPayAmt();
if(payLog.getOutAcntTp()==0){
tempLogList.add(payLog);
count+=1;
}else if(payLog.getOutAcntTp()==1){
tempLogList2.add(payLog);
count2+=1;
}
//对公生成文件
if(count==1000){
if((i+1)!=payLogList.size()){//最后一个文件不再生成序列
fileNo = seqService.updateAndGetSequence(SeqServiceImpl.TRANSFER_FILE_SEQ_DAY, 4);
}
<span style="white-space:pre"> </span>//第一个红色高亮部分
<strong><span style="color:#ff0000;"> String fileTitle = DateUtils.getCurrentDate("yyyyMMdd").substring(2)+fileNo;
String[] titleNms={"*商户流水号","*收款方账户类型","*收款方开户机构","*开户机构支行全称","*收款方户名","*收款方账号","*金额(元)","收款方手机号码","省","市","备注"};
String[] columMethodNms={"getPayBatchNoNine","getAccountType","getBankNm","getPmsBankNm","getOutAcntNm","getOutAcntNo","getPayAmtString","getMobilePhoneNull","getProvNm","getCityNm","getChnlPayCmt"};
try {
Workbook workbook = ExcelUtil.createExcel2(2003, "联动批付第"+ ++num +"页", titleNms, columMethodNms, tempLogList);
//ExcelUtil.workbook2InputStream(response, workbook3, fileTitle, ".xlsx");
workbookTemp=null;
workbookTemp =workbook;
} catch (Exception e) {
e.printStackTrace();
}</span></strong>
fileNameList.add(fileName);
fos = new FileOutputStream(filePath+"/"+fileName, true);
<span style="white-space:pre"> </span>//第三个红色高亮部分
<span style="color:#ff0000;">FileUtil.writeBackFileExcel(workbookTemp,fos);</span>
fileName = DateUtils.getCurrentDate("yyyyMMdd").substring(2)+fileNo+".xls";
sumAmt=0;
count=0;
tempLogList = new ArrayList<CasPayLog>();
if(fos!=null){
fos.close();
}
}
//对私生成文件
if(count2==1000){
if((i+1)!=payLogList.size()){//最后一个文件不再生成序列
fileNo = seqService.updateAndGetSequence(SeqServiceImpl.TRANSFER_FILE_SEQ_DAY, 4);
}
String fileTitle = DateUtils.getCurrentDate("yyyyMMdd").substring(2)+fileNo2;
String[] titleNms={"*商户流水号","*收款方账户类型","*收款方开户机构","*收款方户名","*收款方账号","*金额(元)","是否需审核", "开户机构支行全称", "收款方证件名称","收款方证件号码", "收款方手机号码","省","市","备注"};
String[] columMethodNms={"getPayBatchNoNine","getAccountType","getBankNm","getOutAcntNm","getOutAcntNo","getPayAmtString","getCheck", "getPmsBankNm","getOutAcntOwnerIdTpNm","getOutAcntOwnerId","getMobilePhoneNull","getProvNm","getCityNm","getChnlPayCmt"};
try {
Workbook workbook2 = ExcelUtil.createExcel2(2003, "联动批付第"+ ++num2 +"页", titleNms, columMethodNms, tempLogList2);
//ExcelUtil.workbook2InputStream(response, workbook4, fileTitle, ".xlsx");
workbookTemp=null;
workbookTemp =workbook2;
} catch (Exception e) {
e.printStackTrace();
}
fileNameList.add(fileName2);
fos = new FileOutputStream(filePath+"/"+fileName2, true);
FileUtil.writeBackFileExcel(workbookTemp,fos);
fileName2 = DateUtils.getCurrentDate("yyyyMMdd").substring(2)+fileNo2+".xls";
sumAmt=0;
count2=0;
tempLogList2 = new ArrayList<CasPayLog>();
if(fos!=null){
fos.close();
}
}
//不够1000的倍数,数据结束
if((i+1)==payLogList.size()){
if(count!=0){
if((i+1)!=payLogList.size()){//最后一个文件不再生成序列
fileNo = seqService.updateAndGetSequence(SeqServiceImpl.TRANSFER_FILE_SEQ_DAY, 4);
}
String fileTitle = DateUtils.getCurrentDate("yyyyMMdd").substring(2)+fileNo;
String[] titleNms={"*商户流水号","*收款方账户类型","*收款方开户机构","*开户机构支行全称","*收款方户名","*收款方账号","*金额(元)","收款方手机号码","省","市","备注"};
String[] columMethodNms={"getPayBatchNoNine","getAccountType","getBankNm","getPmsBankNm","getOutAcntNm","getOutAcntNo","getPayAmtString","getMobilePhoneNull","getProvNm","getCityNm","getChnlPayCmt"};
try {
Workbook workbook3 = ExcelUtil.createExcel2(2003, "联动批付第"+ ++num +"页", titleNms, columMethodNms, tempLogList);
//ExcelUtil.workbook2InputStream(response, workbook3, fileTitle, ".xlsx");
workbookTemp=null;
workbookTemp =workbook3;
} catch (Exception e) {
e.printStackTrace();
}
fileNameList.add(fileName);
fos = new FileOutputStream(filePath+"/"+fileName, true);
FileUtil.writeBackFileExcel(workbookTemp,fos);
fileName = DateUtils.getCurrentDate("yyyyMMdd").substring(2)+fileNo+".xls";
sumAmt=0;
count=0;
tempLogList = new ArrayList<CasPayLog>();
if(fos!=null){
fos.close();
}
}
if(count2!=0){
if((i+1)!=payLogList.size()){//最后一个文件不再生成序列
fileNo = seqService.updateAndGetSequence(SeqServiceImpl.TRANSFER_FILE_SEQ_DAY, 4);
}
String fileTitle = DateUtils.getCurrentDate("yyyyMMdd").substring(2)+fileNo2;
String[] titleNms={"*商户流水号","*收款方账户类型","*收款方开户机构","*收款方户名","*收款方账号","*金额(元)","是否需审核","开户机构支行全称","收款方证件名称","收款方证件号码","收款方手机号码","省","市","备注"};
String[] columMethodNms={"getPayBatchNoNine","getAccountType","getBankNm","getOutAcntNm","getOutAcntNo","getPayAmtString","getCheck","getPmsBankNm","getOutAcntOwnerIdTpNm","getOutAcntOwnerId","getMobilePhoneNull","getProvNm","getCityNm","getChnlPayCmt"};
try {
Workbook workbook4 = ExcelUtil.createExcel2(2003, "联动批付第"+ ++num2 +"页", titleNms, columMethodNms, tempLogList2);
//ExcelUtil.workbook2InputStream(response, workbook4, fileTitle, ".xlsx");
workbookTemp=null;
workbookTemp =workbook4;
} catch (Exception e) {
e.printStackTrace();
}
fileNameList.add(fileName2);
fos = new FileOutputStream(filePath+"/"+fileName2, true);
FileUtil.writeBackFileExcel(workbookTemp,fos);
fileName2 = DateUtils.getCurrentDate("yyyyMMdd").substring(2)+fileNo2+".xls";
sumAmt=0;
count2=0;
tempLogList2 = new ArrayList<CasPayLog>();
if(fos!=null){
fos.close();
}
}
}
}
if(fileNameList.size()>0){//<span style="font-size:18px; white-space: pre;">第五个红色高亮部分</span>
<span style="color:#ff0000;"><strong>fileName = "联动批付划款文件"+DateUtils.getCurrentDate("yyyyMMddHHmmsss")+ ".zip";
String fileNameShort = "联动批付划款文件"+DateUtils.getCurrentDate("yyyyMMddHHmmsss");
ZipUtil.fileToZip(filePath, fileNameList, fileName);</strong></span>
//读取zip文件,并下载到浏览器
<span style="white-space:pre"> </span>//第七个红色高亮部分
<strong><span style="color:#ff0000;">File file = new File(filePath + "/" + fileName);
fis = new FileInputStream(file);
byte [] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
response.reset();
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.addHeader("Content-Length", "" + file.length());
OutputStream ous = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream");
ous.write(buffer);
ous.flush();
ous.close();</span></strong>
//ZipUtil.zipToDown(response,fis,fileName);
}
return fileName;
}
//第二个红色高亮部分
<span style="color:#ff0000;">public static Workbook createExcel2(int version,String sheetNm, String[] titleNms,
<span style="white-space:pre"> </span>String[] columMethodNms, List<?> list) throws Exception {
<span style="white-space:pre"> </span>Workbook workbook=null;
<span style="white-space:pre"> </span>if(version==2007){
<span style="white-space:pre"> </span>workbook = new XSSFWorkbook();
<span style="white-space:pre"> </span>}else{
<span style="white-space:pre"> </span>workbook = new HSSFWorkbook();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>Sheet sheet = workbook
<span style="white-space:pre"> </span>.createSheet(StringUtils.isEmpty(sheetNm) ? "sheet1" : sheetNm);
<span style="white-space:pre"> </span>CellStyle titleStyle=titleStyle(workbook);
<span style="white-space:pre"> </span>CellStyle dataStyle=dataStyle(workbook);
<span style="white-space:pre"> </span>// 标题
<span style="white-space:pre"> </span>Row titleRow = sheet.createRow(1); // 创建标题行(第一行)
<span style="white-space:pre"> </span>titleRow.setHeight((short) 400);// 设置第一行的行高
<span style="white-space:pre"> </span>Cell cell = null;
<span style="white-space:pre"> </span>for (int i = 0; i < titleNms.length; i++) {
<span style="white-space:pre"> </span>sheet.setColumnWidth(i, 3500);// 设置单元格的宽
<span style="white-space:pre"> </span>cell = titleRow.createCell(i);
<span style="white-space:pre"> </span>cell.setCellType(Cell.CELL_TYPE_STRING);
<span style="white-space:pre"> </span>cell.setCellValue(titleNms[i]);
<span style="white-space:pre"> </span>cell.setCellStyle(titleStyle);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>// 数据
<span style="white-space:pre"> </span>Row dataRow = null;
<span style="white-space:pre"> </span>for (int i = 0; i < list.size(); i++) {
<span style="white-space:pre"> </span>dataRow = sheet.createRow(i + 2);// 创建行
<span style="white-space:pre"> </span>for (int j = 0; j < columMethodNms.length; j++) {
<span style="white-space:pre"> </span>cell = dataRow.createCell(j);// 创建列
<span style="white-space:pre"> </span>// 设值
<span style="white-space:pre"> </span>Method method = list
<span style="white-space:pre"> </span>.get(i)
<span style="white-space:pre"> </span>.getClass()
<span style="white-space:pre"> </span>.getMethod(columMethodNms[j]);
<span style="white-space:pre"> </span>String returnType = method.getReturnType().getName()
<span style="white-space:pre"> </span>.toLowerCase();
<span style="white-space:pre"> </span>Object value = method.invoke(list.get(i));
<span style="white-space:pre"> </span>cell.setCellStyle(dataStyle);
<span style="white-space:pre"> </span>if (returnType.indexOf("string") != -1) {
<span style="white-space:pre"> </span>cell.setCellType(Cell.CELL_TYPE_STRING);
<span style="white-space:pre"> </span>cell.setCellValue(value == null ? "" : value.toString());
<span style="white-space:pre"> </span>} else if (returnType.indexOf("integer") != -1
<span style="white-space:pre"> </span>|| returnType.indexOf("int") != -1
<span style="white-space:pre"> </span>|| returnType.indexOf("bigdecimal") != -1
<span style="white-space:pre"> </span>|| returnType.indexOf("double") != -1
<span style="white-space:pre"> </span>|| returnType.indexOf("long") != -1
<span style="white-space:pre"> </span>|| returnType.indexOf("float") != -1) {
<span style="white-space:pre"> </span>cell.setCellType(Cell.CELL_TYPE_NUMERIC);
<span style="white-space:pre"> </span>cell.setCellValue(value == null ? null : new Double(value
<span style="white-space:pre"> </span>.toString()));
<span style="white-space:pre"> </span>} else if (returnType.indexOf("date") != -1) {
<span style="white-space:pre"> </span>cell.setCellType(Cell.CELL_TYPE_STRING);
<span style="white-space:pre"> </span>cell.setCellValue(value == null ? null : (Date) value);
<span style="white-space:pre"> </span>} else {
<span style="white-space:pre"> </span>cell.setCellType(Cell.CELL_TYPE_STRING);
<span style="white-space:pre"> </span>cell.setCellValue(value == null ? "" : value.toString());
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>return workbook;
<span style="white-space:pre"> </span>}</span>
//第四个红色高亮部分
<span style="color:#ff0000;">public static void writeBackFileExcel(Workbook workbook,FileOutputStream fos)throws IOException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>workbook.write(fos);
<span style="white-space:pre"> </span>} catch (IOException e1) {
<span style="white-space:pre"> </span>e1.printStackTrace();
<span style="white-space:pre"> </span>throw e1;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
</span>
//第六个红色高亮部分
<span style="color:#ff0000;">public static boolean fileToZip(String filePath,List<String> fileNames,String fileName) {
boolean flag = false;
File sourceFile = new File(filePath);
FileInputStream fis = null;
BufferedInputStream bis = null;
FileOutputStream fos = null;
ZipOutputStream zos = null;
if(!sourceFile.exists()) {
LogWriter.error(">>>>>> 待压缩的文件目录:" + filePath + " 不存在. <<<<<<");
} else {
try {
File zipFile = new File(filePath + "/" + fileName);
if(zipFile.exists()) {
<span style="white-space:pre"> </span>LogWriter.error(">>>>>> " + filePath + " 目录下存在名字为:" + fileName + ".zip" + " 打包文件. <<<<<<");
} else {
<span style="white-space:pre"> </span>fos = new FileOutputStream(zipFile);
<span style="white-space:pre"> </span>zos = new ZipOutputStream(new BufferedOutputStream(fos));
<span style="white-space:pre"> </span>byte[] bufs = new byte[1024 * 10];
<span style="white-space:pre"> </span>for (int i = 0; i < fileNames.size(); i++) {
<span style="white-space:pre"> </span>File tempFile = new File(filePath+"/"+fileNames.get(i));
<span style="white-space:pre"> </span>// 创建ZIP实体,并添加进压缩包
<span style="white-space:pre"> </span>ZipEntry zipEntry = new ZipEntry(tempFile.getName());
<span style="white-space:pre"> </span>zos.putNextEntry(zipEntry);
<span style="white-space:pre"> </span>// 读取待压缩的文件并写进压缩包里
<span style="white-space:pre"> </span>fis = new FileInputStream(tempFile);
<span style="white-space:pre"> </span>bis = new BufferedInputStream(fis, 1024 * 10);
<span style="white-space:pre"> </span>int read = 0;
<span style="white-space:pre"> </span>while ((read = bis.read(bufs, 0, 1024 * 10)) != -1) {
<span style="white-space:pre"> </span>zos.write(bufs, 0, read);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>flag = true;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
throw new RuntimeException(e);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
// 关闭流
try {
if(null != bis) bis.close();
if(null != zos) zos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return flag;
}
</span>
========================================================================================================================================================================================================================================================================