如何使用Spring Data JPA上传Excel文件或从Excel文件导入数据

huangapple go评论87阅读模式
英文:

How to upload excel or import data from excel file using Spring Data JPA

问题

以下是翻译好的内容:

控制器部分代码:

@RestController
@RequestMapping("api/auditors_report")
public class AuditorStampDutyReportController {

    @Autowired
    public AuditorStampDutyReportRepository auditorStampDutyReportRepository;

    @Autowired
    public AuditorStampDutyReportService auditorStampDutyReportService;

    @Transactional
    @PostMapping(path = "/upload", consumes = MediaType.MULTIPART_FORM_DATA_VALUE, produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<Object> uploadAuditorReport(@RequestPart("file") MultipartFile file) {
        List<AuditorStampDutyReport> uploadExcelSheet = auditorStampDutyReportService.importAuditorStampDutyReportDetails(file);
        return ResponseEntity.ok(new JsonResponse("See Data object for Details!", uploadExcelSheet));
    }
}

Excel帮助类部分代码:

@Component
@Data
public class ImportExcelHelper {
    // ... 省略其他方法
}

服务部分代码:

@Transactional
public List<AuditorStampDutyReport> importAuditorStampDutyReportDetails(MultipartFile mfile) {
    // ... 省略部分代码
}

DTO部分代码:

import lombok.Data;
import java.time.LocalDate;

@Data
public class AuditorStampDutyReportDto {
    // ... 省略属性
}

主实体类部分代码:

import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;
import java.time.LocalDate;

@Data
@Entity
@Table(name = "auditor_stamp_duty_report")
public class AuditorStampDutyReport implements Serializable {
    // ... 省略属性
}

Postman的错误信息部分翻译:

{
    "status": 415,
    "error": "不支持的媒体类型",
    "trace": "org.springframework.web.HttpMediaTypeNotSupportedException: 不支持的内容类型 ''\r\n\tat org.springframework.web.servlet.mvc.method.RequestMappingInfoHandlerMapping.handleNoMatch(RequestMappingInfoHandlerMapping.java:227)\r\n\tat org.springframework.web.servlet.handler.AbstractHandlerMethodMapping.lookupHandlerMethod(AbstractHandlerMethodMapping.java:422)\r\n\tat org.springframework.web.servlet.handler.AbstractHandlerMethodMapping.getHandlerInternal(AbstractHandlerMethodMapping.java:367)\r\n\tat org.springframework.web.servlet.mvc.method..."
}

如有需要,你可以继续提问。

英文:

I am trying to import data from excel sheet using DTO in Spring Data Jpa. However whenever I make the post from postman, I get an error that says media type not supported.

please see my postman settings in the screenshot:

如何使用Spring Data JPA上传Excel文件或从Excel文件导入数据

please find the code for controller below:

@RestController
@RequestMapping(&quot;api/auditors_report&quot;)
public class AuditorStampDutyReportController {

    @Autowired
    public AuditorStampDutyReportRepository auditorStampDutyReportRepository;

    @Autowired
    public AuditorStampDutyReportService auditorStampDutyReportService;


@Transactional
    @PostMapping(path = &quot;/upload&quot;, consumes = MediaType.MULTIPART_FORM_DATA_VALUE,produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity&lt;Object&gt; uploadAuditorReport(@RequestPart(&quot;file&quot;)MultipartFile file){
    List &lt;AuditorStampDutyReport&gt; uploadExcelSheet = auditorStampDutyReportService.importAuditorStampDutyReportDetails(file);
    return ResponseEntity.ok(new JsonResponse(&quot;See Data object for Details!&quot;, uploadExcelSheet));
}

}

here is my excel helper

@Component
@Data
public class ImportExcelHelper {
    
    public static String getOrDefault(Cell cell, String defaultValue) {
        return isCellEmpty(cell) ? defaultValue : getString(cell);
    }

    public static Double getDouble(Cell cell) {
        return cell.getCellType() == CellType.STRING ? Double.valueOf(cell.getStringCellValue()) : cell.getNumericCellValue();
    }

    public static Integer getInteger(Cell cell){
        return cell.getCellType() == CellType.STRING ?
                Integer.valueOf(cell.getStringCellValue()) : Integer.valueOf(cell.getNumericCellValue()+&quot;&quot;);
    }

    public static Long getLong(Cell cell){
        return  cell.getCellType() == CellType.STRING ?
                Long.valueOf(cell.getStringCellValue()): Long.valueOf(cell.getNumericCellValue()+ &quot;&quot;);
    }

