ThinkPHP中PHPExcel的使用(包含日期格式)

时间:2022-09-24 16:16:40

1.实现excel导入

 

 /**实现导入excel
*
*/
function impUser($tid){
if (!empty($_FILES)) {

$config
=array(
'exts'=>array('xlsx','xls'),
'rootPath'=>"./Public/",
'savePath'=>'Excel/',
'autoSub' => true,
'subName' => array('date','Ymd'),
);
$upload
= new \Think\Upload($config);
if (!$info=$upload->upload()) {
$
this->error($upload->getError());
}
vendor(
"PHPExcel.Classes.PHPExcel");
$file_name
=$upload->rootPath.$info['import']['savepath'].$info['import']['savename'];
//var_dump($file_name);exit;
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel
= $objReader->load($file_name,$encode='utf-8');
$sheet
= $objPHPExcel->getSheet(0);
$highestRow
= $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
for($i=3;$i<=$highestRow;$i++)
{
$data[
'tid']= $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
$data[
'content']=$objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
$data[
'aa']= $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
$data[
'ab'] = $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
$data[
'ac'] = $objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue();
$data[
'ad']= $objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue();
$data[
'answer']= $objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue();
$data[
'addtime']= gmdate("Y-m-d H:i:s", \PHPExcel_Shared_Date::ExcelToPHP($objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue()));
$data[
'addtime']=strtotime( $data['addtime']);
$data[
'quescore']= $objPHPExcel->getActiveSheet()->getCell("j".$i)->getValue();

M(
'Quest')->add($data);

}
$
this->success('导入成功!');
}
else
{
$
this->error("请选择上传的文件");
}

 

2.上传文件的格式

ThinkPHP中PHPExcel的使用(包含日期格式)

3.文件导出

//导出excel
function expUser($tid){
$xlsName
= M('Test') -> where(array('id'=>$tid))->getField('title');
//$xlsName = "我是导出的excel";
$xlsCell = array(
array(
'id','题号id'),
array(
'tid','题试卷id'),
array(
'content','问题名称'),
array(
'aa','选项A'),
array(
'ab','选项B'),
array(
'ac','选项C'),
array(
'ad','选项D'),
array(
'answer','选项'),
array(
'addtime','时间'),
array(
'quescore','分数')

);
$xlsModel
= M('Quest');

$xlsData
= $xlsModel-> where(array('tid'=>$tid))->Field('id,tid,content,aa,ab,ac,ad,answer,addtime,quescore')->select();

$
this->exportExcel($xlsName,$xlsCell,$xlsData);
}

4.注意引入的phpexcel的路径

把phpexcel的包放入 ThinkPHP/Library/Vendor 下面

 $objReader = \PHPExcel_IOFactory::createReader//这里要注意‘\’ 要有这个