java - 使用spring boot和poi将4个表加入excel后创建一个excel表数据

Question

我想创建一个关于击中弹簧靴休息 Api 的 excel。我使用 POI jar 来创建 excel，但总是收到以下响应 "{ "timestamp": "2020-11-25T10:10:16.723+00:00", "message": "query did not return a unique result: 2; 嵌套异常是 javax.persistence.NonUniqueResultException: 查询没有返回唯一结果: 2", "status": 500 }请帮助我是个大人物"

// Exceel helper class
public class ExcelHelper {

    public static String TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    static String[] HEADERs = { 
            "studentRollNo", 
            "studentFullName",
            "studentFatherName",
            "studentMotherName",
            "studentDob",
            "studentGender",
            "studentEmail", 
            "studentContactNo",
            "studentAddress",
            "studentZipCode",
            "studentState",
            "studentCity",
            "studentEducationId",
            "studentEducationSscMarks",
            "studentEducationSscPassYear",
            "studentEducationHsscMarks",
            "studentEducationHsscStream",
            "studentEducationHsscPasYear",
            "studentEducationGradCourse",
            "studentEducationGradsCgpa", 
            "studentEducationGradPassYear",
            "studentEducationPgCourse",
            "studentEducationPgCgpa",
            "studentEducationPgPassYear",
            "studentEducationYear",
            "studentEducationGapReason",
            "studentcertificationId",
            "studentcertificationOrgiDetails",
            "studentCertificationTitle",
            "studentcertificationIssueDate"
            };
    
    public static ByteArrayInputStream tutorialsToExcel(List<ExcelPojo> student ) {

        try (Workbook workbook = new XSSFWorkbook(); 
                ByteArrayOutputStream out = new ByteArrayOutputStream();) 
        {
            CreationHelper createHelper = workbook.getCreationHelper();
            Sheet sheet = workbook.createSheet();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setColor(IndexedColors.BLUE.getIndex());
            CellStyle headerCellStyle = workbook.createCellStyle();
            headerCellStyle.setFont(headerFont);
          // Row for Header
          Row headerRow = sheet.createRow(0);
          // Header
          for (int col = 0; col < HEADERs.length; col++) {
            Cell cell = headerRow.createCell(col);
            cell.setCellValue(HEADERs[col]);
            cell.setCellStyle(headerCellStyle);
          }
       // CellStyle for Age
          CellStyle ageCellStyle = workbook.createCellStyle();
          ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
          
          int rowIdx = 1;
          for (ExcelPojo tutorial : student) {
            Row row = sheet.createRow(rowIdx++);

            row.createCell(0).setCellValue(tutorial.getStudentRollNo());
            row.createCell(1).setCellValue(tutorial.getStudentFullName());
            row.createCell(2).setCellValue(tutorial.getStudentFatherName());
            row.createCell(3).setCellValue(tutorial.getStudentMotherName());
            row.createCell(4).setCellValue(tutorial.getStudentDob());
            row.createCell(5).setCellValue(tutorial.getStudentGender());
            row.createCell(6).setCellValue(tutorial.getStudentEmail());
            row.createCell(7).setCellValue(tutorial.getStudentContactNo());
            row.createCell(8).setCellValue(tutorial.getStudentAddress());
            row.createCell(9).setCellValue(tutorial.getStudentZipCode());
            row.createCell(10).setCellValue(tutorial.getStudentState());
            row.createCell(11).setCellValue(tutorial.getStudentCcity());
            row.createCell(12).setCellValue(tutorial.getStudentEducationId());
            row.createCell(13).setCellValue(tutorial.getStudentEducationSscMarks());
            row.createCell(14).setCellValue(tutorial.getStudentEducationSscPassYear());
            row.createCell(15).setCellValue(tutorial.getStudentEducationHsscMarks());
            row.createCell(16).setCellValue(tutorial.getStudentEducationHsscStream());
            row.createCell(17).setCellValue(tutorial.getStudentEducationHsscPasYear());
            row.createCell(18).setCellValue(tutorial.getStudentEducationGradCourse());
            row.createCell(19).setCellValue(tutorial.getStudentEducationGradsCgpa());
            row.createCell(20).setCellValue(tutorial.getStudentEducationGradPassYear());
            row.createCell(21).setCellValue(tutorial.getStudentEducationPgCourse());
            row.createCell(22).setCellValue(tutorial.getStudentEducationPgCgpa());
            row.createCell(23).setCellValue(tutorial.getStudentEducationPgPassYear());
            row.createCell(24).setCellValue(tutorial.getStudentEducationYear());
            row.createCell(25).setCellValue(tutorial.getStudentEducationGapReason());
            row.createCell(26).setCellValue(tutorial.getStudentcertificationId());
            row.createCell(27).setCellValue(tutorial.getStudentcertificationOrgiDetails());
            row.createCell(28).setCellValue(tutorial.getStudentcertificationTitle());
            row.createCell(29).setCellValue(tutorial.getStudentcertificationIssueDate());
          }

          workbook.write(out);
          return new ByteArrayInputStream(out.toByteArray());
        } catch (IOException e) {
          throw new RuntimeException("fail to import data to Excel file: " + e.getMessage());
        }
      }
    
}




