上傳excel檔案並變更格式範例(使用PHPExcel)-1

把最近寫好的code丟上來分享XD~
裡面用到一些fputs與PHPExcel以及時間格式的觀念

include 'PHPExcel/IOFactory.php';
$reader = PHPExcel_IOFactory::createReader('Excel5'); // 讀取舊版 excel 檔案
$PHPExcel = $reader->load('xxx'); // 檔案名稱
$sheet = $PHPExcel->getSheet(0); // 讀取第一個工作表(編號從 0 開始)
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
// $objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $sheet->getHighestRow(); // e.g. 10
// $highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
echo "HighestRow = " . $highestRow . '
';

function exceltime($days)
{
global $myDateStr;
if (is_numeric($days)) {
// based on 1900-1-1
$jd = GregorianToJD(1, 1, 1970);
$gregorian = JDToGregorian($jd + intval($days) - 25569);
$myDate = explode('/', $gregorian);
$myDateStr = str_pad($myDate[2], 4, '0', STR_PAD_LEFT)
. "-" . str_pad($myDate[0], 2, '0', STR_PAD_LEFT)
. "-" . str_pad($myDate[1], 2, '0', STR_PAD_LEFT);
return $myDateStr;
}
return $days;
}

for($i = 0;$i <= 16;$i++) {
for($j = 0;$j <= $highestRow;$j++) {
if ($i == 7 || $i == 11 || $i == 14) {
$date_temp = $sheet->getCellByColumnAndRow($i, $j)->getValue();
exceltime($date_temp);
$put[$i][$j] = $myDateStr;
} else {
$put[$i][$j] = $sheet->getCellByColumnAndRow($i, $j)->getValue();
}
}
}

for ($num = 2;$num <= $highestRow ; $num++) {
//$string = $put[8][$num];
//$string = substr($string, 0, 237);
for($point = 0; $point <= 254 ; $point++){
$put[8][$num] = mb_substr($put[8][$num], 0, 254,'utf-8');
$check_chinese = mb_substr ($put[8][$num] , $point,1,'utf-8');
$ascii = ord ($check_chinese);
// echo $check_chinese.$ascii."
";
if ($ascii > 123) {
$put[8][$num] = mb_ereg_replace($check_chinese,' ',$put[8][$num]);
}
}
}

foreach($put as $key1 => $v1) {
foreach ($v1 as $key => $v2) {
echo $key1;
echo $key . ' : ' . $v2 . "
";
}
}

$SALES_DB_NAME = 'xxx';
$SALES_DB_HOST = 'xxx';
$SALES_DB_USER = 'xxx;
$SALES_DB_PASS = 'xxx';
$sales_link = @mysql_connect($SALES_DB_HOST,$SALES_DB_USER,$SALES_DB_PASS)
or die("無法連線到 MySQL 伺服器 : $SALES_DB_HOST
\n ".
"可能是對方 MySQL 伺服器沒啟動, 或拒絕連線。
\n" .
"請洽系統管理員，或過一陣子再試試看。
\n");
mysql_select_db($SALES_DB_NAME,$sales_link);

$fo = fopen("GALAXY_TAIWAN.".date(Ymd), "w");
for($all = 2; $all <= $highestRow ; $all++) {
if ($put[6][$all] == "")
$put[6][$all] = 'null';
$creationdate = $put[0][$all];
$db_get_creation = "select creationdate from altera_all_dr where trackingnumber ='$creationdate'";
$result =mysql_query ($db_get_creation)
or die ("error");
$cd=mysql_result($result,0);

fputs($fo,
'|'
. $put[2][$all] . '|'
. $put[3][$all] . '|'
. '|'
. $put[0][$all] . '|'
. '|'
. $put[12][$all] . '|'
. $put[12][$all] . '|'
. $put[1][$all] . '|'
. $put[4][$all] . '|'
. '|'
. $put[5][$all] . '|'
. $put[6][$all] . '|'
. '1|'
. $put[10][$all] . '|'
. $put[9][$all] . '|'
. '|'
. '|'
. '|'
. $put[13][$all] . '|'
. $cd . '|'
. $put[14][$all] . '|'
. $put[8][$all] . '|'
. '|'
. '|'
. '|'
. '|'
. '|'
. '|'
. '|'
. '|'
. '|'
. $put[11][$all] . '|'
. 'GFEC|'
. '|'
. 'TAIWAN'
);
fputs($fo, "\r\n");
}
fclose($fo);

?>