使用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>