excel - 使用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);
}
解决方案
我解决了它,对于任何遇到此问题的人,您可以通过将其添加到您的配置类来为整个应用程序设置时区:
@PostConstruct
void started() {
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
}
推荐阅读
- python - np.arange 还是 np.arage ?错误:使用 plot_confusion_matrix 时模块“numpy”没有属性“arage”
- python - Selenium 将打开 chrome 选项卡但找不到 URL
- reactjs - 当我在酶中使用安装时,嵌套按钮组件单击处理程序是“未定义”,这是为什么?
- php - 来自 Woocommerce Order itemmeta 表的 2 个元键的 SQL 查询
- php - 数据库 [] 未配置 Laravel 6.5.2
- python - 当值在列表中时,Pyspark 替换 DF 值
- node.js - 如何在循环外访问结果
- javascript - 如何使用附加到单选按钮的事件侦听器在 HTML 中插入不同的文本消息
- sql - 更新数据库记录并重定向到不同页面的动态操作
- python - 通过 post 请求将二进制数据作为文件提交