thinkphp5⽂件导⼊导出功能
⾸先将下载好的phpexcel扩展包解压
将⽂件夹下⾯的classes更名为PHPExcel放到thinkphp5的vendor⽂件夹下⾯
这⾥是下载链接
导⼊导出提取链接 ：
提取码：56ky
⾸先是导出功能
// 导出数据
public function order()
{
// 1.选取表中要输出数据
$con=Db::name('content')->select();
$this->assign('con',$con);
//2.加载PHPExcle类库
vendor('PHPExcel.PHPExcel');
//3.实例化PHPExcel类
$objPHPExcel=new\PHPExcel();
//4.激活当前的sheet表
$objPHPExcel->setActiveSheetIndex(0);
//5.设置表格头（即excel表格的第⼀⾏）
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1','ID')
-
>setCellValue('B1','姓名')
->setCellValue('C1','性别')
->setCellValue('D1','年龄')
->setCellValue('E1','电话')
->setCellValue('F1','地址')
->setCellValue('G1','详细地址');
// 设置表格头⽔平居中
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A1')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C1')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D1')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E1')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F1')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G1')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置列⽔平居中
$objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()
-
>setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置单元格宽度
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('A')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('B')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('C')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('D')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('E')->setWidth(10);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('F')->setWidth(30);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension('G')->setWidth(30);
//6.循环刚取出来的数组，将数据逐⼀添加到excel表格。
for($i=0;$i<count($con);$i++){
$objPHPExcel->getActiveSheet()->setCellValue('A'.($i+2),$con[$i]['id']);//ID
$objPHPExcel->getActiveSheet()->setCellValue('B'.($i+2),$con[$i]['name']);//姓名
$objPHPExcel->getActiveSheet()->setCellValue('C'.($i+2),$con[$i]['sex']);//性别
$objPHPExcel->getActiveSheet()->setCellValue('D'.($i+2),$con[$i]['age']);//年龄
$objPHPExcel->getActiveSheet()->setCellValue('E'.($i+2),$con[$i]['phone']);//电话
$objPHPExcel->getActiveSheet()->setCellValue('F'.($i+2),$con[$i]['cho_Province'].$con[$i]['cho_City'].$con[$i]['cho_Area']);//地址$objPHPExcel->getActiveSheet()->setCellValue('G'.($i+2),$con[$i]['address']);//详细地址
}
//7.设置保存的Excel表格名称
$filename='user'.date('ymd',time()).'.xls';
//8.设置当前激活的sheet表格名称
$objPHPExcel->getActiveSheet()->setTitle('user');
//9.设置浏览器窗⼝下载表格
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="'.$filename.'"');
//⽣成excel⽂件
$objWriter= \PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
/
/下载⽂件在浏览器窗⼝
$objWriter->save('php://output');
exit;
}
根据⾃⼰的需求进⾏修改
然后是导⼊功能
// 终端上传
public function upload()
{
// 引⼊核⼼⽂件
require'vendor/PHPExcel/PHPExcel.php';
header("content-type:text/html;charset=utf-8");
//上传excel⽂件
$file=request()->file('excel');
$files=$_FILES['excel'];
//将⽂件保存到public/uploads⽬录下⾯
$info=$file->validate(['size'=>1048576,'ext'=>'xls,xlsx'])->move('./uploads');
if($info){
//获取上传到后台的⽂件名
$fileName=$info->getSaveName();
//获取⽂件路径
$filePath= Env::get('root_path').'public'.DIRECTORY_SEPARATOR.'uploads'.DIRECTORY_SEPARATOR.$fileName;
//获取⽂件后缀
$suffix=$info->getExtension();
//判断哪种类型
if($suffix=="xlsx"){
$reader= \PHPExcel_IOFactory::createReader('Excel2007');
}else{
$reader= \PHPExcel_IOFactory::createReader('Excel5');
}
}else{
$this->error('⽂件过⼤或格式不正确导致上传失败-_-!');
}
$objContent=$reader->load($files['tmp_name']);
$sheetContent=$objContent->getSheet(0)->toArray();
unset($sheetContent[0]);
unset($sheetContent[0]);
foreach($sheetContent as$k=>$v){
$arr['termid']=$v[0];
$arr['termsn']=$v[1];
$arr['termmodel']=$v[2];
$arr['createtime']=time();
$arr['orderid']=$v[3];
$arr['firm']=$v[4];
$arr['sn']=substr($arr['termsn'],-6);
$res[]=$arr;
}
$total=count($res);//导⼊总数
foreach($res as$key=>$val){
$select[]= Db::name('terminus')->where('termsn',$val['termsn'])->find();
}
//查询出来已存在的机具编号
foreach($select as$k=>$v){
$number[]=$v['termsn'];
}
/
/数据表中已存在总数
if(empty($number)){
$already=0;
}else{
$already=count(array_filter($number));//去除空数组并统计
}
//操作数据库
foreach($res as$k=>$v){
$find= Db::name('terminus')->where('termsn',$v['termsn'])->find();
if($find){
echo"<script>alert('导⼊成功，共$total条信息,$already条数据已存在'),(-2)</script>"; }else{
$int= Db::name('terminus')->insert($v);
if($int){
echo"<script>alert('导⼊成功，共$total条信息,$already条数据已存在'),(-2)</script>"; }else{
echo"<script>alert('导⼊失败'),(-2)</script>";
}
}
}
getsavefilename}
根据⾃⼰需求进⾏修改
这些就是thinkphp5的导⼊导出功能

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。