Spring Boot导入Excel至SQL Server报错,已加依赖配置仍未解决
Spring Boot Excel导入SQL Server问题排查
我尝试用以下Spring Boot代码将Excel数据导入SQL Server,希望通过SQL Server Management Studio(SSMS)进行CRUD操作,但一直遇到相同错误。已经添加了对应依赖和配置属性,问题仍未解决。相关代码如下:
Repository代码
public interface AjustageRepository extends JpaRepository<Ajustage, Long> { @Query("SELECT v FROM Ajustage v WHERE v.date = (SELECT MAX(v2.date) FROM Ajustage v2)") Ajustage lastInput(); }
Service代码
@Service public class ExcelService { @Autowired private AjustageRepository repository; public void processExcelFile(MultipartFile file) { try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) { Sheet sheet = workbook.getSheetAt(1); // 第二个工作表(索引1) int i = 0; for (Row row : sheet) { i++; if(i>1) { Ajustage ajustage = new Ajustage(); if (row.getCell(0) != null && row.getCell(0).getCellType() != CellType.BLANK) break; ajustage.setDate(row.getCell(0).getDateCellValue()); ajustage.setLib(row.getCell(1).getStringCellValue()); ajustage.setCompte(row.getCell(2).getStringCellValue()); ajustage.setNombre(Double.valueOf(row.getCell(3).getNumericCellValue()).longValue()); ajustage.setDebit(Double.valueOf(row.getCell(4).getNumericCellValue()).longValue()); ajustage.setCredit(Double.valueOf(row.getCell(5).getNumericCellValue()).longValue()); System.out.println(ajustage.toString()); repository.save(ajustage); } } } catch (IOException e) { // 处理Excel文件读取错误 System.out.println(e.getMessage()); } } public void insertNewRecord(MultipartFile file) { try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) { Sheet sheet = workbook.getSheetAt(1); // 第二个工作表(索引1) int lastRowNum = this.lastRowNum(sheet); Ajustage last = repository.lastInput(); Row row = sheet.getRow(lastRowNum); if(last != null) { Date toInsert = row.getCell(0).getDateCellValue(); while(toInsert.after(last.getDate())) { Ajustage ajustage = new Ajustage(); ajustage.setDate(row.getCell(0).getDateCellValue()); ajustage.setLib(row.getCell(1).getStringCellValue()); ajustage.setCompte(row.getCell(2).getStringCellValue()); ajustage.setNombre(Double.valueOf(row.getCell(3).getNumericCellValue()).longValue()); ajustage.setDebit(Double.valueOf(row.getCell(4).getNumericCellValue()).longValue()); ajustage.setCredit(Double.valueOf(row.getCell(5).getNumericCellValue()).longValue()); System.out.println(ajustage.toString()); repository.save(ajustage); // 循环迭代 lastRowNum--; row = sheet.getRow(lastRowNum); toInsert = row.getCell(0).getDateCellValue(); } } else { while(lastRowNum > 0) { Ajustage ajustage = new Ajustage(); ajustage.setDate(row.getCell(0).getDateCellValue()); ajustage.setLib(row.getCell(1).getStringCellValue()); ajustage.setCompte(row.getCell(2).getStringCellValue()); ajustage.setNombre(Double.valueOf(row.getCell(3).getNumericCellValue()).longValue()); ajustage.setDebit(Double.valueOf(row.getCell(4).getNumericCellValue()).longValue()); ajustage.setCredit(Double.valueOf(row.getCell(5).getNumericCellValue()).longValue()); System.out.println(ajustage.toString()); repository.save(ajustage); // 循环迭代 lastRowNum--; row = sheet.getRow(lastRowNum); } } } catch (IOException e) { // 处理Excel文件读取错误 System.out.println(e.getMessage()); } } public int lastRowNum(Sheet sheet) { int lastRowNum = sheet.getLastRowNum(); for(int rowNum = lastRowNum; rowNum >= 0; rowNum--) { Row row = sheet.getRow(rowNum); if (row != null && row.getCell(0) != null && row.getCell(0).getCellType() != CellType.BLANK) return rowNum; } return -1; } }
Controller代码
@Controller @RequestMapping("/api/excel") public class ExcelController { private final ExcelService excelService; @Autowired public ExcelController(ExcelService excelService) { this.excelService = excelService; } @GetMapping("/test") public ResponseEntity<String> test() { return ResponseEntity.ok("Excel文件处理成功"); } @PostMapping("/upload") public ResponseEntity<String> uploadExcelFile(@RequestPart MultipartFile file) { System.out.println("OKK##########"); try { excelService.processExcelFile(file); // 调用Excel文件处理方法 return ResponseEntity.ok("Excel文件处理成功"); } catch (Exception e) { e.printStackTrace(); return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body("处理Excel文件时出错"); } } @PostMapping("/version2") public ResponseEntity<String> insertNew(@RequestPart MultipartFile file) { System.out.println("OKK##########"); try { excelService.insertNewRecord(file); // 调用Excel文件处理方法 return ResponseEntity.ok("Excel文件处理成功"); } catch (Exception e) { e.printStackTrace(); return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body("处理Excel文件时出错"); } } }
内容的提问来源于stack exchange,提问作者azerty1012
相关产品推荐
相关产品推荐

