easyexcel简单几行代码解决导出
Controller层
@GetMapping(“download”)
public void download(HttpServletResponse response) throws IOException {
// 这里注意 使用swagger 会导致各种问题,请直接用浏览器或者用postman
response.setContentType(“application/vnd.ms-excel”);
response.setCharacterEncoding(“utf-8”);
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(“注意”, “UTF-8”);
response.setHeader(“Content-disposition”, “attachment;filename=” + fileName + “.xlsx”);
// Student.class 是按导出类 data()应为数据库查询数据,这里只是模拟
EasyExcel.write(response.getOutputStream(), CompanyPo.class).sheet(“模板”).doWrite(data());
}
private List data() {
/* List list = new ArrayList();
for (int i = 0; i < 10; i++) {
Student student = new Student();
list.add(student);
student.setName(“张三” + i);
student.setAge(18 + i);
student.setScore(new BigDecimal(i + “”));
}
return list;*/
String roleId=“1”;
/获得前端传来的company信息/
List<HashMap<String, Object>> companyPosList=null;
if(“1”.equals(roleId)){
/说明这是省管理员/
CompanyPo body = new CompanyPo();
companyPosList = companyMapper.selectAllCompany(body);
}
/创建一个集合/
ArrayList newCompanyPosList = new ArrayList<>();
/将表中的公司进行遍历/
for (HashMap<String, Object> companyPo : companyPosList) {
/创建公司的对象,将遍历集合取出后填进去/
CompanyPo newCompanyPo = new CompanyPo();
/公司Id/
newCompanyPo.setcId(String.valueOf(companyPo.get(“ID”)));
/公司所在城市名字/
newCompanyPo.setCityName(String.valueOf(companyPo.get(“NAME”)));
/公司所在区县名字/
newCompanyPo.setCountryName(String.valueOf(companyPo.get(“AREANAME”)));
/公司名字/
newCompanyPo.setCompanyName(String.valueOf(companyPo.get(“COMPANY_NAME”)));
/公司介绍/
newCompanyPo.setCompanyIntro(String.valueOf(companyPo.get(“COMPANY_INTRO”)));
/公司地址/
newCompanyPo.setCompanyAddress(String.valueOf(companyPo.get(“COMPANY_ADDRESS”)));
/审核状态/
newCompanyPo.setAuditStatus(String.valueOf(companyPo.get(“AUDIT_STATUS”)));
String s = String.valueOf(companyPo.get(“AUDIT_STATUS”));
if(“E”.equals(s)){
newCompanyPo.setAuditStatusName(“审核通过”);
}else if(“N”.equals(s)){
newCompanyPo.setAuditStatusName(“待审核”);
}else if(“F”.equals(s)){
newCompanyPo.setAuditStatusName(“审核未通过”);
}
/公司CEO的名字/
String staff_name = String.valueOf(companyPo.get(“STAFF_NAME”));
if(!(“null”.equals(staff_name))){
/取出CEO电话/
String mobile = String.valueOf(companyPo.get(“MOBILE”));
newCompanyPo.setCompanyCEO(staff_name+": “+mobile);
}
Object create_date = companyPo.get(“CREATE_DATE”);
String time=String.valueOf(create_date);
String localTime = time.substring(0, time.length() - 2);
newCompanyPo.setCreateTime(localTime);
/然后根据公司id去查它下面对应的学校/
List schoolPos = schoolMapper.selectSchoolByCompanyId(String.valueOf(companyPo.get(“ID”)));
if(0!=schoolPos.size()){
StringBuffer stringBuffer = new StringBuffer();
for (SchoolPo schoolPo : schoolPos) {
stringBuffer.append(schoolPo.getSchoolName()+”,");
}
String toString = stringBuffer.toString();
String substring = toString.substring(0, toString.length() - 1);
newCompanyPo.setSchoolList(substring.toString());
}else{
newCompanyPo.setSchoolList(null);
}
newCompanyPosList.add(newCompanyPo);
}
return newCompanyPosList;
}
下面大致讲解
调用这个方法就可以了
需要在excel中显示的对象
package cn.com.doone.sc.tx.cloud.dyb.system.bean.po.company;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import java.util.Date;
import java.util.List;
/**
-
@author lliufei
*/
public class CompanyPo {
@ExcelIgnore
private String cId;
@ExcelIgnore
private String cityId;
@ExcelProperty(“地市”)
private String cityName;
@ExcelIgnore
private String countryId;
@ExcelProperty(“区县”)
private String countryName;
@ExcelProperty(“公司名称”)
@ColumnWidth(20)
private String companyName;
@ExcelProperty(“公司地址”)
@ColumnWidth(40)
private String companyAddress;
@ExcelProperty(“对应学校”)
@ColumnWidth(80)
private String schoolList;
private ListnewSchoolList;
@ExcelProperty(“公司CEO”)
@ColumnWidth(20)
private String companyCEO;
@ExcelIgnore
private String companyIntro;
@ExcelIgnore
private String companyStatus;
@ExcelIgnore
private String createTime;
@ExcelIgnore
private Date createDate;
@ExcelIgnore
private String state;
/审核状态/
@ExcelIgnore
private String auditStatus;
@ExcelProperty(“审核状态”)
@ColumnWidth(20)
private String auditStatusName;
@ExcelIgnore
private String staffId;
@ExcelIgnore
private Integer page;
@ExcelIgnore
private Integer limit;public String getcId() {
return cId;
}public void setcId(String cId) {
this.cId = cId;
}public String getCityId() {
return cityId;
}public void setCityId(String cityId) {
this.cityId = cityId;
}public String getCountryId() {
return countryId;
}public void setCountryId(String countryId) {
this.countryId = countryId;
}public String getCompanyName() {
return companyName;
}public void setCompanyName(String companyName) {
this.companyName = companyName;
}public String getCompanyAddress() {
return companyAddress;
}public void setCompanyAddress(String companyAddress) {
this.companyAddress = companyAddress;
}public String getSchoolList() {
return schoolList;
}public void setSchoolList(String schoolList) {
this.schoolList = schoolList;
}public String getCompanyCEO() {
return companyCEO;
}public void setCompanyCEO(String companyCEO) {
this.companyCEO = companyCEO;
}public String getCompanyIntro() {
return companyIntro;
}public void setCompanyIntro(String companyIntro) {
this.companyIntro = companyIntro;
}public Integer getPage() {
return page;
}public void setPage(Integer page) {
this.page = page;
}public Integer getLimit() {
return limit;
}public void setLimit(Integer limit) {
this.limit = limit;
}public String getCompanyStatus() {
return companyStatus;
}public void setCompanyStatus(String companyStatus) {
this.companyStatus = companyStatus;
}public String getCityName() {
return cityName;
}public void setCityName(String cityName) {
this.cityName = cityName;
}public String getCountryName() {
return countryName;
}public void setCountryName(String countryName) {
this.countryName = countryName;
}public Date getCreateDate() {
return createDate;
}public void setCreateDate(Date createDate) {
this.createDate = createDate;
}public String getState() {
return state;
}public void setState(String state) {
this.state = state;
}public String getStaffId() {
return staffId;
}public void setStaffId(String staffId) {
this.staffId = staffId;
}public String getCreateTime() {
return createTime;
}public void setCreateTime(String createTime) {
this.createTime = createTime;
}public String getAuditStatus() {
return auditStatus;
}public void setAuditStatus(String auditStatus) {
this.auditStatus = auditStatus;
}public String getAuditStatusName() {
return auditStatusName;
}public void setAuditStatusName(String auditStatusName) {
this.auditStatusName = auditStatusName;
}public List getNewSchoolList() {
return newSchoolList;
}public void setNewSchoolList(List newSchoolList) {
this.newSchoolList = newSchoolList;
}@Override
public String toString() {
return “CompanyPo{” +
“cId=’” + cId + ‘’’ +
“, cityId=’” + cityId + ‘’’ +
“, cityName=’” + cityName + ‘’’ +
“, countryId=’” + countryId + ‘’’ +
“, countryName=’” + countryName + ‘’’ +
“, companyName=’” + companyName + ‘’’ +
“, companyAddress=’” + companyAddress + ‘’’ +
“, schoolList=’” + schoolList + ‘’’ +
“, newSchoolList=” + newSchoolList +
“, companyCEO=’” + companyCEO + ‘’’ +
“, companyIntro=’” + companyIntro + ‘’’ +
“, companyStatus=’” + companyStatus + ‘’’ +
“, createTime=’” + createTime + ‘’’ +
“, createDate=” + createDate +
“, state=’” + state + ‘’’ +
“, auditStatus=’” + auditStatus + ‘’’ +
“, auditStatusName=’” + auditStatusName + ‘’’ +
“, staffId=’” + staffId + ‘’’ +
“, page=” + page +
“, limit=” + limit +
‘}’;
}
}