Example of the time when phpexcel reads an excel table

Edit and modify a large number of product prices and promotion time in an excel worksheet, so that our technology can be imported to online databases in batches.

This is a simple task for us. It protects phpexcel table export and instantly solves the problem.

However, when you enter the database, you can see that there is a problem with the import time format. The import time is not displayed in time, it is a number, and it is depressing.

Then output through php, which is not in the time format.

Baidu once discovered that phpexcel provided the getFormattedValue () method to read the time and replace getValue ()
GetFormattedValue ();

$ Abc = $ currentSheet-> getCell ('A'. $ currentRow)-> getFormattedValue ();

In this way, you can read the time in the excel table, update the database again, and OK.

The following is an example.

Error_reporting (E_ALL );
Date_default_timezone_set ('Asia/Shanghai ');
/** PHPExcel_IOFactory */
Require_once '../Classes/PHPExcel/IOFactory. Php ';
$ InputFileName = '6081076641077444758.xls ';
$ ObjReader = new PHPExcel_Reader_Excel5 ();
$ ObjPHPExcel = $ objReader-> load ($ inputFileName );
$ Sheet = $ objPHPExcel-> getSheet (0 );
$ HighestRow = $ sheet-> getHighestRow (); // gets the total number of rows.
$ HighestColumn = $ sheet-> getHighestColumn (); // gets the total number of columns

$ TempArray = array ();
For ($ j = 2; $ j <= $ highestRow; $ j ++ ){
For ($ k = 'a'; $ k <= $ highestColumn; $ k ++ ){
If ($ k = 'M' | $ k = 'O') // The M column and the O column are time
$ TempArray [] = excelTime ($ objPHPExcel-> getActiveSheet ()-> getCell ("$ k $ j")-> getValue ());
Else
$ TempArray [] = $ objPHPExcel-> getActiveSheet ()-> getCell ("$ k $ j")-> getValue ();
 }
Print_r ($ tempArray );
Unset ($ tempArray );
}

Function excelTime ($ date, $ time = false ){
If (function_exists ('gregoriantojd ')){
If (is_numeric ($ date )){
$ Jd = GregorianToJD (1, 1, 1970 );
$ Gregorian = JDToGregorian ($ jd + intval ($ date)-25569 );
$ Date = explode ('/', $ gregorian );
$ Date_str = str_pad ($ date [2], 4, '0', STR_PAD_LEFT)
. "-". Str_pad ($ date [0], 2, '0', STR_PAD_LEFT)
. "-". Str_pad ($ date [1], 2, '0', STR_PAD_LEFT)
. ($ Time? "00:00:00 ":'');
Return $ date_str;
  }
} Else {
$ Date = $ date> 25568? $ Date + 1: 25569;
/* There was a bug if Converting date before 1-1-1970 (tstamp 0 )*/
$ Ofs = (70*365 + 17 + 2) * 86400;
$ Date = date ("Y-m-d", ($ date * 86400)-$ ofs). ($ time? "00:00:00 ":'');
 }
Return $ date;
}