Java从服务端下载Excel模板文件
宋发元 人气:0方法一 (2021年01月更新)
生成excel模板
@RequestMapping("/downloadExcel") public void downloadExcel(HttpServletResponse response, HttpServletRequest request) { String [] excelHeader = {"姓名","手机号(必填)","渠道名","产品名","手机操作系统(IOS/安卓)","是否是XX数据"}; List<Object> list = new ArrayList<>(); 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 com.abc.common.utils.file; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import java.util.Map; /** * 文件导出工具 * @author abc * @date 2019/01/08 */ public class FileExport { private static final Logger logger = LoggerFactory.getLogger(FileExport.class); /** CSV文件列分隔符 */ private static final String CSV_COLUMN_SEPARATOR = ","; private static final String CSV_COLUM_TABLE = "\t"; /** CSV文件列分隔符 */ private static final String CSV_RN = "\r\n"; /** * 导出Excel文件 * * @param excelHeader * 导出文件中表格头 * @param list * 导出的内容 * @param response * HttpServletResponse对象,用来获得输出流向客户端写导出的文件 * @param sheetName * Excel的sheet名称,加上时间戳作为导出文件的名称 */ public static void exportExcel(String [] excelHeader, List<Object> list, String sheetName, HttpServletResponse response, HttpServletRequest request) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(sheetName); HSSFRow row = sheet.createRow((int) 0); /******设置单元格是否显示网格线******/ sheet.setDisplayGridlines(false); /******设置头单元格样式******/ HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); Font fontHeader = 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 (int i = 0; i < excelHeader.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(" " +excelHeader[i] + " "); cell.setCellStyle(style); } /******设置内容单元格样式******/ HSSFCellStyle styleCell = wb.createCellStyle(); Font fontCell = 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 (int i = 0; i < list.size(); i++) { row = sheet.createRow(i + 1); /******设置行高******/ row.setHeightInPoints(20); Object[] obj = (Object[]) list.get(i); for (int j = 0; j < excelHeader.length; j++) { styleCell.setWrapText(false); HSSFCell cell = 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); } } OutputStream ouputStream = null; try { String encoding = "UTF-8"; /** 获取浏览器相关的信息 */ String userAgent = request.getHeader("user-agent"); /** 判断是否为msie浏览器 */ if (userAgent.toLowerCase().indexOf("msie") != -1){ encoding = "gbk"; } response.setCharacterEncoding(encoding); response.setContentType("application/vnd.ms-excel"); String fileName = sheetName; SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHMMSS"); fileName += (dateFormat.format(new Date())).toString()+".xls"; response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, encoding)); ouputStream = response.getOutputStream(); wb.write(ouputStream); ouputStream.flush(); } catch (Exception e) { e.printStackTrace(); } finally { try { if(ouputStream!=null) { ouputStream.close(); } } catch (IOException e) { e.printStackTrace(); } } } /** * 导出CSV文件 * @param dataList 集合数据 * @param colNames 表头部数据 * @param mapKey 查找的对应数据 */ public static boolean doExport(List<Map<String, Object>> dataList, String colNames, String mapKey, OutputStream os) { try { StringBuffer buf = new StringBuffer(); String[] colNamesArr = null; String[] mapKeyArr = null; colNamesArr = colNames.split(","); mapKeyArr = mapKey.split(","); /******完成数据csv文件的封装******/ /******输出列头******/ for (int i = 0; i < colNamesArr.length; i++) { buf.append(colNamesArr[i]).append(CSV_COLUMN_SEPARATOR); } buf.append(CSV_RN); if (null != dataList) { /******输出数据******/ for (int i = 0; i < dataList.size(); i++) { for (int j = 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(); return true; } catch (Exception e) { logger.error("doExport错误...", e); } return false; } /** * 设置响应格式 * @param fileName * @param response * @throws UnsupportedEncodingException */ public static void responseSetProperties(String fileName, HttpServletResponse response) throws UnsupportedEncodingException { /******设置文件后缀******/ SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss"); String fn = fileName + sdf.format(new Date()).toString() + ".csv"; /******读取字符编码******/ String utf = "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") public void exportFailureRecord(String batchNumber, HttpServletResponse response) { if (StringUtils.isBlank(batchNumber)) { log.warn("失败记录导出失败,批次号为空..."); return; } //这里根据你的业务查询出数据 List<ImportFailureRecord> list = importFailureRecordService.selectList(new EntityWrapper<ImportFailureRecord>() .eq("is_delete", 0) .eq("batch_number", batchNumber)); if (CollectionUtil.isEmpty(list)) { log.warn("未查询到可导出的数据..."); return; } log.info("===========查询到{}条可导出数据==============", list.size()); String sTitle = "用户姓名,手机号,失败原因"; String fName = "xxx失败记录数据_"; String mapKey = "userName,userPhone,failureReason"; List<Map<String, Object>> dataList = new ArrayList<>(); for (ImportFailureRecord data : list) { Map<String, Object> map = new HashMap<>(); 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 (final OutputStream os = response.getOutputStream()) { log.info("=============失败记录导出开始============"); FileExport.responseSetProperties(fName, response); FileExport.doExport(dataList, sTitle, mapKey, os); log.info("=============失败记录导出结束============"); } catch (Exception e) { log.error("导出失败记录数据失败", e); } }
方法二
/** * 描述:下载外部案件导入模板 * @param response * @param request * @author songfayuan * 2018年6月7日下午5:03:59 */ @RequestMapping("/downloadExcel") @ResponseBody public void downloadExcel(HttpServletResponse response,HttpServletRequest request) { //方法一:直接下载路径下的文件模板(这种方式貌似在SpringCloud和Springboot中,打包成JAR包时,无法读取到指定路径下面的文件,不知道记错没,你们可以自己尝试下!!!) try { //获取要下载的模板名称 String fileName = "ApplicationImportTemplate.xlsx"; //设置要下载的文件的名称 response.setHeader("Content-disposition", "attachment;fileName=" + fileName); //通知客服文件的MIME类型 response.setContentType("application/vnd.ms-excel;charset=UTF-8"); //获取文件的路径 String filePath = getClass().getResource("/template/" + fileName).getPath(); FileInputStream input = new FileInputStream(filePath); OutputStream out = response.getOutputStream(); byte[] b = new byte[2048]; int len; 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(); //return Response.ok("应用导入模板下载完成"); } catch (Exception ex) { logger.error("getApplicationTemplate :", ex); //return Response.ok("应用导入模板下载失败!"); } //方法二:可以采用POI导出excel,但是比较麻烦(这里类似方法一) /*try { Workbook workbook = new HSSFWorkbook(); request.setCharacterEncoding("UTF-8"); response.setCharacterEncoding("UTF-8"); response.setContentType("application/x-download"); String filedisplay = "导入模板.xls"; filedisplay = URLEncoder.encode(filedisplay, "UTF-8"); response.addHeader("Content-Disposition", "attachment;filename="+ filedisplay); // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet Sheet sheet = workbook.createSheet("导入模板"); // 第三步,在sheet中添加表头第0行 Row row = sheet.createRow(0); // 第四步,创建单元格,并设置值表头 设置表头居中 CellStyle style = workbook.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式 Cell cell = 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 { OutputStream out = response.getOutputStream(); workbook.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } } catch (Exception e) { e.printStackTrace(); }*/ }
模板位置:
加载全部内容