个人随笔之php表格导入类库phpspreadsheet

• composer安装包

composer require phpoffice/phpspreadsheet

一段读取上传表格的文件的代码实例

/**
     * 导入表格数据
     * User: zmq3821@163.com
     * Date: 2021/5/15 0:07
     * @param $files
     * @return bool
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
 public function import($files): bool
    {
        foreach($files as $file) {
            $inputFileName = "xxx.xls";
            # 检测文件类型
            $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);
            # 创建读操作
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
            # 打开文件、载入excel表格
            $spreadsheet = $reader->load($inputFileName);
            # 获取活动工作薄
            $sheet = $spreadsheet->getActiveSheet();
            # 获取总列数
            $highestColumn = $sheet->getHighestColumn();
            # 获取总行数
            $highestRow = $sheet->getHighestRow();
            # 列数 改为数字显示
            $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
            if ($highestRow-1 > 500) {
                $this->error = '导入最大条数不能超过500条';
                return false;
            }
            //序号	申请单号*  设备编号  设备名称*  外委单位*  预计金额(元)	工期要求*	外委理由	  申请时间	申请人
            $fields = [ 'number', 'sn', 'dev_sn', 'dev_name', 'department', 'plan_amount', 'time_request', 'reason', 'apply_time', 'creator' ];

            $data = [];
            for($a=2; $a<=$highestRow; $a++){
                $rowData = [];
                foreach ($fields as $k => $field) {
                    if ($k == 'number') continue;
                    $_value = trim($sheet->getCellByColumnAndRow($k+1, $a)->getValue());
                    switch ($field)
                    {
                        case 'number':
                            $_value = intval($_value);
                            break;
                        case 'plan_amount':
                            $_value = intval(floatval($_value) * 100);
                            break;
                        case 'apply_time':
                            $_value = $this->excelTime($_value);
                            break;
                    }
                    $rowData[$field] = $_value;
                }
                //检查数据
                $re = $this->checkImportData($rowData);
                if (true !== $re) {
                    $this->error = "第{$a}行{$re}";
                    return false;
                }
                $rowData['process_status'] = 4;
                $rowData['create_time'] = time();
                $data[] = $rowData;
            }
            if (empty($data)) {
                $this->error = '导入数据为空,请检查';
                return false;
            }
            //插入数据
            $insert_number = Db::name('var_device_repair_outer')->insertAll($data);
            if (!$insert_number) {
                $this->error = '数据导入失败';
                return false;
            }
            $this->result = $insert_number;
            return true;
        }
    }

/**
     * 检查导入表格数据
     * User: zmq3821@163.com
     * Date: 2021/5/15 1:15
     * @param array $row
     * @return string
     */
    public function checkImportData(array $row)
    {
        $requireFields = ['sn'=>'设备编号', 'dev_name'=>'设备名称', 'department'=>'外委单位', 'time_request'=>'工期要求', 'apply_time'=>'申请时间', 'creator'=>'申请人'];
        foreach ($row as $f => $datum) {
            if (array_key_exists($f, $requireFields) && empty($datum)) {
               return $requireFields[$f].'不能为空';
            }
        }
        return true;
    }


/**
     * 转换excel的日期为时间戳
     * User: zmq3821@163.com
     * Date: 2021/5/15 10:25
     * @param string $value
     * @return int
     */
    public function excelTime(string $value)
    {
        //如果是数字则转化，如果是有 - 或者 /，视作文本格式不作处理
        $type1 = strpos($value, '/');
        $type2 = strpos($value, '-');
        if ($type1 || $type2) {
            $toTimestamp = intval(strtotime($value));
        } else {
            $toTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($value, 'PRC');
        }
        return $toTimestamp;
    }



/**
     * @todo 下载文件
     */
    public function downlaodImportTemplate(){
        header("Content-type:text/html;charset=utf-8");
        $root_path = app()->getRootPath();
        $file_name = 'BwDeviceManage/static/assets/data/excel/DeviceRepairOuterImportTmeplate.xlsx';
        $file_name = iconv("utf-8", "gb2312", $file_name);
        $file_path = $root_path . $file_name;
        if (!file_exists($file_path)) {
            $view = View::instance();
            $view->assign('msg', '下载文件不存在');
            $data     = $view->fetch(config('view.dispatch_error_tmpl'));
            $response = Response::create($data, 'html');
            throw new HttpResponseException($response);
        }
        $fp = fopen($file_path, "r");
        $file_size = filesize($file_path);
        //下载文件需要用到的头
        Header("Content-type: application/octet-stream");
        Header("Accept-Ranges: bytes");
        Header("Accept-Length:".$file_size);
        Header("Content-Disposition: attachment; filename=".$file_name);
        $buffer = 1024;
        $file_count = 0;
        while (!feof($fp) && $file_count < $file_size) {
            $file_con = fread($fp, $buffer);
            $file_count += $buffer;
            echo $file_con;
        }
        fclose($fp);
    }