// POJO 
public class ExcelPojo {
    
    private String studentRollNo;
    private String studentFullName;
    private String studentFatherName;
    private String studentMotherName;
    private String studentDob;
    private String studentGender;
    private String studentEmail; 
    private String studentContactNo;
    private String studentAddress;
    private int studentZipCode;
    private String studentState;
    private String studentCcity;
    private long studentEducationId;
    private float studentEducationSscMarks;
    private int studentEducationSscPassYear;
    private float studentEducationHsscMarks;
    private String studentEducationHsscStream;
    private int studentEducationHsscPasYear;
    private String studentEducationGradCourse;
    private float studentEducationGradsCgpa; 
    private int studentEducationGradPassYear;
    private String StudentEducationPgCourse;
    private float studentEducationPgCgpa;
    private int studentEducationPgPassYear;
    private int studentEducationYear;
    private String studentEducationGapReason;
    private String studentcertificationId; 
    private String studentcertificationOrgiDetails; 
    private String studentcertificationTitle; 
    private String studentcertificationIssueDate;
    
    public String getStudentGender() {
        return studentGender;
    }
    public void setStudentGender(String studentGender) {
        this.studentGender = studentGender;
    }
    public String getStudentcertificationIssueDate() {
        return studentcertificationIssueDate;
    }
    public void setStudentcertificationIssueDate(String studentcertificationIssueDate) {
        this.studentcertificationIssueDate = studentcertificationIssueDate;
    }

    

    public String getStudentRollNo() {
        return studentRollNo;
    }
    public void setStudentRollNo(String studentRollNo) {
        this.studentRollNo = studentRollNo;
    }
    public String getStudentFullName() {
        return studentFullName;
    }
    public void setStudentFullName(String studentFullName) {
        this.studentFullName = studentFullName;
    }
    public String getStudentFatherName() {
        return studentFatherName;
    }
    public void setStudentFatherName(String studentFatherName) {
        this.studentFatherName = studentFatherName;
    }
    public String getStudentMotherName() {
        return studentMotherName;
    }
    public void setStudentMotherName(String studentMotherName) {
        this.studentMotherName = studentMotherName;
    }
    public String getStudentDob() {
        return studentDob;
    }
    public void setStudentDob(String studentDob) {
        this.studentDob = studentDob;
    }
    
