基础知识和案例可以看这个链接:
相关链接:Apache POI Excel 导入、导出简单使用_apache poi导入excel-CSDN博客
一、模板下载
1、controller层
/**
* 模板下载
* @param response
*/
@GetMapping("/download")
public void export(HttpServletResponse response){
log.info("下载模板:{}",response);
orderSettingService.download(response);
}
2、service层
/**
* 模板下载
* @param response
*/
public void download(HttpServletResponse response) {
//导出表名称,但Postman统一为response,网页上下载会以此命名
InputStream inputStream = response.getClass().getClassLoader().getResourceAsStream("templates" + File.separator + "ordersetting_template.xlsx");
try {
//设置类型
response.setContentType("application/vnd.ms-excel");
//导出表名称,但Postman统一为response,网页上下载会以此命名
response.setHeader("content-Disposition", "attachment;filename = ordersetting_template.xlsx");
//通过输入流读取指定的Excel文件
XSSFWorkbook excel = new XSSFWorkbook(inputStream);
ServletOutputStream outputStream = response.getOutputStream();
excel.write(outputStream);
outputStream.close();
excel.close();
} catch (IOException e) {
e.printStackTrace();
}
}
3、excel下载完成
二、 文件上传
/**
* 文件上传,实现预约设置数据批量导入
* @param excelFile
* @return
*/
@PostMapping("/upload")
public Result upload(@RequestParam("excelFile") MultipartFile excelFile){
log.info("文件上传:{}", excelFile);
try {
// 通过工具类,从Excel文件中读取数据到字符串数组列表
List<String[]> list = POIUtils.readExcel(excelFile);
List<OrderSetting> data = new ArrayList<>();
// 遍历读取到的数据,构建OrderSetting对象,并添加到数据列表中
for (String[] strings : list) {
String orderData = strings[0];
String number = strings[1];
OrderSetting orderSetting = new OrderSetting(new Date(orderData), Integer.parseInt(number));
data.add(orderSetting);
}
orderSettingService.add(data);
return new Result(true,MessageConstant.IMPORT_ORDERSETTING_SUCCESS);//上传成功
} catch (IOException e) {
e.printStackTrace();
return new Result(false, MessageConstant.IMPORT_ORDERSETTING_FAIL);//上传失败
}
}
三、导出Excel
/**
* 导出Excel
* @return
*/
@GetMapping("/exportBusinessReport")
public Result exportBusinessReport(HttpServletResponse response){
try {
Map<String,Object> result = reportService.getBusinessReportData();
//取出返回结果,准备将报表数据写入到Excel文件
String reportDate = (String) result.get("reportDate");
Integer todayNewMember = (Integer) result.get("todayNewMember");
Integer totalMember = (Integer) result.get("totalMember");
Integer thisWeekNewMember = (Integer) result.get("thisWeekNewMember");
Integer thisMonthNewMember = (Integer) result.get("thisMonthNewMember");
Integer todayOrderNumber = (Integer) result.get("todayOrderNumber");
Integer thisWeekOrderNumber = (Integer) result.get("thisWeekOrderNumber");
Integer thisMonthOrderNumber = (Integer) result.get("thisMonthOrderNumber");
Integer todayVisitsNumber = (Integer) result.get("todayVisitsNumber");
Integer thisWeekVisitsNumber = (Integer) result.get("thisWeekVisitsNumber");
Integer thisMonthVisitsNumber = (Integer) result.get("thisMonthVisitsNumber");
List<Map> hotSetmeal = (List<Map>) result.get("hotSetmeal");
// 获得excel模板文件绝对路径
InputStream inputStream = response.getClass().getClassLoader().getResourceAsStream("templates" + File.separator + "report_template.xlsx");
//基于提供的Excel模板文件在内存中创建一个excel表格对象
XSSFWorkbook excel = new XSSFWorkbook(inputStream);
// 读取第一个工作表
XSSFSheet sheet = excel.getSheetAt(0);
// 获得第三行
XSSFRow row = sheet.getRow(2);
row.getCell(5).setCellValue(reportDate);//报表日期
row = sheet.getRow(4);
row.getCell(5).setCellValue(todayNewMember);//新增会员数(本日)
row.getCell(7).setCellValue(totalMember);//总会员数
row = sheet.getRow(5);
row.getCell(5).setCellValue(thisWeekNewMember);//本周新增会员数
row.getCell(7).setCellValue(thisMonthNewMember);//本月新增会员数
row = sheet.getRow(7);
row.getCell(5).setCellValue(todayOrderNumber);//今日预约数
row.getCell(7).setCellValue(todayVisitsNumber);//今日到诊数
row = sheet.getRow(8);
row.getCell(5).setCellValue(thisWeekOrderNumber);//本周预约数
row.getCell(7).setCellValue(thisWeekVisitsNumber);//本周到诊数
row = sheet.getRow(9);
row.getCell(5).setCellValue(thisMonthOrderNumber);//本月预约数
row.getCell(7).setCellValue(thisMonthVisitsNumber);//本月到诊数
int rowNum = 12;
for (Map map : hotSetmeal) {
String name = (String) map.get("name");
Long setmeal_count = (Long) map.get("setmeal_count");
BigDecimal proportion = (BigDecimal) map.get("proportion");
row = sheet.getRow(rowNum++);
row.getCell(4).setCellValue(name);//套餐名称
row.getCell(5).setCellValue(setmeal_count);//预约数量
row.getCell(6).setCellValue(proportion.doubleValue());//占比
}
//通过输出流进行文件下载
ServletOutputStream outputStream = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("content-Disposition", "attachment;filename=report.xlsx");
excel.write(outputStream);
outputStream.flush();
outputStream.close();
excel.close();
return null;
} catch (Exception e) {
e.printStackTrace();
return new Result(false,MessageConstant.GET_BUSINESS_REPORT_FAIL);
}
}
最后,直接导出excel成功!!!