1.easypoi的pom依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.1</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.1</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.1</version>
</dependency>
2.下载文件
/**
* 下载导入模板
* @param response
*/
@RequestMapping(value = "/downloadmb", method = RequestMethod.GET)
public void downloadmb(HttpServletResponse response,HttpServletRequest request) {
BufferedInputStream in = null;
BufferedOutputStream out = null;
try {
File file = new File("E://专业计划导入模板.xlsx");
in = new BufferedInputStream(new FileInputStream(file));
out = new BufferedOutputStream(response.getOutputStream());
response.setContentType(new MimetypesFileTypeMap().getContentType(file));// 设置response内容的类型
String filename="专业导入模板";
if (isMSBrowsers.isMSBrowser(request)) {
filename = URLEncoder.encode(filename, "UTF-8");
} else {
filename = new String(filename.getBytes("utf-8"), "ISO8859-1");
}
response.setHeader("Content-disposition", "attachment;filename=" + filename + "." +"xlsx");// 设置头部信息
byte[] buffer = new byte[1024];
int length = 0;
while ((length = in.read(buffer)) > 0) {
out.write(buffer, 0, length);
}
out.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (in != null) {
in.close();
}
if (out != null) {
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
3.批量导入
/**
* *批量导入
* @param file
* @return
*/
@RequestMapping("/imports")
@ResponseBody
public MyAjaxResult imports(MultipartFile file) {
ImportParams params = new ImportParams();
List<Map<String, Object>> list = null;
Map<String,Object> map;
try {
params.setTitleRows(1);
params.setHeadRows(1);
list = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, params);
map=majorService.imports(list,"1");
} catch (Exception e) {
e.printStackTrace();
log.info("批量导入转换异常");
return MyAjaxResult.fail_300("批量导入转换异常");
}
for ( Map<String, Object> s : list) {
System.out.println(s.toString());
}
return MyAjaxResult.success(map);
}
4.批量导出数据
/**
* 批量导出数据
* @param response
*/
@RequestMapping(value = "/download")
public void download(HttpServletResponse response,HttpServletRequest request,String ids) {
List<MajorEntity> am=null;
if(ids==null) {//查询所有数据
am=majorService.selectAllMajor();
}else {//查询指定数据
try {
List<Integer> list;
String id[]=ids.split("-");
list = new ArrayList<>();
for(int i=0,j=id.length;i<j;i++) {
list.add(Integer.valueOf(id[i]));
}
am=majorService.selectAllMajor(list);
} catch (NumberFormatException e) {
e.printStackTrace();
}
}
try {
String filename="专业";
if (isMSBrowsers.isMSBrowser(request)) {
filename = URLEncoder.encode(filename, "UTF-8");
} else {
filename = new String(filename.getBytes("utf-8"), "ISO8859-1");
}
response.setContentType("application/ms-excel;charset=utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + filename + "." +"xls");// 设置头部信息
ServletOutputStream out=response.getOutputStream();
Workbook wb=getExcel(am);
wb.write(out);
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 封装
* @param l
* @return
*/
private Workbook getExcel(List<MajorEntity> l) {
List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
ExcelExportEntity colEntity = new ExcelExportEntity("序号", "xh");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("专业代号", "zydh");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("专业代码", "zydm");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("专业名称", "zymc");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("文科报名人数", "wkbmrs");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("理科报名人数", "lkbmrs");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("对口报名人数", "dkbmrs");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("报名总人数", "bmzrs");
colEntity.setNeedMerge(true);
colList.add(colEntity);
colEntity = new ExcelExportEntity("计划人数", "jhrs");
colEntity.setNeedMerge(true);
colList.add(colEntity);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int i=1;
for (MajorEntity m:l) {
Map<String, Object> valMap = new HashMap<String, Object>();
valMap.put("xh",i++);
valMap.put("zydh",m.getZydh());
valMap.put("zydm",m.getZydm());
valMap.put("zymc",m.getZymc());
valMap.put("wkbmrs",m.getWkbmrs());
valMap.put("lkbmrs",m.getLkbmrs());
valMap.put("dkbmrs",m.getDkbmrs());
valMap.put("bmzrs",m.getWkbmrs()+m.getLkbmrs()+m.getDkbmrs());
valMap.put("jhrs",m.getJhrs());
list.add(valMap);
}
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("专业计划", "数据"), colList,list);
return workbook;
}