    public String getStudentEmail() {
        return studentEmail;
    }
    public void setStudentEmail(String studentEmail) {
        this.studentEmail = studentEmail;
    }
    public String getStudentContactNo() {
        return studentContactNo;
    }
    public void setStudentContactNo(String studentContactNo) {
        this.studentContactNo = studentContactNo;
    }
    public String getStudentAddress() {
        return studentAddress;
    }
    public void setStudentAddress(String studentAddress) {
        this.studentAddress = studentAddress;
    }
    public int getStudentZipCode() {
        return studentZipCode;
    }
    public void setStudentZipCode(int studentZipCode) {
        this.studentZipCode = studentZipCode;
    }
    public String getStudentState() {
        return studentState;
    }
    public void setStudentState(String studentState) {
        this.studentState = studentState;
    }
    public String getStudentCcity() {
        return studentCcity;
    }
    public void setStudentCcity(String studentCcity) {
        this.studentCcity = studentCcity;
    }
    public long getStudentEducationId() {
        return studentEducationId;
    }
    public void setStudentEducationId(long studentEducationId) {
        this.studentEducationId = studentEducationId;
    }
    public float getStudentEducationSscMarks() {
        return studentEducationSscMarks;
    }
    public void setStudentEducationSscMarks(float studentEducationSscMarks) {
        this.studentEducationSscMarks = studentEducationSscMarks;
    }
    public int getStudentEducationSscPassYear() {
        return studentEducationSscPassYear;
    }
    public void setStudentEducationSscPassYear(int studentEducationSscPassYear) {
        this.studentEducationSscPassYear = studentEducationSscPassYear;
    }
    public float getStudentEducationHsscMarks() {
        return studentEducationHsscMarks;
    }
    public void setStudentEducationHsscMarks(float studentEducationHsscMarks) {
        this.studentEducationHsscMarks = studentEducationHsscMarks;
    }
    public String getStudentEducationHsscStream() {
        return studentEducationHsscStream;
    }
    public void setStudentEducationHsscStream(String studentEducationHsscStream) {
        this.studentEducationHsscStream = studentEducationHsscStream;
    }
    public int getStudentEducationHsscPasYear() {
        return studentEducationHsscPasYear;
    }
    public void setStudentEducationHsscPasYear(int studentEducationHsscPasYear) {
        this.studentEducationHsscPasYear = studentEducationHsscPasYear;
    }
    public String getStudentEducationGradCourse() {
        return studentEducationGradCourse;
    }
    public void setStudentEducationGradCourse(String studentEducationGradCourse) {
        this.studentEducationGradCourse = studentEducationGradCourse;
    }
    public float getStudentEducationGradsCgpa() {
        return studentEducationGradsCgpa;
    }
    public void setStudentEducationGradsCgpa(float studentEducationGradsCgpa) {
        this.studentEducationGradsCgpa = studentEducationGradsCgpa;
    }
    public int getStudentEducationGradPassYear() {
        return studentEducationGradPassYear;
    }
    public void setStudentEducationGradPassYear(int studentEducationGradPassYear) {
        this.studentEducationGradPassYear = studentEducationGradPassYear;
    }
    public String getStudentEducationPgCourse() {
        return StudentEducationPgCourse;
    }
    public void setStudentEducationPgCourse(String studentEducationPgCourse) {
        StudentEducationPgCourse = studentEducationPgCourse;
    }
    public float getStudentEducationPgCgpa() {
        return studentEducationPgCgpa;
    }
    public void setStudentEducationPgCgpa(float studentEducationPgCgpa) {
        this.studentEducationPgCgpa = studentEducationPgCgpa;
    }
    public int getStudentEducationPgPassYear() {
        return studentEducationPgPassYear;
    }
    public void setStudentEducationPgPassYear(int studentEducationPgPassYear) {
        this.studentEducationPgPassYear = studentEducationPgPassYear;
    }
    public int getStudentEducationYear() {
        return studentEducationYear;
    }
    public void setStudentEducationYear(int studentEducationYear) {
        this.studentEducationYear = studentEducationYear;
    }
    public String getStudentEducationGapReason() {
        return studentEducationGapReason;
    }
    public void setStudentEducationGapReason(String studentEducationGapReason) {
        this.studentEducationGapReason = studentEducationGapReason;
    }
    public String getStudentcertificationId() {
        return studentcertificationId;
    }
    public void setStudentcertificationId(String studentcertificationId) {
        this.studentcertificationId = studentcertificationId;
    }
    public String getStudentcertificationOrgiDetails() {
        return studentcertificationOrgiDetails;
    }
    public void setStudentcertificationOrgiDetails(String studentcertificationOrgiDetails) {
        this.studentcertificationOrgiDetails = studentcertificationOrgiDetails;
    }
    public String getStudentcertificationTitle() {
        return studentcertificationTitle;
    }
    public void setStudentcertificationTitle(String studentcertificationTitle) {
        this.studentcertificationTitle = studentcertificationTitle;
    }
    
