如何使用springboot将Excel上传到mysql数据库

Question

这是ExcelController.java模块

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.core.io.Resource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import com.bezkoder.spring.files.excel.helper.ExcelHelper;
import com.bezkoder.spring.files.excel.message.ResponseMessage;
import com.bezkoder.spring.files.excel.model.Tutorial;
import com.bezkoder.spring.files.excel.service.ExcelService;
@CrossOrigin("http://localhost:8080")
@Controller
@RequestMapping("/api/excel")
public class ExcelController {
@Autowired
ExcelService fileService;
@PostMapping("/upload")
public ResponseEntity<ResponseMessage> uploadFile(@RequestParam("file") MultipartFile file) {
String message = "";
if (ExcelHelper.hasExcelFormat(file)) {
try {
fileService.save(file);
message = "Uploaded the file successfully: " + file.getOriginalFilename();
return ResponseEntity.status(HttpStatus.OK).body(new ResponseMessage(message));
} catch (Exception e) {
message = "Could not upload the file: " + file.getOriginalFilename() + "!";
return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(new ResponseMessage(message));
}
}
message = "Please upload an excel file!";
return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(new ResponseMessage(message));
}
@GetMapping("/tutorials")
public ResponseEntity<List<Tutorial>> getAllTutorials() {
try {
List<Tutorial> tutorials = fileService.getAllTutorials();
if (tutorials.isEmpty()) {
return new ResponseEntity<>(HttpStatus.NO_CONTENT);
}
return new ResponseEntity<>(tutorials, HttpStatus.OK);
} catch (Exception e) {
return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
}
}
@GetMapping("/download")
public ResponseEntity<Resource> getFile() {
String filename = "tutorials.xlsx";
InputStreamResource file = new InputStreamResource(fileService.load());
return ResponseEntity.ok()
.header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + filename)
.contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
.body(file);
}
}

这是FileUploadException.java模块

import org.springframework.web.multipart.MaxUploadSizeExceededException;
import org.springframework.web.servlet.mvc.method.annotation.ResponseEntityExceptionHandler;
import com.bezkoder.spring.files.excel.message.ResponseMessage;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
@ControllerAdvice
public class FileUploadExceptionAdvice extends ResponseEntityExceptionHandler {
@ExceptionHandler(MaxUploadSizeExceededException.class)
public ResponseEntity<ResponseMessage> handleMaxSizeException(MaxUploadSizeExceededException exc) {
return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(new ResponseMessage("File too large!"));
}
}

这是ExcelHelper.java模块

package com.bezkoder.spring.files.excel.helper;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import com.bezkoder.spring.files.excel.model.Tutorial;
public class ExcelHelper {
public static String TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
static String[] HEADERs = { "Id", "Title", "Description", "Published" };
static String SHEET = "Tutorials";
public static boolean hasExcelFormat(MultipartFile file) {
if (!TYPE.equals(file.getContentType())) {
return false;
}
return true;
}
public static ByteArrayInputStream tutorialsToExcel(List<Tutorial> tutorials) {
try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream();) {
Sheet sheet = workbook.createSheet(SHEET);
// Header
Row headerRow = sheet.createRow(0);
for (int col = 0; col < HEADERs.length; col++) {
Cell cell = headerRow.createCell(col);
cell.setCellValue(HEADERs[col]);
}
int rowIdx = 1;
for (Tutorial tutorial : tutorials) {
Row row = sheet.createRow(rowIdx++);
row.createCell(0).setCellValue(tutorial.getId());
row.createCell(1).setCellValue(tutorial.getTitle());
row.createCell(2).setCellValue(tutorial.getDescription());
row.createCell(3).setCellValue(tutorial.isPublished());
}
workbook.write(out);
return new ByteArrayInputStream(out.toByteArray());
} catch (IOException e) {
throw new RuntimeException("fail to import data to Excel file: " + e.getMessage());
}
}
public static List<Tutorial> excelToTutorials(InputStream is) {
try {
//      Workbook workbook = new XSSFWorkbook(is);
//
//      Sheet sheet = workbook.getSheet(SHEET);
Workbook workbook = WorkbookFactory.create(is);
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rows = sheet.iterator();
List<Tutorial> tutorials = new ArrayList<Tutorial>();
int rowNumber = 0;
while (rows.hasNext()) {
Row currentRow = rows.next();
// skip header
if (rowNumber == 0) {
rowNumber++;
continue;
}
Iterator<Cell> cellsInRow = currentRow.iterator();
Tutorial tutorial = new Tutorial();
int cellIdx = 0;
while (cellsInRow.hasNext()) {
Cell currentCell = cellsInRow.next();
switch (cellIdx) {
case 0:
tutorial.setId((long) currentCell.getNumericCellValue());
break;
case 1:
tutorial.setTitle(currentCell.getStringCellValue());
break;
case 2:
tutorial.setDescription(currentCell.getStringCellValue());
break;
case 3:
tutorial.setPublished(currentCell.getBooleanCellValue());
break;
default:
break;
}
cellIdx++;
}
tutorials.add(tutorial);
}
workbook.close();
return tutorials;
} catch (IOException e) {
throw new RuntimeException("fail to parse Excel file: " + e.getMessage());
}
}
}

这是ResponseMessage.java模块

package com.bezkoder.spring.files.excel.message;
public class ResponseMessage {
private String message;
public ResponseMessage(String message) {
this.message = message;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
}

这是tutorial.java模块

package com.bezkoder.spring.files.excel.model;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "tutorials")
public class Tutorial {
@Id
@Column(name = "id")
private long id;
@Column(name = "title")
private String title;
@Column(name = "description")
private String description;
@Column(name = "published")
private boolean published;
public Tutorial() {
}
public Tutorial(String title, String description, boolean published) {
this.title = title;
this.description = description;
this.published = published;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public boolean isPublished() {
return published;
}
public void setPublished(boolean isPublished) {
this.published = isPublished;
}
@Override
public String toString() {
return "Tutorial [id=" + id + ", title=" + title + ", desc=" + description + ", published=" + published + "]";
}
}

这是教程Respository.java模块

package com.bezkoder.spring.files.excel.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import com.bezkoder.spring.files.excel.model.Tutorial;
public interface TutorialRepository extends JpaRepository<Tutorial, Long> {
}

这是我的application.properties模块

spring.datasource.url= jdbc:mysql://localhost:3306/test?allowPublicKeyRetrieval=true&useSSL=false
spring.datasource.username= root
spring.datasource.password= 1234
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.hibernate.ddl-auto= update
spring.servlet.multipart.max-file-size=2MB
spring.servlet.multipart.max-request-size=2MB

这是Postman接口Postman标头在启用内容类型为"；多部分/表单数据；边界＝"；我收到了一个没有消息的空白回复。在启用具有"0"的内容类型时；application/vnd.msexcel"；我得到一个500内部服务器错误，消息是：；当前请求不是一个多部分请求"；。邮差尸体

因此，使用Postman上传excel并不能按预期工作。下载链接运行良好，它下载了一个只有列标题的空白excel表。此外，表结构正在MySQL数据库中创建。如何使上传功能正常工作？

Answer 1

您需要在Postman中的键字段(上传文件的位置旁边(中添加"file"(根据您的代码(。我看到它不见了。