요즘은 엑셀 데이터를 MySQL 테이블에 써 넣는 부분을 공부하는 중이다. 오래 전에 ‘phpExcelReader’라는 오픈 소스를 발견하여 시도해보았지만 초보자로서는 사용법을 익히는 것이 역부족이었기에 포기하고 있다가 최근 들어서 다시 시도하는 것이다. 당시에는 ‘excel_reader2.php’라는 코드를 이용했는데 최근에 ‘PHPExcel.php’라는 새로운 코드를 발견해서 이것을 적용 중이다. 오브젝트 개념이 도입된 것이어서 훨씬 사용이 편리 하다는 것을 새삼 느끼고 있다.
날짜 시간 데이터에 있어서는 엑셀의 날짜 시간 값과 php의 날짜시간값이 서로 다른 기준에 의해서 만들어지기 때문에 사용상에 약간의 불편한 점이 발생한다. 물론 getFormattedValue() 함수를 이용하면 엑셀에서 설정한 데이터의 표현 형식을 그대로 읽어게 되므로 문제 될 것이 없지만 경우에 따라서는 데이터 업로드 전에 일일이 데이터 형식을 그에 맞추러서 다시 설정해 주어야하는 불편함이 있다.
특히 날자의 형식이 ‘yyyy-mm-dd’ 로 되어 있는데도 불구하고 읽어온 값이 ‘mm/dd/yy’이렇게 표시되기때문에 php에서 date()함수를 이용해서 숫자값으로 변환하면 날자를 잘못 되게 표현하는 문제가 있었다.
결국 시간을 포함하지 않는 순수한 날자 데이터도 ‘yyyy-mm-dd hh:mm:ss’ 이렇게 변형해서 적용할 수 밖에 없었다.
이런 문제는 엑셀에서 저장하고 있는 숫자로 된 time_stamp 값을 그대로 읽어오고 (getFormattedValue() 함수 대신에 getValue() 함수사용하면 된다. ) 이것을 php 형식으로의 값으로 변환할 수 있다면 해결할 수 있다.
‘how to convert excel timestamp to php timestamp’ 이런 구문으로 구글링 해보니 ( 원저의 작성자가 한 사람으로 보이는) 간단한 함수식을 발견했다.
UNIX_DATE = (EXCEL_DATE - 25569) * 86400 EXCEL_DATE = 25569 + (UNIX_DATE / 86400)
‘25569’는 엑셀의 시간 기준일 ‘1900/01/01’과 PHP의 시간 기준일 ‘1970/01/01’사이의 시간차이를 날짜로 환산한 값이고 ‘86400’은 1일을 초로 환산한 값이다. (엑셀은 1일을 값’1’로 정의 하지만 PHP는 1초를 값’1’로 정의 하기 때문이란다. )
function time_convert_EXCEL_to_PHP($time){
$t=( $time- 25569) * 86400;
return $t;
}
간단하다!! 이런 함수를 적용해서 변환하면 된다.
헌데 …
echo date('Y-m-d H:i:s',time_convert_EXCEL_to_PHP($time));
이렇게 해서 화면에 표시해보니 엑셀에 표시된 날자시간값 과 변환된 날자시간값 사이에 두 가지 점에서 차이가 발생한다.
첫째는 모든 값이 엑셀의 원래의 시간보다 9시간이 빠르게 표시되는 것이고, 두번째는 간간이 (어떤 눈에 띠는 규칙을 발견하기 힘들 정도로 ) 원래의 값보다 1초가 작게 표시되는 것들이 발견된다는 점이다. 첫번째 것이라면 시간 데이터를 발생시킨 기준이 한국시간 기준인데 php는 국제표준시간(GMT) 기준으로 계산했기 때문이라는것을 쉽게 알수 있었지만 두번째 것은 어찌하면 좋을까?
이런저런 생각 끝에 혹시 반올림의 문제는 아닐까 생각해서 여러가지 변환을 시도해보고 결국 해법을 찾았다.
$t=round($time*10)/10;
함수에서 이런 변환을 한번 더 넣어주니 말끔하게 해결되었다.
결국 완전한 함수의 형태는 아래와 같다.
function time_convert_EXCEL_to_PHP($time){
$t=( $time- 25569) * 86400-60*60*9;
$t=round($t*10)/10;
return $t;
}
마친다.
1시간동안 삽질하고 있었는데 덕분에 해결했습니다.
복받으세요!
도움이 되셨다니 기쁨니다^^