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

easyexcel导出一篇解决 详细解析

导入依赖

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
</dependency>
<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.62</version>
</dependency>

新建实体类

@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<String, Object> 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<String, Object> bodyMap,HttpServletResponse response) throws ParseException {
        //设置导出路径
        String fileName = "设备档案.xlsx";
        //获取表单数据
        List<DeviceExport> 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查询数据

欢迎留言讨论