<?php

namespaceapp\cadmin\controller;

usethink\Controller;

usethink\Db;

usethink\Loader;

usePHPExcel;

usePHPExcel_IOFactory;

usePHPExcel_Cell;

usePHPExcel_Writer_Excel5;

usePHPExcel_Writer_Excel2007;

classExcelextendsController

{

publicfunctionindex()

{

return123;

}

publicfunctionexcel(){

if(request()->isPost()){

Loader::import('PHPExcel.PHPExcel');

Loader::import('PHPExcel.PHPExcel.PHPExcel_IOFactory');

Loader::import('PHPExcel.PHPExcel.PHPExcel_Cell');

//實例化PHPExcel

$objPHPExcel=newPHPExcel();

$file=request()->file('excel');

if($file){

$file_types=explode(".",$_FILES['excel']['name']);// ["name"] => string(25) "excel文件名.xls"

$file_type=$file_types[count($file_types)-1];//xls后綴

dump($file_type);

die;

$file_name=$file_types[count($file_types)-2];//xls去后綴的文件名

/*判別是不是.xls文件，判別是不是excel文件*/

if(strtolower($file_type)!="xls"&&strtolower($file_type)!="xlsx"){

echo'不是Excel文件，重新上傳';

die;

}

$info=$file->rule('uniqid')->move(ROOT_PATH.'public'.DS.'excel');//上傳位置

$path=ROOT_PATH.'public'.DS.'excel'.DS;

$file_path=$path.$info->getSaveName();//上傳后的EXCEL路徑

//獲取上傳的excel表格的數(shù)據(jù)，形成數(shù)組

$re=$this->actionRead($file_path,'utf-8');

array_splice($re,1,0);

unset($re[0]);

/*將數(shù)組的鍵改為自定義名稱*/

$keys=array('id','username','nickname','password','salt','avatar','email','loginfailure','logintime','createtime','updatetime','token','status');

foreach($reas$i=>$vals){

$re[$i]=array_combine($keys,$vals);

}

//遍歷數(shù)組寫入數(shù)據(jù)庫

for($i=1;$i<count($re);$i++){

$data=$re[$i];

$res=db('admin')->insert($data);

}

}

}

}

publicfunctionactionRead($filename,$encode='utf-8')

{

$objReader=PHPExcel_IOFactory::createReader('Excel2007');

$objReader->setReadDataOnly(true);

$objPHPExcel=$objReader->load($filename);

$objWorksheet=$objPHPExcel->getActiveSheet();

$highestRow=$objWorksheet->getHighestRow();

$highestColumn=$objWorksheet->getHighestColumn();

$highestColumnIndex=PHPExcel_Cell::columnIndexFromString($highestColumn);

$excelData=array();

for($row=1;$row<=$highestRow;$row++)

{

for($col=0;$col<$highestColumnIndex;$col++)

{

$excelData[$row][]=(string)$objWorksheet->getCellByColumnAndRow($col,$row)->getValue();

}

}

return$excelData;

}

//導出

publicfunctionout($hid){

$path=dirname(__FILE__);//找到當前腳本所在路徑

Loader::import("PHPExcel.PHPExcel");

Loader::import("PHPExcel.PHPExcel.Writer.IWriter");

Loader::import("PHPExcel.PHPExcel.Writer.Abstract");

Loader::import("PHPExcel.PHPExcel.Writer.Excel5");

Loader::import("PHPExcel.PHPExcel.Writer.Excel2007");

Loader::import("PHPExcel.PHPExcel.IOFactory");

$objPHPExcel=newPHPExcel();

$objWriter=newPHPExcel_Writer_Excel5($objPHPExcel);

$objWriter=newPHPExcel_Writer_Excel2007($objPHPExcel);

// 實例化完了之后就先把數(shù)據(jù)庫里面的數(shù)據(jù)查出來

// $sql = db('coach')->select();

$sql=Db::name('bactivity b')->join("user u","b.uid=u.uid")->where('hid',$hid)->select();

// 設置表頭信息

$objPHPExcel->setActiveSheetIndex(0)

->setCellValue('A1','uuname')

->setCellValue('B1','uphone')

->setCellValue('C1','time');

// ->setCellValue('D1', 'password')

// ->setCellValue('E1', 'salt')

// ->setCellValue('F1', 'avatar')

// ->setCellValue('G1', 'email')

// ->setCellValue('H1', 'loginfailure')

// ->setCellValue('I1', 'logintime')

// ->setCellValue('J1', 'createtime')

// ->setCellValue('K1', 'updatetime')

// ->setCellValue('L1', 'token')

// ->setCellValue('M1', 'status');

$i=2;//定義一個i變量，目的是在循環(huán)輸出數(shù)據(jù)是控制行數(shù)

$count=count($sql);//計算有多少條數(shù)據(jù)

for($i=2;$i<=$count+1;$i++){

$objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$sql[$i-2]['uuname']);

$objPHPExcel->getActiveSheet()->setCellValue('B'.$i,$sql[$i-2]['uphone']);

$objPHPExcel->getActiveSheet()->setCellValue('C'.$i,$sql[$i-2]['time']);

// $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $sql[$i-2]['password']);

// $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $sql[$i-2]['salt']);

// $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $sql[$i-2]['avatar']);

// $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $sql[$i-2]['email']);

// $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $sql[$i-2]['loginfailure']);

// $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $sql[$i-2]['logintime']);

// $objPHPExcel->getActiveSheet()->setCellValue('J' . $i, $sql[$i-2]['createtime']);

// $objPHPExcel->getActiveSheet()->setCellValue('K' . $i, $sql[$i-2]['updatetime']);

// $objPHPExcel->getActiveSheet()->setCellValue('L' . $i, $sql[$i-2]['token']);

// $objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $sql[$i-2]['status']);

}

$objPHPExcel->getActiveSheet()->setTitle('admin');//設置sheet的名稱

$objPHPExcel->setActiveSheetIndex(0);//設置sheet的起始位置

$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');//通過PHPExcel_IOFactory的寫函數(shù)將上面數(shù)據(jù)寫出來

$PHPWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,"Excel2007");

header('Content-Disposition: attachment;filename="活動報名表.xlsx"');

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

$PHPWriter->save("php://output");//表示在$path路徑下面生成demo.xlsx文件

}

}