利用Hutool工具类下载Excel
`
<!-- Hutool引入maven依赖-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
`
`
package com.yoyo.excel.vo;
import cn.hutool.core.annotation.Alias;
import java.io.Serializable;
public class StudentVo implements Serializable {
/**
* @Alias("")
* 此注解为hutool用注解，效果是生成表头
* 但是请注意！
* 此注解于ExcelWriter.addHeaderAlias("sex", "性别 ");冲突只能选用其一
*/
private String name;
/**
* @Alias("年龄")
*/
private Integer age;
private String sex;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
/**
* 为了测试效果，线上可以不加
*/
public String toString() {
return "StudentVo{" +
"name='" + name + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
'}';
}
/**
* 下面全部为建造者模式代码
* @param builder
*/
public StudentVo(Builder builder) {
this.name = builder.name;
this.age = builder.age;
this.sex = builder.sex;
}
public static Builder newBuilder () {
return new Builder();
}
public static final class Builder {
private String name;
private Integer age;
private String sex;
private Builder() {
}
public Builder name(String val) {
this.name = val;
return this;
}
public Builder age(Integer val) {
this.age = val;
return this;
}
public Builder sex(String val) {
this.sex = val;
return this;
}
public StudentVo builder() {
return new StudentVo(this);
}
}
}
package com.yoyo.excel.controller;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import com.yoyo.excel.vo.StudentVo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@RestController
@RequestMapping(value = "/hutool")
public class HutoolController {
@Autowired
private static final Logger log = LoggerFactory.getLogger(HutoolController.class);
@RequestMapping(value = "/test", method = RequestMethod.GET)
public void hutoolExcel(HttpServletResponse response) {
ExcelWriter ExcelWriter = ExcelUtil.getWriter("test.xls");
//设置表头
ExcelWriter.addHeaderAlias("name", "名字");
ExcelWriter.addHeaderAlias("age", "年龄");
ExcelWriter.addHeaderAlias("sex", "性别 ");
//测试数据，使用建造者模式构造
StudentVo studentVo1 = StudentVo.newBuilder()
.name("张三")
.age(18)
.sex("男")
.builder();
StudentVo studentVo2 = StudentVo.newBuilder()
.name("李四")
.age(19)
.sex("男")
.builder();
StudentVo studentVo3 = StudentVo.newBuilder()
.name("王五")
.age(20)
.sex("女")
.builder();
StudentVo studentVo4 = StudentVo.newBuilder()
.name("赵六")
.age(21)
.sex("女")
.builder();
List<StudentVo> rows = CollUtil.newArrayList(studentVo1, studentVo2, studentVo3, studentVo4);
rows.forEach(x->{
log.info("对象 {}", x.toString());
});
log.info("准备生成Excel {}",ExcelWriter.getRowCount());
ExcelWriter.write(rows, true);
log.info("生成完毕 准备输出");
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + "test" + ".xls");
try {
ServletOutputStream out = response.getOutputStream();
ExcelWriter.flush(out, true);
ExcelWriter.close();
IoUtil.close(out);
} catch (IOException e) {
log.error("发生异常 异常原因:{} {}", e.getMessage(), e);
} catch (Exception j) {
log.error("发生异常 异常原因:{} {}", j.getMessage(), j);
}
}
}
`
例如：
@GetMapping(value = "/getCapitalOrderListDtoDownload")
public void getCapitalOrderListDtoDownload(HttpServletResponse response,
@RequestParam(value = "supplierId") Long supplierId,
@ApiParam(name = "orderType", value = "订单类型（-1全部 0自营，1分销，2，免单，3直播订单）")
@RequestParam(value = "orderType", required = false) Integer orderType,
@RequestParam(required = false, value = "startTime") String startDate,
@RequestParam(required = false, value = "endTime") String endDate,
@RequestParam(value = "name") String name) {
List<CapitalOrderResponse> employees0 = orderDetailService.getCapitalOrderListDtoDownLoad(supplierId, orderType, startDate, endDate, name,1,10000);
List<CapitalOrderResponseV2> employees=new ArrayList<>();
if(CollUtil.isNotEmpty(employees0)){
for (CapitalOrderResponse item :employees0){
CapitalOrderResponseV2 model=new CapitalOrderResponseV2();
model.setAgentName(item.getAgentName());
model.setOrderNo(item.getOrderNo());
model.setAmount(item.getAmount());
model.setAcceptedAmount(item.getAcceptedAmount());
model.setDueInAmount(item.getDueInAmount());
model.setCreateDate(DateUtil.formatDateTime(item.getCreateDate()));
model.setFinishTime(DateUtil.formatDateTime(item.getFinishTime()));
employees.add(model);
}
}
ExcelWriter writer = ExcelUtil.getWriter();
writer.addHeaderAlias("agentName", "店铺名称");
writer.addHeaderAlias("orderNo", "订单号");
writer.addHeaderAlias("amount", "实付金额");
writer.addHeaderAlias("acceptedAmount", "已收金额");
writer.addHeaderAlias("dueInAmount", "待收金额");
writer.addHeaderAlias("createDate", "下单时间");
writer.addHeaderAlias("finishTime", "完成时间");
writer.write(employees, true);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
String namettt = "zijintongji";
response.setHeader("Content-Disposition", "attachment;filename=" + namettt + ".xls");
ServletOutputStream out = null;
try {
out = response.getOutputStream();
writer.flush(out, true);
} catch (IOException e) {
e.printStackTrace();
} finally {
writer.close();
}
IoUtil.close(out);
}
//二次优化后的接口，使代码看起来更简洁
@GetMapping("/getMembershipLevelListDownload")
@ApiOperation(value = "多条件查询会员列表", response = MemberInfoDto.class)
public void getMembershipLevelListDownload(HttpServletResponse response,
@RequestParam(required = false) @ApiParam(name = "beginTime", value = "注册开始时间") String beginTime,
@RequestParam(required = false) @ApiParam(name = "endTime", value = "注册结束时间") String endTime,
@RequestParam(required = false) @ApiParam(name = "certificationStatus", value = "实名认证状态（0：未认证，1：已认证）") Integer certificationStatus,
@RequestParam(required = false) @ApiParam(name = "status", value = "会员状态：0：正常，1：冻结，2：拉黑") Integer status,
@RequestParam(required = false) @ApiParam(name = "grade", value = "等级名称") String grade,
@RequestParam(required = false) @ApiParam(name = "address", value = "地区") String address,
@RequestParam(required = false) @ApiParam(name = "userType", value = "会员类型") String userType,
@RequestParam(required = false) @ApiParam(name = "keyWords", value = "搜索关键词") String keyWords,
@RequestParam(required = false) @ApiParam(name = "page", value = "当前页", defaultValue = "1") Integer page,
@RequestParam(required = false) @ApiParam(name = "pageSize", value = "每页条数", defaultValue = "10") Integer pageSize
) {
try {
Page<MemberInfoDto> list = tbUserInfoService.getMembershipLevelList(beginTime, endTime, certificationStatus, status, grade, address, userType, keyWords, page, pageSize);
List<MemberInfoDto> memberInfoDtoList = list.getRecords();
//此处为需要下载字段的对象二次封装，和原对象完全一致可以跳过这一步
List<MemberInfoDownload> dataList = new ArrayList();
for(MemberInfoDto m : memberInfoDtoList ){
MemberInfoDownload md = new MemberInfoDownload();
BeanUtils.copyProperties(m,md);
dataList.add(md);
}
String fileName = "huiyuan" +new SimpleDateFormat("yyyy-MM-dd").format(new Date());
ServletOutputStream out = response.getOutputStream();
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
List<String> header = CollUtil.newArrayList("用户名称", "真实姓名", "手机号", "身份证号",
"推荐人名称", "会员状态", "余额", "提现金额",
"消费金额","充值金额", "自营产品数", "分销产品数",
"注册时间","登录时间", "最后一次登录地址");
//isXlsx 是否为xlsx格式
ExcelWriter excelWriter = ExcelUtil.getWriter(true);
excelWriter.writeHeadRow(header);
excelWriter.write(getListRow(dataList));
excelWriter.flush(response.getOutputStream());
excelWriter.close();
out.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
private List<MemberInfoDownload> getListRow(List<MemberInfoDownload> memberInfoDtoList) {
List<MemberInfoDownload> rows = new ArrayList<>();
memberInfoDtoList.stream().forEach(md -> {
MemberInfoDownload me = new MemberInfoDownload();
BeanUtils.copyProperties(md,me);
rows.add(me);
});
return rows;
}
`
