package com.xmyq.pojo;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.Value;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import java.util.Date;
@Data
@TableName("report")
@AllArgsConstructor
@NoArgsConstructor
public class Report {
private static final long serialVersionUID = 1L;
@TableId(type = IdType.AUTO)
@GeneratedValue
/**
* id
*/
@Column(name = "rep_id")
private Long repId;
/**
* 报告编号
*/
@ExcelProperty(value = "报告编号")
@Column(name = "rep_number")
private String repNumber;
/**
* 关联的合同id
*/
@ExcelProperty(value = "关联的合同id")
@Column(name = "rep_order")
private Long repOrder;
/**
* 生成时间
*/
@ExcelProperty(value = "生成时间")
@Column(name = "rep_time")
private Date repTime;
/**
* 更新时间
*/
@ExcelProperty(value = "更新时间")
@Column(name = "update_time")
private Date updateTime;
/**
* 外键id
*/
@Column(name = "rep_fk_id")
@ExcelProperty(value = "外键id")
private String repFkId;
/**
* pdf地址
*/
@Column(name = "rep_pdf_url")
@ExcelProperty(value = "pdf地址")
private String repPdfUrl;
/**
* 创建者
*/
@Column(name = "create_by")
@ExcelProperty(value = "创建者")
private Long createBy;
/**
* 更新者
*/
@Column(name = "update_by")
@ExcelProperty(value = "更新者")
private Long updateBy;
/**
* 文件路径
*/
@Column(name = "fil_dir")
@ExcelProperty(value = "文件路径")
private String filDir;
/**
* 文件名称
*/
@ExcelProperty(value = "文件名称")
@Column(name = "fil_name")
private String filName;
/**
* 报告状态
*/
@ExcelProperty(value = "报告状态")
@Column(name = "rep_status")
private Integer repStatus;
/**
* 审核意见
*/
@Column(name = "rep_audit_opinion")
@ExcelProperty(value = "审核意见")
private String repAuditOpinion;
/**
* 审批意见
*/
@Column(name = "rep_approval_opinion")
@ExcelProperty(value = "审批意见")
private String repApprovalOpinion;
/**
* 用户名
*/
@Column(name = "user_name")
@ExcelProperty(value = "用户名")
private String userName;
/**
* 状态
*/
@Column(name = "status")
@ExcelProperty(value = "状态")
private int status;
/**
* 创建时间
*/
@Column(name = "create_time")
@ExcelProperty(value = "创建时间")
private Date createTime;
}
package com.xmyq.controller;
import com.xmyq.service.ReportService;
import org.apache.ibatis.annotations.Param;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Base64;
@RestController
@RequestMapping("/reports")
public class ReportController {
@Autowired
private ReportService reportService;
@GetMapping("/export/excel")
public String exportExcel(HttpServletResponse response) {
return reportService.exportExcel(response);
}
@RequestMapping("download")
public void download(@Param("source") String source, HttpServletRequest request, HttpServletResponse response) throws Exception{
File file = new File(source);
if (file == null || !file.exists()) {
throw new FileNotFoundException("请求的文件不存在");
}
OutputStream out = null;
try {
response.reset();
response.setContentType("application/octet-stream; charset=utf-8");
String agent = (String)request.getHeader("USER-AGENT");
String fileName = file.getName();
if(agent != null && agent.indexOf("MSIE") == -1) {
// FF
String enableFileName = "=?UTF-8?B?" + (new String(Base64.getEncoder().encode(fileName.getBytes("UTF-8")))) + "?=";
response.setHeader("Content-Disposition", "attachment; filename=" + enableFileName); }
else {
// IE
String enableFileName = new String(fileName.getBytes("GBK"), "ISO-8859-1");
response.setHeader("Content-Disposition", "attachment; filename=" + enableFileName);
}
// response.setHeader("Content-Disposition", "attachment; filename=" + file.getName());
out = response.getOutputStream();
out.write(org.apache.commons.io.FileUtils.readFileToByteArray(file));
out.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
package com.xmyq.service.impl;
import com.xmyq.mapper.ReportMapper;
import com.xmyq.pojo.Report;
import com.xmyq.service.ReportService;
import org.apache.commons.compress.utils.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
@Service
public class ReportServiceImpl implements ReportService {
@Autowired
private ReportMapper reportMapper;
public String exportExcel(HttpServletResponse response) {
//查询数据库获取营业数据
List<Report> reportList = reportMapper.excelBusinessData();
try {
//通过poi将数据写入到excel文件中
// 读取模板文件
InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("template/template.xlsx");
XSSFWorkbook excel = new XSSFWorkbook(resourceAsStream);
XSSFSheet sheet1= excel.getSheet("Sheet1");
if (sheet1 == null) {
throw new RuntimeException("模板文件中没有名为'Sheet1'的工作表");
}
// 填充数据
int rowNum = 1;
for (Report report : reportList) {
XSSFRow row = sheet1.createRow(rowNum++);
row.createCell(0).setCellValue(report.getFilName());
row.createCell(1).setCellValue(report.getRepOrder());
row.createCell(2).setCellValue(report.getRepPdfUrl());
row.createCell(3).setCellValue(report.getStatus());
row.createCell(4).setCellValue(report.getUserName());
}
// 生成文件名
String fileName = "exportedFile.xlsx";
String filePath = fileName;
// 写入到文件
try (FileOutputStream fileOut = new FileOutputStream(filePath)) {
excel.write(fileOut);
}
// 上传文件到Web服务器
// 这里需要根据具体的Web服务器进行文件上传操作，并获取上传后的URL地址
String fileUrl = " http://localhost:9999/file/download?source=" + fileName;
http://localhost:9999/file/download?source=
return fileUrl; // 返回文件URL地址
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
这是模版存放位置