    public ExcelPojo() {
        
    }

}
// REST CONTROLLER 

    @GetMapping("/download/{companyId}")
     public ResponseEntity<InputStreamResource> excelCustomersReport(@PathVariable  long companyId) throws IOException {
        List<ExcelPojo> customers =  companyService.load(companyId);
                
    
    ByteArrayInputStream in = ExcelHelper.tutorialsToExcel(customers);
    // return IOUtils.toByteArray(in);
    
    HttpHeaders headers = new HttpHeaders();
        headers.add("Content-Disposition", "attachment; filename="+companyId+".xlsx");
    
     return ResponseEntity
                  .ok()
                  .headers(headers)
                  .body(new InputStreamResource(in));
    }
    
// Service implementation

    public List<ExcelPojo> load(long companyId) {
        
        Set<StudentPersonalDetails> list= getPersonalDetails(companyId);
        List<ExcelPojo> tutorials = new ArrayList<ExcelPojo>();
        for(StudentPersonalDetails student :list) {
            ExcelPojo excel = new ExcelPojo();
            excel.setStudentRollNo(student.getRollNo());
            excel.setStudentFullName(student.getFullName());
            excel.setStudentFatherName(student.getFatherName());
            excel.setStudentMotherName(student.getMotherName());
            
            if(student.getGender()==0){
                    excel.setStudentGender("Male");
                }else {
                    excel.setStudentGender("Female");
            }
            excel.setStudentEmail(student.getEmail());
            excel.setStudentContactNo(student.getContactNo());
            excel.setStudentAddress(student.getAddress());
            excel.setStudentZipCode(student.getZipCode());
            excel.setStudentState(student.getState());
            excel.setStudentCcity(student.getCity());
            StudentEducationalDetails education = studentEduDao.findByStu(student);
            excel.setStudentEducationId(education.getId());
            excel.setStudentEducationSscMarks(education.getSscMarks());
            excel.setStudentEducationSscPassYear(education.getSscPassYear());
            excel.setStudentEducationHsscMarks(education.getHsscMarks());
            excel.setStudentEducationHsscStream(education.getHsscStream());
            excel.setStudentEducationHsscPasYear(education.getHsscPasYear());
            excel.setStudentEducationGradCourse(education.getGradCourse());
            excel.setStudentEducationGradsCgpa(education.getGradsCgpa());
            excel.setStudentEducationGradPassYear(education.getGradPassYear());
            excel.setStudentEducationPgCourse(education.getPgCourse());
            excel.setStudentEducationPgCgpa(education.getPgCgpa());
            excel.setStudentEducationPgPassYear(education.getPgPassYear());
            excel.setStudentEducationYear(education.getYear());
            excel.setStudentEducationGapReason(education.getGapReason());
            List<StudentCertification> certi = studentCertiDao.findByStu(student);
            if(certi.isEmpty()) {
                excel.setStudentcertificationId(null);
                excel.setStudentcertificationOrgiDetails(null);
                excel.setStudentcertificationTitle(null);
                excel.setStudentcertificationIssueDate(null);
                
            }else {
                StringBuffer sb[] = new StringBuffer[4];
                boolean isFirst= true;
            for(StudentCertification certifica :certi) {
                if(! isFirst) {
                    sb[0].append(", ");
                    sb[1].append(", ");
                    sb[2].append(", ");
                    sb[3].append(", ");
                }
                sb[0].append(certifica.getCertificateId());
                sb[2].append(certifica.getCertiTitle());
                sb[1].append(certifica.getOrgiDetails());
                sb[3].append(certifica.getIssueDate());
                isFirst= false;         }
            excel.setStudentcertificationId(sb[0].toString());
            excel.setStudentcertificationOrgiDetails(sb[1].toString());
            excel.setStudentcertificationTitle(sb[2].toString());
            excel.setStudentcertificationIssueDate(sb[3].toString());
            
            }
            tutorials.add(excel);
        }
        
        return tutorials;
        
    }

标签： javaexcelspring-bootapiapache-poi

Answer 1

javax.persistence.NonUniqueResultException 错误是 JPA 而非 POI 的典型错误，请添加您的查询代码。

通常发生在您使用 getSingleResult() 方法并且查询返回多个结果时。