You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.07.13 21:08:09