~~~
public function xlstools()
{
//測試專用
/* $filename="./Public/Uploads/20180917/ddd.xlsx";
$arr=$this->xlsin($filename);
$this->insetgoodlist($arr);*/
$table = I('post.');
$config=array(
'exts'=>array('xlsx','xls'),
'rootPath'=>"./Public/",
'savePath'=>'Uploads/',
//'autoSub' => true,
'subName' => array('date','Ymd'),
);
if(!empty($_FILES['excel']['name'])){
$upload = new \Think\Upload($config);
//var_dump($upload);exit;
if (!$info=$upload->uploadone($_FILES['excel'])) {
$this->error($upload->getError());
}
//要導入的xls文件，位于根目錄下的Public文件夾
$filename = $upload->rootPath.$info['savepath'].$info['savename'];
//dump($filename);exit();
//$filename="./Public/Uploads/20180917/5b9f58bc7de0c.xlsx";
$arr=$this->xlsin($filename);
$this->insetgoodlist($arr);
}else{
$this->error('請選擇文件');
}
}
public function insetgoodlist($arr){
//dump($arr);exit();
$i = 0;
foreach($arr as $key=>$list){
$i++;
if ($key!=1) {
foreach($list as $k=>$v){
switch($k){
case 'A':
$k='gid';
break;
case 'B':
$k='title';
break;
case 'C':
$k='barcode';
break;
case 'D':
$k='addtime1';
break;
case 'E':
$k='store';
break;
case 'F':
$k='rjiage';
break;
case 'G':
$k='xjiage';
break;
case 'H':
$k='bianhao';
break;
case 'I':
$k='jingban';
break;
}
$map[$k]=$v;
//$map['addtime']=date('Y-m-d H:i:s',time());
$map['uid']='';
$map['uname']=$map['jingban'];
$map['addtime']=date("Y-m-d h:i:s",$map['addtime1']);
$update[$k]=$v;
$update['uid']='';
$update['addtime']=$map['addtime'];
$update['uname']=$map['jingban'];
$update['shijian']= $update['addtime'];
$update['sn']='H987'.time().$i;
$update['status']=2;
}
//dump($map);exit();
$ids= M('goodlist')->add($map);
//dump($ids);exit();
if ($ids) {
$update['xid']=$ids;
//dump($update);exit();
M('goodlists')->add($update);
}
}
}
}
public function xlsin($filename)
{
//導入PHPExcel類庫，因為PHPExcel沒有用命名空間，只能inport導入
import("Org.Util.PHPExcel");
//創建PHPExcel對象，注意，不能少了\
$PHPExcel = new \PHPExcel();
//如果excel文件后綴名為.xls，導入這個類
import("Org.Util.PHPExcel.Reader.Excel5");
//如果excel文件后綴名為.xlsx，導入這下類
//import("Org.Util.PHPExcel.Reader.Excel2007");
//$PHPReader=new \PHPExcel_Reader_Excel2007();
$PHPReader = new \PHPExcel_Reader_Excel2007();
//載入文件
$PHPExcel = $PHPReader->load($filename);
//獲取表中的第一個工作表，如果要獲取第二個，把0改為1，依次類推
$currentSheet = $PHPExcel->getSheet(0);
//獲取總列數
$allColumn = $currentSheet->getHighestColumn();
//獲取總行數
$allRow = $currentSheet->getHighestRow();
//循環獲取表中的數據，$currentRow表示當前行，從哪行開始讀取數據，索引值從0開始
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
//從哪列開始，A表示第一列
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
//數據坐標
$address = $currentColumn . $currentRow;
//$arr[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
if($address=='D'.$currentRow){
$date= $currentSheet->getCell($address)->getValue();
$stamp_date = \PHPExcel_Shared_Date::ExcelToPHP($date);//將獲取的奇怪數字轉成時間戳，該時間戳會自動帶上當前日期
//$arr[$currentRow][$currentColumn] = gmdate("H:i:s",$stamp_date);//這個就是excel表中的數據了，棒棒的！
$arr[$currentRow][$currentColumn] = $stamp_date;
}else{
//讀取到的數據，保存到數組$arr中
$arr[$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
}
}
}
return $arr;
//var_dump($arr);exit();
}
~~~
