java创建excel表并同其他文件一起压缩进压缩包进行下载
不多说，直接上代码。
public ServerResponse downloadFiles(Integer id) {
if (id==null||"".equals(id)){
return ServerResponse.createBySuccessMessage("请选择要下载的文件");
}
List<ProjectResult> projectResult=projectUserMapper.getProject(id);//从数据库获取需要生成excel的数据
List<List<String>> lists=new ArrayList<List<String>>();
String[] label = getFormFile(projectResult, lists);//获取excel表表头
String zipFileNameEn=null;
String zipFilePath=null;
List<String> fileName=new ArrayList<>();
List<String> filePath=new ArrayList<>();
zipFileNameEn = "生成excel名称";
try {
zipFilePath = addFileExcel.createExcel(zipFileNameEn,"数据表",label,lists);//生成excel文件
} catch (IOException e) {
e.printStackTrace();
}
fileName.add("物客一张表.xlsx");
filePath.add(zipFilePath);
downloadFiles(response,fileName,filePath,nasPath+"/exportFile",zipFileNameEn);
//从文件夹删除代码生成的excel文件
File file=new File(zipFilePath);
file.delete();
return ServerResponse.createBySuccessMessage(SuccessConstant.handleSuccess);
}
//生成excel文件并返回文件路径
public String createExcel(String fileName,String sheetName ,String[] label, List<List<String>> list ) throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet stuSheet = wb.createSheet(sheetName);
Row titleRow = stuSheet.createRow(0);
CellStyle style = wb.createCellStyle();
Cell cell = null;
//把已经写好的标题行写入excel文件中
for (int i = 0; i < label.length; i++) {
cell = titleRow.createCell(i);
cell.setCellValue(label[i]);
cell.setCellStyle(style);
}
//把从数据库中取得的数据一一写入excel文件中
Row row = null;
List<String> stuList = new ArrayList<>();
for (int i = 0; i < list.size(); i++) {
//创建list.size()行数据
row = stuSheet.createRow(i + 1);
//把值一一写进单元格里
//设置第一列为自动递增的序号
for(int j=0;j<list.get(i).size();j++){
row.createCell(j).setCellValue(list.get(i).get(j));
}
}
//设置单元格宽度自适应，在此基础上把宽度调至1.5倍
for (int i = 0; i < label.length; i++) {
stuSheet.autoSizeColumn(i, true);
stuSheet.setColumnWidth(i, stuSheet.getColumnWidth(i) * 15 / 10);
}
//获取配置文件中保存对应excel文件的路径，本地也可以直接写成F：excel/stuInfoExcel路径
String folderPath="/app/file/notice/files/temp/materialCard/";
//创建上传文件目录
File folder = new File(folderPath);
//如果文件夹不存在创建对应的文件夹
if (!folder.exists()) {
folder.mkdirs();
}
//设置文件名
String fileNames = fileName + ".xlsx";
String savePath = folderPath + File.separator + fileNames;
// System.out.println(savePath);
OutputStream fileOut = new FileOutputStream(savePath);
wb.write(fileOut);
fileOut.close();
//返回文件保存全路径
return savePath;
}
//生成压缩包文件并把所有文件压缩进压缩包进行下载
public void downloadFiles(HttpServletResponse response, List<String> name,
List<String> paths,String directoryPath,
String zipFileNameEn) {
java.io.File directoryFile=new java.io.File(directoryPath);
if(!directoryFile.isDirectory() && !directoryFile.exists()){
directoryFile.mkdirs();
}
//设置最终输出zip文件的目录+文件名
SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
String zipFileName =zipFileNameEn+".zip";
String strZipPath = directoryPath+"/"+zipFileName;
ZipOutputStream zipStream = null;
FileInputStream zipSource = null;
BufferedInputStream bufferStream = null;
java.io.File zipFile = new java.io.File(strZipPath);
try{
//构造最终压缩包的输出流
zipStream = new ZipOutputStream(new FileOutputStream(zipFile));
for (int i = 0; i<paths.size() ;i++){
//解码获取真实路径与文件名
String realFilePath = java.net.URLDecoder.decode(paths.get(i),"UTF-8");
java.io.File file = new File(realFilePath);
if(file.exists())
{
zipSource = new FileInputStream(file);//将需要压缩的文件格式化为输入流
/**
* 压缩条目不是具体独立的文件，而是压缩包文件列表中的列表项，称为条目，就像索引一样这里的name就是文件名,
* 文件名和之前的重复就会导致文件被覆盖
*/
ZipEntry zipEntry = new ZipEntry(name.get(i));//在压缩目录中文件的名字
zipStream.putNextEntry(zipEntry);//定位该压缩条目位置，开始写入文件到压缩包中
bufferStream = new BufferedInputStream(zipSource, 1024 * 10);
int read = 0;
byte[] buf = new byte[1024 * 10];
while((read = bufferStream.read(buf, 0, 1024 * 10)) != -1)
{
zipStream.write(buf, 0, read);
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭流
try {
if(null != bufferStream) {
bufferStream.close();
}
if(null != zipStream){
zipStream.flush();
zipStream.close();
}
if(null != zipSource){
zipSource.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
//判断当前压缩文件是否生成存在：true-把该压缩文件通过流输出给客户端后删除该压缩文件
if(zipFile.exists()){
//发送给客户端
fileUtils.downImgClient(response,zipFileName,strZipPath);
//删除本地存储的文件
zipFile.delete();
}
}
//获取excel表头
private String[] getFormFile(List<ProjectResult> projectResult, List<List<String>> lists) {
for (ProjectResult item : projectResult) {
DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm");
List<String> tem = new ArrayList<String>();
//第一份
tem.add(item.getProjectInfo().getDeptName());
tem.add(item.getProjectInfo().getGroupNum());
tem.add(item.getProjectInfo().getGroupName());
tem.add(item.getProjectInfo().getCustManager());
tem.add(item.getProjectInfo().getTmtType());
tem.add(item.getProjectInfo().getProjectScene());
tem.add(item.getProjectInfo().getProjectName());
tem.add(item.getProjectInfo().getPlatformType());
tem.add(item.getProjectInfo().getPackageSystem());
tem.add(item.getProjectInfo().getPackageAll());
//第二份
tem.add(format.format(item.getProjectCpu().getAgreementDate()));
tem.add(item.getProjectCpu().getProjectDiscount()+"%");
tem.add(item.getProjectCpu().getProof());
tem.add(item.getProjectCpu().getLead());
tem.add(format.format(item.getProjectCpu().getValidDate()));
tem.add(format.format(item.getProjectCpu().getValidEndDate()));
tem.add(item.getProjectCpu().getMarketingNum());
tem.add(item.getProjectCpu().getMarkejobNum());
tem.add(item.getProjectCpu().getDiscountsWay());
tem.add(item.getProjectCpu().getSystemDiscount()+"%");
tem.add(item.getProjectCpu().getBackMoneyRatio()+"%");
tem.add(Integer.toString(item.getProjectCpu().getSaleOpenNum()));
tem.add(item.getProjectCpu().getSignFileName());
tem.add(item.getProjectCpu().getMarketingFileName());
//第三份
tem.add(format.format(item.getProjectUser().getOpenDate()));
tem.add(Integer.toString(item.getProjectUser().getOpenNum()));
tem.add(item.getProjectUser().getOpenName());
tem.add(item.getProjectUser().getAgreementIpv());
tem.add(item.getProjectUser().getHandlingFormIpv());
tem.add(item.getProjectUser().getApn());
tem.add(item.getProjectUser().getApnName());
tem.add(Integer.toString(item.getProjectUser().getOpenAll()));
tem.add(item.getProjectUser().getAgreementFileName());
tem.add(item.getProjectUser().getHandlingFileName());
tem.add(item.getProjectUser().getBusinessFileName());
tem.add(item.getProjectUser().getAutFileName());
tem.add(item.getProjectUser().getCardFileName());
tem.add(item.getProjectUser().getPortraitFileName());
tem.add(item.getProjectUser().getNumberFileName());
lists.add(tem);
}
return new String[]{"归属", "集团编号", "集团名称", "客户经理", "行业类型", "项目场景", "项目名称", "卡平台类型",
"套餐资费体系", "套餐名称+套餐程控资费", "客户协议签订时间", "项目签约折扣", "签报文号", "签报审批领导",
"签报有效起始时间","签报有效终止时间", "营销案编号","营销活动编号", "优惠实现方式", "系统折扣", "馈赠金返充比例",
"优惠开户数", "签报文件上传", "营销案上传","开户时间", "本次开户数量", "开户户名", "协议版本", "受理单版本",
"是否APN", "APN名称", "累计开户量", "协议上传", "受理单上传",
"营业执照上传", "授权书上传", "经办人身份证正反面上传", "人像采集上传", "号码清单上传"};
}
扫一扫
1128
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
