PHPOffice/PhpSpreadsheet的导入导出操作基本使用

phpspreadsheet 引入

由于PHPExcel早就停止更新维护，所以适用phpspreadsheet。不知道如何通过composer拉取项目包的同学，可以查看Composer学习一文。引入方法：

composer require phpoffice/phpspreadsheet
1

注：可能会出现报错：Your requirements could not be resolved to an installable set of packages，这是因为不匹配composer.json要求的版本，可以在命令后面加上 –ignore-platform-req 来忽略版本匹配 或 加上具体版本号

PhpSpreadsheet的基本使用

注：我使用的是thinkphp5.0框架

引入命名空间(可按需引入)

namespace app\admin\controller;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
1
2
3
4
5
6
7
8

Excel导入操作 (例子)

/**
 * Excel导入人员信息
 */
public function import_excel()
{
    //获取表格的大小，限制上传表格的大小5M
    $file_size = $_FILES['file']['size'];
    if ($file_size > 5 * 1024 * 1024) {
        $this->error('文件大小不能超过5M');
        exit();
    }

    //限制上传表格类型
    $fileExtendName = substr(strrchr($_FILES['file']["name"], '.'), 1);
    if (!in_array($fileExtendName, ['xls','xlsx'])) {
        $this->error('必须为excel表格，且必须为xlsx或xls格式！');
        exit();
    }

    if (is_uploaded_file($_FILES['file']['tmp_name'])) {
        // 有Xls和Xlsx格式两种
        $objReader = IOFactory::createReader('Xlsx');

        $file_name = $_FILES['file']['name'];
        $filename = $_FILES['file']['tmp_name'];

        if (!file_exists($filename)) {
            die('no file!');
        }
        $extension = strtolower( pathinfo($file_name, PATHINFO_EXTENSION) );
        if ($extension =='xlsx') {
            $objReader = IOFactory::createReader('Xlsx');
            $objPHPExcel = $objReader ->load($filename);
        } else if ($extension =='xls') {
            $objReader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
            $objPHPExcel = $objReader ->load($filename);
        } else if ($extension=='csv') {
            $PHPReader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
            $PHPReader->setInputEncoding('GBK');    //默认输入字符集
            $PHPReader->setDelimiter(',');      //默认的分隔符
            $objPHPExcel = $PHPReader->load($filename);     //载入文件
        }
        
        $objReader->setReadDataOnly(true);	// 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率
        $sheet = $objPHPExcel->getSheet(0); //excel中的第一张sheet
        
        //已有用户列表
        $had_userlist = db('userinfo')->where($where)->select();
        
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumn = $sheet->getHighestColumn();   // 取得总列数
        

        $sheetData = $objPHPExcel->getActiveSheet()->ToArray();   // 转成数组
        
        // 测试获取值的方式（可忽略）
        // $sheetData = $objPHPExcel->getActiveSheet()->rangeToArray("A1:G".$highestRow);
        // $sheetData= $objPHPExcel->getActiveSheet()->getCell("E3")->getValue();
        // $sheetData= $objPHPExcel->getActiveSheet()->getCell("E3")->getCalculatedValue();
        // $sheetData= $objPHPExcel->getActiveSheet()->getCell("D3")->getOldCalculatedValue();
        // return $sheetData;
        
        //数据的顺序排列（为了获取对应字段的值）
        $sort = ['name' => '', 'stu' => '', 'sex' => '', 'number' => '', 'phone' => '', 'class' => ''];
        for($i=0; $i < 2; $i++){
            for($key = 0; $key < count($sheetData[$i]); $key++){
                $str = $sheetData[$i][$key];
                if( in_array($str, ['姓名','名字','学生姓名']) ){
                    $sort['name'] = $key;
                }elseif( in_array($str, ['工号','学号']) ){
                    $sort['stu'] = $key;
                }elseif( in_array($str, ['性别']) ){
                    $sort['sex'] = $key;
                }elseif( in_array($str, ['手机','手机号','手机号码']) ){
                    $sort['phone'] = $key;
                }elseif( in_array($str, ['身份证号','身份证','身份证号码','证件号码']) ){
                    $sort['number'] = $key;
                }elseif( in_array($str, ['班级','院系']) ){
                    $sort['class'] = $key;
                }
            }
        }
        // return $sort;

        // 定义$usersExits，循环表格的时候，找出已存在的用户。
        $usersExits = [];
        foreach($sheetData as $k => $v){
            if($k == 0){ unset($sheetData[0]);continue; }

            $id_number = empty($sort['number']) ? '' : $v[$sort['number']];
            if(!$id_number || !validateIDCard($id_number)){ unset($sheetData[$k]);continue; }

            //其他数据处理。。。
           
            $data[$k] = [        
                'stu_number' => empty($sort['stu']) ? '' : $v[$sort['stu']],
                'username' => empty($sort['name']) ? '' : $v[$sort['name']],
                'id_number' => $id_number,
                'phone' => empty($sort['phone']) ? '' : $v[$sort['phone']],            
                'create_time' => time()
            ];

            //看下用户是否存在。将存在的用户保存在数组里。
            foreach($had_userlist as $k2=>$v2){
                if($id_number == $v2['id_number']){
                    $data[$k]['id'] = $v2['id'];
                    array_push($usersExits, $data[$k]);
                    unset($data[$k]);
                    break;
                }
            }
        }
        
        //如果有已存在的用户名，就不插入数据库了,直接更新。
        if ($usersExits != []) {
            foreach($usersExits as $k3=>$v3){
                $udata = [          
                    'stu_number' => $v3['stu_number'],
                    'username' => $v3['username'],
                    'id_number' => $v3['id_number'],
                    'phone' => $v3['phone'],
                    'level' => $v3['level'],          
                    'sex' => $v3['sex'],         
                    'classroom_id' => $v3['classroom_id'],
                ];
                db('userinfo')->where($where)->update($udata);
            }
        }
        
        if($data){
            $res = db('userinfo')->insertAll($data);   //全部插入数据库
        }
        
        return writeJson(200, '', '导入成功！');
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136

Excel导出操作 (例子)

/**
 * excel表格导出(封装)
 * @param string $fileName 文件名称 $name='测试导出';
 * @param array $headArr 表头名称 $header=['表头A','表头B'];
 * @param array $data 要导出的数据 $data=[['测试','测试'],['测试','测试']]
 * @param bool $auto 是否开启根据表头自适应宽度 默认开启
 * */
public function excelExport($fileName = '', $headArr = [], $data = [], $auto = true, $format="Xlsx")
{
    $dir = rtrim(str_replace('\\', '/', $_SERVER['DOCUMENT_ROOT']), '/') . '/public/excel';
    if (!is_dir($dir)) {
        mkdir($dir, 0777, true);
    }

    $fileName .= ".".$format;
    $objPHPExcel = new Spreadsheet();
    $objPHPExcel->getProperties();
    $key = ord("A"); // 设置表头
    $key2 = ord("@"); //	超过26列会报错的解决方案

    // 居中
    $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

    // 设置表头
    foreach ($headArr as $v) {
        // 超过26列会报错的解决方案
        if ($key > ord("Z")) {
            $key2 += 1;
            $key = ord("A");
            $colum = chr($key2) . chr($key); //超过26个字母时才会启用
        } else {
            if ($key2 >= ord("A")) {
                $colum = chr($key2) . chr($key);
            } else {
                $colum = chr($key);
            }
        }
        // 写入表头
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
        // 自适应宽度
        if ($auto) {
            // $len = strlen(iconv('utf-8','gb2312',$v));//会报错
            $len = strlen(iconv('utf-8', 'gbk', $v));
            $len = $len > 25 ? 30 : 20;
            $objPHPExcel->getActiveSheet()->getColumnDimension($colum)->setWidth($len+5);
        }
        $key += 1;
    }

    $column = 2;
    $objActSheet = $objPHPExcel->getActiveSheet();
    // 写入行数据
    foreach ($data as $key => $rows) {
        $span = ord("A");
        $span2 = ord("@");
        // 按列写入
        foreach ($rows as $keyName => $value) {
            // 超过26列会报错的解决方案
            if ($span > ord("Z")) {
                $span2 += 1;
                $span = ord("A");
                $tmpSpan = chr($span2) . chr($span); //超过26个字母时才会启用
            } else {
                if ($span2 >= ord("A")) {
                    $tmpSpan = chr($span2) . chr($span);
                } else {
                    $tmpSpan = chr($span);
                }
            }
            // 写入数据
            $objActSheet->setCellValue($tmpSpan . $column, $value);
            $span++;
        }
        $column++;
    }

    // 自动加边框
    $styleThinBlackBorderOutline = array(
        'borders' => array(
            'allborders' => array( //设置全部边框
                'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //粗的是thick
            ),

        ),
    );
    $objPHPExcel->getActiveSheet()->getStyle('A1:' . $colum . --$column)->applyFromArray($styleThinBlackBorderOutline);
    // 重命名表
    $fileName = iconv("utf-8", "gbk", $fileName);
    // 设置活动单指数到第一个表,所以Excel打开这是第一个表
    $objPHPExcel->setActiveSheetIndex(0);
   
    // header("Content-Type: application/octet-stream");
    if ($format == 'Xlsx') {
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    } elseif ($format == 'Xls') {
        header('Content-Type: application/vnd.ms-excel');
    }
    header("Content-Disposition: attachment;filename=$fileName");
    header('Cache-Control: max-age=0');
    //以下为需要用到IE时候设置
    // If you're serving to IE 9, then the following may be needed
    //header('Cache-Control: max-age=1');
    // If you're serving to IE over SSL, then the following may be needed
    //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
    //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    //header('Pragma: public'); // HTTP/1.0

    $writer = IOFactory::createWriter($objPHPExcel, 'Xlsx');
    // $writer = new Xlsx($objPHPExcel);
    // $writer->save('php://output'); // 文件通过浏览器下载（不知为何直接打印出来一大堆乱码，不能直接下载）
    
    $writer->save($dir.'/'.$fileName);	// 文件下载到服务器
   
    $result = [
        'fileurl' => 'http://'.$_SERVER['HTTP_HOST'].'/public/excel/'.$fileName,
        'filename' => $fileName,
    ];
    return $result;
    exit();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

更多的Excel操作可以去查文档或百度┗( ▔, ▔ )┛

官网文档：https://phpspreadsheet.readthedocs.io/en/stable/ （注：这是英文文档，中文的暂未找到）