今天来给大家分享一个特别好用的 Excel导入\导出,亲测哦!
一 导入
function impExcel()
{
vendor("phpexcel.PHPExcel"); //下载PHPExcel类
//获取表单上传文件
$file = request()->file('excel');
$info = $file->validate(['ext' => 'xlsx'])->move(ROOT_PATH . 'public' . DS . 'uploads');
if ($info) {
//echo $info->getFilename();die;
$exclePath = $info->getSaveName(); //获取文件名
$file_name = ROOT_PATH . 'public' . DS . 'uploads' . DS . $exclePath; //上传文件的地址
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
$obj_PHPExcel = $objReader->load($file_name, $encode = 'utf-8'); //加载文件内容,编码utf-8
echo "<pre>";
$excel_array = $obj_PHPExcel->getsheet(0)->toArray(); //转换为数组格式
array_shift($excel_array); //删除第一个数组(标题);
$city = [];
foreach ($excel_array as $k => $v) {
$city[$k]['username'] = $v[0];
$city[$k]['password'] = '******';
$city[$k]['email'] =$this->email();
$city[$k]['salt'] =$this->foo();
$city[$k]['regdate'] = '2017-7-14 12:01:27';
}
// var_dump($city);die;
$add=Db::name('user')->insertAll($city); //批量插入数据
//var_dump($add);die;
if($add){
$this->success('添加成功');
}else{
$this->error('失败');
}
} else {
echo $file->getError();
}
}
二 导出
function expExcel()
{
vendor("phpexcel.PHPExcel");
$userName=db('dis_ucenter_members')->field('uid,username')->select();
$PHPExcel = new \PHPExcel();//实例化
$PHPSheet = $PHPExcel->getActiveSheet();
$PHPSheet->setTitle("demo"); //给当前活动sheet设置名称
//$PHPSheet->setCellValue("A1","ID")->setCellValue("B1","username");//表格数据
//$PHPSheet->setCellValue("A2","001")->setCellValue("B2","元宝");//表格数据
$i=1;
foreach($userName as $key=>$val){
$PHPSheet->setCellValue('A'.$i,$val['uid'])->setCellValue('B'.$i,$val['username']);//表格数据
$i++;
}
$PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");//创建生成的格式
header('Content-Disposition: attachment;filename="userName.xlsx"');//下载下来的表格名
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
}
三 随机字母数字
public function foo($size=6) {
$dict = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
$len = $size == 16 ? 36 : mb_strlen($dict);
$res = '';
for($i=0; $i<$size; $i++) $res .= $dict{rand(0, $len - 1)};
//查找数据库是否存在
$str=db('user')->where('salt',$res)->find();
if($str){
$this->foo();
}else{
return $res;
}
}