首页 > 解决方案 > 使用spring boot将日期从excel文件插入mysql

问题描述

我正在尝试使用 spring boot 将数据从 excel 插入数据库 mysql 但我遇到了 date 问题。例如从 excel 我有 06/01/2019 但我在 DB 中找到它 05/01/2019 有没有人可以帮我解决这个问题 :(

这是我的代码:

形成.java

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String Cursus ;
private String Groupe_Action;
  @Temporal(TemporalType.DATE)
private Date Date ;
private String  Module;
private String  Formateur;
private String Nombre_Appt;
private String Nombre_d_absents;
private String Honoraires_Formateurs_HT ;
private String Jours_de_formation;
private String Nbre_D_heures;
private String Taux_h;
private String Ristourne_Prévisionnelle ;
private String Coûts_Hebergement_TTC;
private String Coûts_Pause_Café_TTC;
private String fileType;
@Transient
private MultipartFile file;

FormationFileServiceImpl.java

   @Service
  @Transactional
   public class FormationFileServiceImpl implements FormationFileService {

@Autowired
private FormationFileRepository formationFileRepository;
@Override
public List<Formation> findAll() {
    return (List<Formation>) formationFileRepository.findAll();
}

@Override
public Boolean SaveDataFromFile(MultipartFile file) {

    Boolean isFalg=false;
   String extention = 
    FilenameUtils.getExtension(file.getOriginalFilename());
     if(extention.equalsIgnoreCase("xls") || 
    extention.equalsIgnoreCase("xlsx") ) {
        isFalg=readDataFromExcel(file);
    }
    return isFalg;
}

private Boolean readDataFromExcel(MultipartFile file) {
    Workbook workbook = getWorkBook(file);
     Sheet sheet = workbook.getSheetAt(0);
     Iterator<Row> rows = sheet.iterator();

    rows.next();
    while(rows.hasNext()) {
        Row row = rows.next();
        Formation formation = new Formation();
        if(row.getCell(0).getCellType() == 
     Cell.CELL_TYPE_STRING) {

   formation.setCursus(row.getCell(0).getStringCellValue());
        }
        if(row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) {
            formation.setGroupe_Action(row.getCell(1).getStringCellValue());
        }
        if(row.getCell(2).getCellType() == Cell.CELL_TYPE_NUMERIC) {
            formation.setDate(row.getCell(2).getDateCellValue());

        }if(row.getCell(3).getCellType() == Cell.CELL_TYPE_STRING) {
            formation.setModule(row.getCell(3).getStringCellValue());

        }if(row.getCell(4).getCellType() == Cell.CELL_TYPE_STRING) {
            formation.setFormateur(row.getCell(4).getStringCellValue());

        }if(row.getCell(5).getCellType() == Cell.CELL_TYPE_NUMERIC) {
            String Nombre_Appt = NumberToTextConverter.toText(row.getCell(5).getNumericCellValue());
             formation.setNombre_Appt(Nombre_Appt);

           }if(row.getCell(6).getCellType() == Cell.CELL_TYPE_NUMERIC) {
            String Nombre_d_absents = NumberToTextConverter.toText(row.getCell(6).getNumericCellValue());
            formation.setNombre_d_absents(Nombre_d_absents);

          }if(row.getCell(7).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                String Horaire_Formateur_JH = NumberToTextConverter.toText(row.getCell(7).getNumericCellValue());
                formation.setHonoraires_Formateurs_HT(Horaire_Formateur_JH);

              }if(row.getCell(8).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                String Jour_formation = NumberToTextConverter.toText(row.getCell(8).getNumericCellValue());
                formation.setJours_de_formation(Jour_formation);

              }if(row.getCell(9).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                String Nombre_d_heures = NumberToTextConverter.toText(row.getCell(9).getNumericCellValue());
                formation.setNbre_D_heures(Nombre_d_heures);

              }if(row.getCell(10).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                String taux_h = NumberToTextConverter.toText(row.getCell(10).getNumericCellValue());
                formation.setTaux_h(taux_h);

              }if(row.getCell(11).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                String Ristourne_Prévisionnelle  = NumberToTextConverter.toText(row.getCell(11).getNumericCellValue());
                formation.setRistourne_Prévisionnelle(Ristourne_Prévisionnelle);

              }if(row.getCell(12).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                String Coûts_Hebergement_TTC  = NumberToTextConverter.toText(row.getCell(12).getNumericCellValue());
                formation.setCoûts_Hebergement_TTC(Coûts_Hebergement_TTC);

              } if(row.getCell(13).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                String Coûts_Pause_Café_TTC = NumberToTextConverter.toText(row.getCell(13).getNumericCellValue());
                formation.setCoûts_Pause_Café_TTC(Coûts_Pause_Café_TTC);

              }


        formation.setFileType(FilenameUtils.getExtension(file.getOriginalFilename()));
        formationFileRepository.save(formation);
    }
    return true;
}

private Workbook getWorkBook(MultipartFile file) {
    Workbook workbook = null;
      String extention = FilenameUtils.getExtension(file.getOriginalFilename());
       try {
           if(extention.equalsIgnoreCase("xlsx")) {
               workbook =  new XSSFWorkbook(file.getInputStream());

           }else if (extention.equalsIgnoreCase("xls")) {
             workbook = new XSSFWorkbook(file.getInputStream());  
           }
    } catch (Exception e) {
        e.printStackTrace();
    }

    return workbook;
}

FormationFileController.java

    @RestController
    public class FormationFileController {
      @Autowired
   private FormationFileService formationFileService;

  //@GetMapping(value = "/")
  //public String Home(Model model) {
  //    model.addAttribute("formation", new Formation());
   //   List<Formation> formation = formationFileService.findAll();
  //    model.addAttribute("formation", formation);
  //    return "view/pers";
  //    
  //}
 @PostMapping(value = "/fileupload")
   public ResponseEntity<String> uploadFile(@ModelAttribute Formation 
   formation,RedirectAttributes redirectAttributes) {
String originalName = formation.getFile().getOriginalFilename();

Boolean 
  isFlag=formationFileService.SaveDataFromFile(formation.getFile());

     if(isFlag==true) { 
        //           
        redirectAttributes.addFlashAttribute("successmessage","File upload 
        successfully!"); 
      //             }else { 
          //          
        redirectAttributes.addFlashAttribute("errormessage", "Please try 
     again !");

     }
        //return "redirect:/";
        return new ResponseEntity<String>(originalName, 
             HttpStatus.OK);

           }

标签: excelspring-bootjpamysql-workbench

解决方案


我解决了它,对于任何遇到此问题的人,您可以通过将其添加到您的配置类来为整个应用程序设置时区:

@PostConstruct
void started() {
    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
}

推荐阅读