英文:
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:
please find the code for controller below:
@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));
}
}
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()+"");
}
public static Long getLong(Cell cell){
return cell.getCellType() == CellType.STRING ?
Long.valueOf(cell.getStringCellValue()): Long.valueOf(cell.getNumericCellValue()+ "");
}
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 && 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("d/MM/yyyy"))
.appendOptional(DateTimeFormatter.ofPattern("d/M/yyyy"))
.appendOptional(DateTimeFormatter.ofPattern("dd/M/yyyy"))
.appendOptional(DateTimeFormatter.ofPattern("dd/MM/yyyy"))
.toFormatter();
return LocalDate.parse(date, formatter);
}
}
here is my service:
@Transactional
public List <AuditorStampDutyReport> importAuditorStampDutyReportDetails(MultipartFile mfile) {
List<AuditorStampDutyReport> uploadReport = new ArrayList<>();
AuditorStampDutyReport excelReport = new AuditorStampDutyReport();
int sheetNumber = 0, rowNumber = 0;
try {
Workbook workbook = new XSSFWorkbook(mfile.getInputStream());
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
sheetNumber = i;
Sheet sheet = workbook.getSheetAt(i);
for (Row row : sheet) {
rowNumber = row.getRowNum();
if (rowNumber < 2) {
String possibleTaxPayerId = ImportExcelHelper.getOrDefault(row.getCell(1), "").trim().replaceAll(" +", " ");
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), ""));
continue;
}
if (rowNumber > 2 && rowNumber < 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), "");
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 && deedOfAssignment.getId()>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="auditor_stamp_duty_report")
public class AuditorStampDutyReport implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;
@JsonIgnore
@ManyToOne
@JoinColumn(name = "id_of_tax_payer_organization", referencedColumnName = "id")
private Organization organization;
@Column(name = "tin_of_tax_payer_organization")
private String tin_of_tax_payer_organization;
@Column(name = "period_covered")
private String period_covered;
@Column(name = "date_of_transaction")
private LocalDate date_of_transaction;
@JoinColumn(name = "duty_payer")
private String duty_payer;
@Column(name = "tin_of_duty_payer")
private String tin_of_duty_payer;
@Column(name = "address_of_duty_payer")
private String address_of_duty_payer;
@JsonIgnore
@ManyToOne
@JoinColumn(name = "instrument_id", referencedColumnName = "id")
private Instruments instruments;
@Column(name = "assessment_number")
private Long assessment_number;
@Column(name = "receipt_number")
private String receipt_number;
@Column(name = "consideration")
private Double consideration;
@Column(name = "rate")
private Double rate;
@Column(name = "amount_payable")
private Double amount_payable;
@Column(name = "amount_paid")
private Double amount_paid;
@Column(name = "balance")
private Double balance;
@Column(name = "penalty")
private Double penalty;
@Column(name = "outstanding")
private Double outstanding;
@Column(name = "remarks")
private String remarks;
@Column(name="amount_recoverable")
private Double amount_recoverable;
@Column(name="date_of_payment")
private LocalDate date_of_payment;
@JsonIgnore
@ManyToOne
@JoinColumn(name = "user_profile_id", referencedColumnName = "id")
private UserProfile userProfile;
@OneToOne(cascade = CascadeType.ALL)
@JoinTable(
name="auditor_stamp_duty_report_deed_of_assignment",
joinColumns = @JoinColumn(name="auditor_stamp_duty_report_id", referencedColumnName = "id"),
inverseJoinColumns = @JoinColumn(name="deed_of_assignment_id", referencedColumnName = "id")
)
private DeedOfAssignment deed_of_assignment_id;
}
the error from postman
"status": 415,
"error": "Unsupported Media Type",
"trace": "org.springframework.web.HttpMediaTypeNotSupportedException: Content type '' 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 参数。
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论