Java从服务端下载Excel模板文件的两种方法
本文实例为大家分享了Java从服务端下载excel模板文件的具体实现代码,供大家参考,具体内容如下
方法一 (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;
public class FileExport {
private static final Logger logger = LoggerFactory.getLogger(FileExport.class);
private static final String CSV_COLUMN_SEPARATOR = ",";
private static final String CSV_COLUM_TABLE = "\t";
private static final String CSV_RN = "\r\n";
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");
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();
}
}
}
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(",");
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;
}
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);
}
}
方法二
@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,但是比较麻烦(这里类似方法一)
}
模板位置:
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
相关文章