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

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日 */@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;    @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日 */@Servicepublic 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查询数据

欢迎留言讨论