文件上传下载依赖
<!-- commons-io -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.7</version>
</dependency>
<!-- commons-fileupload -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.4</version>
</dependency>
easypoi依赖
<!--easypoi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
Spring-service配置文件
<!-- 对文件上传的支持-->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="maxUploadSize" value="50000000"></property>
<property name="defaultEncoding" value="UTF-8"></property>
</bean>
1.根据前端获取的文件流实现导入功能
@RequestMapping("/excel1")
@ResponseBody
public String importExcel(MultipartFile file) throws Exception {
ImportParams params = new ImportParams();
//去掉标题行
params.setTitleRows(5);
List<DemoData> list = ExcelImportUtil.importExcel(file.getInputStream(), DemoData.class, params);
for (int i = 0; i < list.size(); i++) {
if (null==list.get(i).getExamNo()){
list.remove(i);
i--;
}
}
if (easyService.save(list)) {
return GetResult.getResultObj("ok");
}
return GetResult.getResultObj("文件插入失败");
}
2.实现模板导出功能
@PostMapping("/excel2")
public void exportExcel(HttpServletRequest request,HttpServletResponse response){
String realPath = request.getSession().getServletContext().getRealPath("/")+"Templete/技工报名表模板.xls";
TemplateExportParams params = new TemplateExportParams(realPath);
HashMap<String, Object> map = new HashMap<String,Object>();
List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
if (params != null){
List<DemoData> demoData = easyService.queryAll();
for (int i = 0; i < demoData.size(); i++) {
HashMap<String, String> lm = new HashMap<String, String>();
lm.put("id", i + 1 + "");
lm.put("examNo",demoData.get(i).getExamNo());
lm.put("name",demoData.get(i).getName());
lm.put("sex",demoData.get(i).getSex());
lm.put("idNo",demoData.get(i).getIdNo());
lm.put("homeTown",demoData.get(i).getHomeTown());
lm.put("highDegree",demoData.get(i).getHighDegree());
lm.put("workYear",demoData.get(i).getWorkYear());
lm.put("career",demoData.get(i).getCareer());
lm.put("workNo",demoData.get(i).getWorkNo());
lm.put("rank",demoData.get(i).getRank());
lm.put("rankNo",demoData.get(i).getRankNo());
lm.put("workCompany",demoData.get(i).getWorkCompany());
listMap.add(lm);
}
map.put("maplist",listMap);
Workbook workbook = ExcelExportUtil.exportExcel(params, map);
try {
export(request, response, workbook, "技工报名表模板");
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* export导出请求头设置
* 防止乱码
* @param response
* @param workbook
* @param fileName
* @throws Exception
*/
private static void export(HttpServletRequest request, HttpServletResponse response, Workbook workbook, String fileName) throws Exception {
response.reset();
response.setContentType("application/x-msdownload");
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1") + ".xls");
ServletOutputStream outStream = null;
try {
outStream = response.getOutputStream();
workbook.write(outStream);
} finally {
outStream.close();
}
}
3.ajax接收文件流
<script src="https://cdn.staticfile.org/axios/0.18.0/axios.min.js"></script>
$("#test3").click(function () {
exportExcel();
});
function exportExcel() {
axios({
method: 'POST',
url: "/book/excel2",
timeout: 5000,
responseType: 'blob'
}).then(function (res) {
var data = res.data;
var blob = new Blob([data], {type: 'application/octet-stream'});
var url = URL.createObjectURL(blob);
var exportLink = document.createElement('a');
exportLink.setAttribute("download","ajax文件下载.xlsx");
exportLink.href = url;
document.body.appendChild(exportLink);
exportLink.click();
})
}
3.模板设置
4.这里介绍下fe的用法:
三目运算 {{test ? obj:obj2}}
n: 表示 这个cell是数值类型 {{n:}}
le: 代表长度{{le:()}} 在if/else 运用{{le:() > 8 ? obj1 : obj2}}
fd: 格式化时间 {{fd:(obj;yyyy-MM-dd)}}
fn: 格式化数字 {{fn:(obj;###.00)}}
fe: 遍历数据,创建row
!fe: 遍历数据不创建row
$fe: 下移插入,把当前行,下面的行全部下移.size()行,然后插入
#fe: 横向遍历
v_fe: 横向遍历值
!if: 删除当前列 {{!if:(test)}}
单引号表示常量值 '' 比如'1' 那么输出的就是 1
&NULL& 空格
]] 换行符 多行遍历导出
sum: 统计数据