php 导入导出

最新推荐文章于 2022-06-27 11:52:36 发布

walter / White

最新推荐文章于 2022-06-27 11:52:36 发布

阅读量253

点赞数

分类专栏： php 文章标签： php

本文链接：https://blog.csdn.net/weixin_52307979/article/details/117078795

版权

php 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在这里插入代码片
```## 标题php 导入导出
首先下载 composer require phpoffice/phpspreadsheet
<php?
	namespace app\controller;
	use PhpOffice\PhpSpreadsheet\Spreadsheet;
	use PhpOffice\PhpSpreadsheet\IOFactory;
	class Customer extends Base
	{
		public function excel(\app\model\Customer $customer)
   		 {
   		 	 $title = ['姓名', '手机号码', '地址', '所属客服', '添加时间'];
   		 	 $filed = [ 'user_name', 'phone','add','kf_id'];
   		 	  $data = $customer->field($filed)->with(['kf' => function (Query$query) {
            $query->field('kf_name,id');
        }])->select()->toArray();
   		 }
		$spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet(); //获取Excel表格
		$titCol = 'A';
        foreach ($title as $key => $value) {
            //单元格表头写入
            $sheet->setCellValue($titCol . '1', $value);
            $titCol++;
        }
         $row = 2;
        foreach ($data as $key => $item) {
            $dataCol = 'A';
            foreach ($item as $k => $val) {
                if ($k === 'kf_id') {
                    //过滤字段
                    continue;
                }
                //单元格内容写入
                if (is_array($val)) {
                    $sheet->setCellValue($dataCol . $row, $val['kf_name']);
                } else {
                    $sheet->setCellValue($dataCol . $row, $val);
                }
                $dataCol++;
            }
            $row++;
        }
        //保存
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $fileName = 'demo.xlsx';
        //输出到浏览器
        header('Content-type: text/html; charset=utf-8');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename=' . $fileName);
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        die;
	}
	//导入
	public function readExcel(\app\model\Customer $customer)
    {

        $file = $this->request->file('file');
        if (empty($file)) {
            echo '没有上传文件';
            die;
        }
        $saveFile = $file->move(public_path() . 'upload');
//          dump($saveFile->getFilename());  //查看文件名
        $fileName = public_path() . 'upload/' . $saveFile->getFilename();//拿到文件路径

        $reader = IOFactory::createReader('Xlsx');
        //判断文件格式是否正确
        if (!$reader->canRead($fileName)) {
            echo '文件格式错误';
            die;
        }
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load($fileName);
        $workSheet = $spreadsheet->getActiveSheet(); //拿到excel表格里面的数据
        $datas = $workSheet->toArray();
        //验证数据
        $successNum = 0;
        $errorNum = 0;
        $errorData = [];
        $excelCfPhone = [];
        //删除表头
        unset($datas[0]);
        //拿到所有手机号码
        $phoneAll = array_column($datas, 1);
        //过滤空值
        $excelCfPhone = array_filter($excelCfPhone);
        //找到Excel重复的手机号码
        $excelPhone = array_count_values($phoneAll);
        foreach ($excelPhone as $key => $val) {
            if ($val > 1) {
                $excelCfPhone[] = $key;
            }
        }
        //导入的手机号与数据库进行对比 找到重复的
        $datasPhone = $customer->whereIn('phone', $phoneAll)->column('phone');
        $addData = [];
        foreach ($datas as $key => $val) {
            $user_name = $val['0'] ?? '';
            $phone = $val['1'] ?? '';
            $add = $val['2'] ?? '';
            $kf_id = $val['3'] ?? '';
            $create_time = time();
            //手机号码不能为空
            if (empty($phone)) {
                $errorNum++;
                $errorData[] = [
                    'line' => $key + 1,
                    'msg' => '手机号不能为空'
                ];
                continue;
            }
            //是否为有效手机号码
            if (!preg_match('/^1[3456789]{1}\d{9}$/', $phone)) {
                $errorNum++;
                $errorData[] = [
                    'line' => $key + 1,
                    'msg' => '手机号不符合规范'
                ];
                continue;
            }
            //手机号重复
            if (in_array($phone, $excelCfPhone)) {
                $errorNum++;
                $errorData[] = [
                    'line' => $key + 1,
                    'msg' => 'Excel表中手机号重复'
                ];
                continue;
            }
            if (in_array($phone, $datasPhone)) {
                $errorNum++;
                $errorData[] = [
                    'line' => $key + 1,
                    'msg' => '系统中手机号已存在不能导入'
                ];
                continue;
            }
            $successNum++;
            $addData = [
                'user_name' => $user_name,
                'phone' => $phone,
                'add' => $add,
                'kf_id' => $kf_id,
                'create_time' => $create_time
            ];

        }
|



        if (!empty($addData)){
            $res = $customer->saveAll([$addData]);
            //判断res 略。。。
        }
        return json([
            'code'=>0,
            'data'=>[
                'successNum'=>$successNum,
                'errorNum'=>$errorNum,
                'errorData'=>$errorData
            ]
        ]);
?>

```php
在这里插入代码片