날짜 및 시간 엑셀 업로드 데이터 컨버전
화가나는 엑셀데이터 간단하게 바꿔치기
엑셀 업로드 할 때 다음과 같은 로직을 통해 엑셀의 숫자값을 날짜/시간으로 변경할 수 있다.
사용자에게 제공하는 프로그램보다, 내가 마이그레이션 하다가 열받아서 하는 기록
DATA: lv_date(100),
lv_time(100),
lv_result(4).
lv_date = '45329'.
lv_time = '0.291840277777778'.
PERFORM date_validation CHANGING lv_date lv_result.
PERFORM time_validation CHANGING lv_time lv_result.
BREAK-POINT.
*&---------------------------------------------------------------------*
*& Form DATE_VALIDATION
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_<LV_VAL> text
* <--P_LV_TYPE text
*----------------------------------------------------------------------*
FORM date_validation CHANGING pv_val
pv_result.
DATA lv_datum TYPE sy-datum.
CLEAR pv_result.
REPLACE ALL OCCURRENCES OF: '-' IN pv_val WITH '',
'.' IN pv_val WITH '',
'/' IN pv_val WITH ''.
CONDENSE pv_val NO-GAPS.
IF strlen( pv_val ) = 5.
lv_datum = '19000101'.
lv_datum = lv_datum + pv_val - 2.
ELSE.
lv_datum = pv_val.
ENDIF.
CALL FUNCTION 'DATE_CHECK_PLAUSIBILITY'
EXPORTING
date = lv_datum
EXCEPTIONS
plausibility_check_failed = 1
OTHERS = 2.
IF sy-subrc = 0.
pv_val = lv_datum.
ELSE.
pv_result = 'FAIL'.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form TIME_VALIDATION
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_<LV_VAL> text
* <--P_LV_TYPE text
*----------------------------------------------------------------------*
FORM time_validation CHANGING pv_val
pv_result.
DATA: lv_uzeit TYPE sy-uzeit,
lv_type(4).
CLEAR pv_result.
IF pv_val = '0'.
CLEAR pv_val.
EXIT.
ENDIF.
IF pv_val(2) = '0.'.
PERFORM set_tformat USING pv_val.
ELSE.
REPLACE ALL OCCURRENCES OF: ':' IN pv_val WITH ''.
ENDIF.
CONDENSE pv_val NO-GAPS.
lv_uzeit = pv_val.
PERFORM numeric_check CHANGING pv_val lv_type.
IF lv_type = 'NUMC'.
IF lv_uzeit+4(2) = '60'.
ADD 0 TO lv_uzeit.
ENDIF.
ELSE.
pv_result = 'FAIL'.
EXIT.
ENDIF.
CALL FUNCTION 'TIME_CHECK_PLAUSIBILITY'
EXPORTING
time = lv_uzeit
EXCEPTIONS
plausibility_check_failed = 1
OTHERS = 2.
IF sy-subrc = 0.
pv_val = lv_uzeit.
ELSE.
pv_result = 'FAIL'.
ENDIF.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form SET_TFORMAT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_PV_VAL text
*----------------------------------------------------------------------*
FORM set_tformat USING pv_val.
DATA : lv_h TYPE n LENGTH 2,
lv_m TYPE n LENGTH 2,
lv_s TYPE n LENGTH 2,
lv_uzeit TYPE sy-uzeit,
lv_num TYPE float.
lv_num = pv_val.
lv_num = pv_val * 24 .
lv_h = floor( lv_num ).
lv_num = lv_num - lv_h.
lv_num = lv_num * 60.
lv_m = floor( lv_num ).
lv_num = lv_num - lv_m.
lv_num = lv_num * 60.
lv_s = lv_num.
pv_val = lv_h && lv_m && lv_s.
ENDFORM.
*&---------------------------------------------------------------------*
*& Form NUMERIC_CHECK
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_<LV_VAL> text
*----------------------------------------------------------------------*
FORM numeric_check CHANGING pv_val
pv_type TYPE datatype_d.
CLEAR pv_type.
REPLACE ALL OCCURRENCES OF '.' IN pv_val WITH space.
REPLACE ALL OCCURRENCES OF ',' IN pv_val WITH space.
CONDENSE pv_val NO-GAPS.
CALL FUNCTION 'NUMERIC_CHECK'
EXPORTING
string_in = pv_val
IMPORTING
htype = pv_type.
IF pv_type <> 'NUMC'.
pv_type = 'FAIL'.
ENDIF.
ENDFORM.
