easyexcel导出一篇解决 详细解析
导入依赖
com.alibaba
easyexcel
3.0.5
com.alibaba
fastjson
1.2.62
新建实体类
@ExcelProperty (需要导出的字段)
@ExcelIgnore (不需要导出的字段)
@ColumnWidth(15) (可以设置宽度)
package com.hzwaterit.iot.device.entity;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import java.io.Serializable;
/**
* @author waterit063
* @ClassName DeviceEntity
* @description: TODO
* @date 2023年06月06日
*/
@Data
public class DeviceExport implements Serializable {
@ColumnWidth(15)
@ExcelProperty(value = "设备编号", index = 0)
private String Id;
@ColumnWidth(30)
@ExcelProperty(value = "安装地址", index = 1)
private String InstallAddress;
@ColumnWidth(11)
@ExcelProperty(value = "状态", index = 2)
private String State;
@ColumnWidth(11)
@ExcelProperty(value = "所属单位", index = 3)
private String Company;
@ColumnWidth(11)
@ExcelProperty(value = "所属厂家", index = 4)
private String Factory;
@ColumnWidth(11)
@ExcelProperty(value = "设备类型",index = 5)
private String ProductId;
@ExcelIgnore
private Long CommTime;
@ColumnWidth(15)
@ExcelProperty(value = "最后通讯时间", index = 6)
private String EndTime;
}
controller层
//前端传给后端数据我是以map接收,也可以表单接收
package com.hzwaterit.iot.device.controller;
import com.fawkes.core.base.api.ApiResponseBody;
import com.hzwaterit.iot.device.service.DeviceSevice;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.text.ParseException;
import java.util.Map;
/**
* @author lsh
* @ClassName DeviceController
* @description: TODO
* @date 2023年06月06日
*/
@RestController
@RequestMapping("/device/")
@Api(value = "Device", description = "设备管理")
public class DeviceController {
@Autowired
private DeviceSevice deviceSevice;
/**
* Excel批量导出数据
* @param
*/
@ApiOperation(value = "导出设备档案", notes = "POST请求,导出设备档案")
@PostMapping(value = "/exportDeviceList")
public ApiResponseBody exportDeviceList(@RequestBody Map BodyMap, HttpServletResponse response) throws ParseException {
return deviceSevice.exportDeviceList(BodyMap, response);
}
}
serviceimpl层
//这里如果前端不需要流的形式接收,而是想直接导出,就把response.setHeader去掉,response.setHeader替换成fileName 路径+文件名.xlsx
response.setHeader(设置导出文件名)
response.getOutputStream() (转成字符流的形式)
//然后list是数据库查询结果集合,直接写入
package com.hzwaterit.iot.device.service.impl;
import com.alibaba.excel.EasyExcel;
import com.fawkes.core.base.api.ApiResponseBody;
import com.hzwaterit.iot.device.entity.DeviceExport;
import com.hzwaterit.iot.device.entity.DeviceImport;
import com.hzwaterit.iot.device.mapper.DeviceMapper;
import com.hzwaterit.iot.device.service.DeviceSevice;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* @author waterit063
* @ClassName DeviceServiceImpl
* @description: TODO
* @date 2023年06月06日
*/
@Service
public class DeviceServiceImpl implements DeviceSevice {
@Autowired
private DeviceMapper deviceMapper;
String path = "D:\";
@Override
public ApiResponseBody exportDeviceList(Map bodyMap,HttpServletResponse response) throws ParseException {
//设置导出路径
String fileName = "设备档案.xlsx";
//获取表单数据
List list = deviceMapper.queryDevice(bodyMap);
//指定实体类class类,以字符流的格式导出
//这里如果前端不需要流的形式接收,而是想直接导出,就把response.setHeader去掉,response.setHeader替换成fileName 路径+名字
try {
response.setHeader("content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8"));
EasyExcel.write(response.getOutputStream(), DeviceExport.class).sheet("模板").doWrite(list);
response.getOutputStream().close();
}catch (Exception e){
return ApiResponseBody.error(500, "数据导出失败");
}
return ApiResponseBody.success(200, "导出成功");
}
}
//接下来就是去mappper.xml查询数据
欢迎留言讨论