    public static String getString(Cell cell) {
        return cell.getCellType() == CellType.NUMERIC ? String.valueOf(cell.getNumericCellValue()) : cell.getStringCellValue().trim();
    }
    
    public static boolean isCellEmpty(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return true;
        }
        return cell.getCellType() == CellType.STRING &amp;&amp; cell.getStringCellValue().trim().isEmpty();
    }

    public static LocalDate getDate(Cell cell) {
        if (isCellEmpty(cell)) {
            return null;
        }
        try {
            Date date = cell.getDateCellValue();
            return date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        } catch (Exception e) {
            return getDate(cell.getStringCellValue());
        }
    }

    public static LocalDate getDate(String date) {
        DateTimeFormatter formatter = new DateTimeFormatterBuilder()
                .appendOptional(DateTimeFormatter.ofPattern(&quot;d/MM/yyyy&quot;))
                .appendOptional(DateTimeFormatter.ofPattern(&quot;d/M/yyyy&quot;))
                .appendOptional(DateTimeFormatter.ofPattern(&quot;dd/M/yyyy&quot;))
                .appendOptional(DateTimeFormatter.ofPattern(&quot;dd/MM/yyyy&quot;))
                .toFormatter();
        return LocalDate.parse(date, formatter);
    }

    
}


here is my service:

