EXCEL:导入
说明:
导入需要列与实体类的注解名一一对应,否则不会报错,会导致无法解析该列数据.导入时可以提前准备excel模板,先下载模板然后填写数据上传导入.
代码:
pom必须引
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.2</version>
</dependency>
导入:
public Response<String> Import(HttpServletResponse resp,HttpServletRequest request,MultipartFile file) {
try {
if (file==null){
return ResponseUtil.buildErrorResponse(ResponseCode.BUS_EXCEPTION);
}
List<BusReimbursementVo> reimbursementVos = FileExcelUtil.importExcel(file, 0, 1, BusReimbursementVo.class);
if (reimbursementVos.size()>0) {
}
System.out.println(reimbursementVos);
} catch (Exception e) {
e.printStackTrace();
return ResponseUtil.buildErrorResponse(ResponseCode.BUS_EXCEPTION);
}
return ResponseUtil.buildErrorResponse(ResponseCode.SUCCESS);
}
EXCEL:导出---无模板导出下载 实体类必须有注解
public void export(HttpServletResponse response,HttpServletRequest request,@RequestParam(value = "orderBy",defaultValue = "id desc")String orderBy) throws Exception {
//模拟数据库数据
try {
Map<String, Object> searchPara=getQueryCondition(request,orderBy);
List<BusReimbursementVo> beans = busReimbursementService.list(searchPara);
//导出
FileExcelUtil.exportExcel(beans,BusReimbursementVo.class,"日常报销单.xlsx",response);
} catch (Exception e) {
e.printStackTrace();
}
}
//实体类片段
/**
* 报销人员姓名
*/
@Size(max=50,message = "报销人员姓名超过最大长度50")
@Excel(name = "报销人员",width = 25)//,orderNum = "0"
private String leaveUserName;
/**
* 填报时间
*/
@cn.afterturn.easypoi.excel.annotation.Excel(name = "填报时间",width = 20,exportFormat = "yyyy-MM-dd",orderNum = "0")
private Timestamp fillingTime;
/**
* 报销金额
*/
@NotNull(message = "报销金额不能为空!!")
@Excel(name = "报销金额",width = 25,orderNum = "1")
private BigDecimal reimbursementAmount;
EXCEL:导出---根据模板导出下载 实体类不需要注解
public void exportTemplateExcel(HttpServletResponse response,HttpServletRequest request,@RequestParam(value = "orderBy",defaultValue = "id desc")String orderBy) throws Exception {
//模拟数据库数据
try {
//获取模板
TemplateExportParams params = new TemplateExportParams("template/日常报销单模板.xlsx");
//整合数据
Map<String, Object> searchPara=getQueryCondition(request,orderBy);
//查询数据
Map<String, Object> map = new HashMap<String, Object>();
map.put("title", "7月报销");
List<BusReimbursementVo> beans = busReimbursementService.list(searchPara);
map.put("maplist", beans);
//生成工作簿
Workbook workbook = ExcelExportUtil.exportExcel(params, map);
//输出流下载
OutputStream outputStream=null;
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode("日常报销单.xlsx", "UTF-8"));
outputStream = response.getOutputStream();
workbook.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
}
}
模板的样式:
导出下载word:
pom添加以下引入:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.0.3</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.0.3</version>
</dependency>
<!--注意:word中要使用循环等标签必须单独导入以下依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.1</version>
</dependency>
! word导出拼凑map数据时 需要对数据校验 不可以有null或""或" " 出现为空最好赋值为无或其他表示说明 否则模板对应字段占位不会取消代替
public void exportTemplateWord(HttpServletResponse response,HttpServletRequest request,@RequestParam(value = "orderBy",defaultValue = "id desc")String orderBy) throws Exception {
String imagePath="D:\\testImage\\1595989432.jpg";
/*String imagePath="http://pic1.win4000.com/pic/5/bf/6de447e563.jpg";
outImage(imagePath,"D:\\testImage","meinv");*/
WordImageEntity entity=new WordImageEntity();
entity.setHeight(300);
entity.setWidth(560);
entity.setUrl(imagePath);
// entity.setUrl("D:\\testImage\\meinv.jpg");
try {
//获取数据
BusReimbursementVo reimbursementVo = busReimbursementService.findById("484096486594842624");
Map<String,Object> map=new HashMap<>();
map.put("leaveUserName",reimbursementVo.getLeaveUserName());
map.put("reimbursementDate",reimbursementVo.getReimbursementDate());
if (reimbursementVo.getProjectName()!=null) {
map.put("projectName",reimbursementVo.getProjectName());
}else {
map.put("projectName","无");
}
if (reimbursementVo.getReimbursementAmount()!=null) {
map.put("reimbursementAmount",reimbursementVo.getReimbursementAmount()+"元/人民币");
}else {
map.put("reimbursementAmount","无");
}
if (reimbursementVo.getReimbursementInstructions()!=null) {
map.put("reimbursementInstructions",reimbursementVo.getReimbursementInstructions());
}else {
map.put("reimbursementInstructions","无");
}
if (reimbursementVo.getReviewerOneName()!=null) {
map.put("reviewerOneName",reimbursementVo.getReviewerOneName());
}else {
map.put("reviewerOneName","无");
}
if (reimbursementVo.getAuditInstructionsOne()!=null) {
map.put("auditInstructionsOne",reimbursementVo.getAuditInstructionsOne());
}else {
map.put("auditInstructionsOne","无");
}
if (reimbursementVo.getReviewerTwoName()!=null) {
map.put("reviewerTwoName",reimbursementVo.getReviewerTwoName());
}else {
map.put("reviewerTwoName","无");
}
if (reimbursementVo.getAuditInstructionsTwo()!=null) {
map.put("auditInstructionsTwo",reimbursementVo.getAuditInstructionsTwo());
}else {
map.put("auditInstructionsTwo","无");
}
map.put("title","公司业务报销单");
map.put("image",entity);
XWPFDocument doc= WordExportUtil.exportWord07("template/报销单.docx",map);
//输出流下载
OutputStream outputStream=null;
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode("日常报销单.docx", "UTF-8"));
outputStream = response.getOutputStream();
doc.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 基于模板导出word 表格遍历数据
* @param response
* @param request
* @param orderBy
* @throws Exception
*/
@RequestMapping("exportTemplateWordList")
@ResponseBody
public void exportTemplateWordList(HttpServletResponse response,HttpServletRequest request,@RequestParam(value = "orderBy",defaultValue = "id desc")String orderBy) throws Exception {
try {
//获取数据
Map<String,Object> par=new HashMap<>();
List<BusReimbursementVo> reimbursementVos = busReimbursementService.list(par);
List<Map<String,Object>> tlist=new ArrayList<>();
Map<String,Object> map=new HashMap<>();
if (reimbursementVos!=null && reimbursementVos.size()>0) {
for (BusReimbursementVo reimbursementVo : reimbursementVos) {
Map<String,Object> mm=new HashMap<>();
mm.put("leaveUserName",reimbursementVo.getLeaveUserName());
mm.put("reimbursementDate",reimbursementVo.getReimbursementDate());
mm.put("reimbursementAmount",reimbursementVo.getReimbursementAmount());
mm.put("fillingTime",reimbursementVo.getFillingTime());
tlist.add(mm);
}
}
map.put("title","公司业务报销单");
map.put("list",tlist);
XWPFDocument doc= WordExportUtil.exportWord07("template/报销单列表.docx",map);
//输出流下载
OutputStream outputStream=null;
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode("日常报销单.docx", "UTF-8"));
outputStream = response.getOutputStream();
doc.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
}
}
word模板:
本文集网络多家帖子整合,亲测可用.如有问题,评论区多多指导