SpringBoot+Mybatis+EasyExcel查询数据并且导出

 使用EasyExcel库

 <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.4</version>
        </dependency>

实体类，也就是查询结果的类

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


@Data
@NoArgsConstructor
@AllArgsConstructor
public class ResultQuery {
    @ExcelProperty("条码")
    private String LotSN;
    @ExcelProperty("盘号")
    private String TraySN;
    @ExcelProperty("箱号")
    private String BoxSN;
    @ExcelProperty("栈板号")
    private String PalletSN;
}

 服务类，导入导出

 

package mybatismes.Service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import jakarta.servlet.http.HttpServletResponse;
import mybatismes.Mapper.UserMapper;
import mybatismes.jojo.ResultQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.web.bind.annotation.*;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.util.List;
@RestController
@RequestMapping("/excel")
public class ExcelServiceD {
    @Autowired
    private UserMapper userMapper;
    @GetMapping("/export")
    public String exportExcel(@RequestParam String SpecifitionName,@RequestParam String ResourceName,
                              @RequestParam("StationTime") @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDateTime StationTime,
                              @RequestParam ("EndTime") @DateTimeFormat(iso = DateTimeFormat.ISO.DATE_TIME) LocalDateTime EndTime,
                              @RequestParam String ProductName, HttpServletResponse response) throws IOException {

        List<ResultQuery> userList = userMapper.SeleQuer(SpecifitionName,ResourceName,StationTime,EndTime,ProductName);
        if (userList.isEmpty()) {
            return "没有相关数据";
        }
        StringBuilder sb = new StringBuilder();
        userList.forEach(user -> {
                    sb.append(user.getLotSN()).append(user.getTraySN())
                    .append(user.getBoxSN()).append(user.getPalletSN());
        });
        long t1 =System.currentTimeMillis();

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); // 正确的内容类型用于.xlsx文件
        response.setCharacterEncoding("utf-8");

        String fileName = URLEncoder.encode("机台产出信息"+t1+".xls", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");

        try (OutputStream outputStream = response.getOutputStream()) {
            ExcelWriter excelWriter = EasyExcel.write(outputStream, ResultQuery.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(2,"机台产出信息").build();
            excelWriter.write(userList, writeSheet);
            excelWriter.finish();
        }
        return "下载成功";
    }
}

 Mapper查询语句，这里不方便展示。查询语句可以替换。使用@Param是将前端传回来的查询条件传递给查询语句中

List<ResultQuery> SeleQuer(@Param("SpecifitionName") String SpecifitionName,
                           @Param("ResourceName")String ResourceName, @Param("StationTime")LocalDateTime  StationTime,
                           @Param("EndTime") LocalDateTime EndTime, @Param("ProductName") String ProductName);

前端就是表单类型的，然后就是请求数据

<script>
    function submitForm(event) {
        event.preventDefault(); // 阻止默认的表单提交行为

        const formData = new FormData(event.target);

        fetch('/excel/export', {
            method: 'GET',
            headers: {
                'Content-Type': 'application/x-www-form-urlencoded'
            },
            body: new URLSearchParams(formData)
        })
            .then(response => response.json())
            .then(data => {
                if (data.message) {
                    showAlert(data.message);
                } else {
                    // 处理成功的情况
                    console.host('下载成功');
                }
            })
            .catch(error => {
                console.error('Error:', error);
            });

        return false; // 阻止默认提交
    }

    function showAlert(message) {
        alert(message);
    }
</script>