    @Transactional
    public List &lt;AuditorStampDutyReport&gt; importAuditorStampDutyReportDetails(MultipartFile mfile) {

        List&lt;AuditorStampDutyReport&gt; uploadReport = new ArrayList&lt;&gt;();
        AuditorStampDutyReport excelReport = new AuditorStampDutyReport();

        int sheetNumber = 0, rowNumber = 0;
        try {
            Workbook workbook = new XSSFWorkbook(mfile.getInputStream());
            for (int i = 0; i &lt; workbook.getNumberOfSheets(); i++) {
                sheetNumber = i;
                Sheet sheet = workbook.getSheetAt(i);
                for (Row row : sheet) {
                    rowNumber = row.getRowNum();
                    if (rowNumber &lt; 2) {
                        String possibleTaxPayerId = ImportExcelHelper.getOrDefault(row.getCell(1), &quot;&quot;).trim().replaceAll(&quot; +&quot;, &quot; &quot;);
                        Organization organization = null;
                        //for name of tax payer
                        if (possibleTaxPayerId != null) {
                            organization = organizationRepository.findFirstNameByNameLike(organization.getId(), possibleTaxPayerId);
                        }
                        excelReport.setOrganization(organization);
                        excelReport.setTin_of_tax_payer_organization(ImportExcelHelper.getString(row.getCell(2)));
                        excelReport.setPeriod_covered(ImportExcelHelper.getOrDefault(row.getCell(3), &quot;&quot;));

                        continue;
                    }

                    if (rowNumber &gt; 2 &amp;&amp; rowNumber &lt; 6){
                        excelReport.setDate_of_transaction(ImportExcelHelper.getDate(row.getCell(2)));
                        excelReport.setDuty_payer(ImportExcelHelper.getString(row.getCell(3)));
                        excelReport.setTin_of_duty_payer(ImportExcelHelper.getString(row.getCell(4)));

                        String possibleInstrumentId = ImportExcelHelper.getOrDefault(row.getCell(5), &quot;&quot;);
                        Instruments instruments = null;
                        if (possibleInstrumentId != null) {
                            instruments = instrumentsRepository.findByNameLike(instruments.getId(), possibleInstrumentId);
                        }
                        excelReport.setInstruments(instruments);

                        excelReport.setAssessment_number(ImportExcelHelper.getLong(row.getCell(6)));

                        DeedOfAssignment deedOfAssignment = null;
if(deedOfAssignment.getId()!=null &amp;&amp; deedOfAssignment.getId()&gt;0) {

}}

here is my dto

import lombok.Data;
import java.time.LocalDate;

@Data
public class AuditorStampDutyReportDto {

    private  Long id;

    private Long id_of_tax_payer_organization;

    private String duty_payer;

    private String tin_of_tax_payer_organization;

    private String period_covered;

    private LocalDate date_of_transaction;

    private String tin_of_duty_payer;

    private String address_of_duty_payer;

    private Long instrument_id;

    private Long assessment_number;

    private String receipt_number;

    private Double consideration;

    private Double rate;

    private Double amount_payable;

    private Double amount_paid;

    private Double balance;

    private Double penalty;

    private LocalDate date_of_payment;

    private Double amount_recoverable;

    private Double outstanding;

    private String remarks;

    private Long user_profile_id;

    private Long deed_of_assignment_id;

    private Long state_id;

    private Long lga_id;

    private Long ward;

    private String description_of_location;
}

my main entity class


import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;
import java.time.LocalDate;

@Data
@Entity
@Table(name=&quot;auditor_stamp_duty_report&quot;)
public class AuditorStampDutyReport implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = &quot;id&quot;)
    private Long id;

    @JsonIgnore
    @ManyToOne
    @JoinColumn(name = &quot;id_of_tax_payer_organization&quot;, referencedColumnName = &quot;id&quot;)
    private Organization organization;

    @Column(name = &quot;tin_of_tax_payer_organization&quot;)
    private String tin_of_tax_payer_organization;

    @Column(name = &quot;period_covered&quot;)
    private String period_covered;

    @Column(name = &quot;date_of_transaction&quot;)
    private LocalDate date_of_transaction;

    @JoinColumn(name = &quot;duty_payer&quot;)
    private String duty_payer;

    @Column(name = &quot;tin_of_duty_payer&quot;)
    private String tin_of_duty_payer;

    @Column(name = &quot;address_of_duty_payer&quot;)
    private String address_of_duty_payer;

    @JsonIgnore
    @ManyToOne
    @JoinColumn(name = &quot;instrument_id&quot;, referencedColumnName = &quot;id&quot;)
    private Instruments instruments;

    @Column(name = &quot;assessment_number&quot;)
    private Long assessment_number;

    @Column(name = &quot;receipt_number&quot;)
    private String receipt_number;

    @Column(name = &quot;consideration&quot;)
    private Double consideration;

    @Column(name = &quot;rate&quot;)
    private Double rate;

    @Column(name = &quot;amount_payable&quot;)
    private Double amount_payable;

    @Column(name = &quot;amount_paid&quot;)
    private Double amount_paid;

    @Column(name = &quot;balance&quot;)
    private Double balance;

    @Column(name = &quot;penalty&quot;)
    private Double penalty;

    @Column(name = &quot;outstanding&quot;)
    private Double outstanding;

    @Column(name = &quot;remarks&quot;)
    private String remarks;

    @Column(name=&quot;amount_recoverable&quot;)
    private Double amount_recoverable;

    @Column(name=&quot;date_of_payment&quot;)
    private LocalDate date_of_payment;

    @JsonIgnore
    @ManyToOne
    @JoinColumn(name = &quot;user_profile_id&quot;, referencedColumnName = &quot;id&quot;)
    private UserProfile userProfile;


        @OneToOne(cascade = CascadeType.ALL)
        @JoinTable(
                name=&quot;auditor_stamp_duty_report_deed_of_assignment&quot;,
                joinColumns = @JoinColumn(name=&quot;auditor_stamp_duty_report_id&quot;, referencedColumnName = &quot;id&quot;),
                inverseJoinColumns = @JoinColumn(name=&quot;deed_of_assignment_id&quot;, referencedColumnName = &quot;id&quot;)
        )
        private DeedOfAssignment deed_of_assignment_id;



}


the error from postman

&quot;status&quot;: 415,
    &quot;error&quot;: &quot;Unsupported Media Type&quot;,
    &quot;trace&quot;: &quot;org.springframework.web.HttpMediaTypeNotSupportedException: Content type &#39;&#39; not supported\r\n\tat org.springframework.web.servlet.mvc.method.RequestMappingInfoHandlerMapping.handleNoMatch(RequestMappingInfoHandlerMapping.java:227)\r\n\tat org.springframework.web.servlet.handler.AbstractHandlerMethodMapping.lookupHandlerMethod(AbstractHandlerMethodMapping.java:422)\r\n\tat org.springframework.web.servlet.handler.AbstractHandlerMethodMapping.getHandlerInternal(AbstractHandlerMethodMapping.java:367)\r\n\tat org.springframework.web.servlet.mvc.method.

I would appreciate your help. Thanks in advance

答案1

得分: 0

我发现问题了,我没有在 Postman 的 key 列下放任何参数。这里是一个截图。对于将来需要测试目的导入文件的读者,以下是如何设置你的 Postman 参数。

如何使用Spring Data JPA上传Excel文件或从Excel文件导入数据

英文:

I found the problem, I did not put any parameter the column for key under postman. Here's a screenshot. For future readers on how to set your postman parameters to import file for testing purposes.

如何使用Spring Data JPA上传Excel文件或从Excel文件导入数据

huangapple
  • 本文由 发表于 2020年8月25日 19:44:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/63578131.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定