导入导出 下载模板

/*

* 导入功能

* */

@PostMapping("/import") //excel/upload

@ResponseBody

public Object importExcel(@RequestParam("file") MultipartFile file) {

ImportParams params = new ImportParams();

//从第一行开始解析

params.setHeadRows(1);

try {

//1.读取execl

List<DepartDto> departs = ExcelImportUtil.importExcel(file.getInputStream(), DepartDto.class,params);

log.info("list:{}", departs.size());

System.out.println("------>打印------>");

for (int i = 0; i < departs.size(); i++){

//execl查出的数据

DepartDto mydepart = departs.get(i);

System.out.println(mydepart);

//判断父部门是否存在

Depart depart = departService.queryByParentDepartment(mydepart.getParentDepartment());

if(depart!=null){

//表明父部门已存在，放到对应的部门

//获取找到的父部门对应的id

String id = String.valueOf(depart.getId());

departService.insertOne(mydepart);

departService.updateByIds(id,mydepart.getName());

}else{

//父不存在，放到默认的部门

//2.添加

departService.insertOne(mydepart);

departService.updateByParentDepartment(mydepart.getName());

}

}

} catch (Exception e) {

e.printStackTrace();

log.error("解析Excel异常{"+e.getMessage()+"}");

return ResultJson.error("解析Excel异常");

}

return ResultJson.success();

}

/*

*

* 导出功能

* */

@GetMapping("export")

public void exportExcel(HttpServletResponse response) throws Exception {

//查询部门所有信息

List<SysDepart> sysDeparts = sysDepartService.queryAll();

//建立一个空的集合

List<DepartVo> list=new ArrayList<>();

//遍历查出来的sysDeparts

for(int i=0;i<sysDeparts.size();i++){

//一一获取查出来的对象

SysDepart sysDepart = sysDeparts.get(i);

//创建对象

DepartVo depart = new DepartVo();

//将查询出来的进行复制

depart.setId(sysDepart.getId());

depart.setName(sysDepart.getName());

depart.setAddress(sysDepart.getAddress());

depart.setPhone(sysDepart.getPhone());

depart.setParentId(sysDepart.getParentId());

depart.setCreateDate(sysDepart.getCreateDate());

depart.setCreateName(sysDepart.getCreateName());

depart.setUpdateDate(sysDepart.getUpdateDate());

depart.setUpdateName(sysDepart.getUpdateName());

depart.setRemark(sysDepart.getRemark());

list.add(depart);

}

// 根据时间戳生成表格名称

String fileName = System.currentTimeMillis()+".xls";

// 导出的表格title和sheet页名

ExportParams exportParams = new ExportParams(null, "sheet");

// 传参是要转换的对象类型，和list

Workbook workbook = ExcelExportUtil.exportExcel(exportParams, DepartVo.class, list);

if (workbook != null) {

//list转excel 返回文件流

try {

response.setCharacterEncoding("UTF-8");

response.setHeader("content-Type", "application/vnd.ms-excel");

response.setHeader("Content-Disposition",

"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20"));

workbook.write(response.getOutputStream());

} catch (Exception e) {

log.error("Excel导出异常{"+e.getMessage()+"}");

throw new ServiceException("Excel导出异常");

} finally {

workbook.close();

}

}

}

private static final String templateExcel = "ImportUsers.xlsx";

/**

* 下载模板 导出的模板在 根目录下

* @param response

*/

@GetMapping("/download")

@SneakyThrows

public void getTemplate(HttpServletResponse response){

File file = ResourceUtils.getFile(this.getClass().getResource("/")+ FILE_PATH_TEMPLATE+templateExcel);

String filedisplay = URLEncoder.encode("ImportUsers.xlsx","utf-8");

//web项目，1、设置下载框的弹出（设置response相关参数)；2、通过httpservletresponse.getOutputStream()获取

//设置下载弹框，设置下载文件名

response.setContentType("application/vnd.ms-excel;charset=utf-8");

response.setHeader("Content-Disposition", "attachment;filename="+filedisplay);

FileInputStream fileInputStream = new FileInputStream(file);

BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());

byte[] buffer = new byte[2048];

int len;

while ((len = fileInputStream.read(buffer, 0, buffer.length)) != -1) {

bos.write(buffer, 0, len);

bos.flush();

}

if (fileInputStream != null) {

fileInputStream.close();

}

if (bos != null) {

bos.close();

}

}

模板类：

导出没测试