在控制器封装导入导出功能
<?php
namespace Admin\Controller;
use Think\Controller;
class ExcelController extends Controller
{
public function index() {
$this->display();
}
/**
*
* 导出Excel
*/
public function expExcel(){//导出Excel
$xlsName = "Contacts";
$xlsCell = array(
array('id','编号'),
array('name','姓名'),
array('age','年龄'),
array('create_time','创建时间',50)
);
$xlsModel = M('test');
$xlsData = $xlsModel->select();
if($xlsData)
{
foreach ($xlsData as $key=>$val)
{
if($val['create_time']){
$xlsData[$key]['create_time'] = date('Y-m-d H:i:s', $val['create_time']);
}
}
}
$this->exportExcel($xlsName,$xlsCell,$xlsData);
}
//导出操作
public function exportExcel($expTitle,$expCellName,$expTableData){
$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
$fileName = $expTitle.date('_Ymd_His');//or $xlsTitle 文件名称可根据自己情况设定
$cellNum = count($expCellName);
$dataNum = count($expTableData);
vendor("PHPExcel.PHPExcel");
$objPHPExcel = new \PHPExcel();
//循环写入标题
$key_num = 'A';
for ($i=0; $i < $cellNum; $i++) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($key_num.'1',$expCellName[$i][1]);
$key_num++;
}
//从第三个单元格开始写入 ,当获取的数据条数数组+1时,则将变量$num+1从而将单元格坐标下移
foreach($expTableData as $key => $value){
$num=$key+2;
$objPHPExcel->setActiveSheetIndex(0)
//Excel的第A列，uid是你查出数组的键值，下面以此类推
->setCellValue('A'.$num,$value['id'])
->setCellValue('B'.$num,$value['name'])
->setCellValue('C'.$num,$value['age'])
->setCellValue('D'.$num,$value['create_time']);
}
//设置每列单元格的宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
/**实现导入excel
**/
public function impExcel(){
if (!empty($_FILES)) {
$upload = new \Think\Upload();// 实例化上传类
$filepath='./public/upload/excle/';
//没有路径就创建
if(!is_file($filepath)){
mkdir($filepath);
}
$upload->exts = array('xlsx','xls');// 设置附件上传类型
$upload->rootPath = $filepath; // 设置附件上传根目录
$upload->saveName = 'time';
$upload->autoSub = false;
if (!$info=$upload->upload()) {
$this->error($upload->getError());
}
foreach ($info as $key => $value) {
unset($info);
$info[0]=$value;
$info[0]['savepath']=$filepath;
}
vendor("PHPExcel.PHPExcel");
$file_name=$info[0]['savepath'].$info[0]['savename'];
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load($file_name,$encode='utf-8');
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$j=0;
for($i=2;$i<=$highestRow;$i++)
{
$data['name']= $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
$data['age']= $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
$data['create_time']= strtotime($objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue());
$res = M('test')->add($data);
if(!$res){
echo 'no';die;
}
$j++;
}
unlink($file_name);
$this->success('导入成功！本次导入测试数据：'.$j.'条');
}else
{
$this->error("请选择上传的文件");
}
}
}
![(5UYX[]X~41FADF0X)MB89O.png](/ueditor/php/upload/image/20190701/1561961325.png "1561961325.png")
![~[6TFTMY_C%Q)58]$4QV9PI.png](/ueditor/php/upload/image/20190701/1561961833.png "1561961833.png")