我是靠谱客的博主 陶醉未来，最近开发中收集的这篇文章主要介绍SpringBoot向Excel模板中写入数据并下载 （无需获取file对象及模板绝对路径！！！），觉得挺不错的，现在分享给大家，希望可以做个参考。
概述
最近工作有一个需求是要进行excel模板下载的，因为excel表头有一些复杂，所以没有用代码的方式来直接生成excel模板，而是将excel表格处理好以后，将查询出的数据写入excel中，而且我觉得最牛逼的是不需要获取file对象以及文件的绝对路径，兄弟们，绝bi好用，吐血推荐！！废话不多说，直接上excel和代码。(因为代码是自己写的，所以不涉及太多泄密内容，因此可以展示出来)
- excel模板如下，模板随意写，简单或更复杂的表头都可以。
代码如下：亲测好用，绝bi好用，兄弟们！！！
//我完全是在controller中写的 因此代码全部奉上
/**
* 指标监测报表导出
*
* @param request
* @return
* @throws Exception
*/
@RequestMapping("/excelTable")
public void excelTable(HttpServletResponse response, HttpServletRequest request) throws Exception{
Map<String, Object> map = new HashMap<>();
// 获取导出的时间参数
String date = request.getParameter("Date");
map.put("Date", date);
String fileName = "zbjc.xlsx";
// 使用类加载器获取excel文件流,基于模板填充数据
ClassPathResource classPathResource = new ClassPathResource(fileName);
InputStream is = null;
XSSFWorkbook workbook = null;
try {
is = classPathResource.getInputStream();
workbook = new XSSFWorkbook(is);
XSSFSheet sheet = null;
// 获取第一个sheet页
// getSheetAt和getsheet参数不一样， getSheetAt 的参数是索引，
// getSheet的参数是sheet的名称， 获取具体名称的sheet。
sheet = workbook.getSheetAt(0);
//去数据库中查询出的数据，这块就不给大家看了
List<SgwpdmSystemParameter> resutList = sgwpdmSystemParameterService.findAll("02", map);
for (int i = 0; i < resutList.size(); i++) {
int j = 4 + i;
writeExcel(sheet, resutList, j, i);
}
} catch (IOException e) {
e.printStackTrace();
}
//文件下載
response.reset();
response.setContentType("text/html;charset=UTF-8");
response.setContentType("application/x-msdownload");
String newName = "";
try {
newName = URLEncoder.encode("指标监测导出" + System.currentTimeMillis() + ".xlsx", "UTF-8");
response.addHeader("Content-Disposition", "attachment;filename="" + newName + """);
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
workbook.write(toClient);
toClient.flush();
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//上述代码中有一个封装的方法，在下面展示
/**
* 指标监测导出封装方法
*
* @param sheet
* @param resutList
* 数据集合
* @param rownum
* excel行数
* @param index
* 集合索引 从0开始
*/
public void writeExcel(XSSFSheet sheet, List<SgwpdmSystemParameter> resutList, Integer rownum, Integer index) {
if (resutList.get(index) != null && !"".equals(resutList.get(index))) {
Row row = sheet.getRow(rownum);
String proName = resutList.get(index).getProName();
Cell cell = row.getCell(0);
cell.setCellValue(proName);
String regDayNum = resutList.get(index).getRegDayNum();// 注册人数当日
cell = row.getCell(2);
cell.setCellValue(regDayNum);
String regNum = resutList.get(index).getRegNum();// 注册人数累计
cell = row.getCell(3);
cell.setCellValue(regNum);
String loginDayNum = resutList.get(index).getLoginDayNum();// 登录人数当日
cell = row.getCell(4);
cell.setCellValue(loginDayNum);
String loginNum = resutList.get(index).getLoginNum();// 登陆人数累计
cell = row.getCell(5);
cell.setCellValue(loginNum);
String flowDayNum = resutList.get(index).getFlowDayNum();// 流程当日
cell = row.getCell(6);
cell.setCellValue(flowDayNum);
String flowNum = resutList.get(index).getFlowNum();// 流程累计
cell = row.getCell(7);
cell.setCellValue(flowNum);
String menuDayNum = resutList.get(index).getMenuDayNum();// 功能当日
cell = row.getCell(8);
cell.setCellValue(menuDayNum);
String menuNum = resutList.get(index).getMenuNum();// 功能累计
cell = row.getCell(9);
cell.setCellValue(menuNum);
}
}
//数据格式如下
{
"successful": true,
"resultValue": {
"itemCount": 0,
"items": [{
"id": "0",
"regNum": "9",
"regDayNum": "0",
"loginNum": "8",
"loginDayNum": "0",
"flowNum": "1671",
"flowDayNum": "0",
"menuNum": "949",
"menuDayNum": "0",
"proName": "北京丰台"
}, {
"id": "1",
"regNum": "9",
"regDayNum": "0",
"loginNum": "6",
"loginDayNum": "0",
"flowNum": "1035",
"flowDayNum": "0",
"menuNum": "231",
"menuDayNum": "0",
"proName": "北京通州"
}, {
"id": "2",
"regNum": "5",
"regDayNum": "0",
"loginNum": "5",
"loginDayNum": "0",
"flowNum": "148",
"flowDayNum": "0",
"menuNum": "113",
"menuDayNum": "0",
"proName": "北京延庆"
}, {
"id": "3",
"regNum": "8",
"regDayNum": "0",
"loginNum": "2",
"loginDayNum": "0",
"flowNum": "500",
"flowDayNum": "0",
"menuNum": "10",
"menuDayNum": "0",
"proName": "北京城区"
}, {
"id": "4",
"regNum": "16",
"regDayNum": "0",
"loginNum": "7",
"loginDayNum": "0",
"flowNum": "1041",
"flowDayNum": "0",
"menuNum": "322",
"menuDayNum": "0",
"proName": "北京昌平"
}, {
"id": "5",
"regNum": "9",
"regDayNum": "0",
"loginNum": "6",
"loginDayNum": "0",
"flowNum": "377",
"flowDayNum": "0",
"menuNum": "186",
"menuDayNum": "0",
"proName": "北京门头沟"
}, {
"id": "6",
"regNum": "10",
"regDayNum": "0",
"loginNum": "6",
"loginDayNum": "0",
"flowNum": "396",
"flowDayNum": "0",
"menuNum": "528",
"menuDayNum": "0",
"proName": "北京大兴"
}, {
"id": "7",
"regNum": "7",
"regDayNum": "0",
"loginNum": "0",
"loginDayNum": "0",
"flowNum": "59",
"flowDayNum": "0",
"menuNum": "0",
"menuDayNum": "0",
"proName": "北京平谷"
}, {
"id": "8",
"regNum": "17",
"regDayNum": "0",
"loginNum": "5",
"loginDayNum": "0",
"flowNum": "963",
"flowDayNum": "0",
"menuNum": "250",
"menuDayNum": "0",
"proName": "北京朝阳"
}, {
"id": "9",
"regNum": "10",
"regDayNum": "0",
"loginNum": "6",
"loginDayNum": "0",
"flowNum": "1234",
"flowDayNum": "0",
"menuNum": "168",
"menuDayNum": "0",
"proName": "北京海淀"
}, {
"id": "10",
"regNum": "7",
"regDayNum": "0",
"loginNum": "5",
"loginDayNum": "0",
"flowNum": "519",
"flowDayNum": "0",
"menuNum": "5",
"menuDayNum": "0",
"proName": "北京石景山"
}, {
"id": "11",
"regNum": "11",
"regDayNum": "0",
"loginNum": "6",
"loginDayNum": "0",
"flowNum": "258",
"flowDayNum": "0",
"menuNum": "567",
"menuDayNum": "0",
"proName": "北京亦庄"
}, {
"id": "12",
"regNum": "11",
"regDayNum": "0",
"loginNum": "7",
"loginDayNum": "0",
"flowNum": "771",
"flowDayNum": "0",
"menuNum": "255",
"menuDayNum": "0",
"proName": "北京顺义"
}, {
"id": "13",
"regNum": "10",
"regDayNum": "0",
"loginNum": "8",
"loginDayNum": "0",
"flowNum": "789",
"flowDayNum": "0",
"menuNum": "333",
"menuDayNum": "0",
"proName": "北京房山"
}, {
"id": "14",
"regNum": "46",
"regDayNum": "0",
"loginNum": "28",
"loginDayNum": "0",
"flowNum": "1715",
"flowDayNum": "0",
"menuNum": "3356",
"menuDayNum": "0",
"proName": "建设咨询公司"
}, {
"id": "15",
"regNum": "9",
"regDayNum": "0",
"loginNum": "8",
"loginDayNum": "0",
"flowNum": "214",
"flowDayNum": "0",
"menuNum": "185",
"menuDayNum": "0",
"proName": "北京怀柔"
}, {
"id": "16",
"regNum": "9",
"regDayNum": "0",
"loginNum": "5",
"loginDayNum": "0",
"flowNum": "445",
"flowDayNum": "0",
"menuNum": "272",
"menuDayNum": "0",
"proName": "北京密云"
}, {
"id": "17",
"regNum": "22",
"regDayNum": "0",
"loginNum": "217",
"loginDayNum": "0",
"flowNum": "1149",
"flowDayNum": "0",
"menuNum": "6271",
"menuDayNum": "0",
"proName": "其它"
}, {
"id": "18",
"regNum": "225",
"regDayNum": "0",
"loginNum": "335",
"loginDayNum": "0",
"flowNum": "13284",
"flowDayNum": "0",
"menuNum": "14001",
"menuDayNum": "0",
"proName": "合计"
}],
"dicts": []
},
"resultHint": "",
"errorPage": "",
"type": ""
}
需要强调一下 springboot的excel文件都是要放在src/main/resources下的 不能放在代码包中，
放在代码包中本地运行没有问题，但是打成jar包上生产机时excel文件会丢失，而且excel模板文件的命名最好用英文，便于代码处理，不要怕名字的问题，导出时可以将excel文件进行改名，一定要特别注意，否则会报空指针异常。
最后
以上就是陶醉未来为你收集整理的SpringBoot向Excel模板中写入数据并下载 （无需获取file对象及模板绝对路径！！！）的全部内容，希望文章能够帮你解决SpringBoot向Excel模板中写入数据并下载 （无需获取file对象及模板绝对路径！！！）所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复