easyexcel导出数据库数据教程以及以流的形式传给前端

easyexcel导出一篇解决 详细解析

导入依赖

com.alibabaeasyexcel3.0.5com.alibabafastjson1.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日 */@Datapublic 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;@ExcelIgnoreprivate 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 {@Autowiredprivate DeviceSevice deviceSevice;/** * Excel批量导出数据 * @param */@ApiOperation(value = "导出设备档案", notes = "POST请求，导出设备档案")@PostMapping(value = "/exportDeviceList")publicApiResponseBody 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日 */@Servicepublic class DeviceServiceImpl implements DeviceSevice {@Autowiredprivate DeviceMapper deviceMapper;String path = "D:\\";@Overridepublic 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查询数据

欢迎留言讨论