PHP中使用ExcelReader讀取EXCEL日期欄位的處理

ExcelReader是個簡單易用的東東，相信很多使用PHP的同學們需要從EXCEL中匯入資料到資料庫時，十有八九都會使用它，因為他實在是簡單易用，看看他的例子就知道了。

 

不過從EXCEL中匯入日期類型的欄位的時候，問題就來了，首先是時區的問題，還有格式的問題。從EXCEL中讀出的都是“d/m/Y”的格式，MYSQL中我一般都用“Y-m-d”的格式。轉換格式比較容易，修改 /Spreadsheet_Excel_Reader/Excel/reader.php中的

 

var $dateFormats = array (        0xe => "d/m/Y",        0xf => "d-M-Y",        0x10 => "d-M",        0x11 => "M-Y",        0x12 => "h:i a",        0x13 => "h:i:s a",        0x14 => "H:i",        0x15 => "H:i:s",        0x16 => "d/m/Y H:i",        0x2d => "i:s",        0x2e => "H:i:s",        0x2f => "i:s.S");*/修改為var $dateFormats = array (        0xe => "Y-m-d",        0xf => "Y-M-d",        0x10 => "M-d",        0x11 => "Y-M",        0x12 => "h:i a",        0x13 => "h:i:s a",        0x14 => "H:i",        0x15 => "H:i:s",        0x16 => "Y-m-d H:i",        0x2d => "i:s",        0x2e => "H:i:s",        0x2f => "i:s.S");

 

$ifile = $_GET['ifile'];
$upfile_type = strtolower(pathinfo($ifile, PATHINFO_EXTENSION));
if ($upfile_type != 'xls') {
 $mess = "{success:false, message:'您選擇的檔案類型不是有效EXCEL檔案！'}";
 $mess = iconv("gb2312","utf-8//IGNORE",$mess);
 echo $mess;
 return;
}

require_once '../ExcelReader/reader.php';

// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$data->setOutputEncoding('CP936');

$data->read($ifile);

error_reporting(E_ALL ^ E_NOTICE);

...