Java怎么从服务端下载Excel模板文件

本篇内容主要讲解“Java怎么从服务端下载Excel模板文件”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Java怎么从服务端下载Excel模板文件”吧!

方法一

生成excel模板

@RequestMapping("/downloadExcel")publicvoiddownloadExcel(HttpServletResponseresponse,HttpServletRequestrequest){String[]excelHeader={"姓名","手机号（必填）","渠道名","产品名","手机操作系统（IOS/安卓)","是否是XX数据"};List<Object>list=newArrayList<>();Object[]obj1={"张三","173*****311?","a1","A","IOS","是"};Object[]obj2={"李四","138*****742","a2","B","安卓","否"};list.add(obj1);list.add(obj2);FileExport.exportExcel(excelHeader,list,"XXX模板",response,request);}

FileExport工具类：

package&nbsp.abcmon.utils.file;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.hssf.util.HSSFColor;importorg.apache.poi.ss.usermodel.BorderStyle;importorg.apache.poi.ss.usermodel.Font;importorg.apache.poi.ss.usermodel.HorizontalAlignment;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;importjava.io.OutputStream;importjava.io.UnsupportedEncodingException;importjava.net.URLEncoder;importjava.text.SimpleDateFormat;importjava.util.Date;importjava.util.List;importjava.util.Map;/***文件导出工具*@authorabc*@date2023/01/08*/publicclassFileExport{privatestaticfinalLoggerlogger=LoggerFactory.getLogger(FileExport.class);/**CSV文件列分隔符*/privatestaticfinalStringCSV_COLUMN_SEPARATOR=",";privatestaticfinalStringCSV_COLUM_TABLE="\t";/**CSV文件列分隔符*/privatestaticfinalStringCSV_RN="\r\n";/***导出Excel文件**@paramexcelHeader*导出文件中表格头*@paramlist*导出的内容*@paramresponse*HttpServletResponse对象，用来获得输出流向客户端写导出的文件*@paramsheetName*Excel的sheet名称，加上时间戳作为导出文件的名称*/publicstaticvoidexportExcel(String[]excelHeader,List<Object>list,StringsheetName,HttpServletResponseresponse,HttpServletRequestrequest){HSSFWorkbookwb=newHSSFWorkbook();HSSFSheetsheet=wb.createSheet(sheetName);HSSFRowrow=sheet.createRow((int)0);/******设置单元格是否显示网格线******/sheet.setDisplayGridlines(false);/******设置头单元格样式******/HSSFCellStylestyle=wb.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);FontfontHeader=wb.createFont();fontHeader.setBold(true);fontHeader.setFontHeight((short)240);style.setFont(fontHeader);style.setBorderBottom(BorderStyle.THIN);style.setBorderLeft(BorderStyle.THIN);style.setBorderRight(BorderStyle.THIN);style.setBorderTop(BorderStyle.THIN);/******设置头内容******/for(inti=0;i<excelHeader.length;i++){HSSFCellcell=row.createCell(i);cell.setCellValue(""+excelHeader[i]+"");cell.setCellStyle(style);}/******设置内容单元格样式******/HSSFCellStylestyleCell=wb.createCellStyle();FontfontCell=wb.createFont();fontCell.setColor(HSSFColor.BLACK.index);styleCell.setAlignment(HorizontalAlignment.CENTER);styleCell.setFont(fontCell);styleCell.setBorderBottom(BorderStyle.THIN);styleCell.setBorderLeft(BorderStyle.THIN);styleCell.setBorderRight(BorderStyle.THIN);styleCell.setBorderTop(BorderStyle.THIN);/******设置单元格内容******/for(inti=0;i<list.size();i++){row=sheet.createRow(i+1);/******设置行高******/row.setHeightInPoints(20);Object[]obj=(Object[])list.get(i);for(intj=0;j<excelHeader.length;j++){styleCell.setWrapText(false);HSSFCellcell=row.createCell(j);if(obj[j]!=null){cell.setCellValue(obj[j].toString());}else{cell.setCellValue("");}//if(obj[j].toString().length()>20)//styleCell.setWrapText(true);cell.setCellStyle(styleCell);sheet.autoSizeColumn(j);}}OutputStreamouputStream=null;try{Stringencoding="UTF-8";/**获取浏览器相关的信息*/StringuserAgent=request.getHeader("user-agent");/**判断是否为msie浏览器*/if(userAgent.toLowerCase().indexOf("msie")!=-1){encoding="gbk";}response.setCharacterEncoding(encoding);response.setContentType("application/vnd.ms-excel");StringfileName=sheetName;SimpleDateFormatdateFormat=newSimpleDateFormat("yyyyMMddHHMMSS");fileName+=(dateFormat.format(newDate())).toString()+".xls";response.setHeader("Content-disposition","attachment;filename="+URLEncoder.encode(fileName,encoding));ouputStream=response.getOutputStream();wb.write(ouputStream);ouputStream.flush();}catch(Exceptione){e.printStackTrace();}finally{try{if(ouputStream!=null){ouputStream.close();}}catch(IOExceptione){e.printStackTrace();}}}/***导出CSV文件*@paramdataList集合数据*@paramcolNames表头部数据*@parammapKey查找的对应数据*/publicstaticbooleandoExport(List<Map<String,Object>>dataList,StringcolNames,StringmapKey,OutputStreamos){try{StringBufferbuf=newStringBuffer();String[]colNamesArr=null;String[]mapKeyArr=null;colNamesArr=colNames.split(",");mapKeyArr=mapKey.split(",");/******完成数据csv文件的封装******//******输出列头******/for(inti=0;i<colNamesArr.length;i++){buf.append(colNamesArr[i]).append(CSV_COLUMN_SEPARATOR);}buf.append(CSV_RN);if(null!=dataList){/******输出数据******/for(inti=0;i<dataList.size();i++){for(intj=0;j<mapKeyArr.length;j++){buf.append(dataList.get(i).get(mapKeyArr[j])).append(CSV_COLUM_TABLE).append(CSV_COLUMN_SEPARATOR);}buf.append(CSV_RN);}}/******写出响应******/os.write(buf.toString().getBytes("GBK"));os.flush();returntrue;}catch(Exceptione){logger.error("doExport错误...",e);}returnfalse;}/***设置响应格式*@paramfileName*@paramresponse*@throwsUnsupportedEncodingException*/publicstaticvoidresponseSetProperties(StringfileName,HttpServletResponseresponse)throwsUnsupportedEncodingException{/******设置文件后缀******/SimpleDateFormatsdf=newSimpleDateFormat("yyyyMMddHHmmss");Stringfn=fileName+sdf.format(newDate()).toString()+".csv";/******读取字符编码******/Stringutf="UTF-8";/******设置响应******/response.setContentType("application/ms-txt.numberformat:@");response.setCharacterEncoding(utf);response.setHeader("Pragma","public");response.setHeader("Cache-Control","max-age=30");response.setHeader("Content-Disposition","attachment;filename="+URLEncoder.encode(fn,utf));}}

导出CSV文件

@GetMapping("/exportFailureRecord")publicvoidexportFailureRecord(StringbatchNumber,HttpServletResponseresponse){if(StringUtils.isBlank(batchNumber)){log.warn("失败记录导出失败，批次号为空...");return;}//这里根据你的业务查询出数据List<ImportFailureRecord>list=importFailureRecordService.selectList(newEntityWrapper<ImportFailureRecord>().eq("is_delete",0).eq("batch_number",batchNumber));if(CollectionUtil.isEmpty(list)){log.warn("未查询到可导出的数据...");return;}log.info("===========查询到{}条可导出数据==============",list.size());StringsTitle="用户姓名,手机号,失败原因";StringfName="xxx失败记录数据_";StringmapKey="userName,userPhone,failureReason";List<Map<String,Object>>dataList=newArrayList<>();for(ImportFailureRecorddata:list){Map<String,Object>map=newHashMap<>();map.put("userName",data.getUserName()==null?"":data.getUserName());map.put("userPhone",data.getUserPhone()==null?"":data.getUserPhone());map.put("failureReason",data.getFailureReason()==null?"":data.getFailureReason());dataList.add(map);}try(finalOutputStreamos=response.getOutputStream()){log.info("=============失败记录导出开始============");FileExport.responseSetProperties(fName,response);FileExport.doExport(dataList,sTitle,mapKey,os);log.info("=============失败记录导出结束============");}catch(Exceptione){log.error("导出失败记录数据失败",e);}}

方法二

/***描述：下载外部案件导入模板*@paramresponse*@paramrequest*@authorsongfayuan*2023年6月7日下午5:03:59*/@RequestMapping("/downloadExcel")@ResponseBodypublicvoiddownloadExcel(HttpServletResponseresponse,HttpServletRequestrequest){//方法一：直接下载路径下的文件模板（这种方式貌似在SpringCloud和Springboot中，打包成JAR包时，无法读取到指定路径下面的文件，不知道记错没，你们可以自己尝试下！！！）try{//获取要下载的模板名称StringfileName="ApplicationImportTemplate.xlsx";//设置要下载的文件的名称response.setHeader("Content-disposition","attachment;fileName="+fileName);//通知客服文件的MIME类型response.setContentType("application/vnd.ms-excel;charset=UTF-8");//获取文件的路径StringfilePath=getClass().getResource("/template/"+fileName).getPath();FileInputStreaminput=newFileInputStream(filePath);OutputStreamout=response.getOutputStream();byte[]b=newbyte[2048];intlen;while((len=input.read(b))!=-1){out.write(b,0,len);}//修正Excel在“xxx.xlsx”中发现不可读取的内容。是否恢复此工作薄的内容？如果信任此工作簿的来源，请点击"是"response.setHeader("Content-Length",String.valueOf(input.getChannel().size()));input.close();//returnResponse.ok("应用导入模板下载完成");}catch(Exceptionex){logger.error("getApplicationTemplate:",ex);//returnResponse.ok("应用导入模板下载失败！");}//方法二：可以采用POI导出excel，但是比较麻烦（这里类似方法一）/*try{Workbookworkbook=newHSSFWorkbook();request.setCharacterEncoding("UTF-8");response.setCharacterEncoding("UTF-8");response.setContentType("application/x-download");Stringfiledisplay="导入模板.xls";filedisplay=URLEncoder.encode(filedisplay,"UTF-8");response.addHeader("Content-Disposition","attachment;filename="+filedisplay);//第二步，在webbook中添加一个sheet,对应Excel文件中的sheetSheetsheet=workbook.createSheet("导入模板");//第三步，在sheet中添加表头第0行Rowrow=sheet.createRow(0);//第四步，创建单元格，并设置值表头设置表头居中CellStylestyle=workbook.createCellStyle();style.setAlignment(CellStyle.ALIGN_CENTER);//创建一个居中格式Cellcell=row.createCell(0);cell.setCellValue("商品名称");cell.setCellStyle(style);sheet.setColumnWidth(0,(25*256));//设置列宽，50个字符宽cell=row.createCell(1);cell.setCellValue("商品编码");cell.setCellStyle(style);sheet.setColumnWidth(1,(20*256));//设置列宽，50个字符宽cell=row.createCell(2);cell.setCellValue("商品价格");cell.setCellStyle(style);sheet.setColumnWidth(2,(15*256));//设置列宽，50个字符宽cell=row.createCell(3);cell.setCellValue("商品规格");cell.setCellStyle(style);sheet.setColumnWidth(3,(15*256));//设置列宽，50个字符宽//第五步，写入实体数据实际应用中这些数据从数据库得到row=sheet.createRow(1);row.createCell(0,Cell.CELL_TYPE_STRING).setCellValue(1);row.createCell(1,Cell.CELL_TYPE_STRING).setCellValue(2);row.createCell(2,Cell.CELL_TYPE_STRING).setCellValue(3);//商品价格row.createCell(3,Cell.CELL_TYPE_STRING).setCellValue(4);//规格//第六步，将文件存到指定位置try{OutputStreamout=response.getOutputStream();workbook.write(out);out.close();}catch(Exceptione){e.printStackTrace();}}catch(Exceptione){e.printStackTrace();}*/}

模板位置：

到此，相信大家对“Java怎么从服务端下载Excel模板文件”有了更深的了解，不妨来实际操作一番吧！这里是主机评测网网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

上一篇：Vue-router子路由怎么创建

下一篇：redis?stream怎么实现消息队列

---