方法一
生成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(); }*/ }
模板位置：
以上就是java怎么从服务端下载excel模板文件的详细内容。