phpspreadsheet读取Excel数据存入mysql

<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/9/27
 * Time: 13:57
 */

namespace site\admin\model;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use system\library\AliYunOSS;
use system\library\Unzip;

class ProductBatchModel extends \site\admin\component\Model
{
    
    public function add($data){
        
        if (!$this->_validate($data, 'add')) {
            return false;
        }
        
        $languageModel = new LanguageModel();
        $categoryModel = new CategoryModel();
        $productModel = new ProductModel();
        $urlAliasModel = new UrlAliasModel();
        
        if(substr(strrchr($data['excel_file'], '.'), 1)<>'xlsx'){
            $this->addError('excel_file', '请上传xlsx格式的Excel文件');
            return false;
        }
        
        $arr_file = explode('com/', $data['excel_file']);
        
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load(DIR_UPLOAD.$arr_file[1]); //载入excel表格
        
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数
        $highestColumn = $worksheet->getHighestColumn(); // 总列数
        $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
        
        $lines = $highestRow - 1;
        if ($lines <= 0) {
            $this->addError('Excel', 'Excel表格中没有数据');
            return false;
        }
        
        $enableRow = array();
        $arr_url = array();
        
        for ($row = 2; $row <= $highestRow; ++$row) {
            
            /******************************验证产品型号***********************************/
            
            $model_no = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
            if(empty($model_no)){
                $this->addError('model_no'.$row, '第'.$row.'行产品型号为空');
            }else{
                $if_exist = $productModel->getProductByModel($model_no);
                if(empty($if_exist)){
                    $enableRow[] = $row; //新增该行
                }
            }
            
                
            /******************************验证产品分类***********************************/
            
            $category_name = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
            if(empty($category_name)){
                $this->addError('category_name'.$row, '第'.$row.'行分类名为空');
            }else{
                $category = $categoryModel->getCategoryIdByName($category_name);
                if(empty($category)){
                    $this->addError('category_name'.$row, '第'.$row.'行分类名不存在');
                }
            }
            
                
            $sort_order = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
            if(empty($sort_order) || !is_numeric($sort_order)){
                $this->addError('sort_order'.$row, '第'.$row.'行排序错误');
            }
            
            $status = $worksheet->getCellByColumnAndRow(4, $row)->getValue();
            if(empty($status) || !is_numeric($status)){
                $this->addError('status'.$row, '第'.$row.'行状态错误');
            }
                
            /******************************验证语言***********************************/
            
            $language_name = $worksheet->getCellByColumnAndRow(5, $row)->getValue();
            if(empty($language_name)){
                $this->addError('language_name'.$row, '第'.$row.'行语言为空');
            }else{
                $languages = $languageModel->getSysLanguageByName($language_name);
                if(empty($languages)){
                    $this->addError('languages'.$row, '第'.$row.'行语言不存在');
                }
            }
            
                
            $name = $worksheet->getCellByColumnAndRow(6, $row)->getValue();
            if(empty($name)){
                $this->addError('name'.$row, '第'.$row.'行名称为空');
            }
            
            $keywords = $worksheet->getCellByColumnAndRow(7, $row)->getValue();
            if(empty($keywords)){
                $this->addError('keywords'.$row, '第'.$row.'行关键词为空');
            }
            
            $url = $worksheet->getCellByColumnAndRow(10, $row)->getValue();
            if(!empty($url)){
                if(in_array($url, $arr_url)){
                    $this->addError('url'.$row, '第'.$row.'行网址重复出现');
                }else{
                    $arr_url[] = $url;
                }
            }

        }
        
        if($this->hasErrors()){
            return false;
        }

        //组装数组
        $enableData = array();
        foreach ($enableRow as $key => $row) {

            $model_no = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
            
            //获取分类ID
            $category_name = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
            $category = $categoryModel->getCategoryIdByName($category_name);
            $category_id = $category['category_id'];
            
            $sort_order = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
            $status = $worksheet->getCellByColumnAndRow(4, $row)->getValue();
            
            //获取语言ID
            $language_name = $worksheet->getCellByColumnAndRow(5, $row)->getValue();
            $languages = $languageModel->getSysLanguageByName($language_name);
            $language_id = $languages['language_id'];
            
            $name = $worksheet->getCellByColumnAndRow(6, $row)->getValue();
            $keywords = $worksheet->getCellByColumnAndRow(7, $row)->getValue();
            $abstract = $worksheet->getCellByColumnAndRow(8, $row)->getValue();
            $description = $worksheet->getCellByColumnAndRow(9, $row)->getValue();
            $url = $worksheet->getCellByColumnAndRow(10, $row)->getValue();
            $meta_title = $worksheet->getCellByColumnAndRow(11, $row)->getValue();
            $meta_keyword = $worksheet->getCellByColumnAndRow(12, $row)->getValue();
            $meta_description = $worksheet->getCellByColumnAndRow(13, $row)->getValue();
            
            if(!isset($enableData[$model_no])){
                $enableData[$model_no] = array(
                        'product_type_id'=>'',
                        'model_no'=>$model_no,
                        'sort_order'=>$sort_order,
                        'status'=>$status,
                        'category'=>array($category_id),
                        'trade'=>'',
                        'producttradeinfo'=>array()
                );
            }
            
            $enableData[$model_no]['desc'][$language_id] = array(
                    'description'=>$description,
                    'name'=>$name,
                    'abstract'=>$abstract,
                    'keywords'=>$keywords,
                    'language_name'=>$language_name,
                    'meta_title'=>$meta_title,
                    'meta_keyword'=>$meta_keyword,
                    'meta_description'=>$meta_description,
                    'url_alias'=>$url
            );
            
        }
        
        //遍历检测网址
        foreach ($enableData as $key => $value){
            if(isset($value['desc']) && !empty($value['desc'])){
                foreach ($value['desc'] as $k => $v){
                    $cc = $urlAliasModel->validate(['keyword' => $v['url_alias'], 'language_id' => $k, 'query' => ''],'add');
                    if($cc==false){
                        return false;
                    }
                }
            }
        }

        //解压缩图片压缩包
        $dirPath = DIR_UPLOAD.$this->session->data['site_id'];
        $file = $dirPath.'/'.$data['images_file'];
        if(!file_exists($file) || (substr(strrchr($file, '.'), 1)<>'zip')){
            $this->addError('imageZip', '请上传产品图片压缩包（zip格式）');
            return false;
        }
        $z    = new Unzip;
        $info = $z->unzip($file, $dirPath.'/', true, false);
        if($info){
            unlink($file); //删除压缩包
        }

        //获取产品图片
        foreach ($enableData as $key => $value){            
            $enableData[$key]['image'] = $this->_getImage($key,$dirPath.'/'.basename($data['images_file'],'.zip'));
        }

        foreach ($enableData as $key => $value){
            try {
                $productModel->add($value);
            } catch (Exception $e) {
                $this->addError('add', $e->getMessage());
                return false;
            }
        }
        return true;

    }
    
    /**
     * 生成模板
     *
     * @access public
     * @return void
     */
    public function createTemplate()
    {
        
        $languageModel = new LanguageModel();
        $languages = $languageModel->getEnabledLanguages();
        
        $categoryModel = new CategoryModel();
        $category = $categoryModel->getRecursionCategory();
        $categoryNme = $this->_getCategoryName($category);
        
        $columnNum = 3000;
        
        $spreadsheet = new Spreadsheet();
        
        /*****************************************设置当前活动页内容***********************************/
        
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', '型号（必填）');
        $sheet->setCellValue('B1', '分类名（必填）');
        $sheet->setCellValue('C1', '排序（必填，请输入数字）');
        $sheet->setCellValue('D1', '状态（必填，1=上架|0=下架）');        
        $sheet->setCellValue('E1', '语言（必填）');
        $sheet->setCellValue('F1', '名称（必填）');
        $sheet->setCellValue('G1', '关键词（必填）');
        $sheet->setCellValue('H1', '简要描述');
        $sheet->setCellValue('I1', '详细描述');
        $sheet->setCellValue('J1', '网址');
        $sheet->setCellValue('K1', 'seo标题');
        $sheet->setCellValue('L1', 'seo关键词');
        $sheet->setCellValue('M1', 'seo描述');
        
        //设置相关属性
        $sheet->setTitle('Product');
        $sheet->getDefaultColumnDimension()->setWidth(30);
        $sheet->getStyle('A1:M1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle('A1:M1')->getFill()->getStartColor()->setARGB('FF808080');
        $sheet->getStyle('A1:M1')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
        
        //分类列
        for($i=2;$i<=$columnNum;$i++){
            $objValidation = $sheet->getDataValidation('B'.$i);
            $objValidation -> setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST)
            -> setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('输入的值有误')
            -> setError('您输入的值不在下拉框列表内')
            -> setPrompt('请选择下拉框列表中的值')
            -> setFormula1('data!$C$3:$C$'.(count($categoryNme)+2));
        }
        
        //状态列
        for($i=2;$i<=$columnNum;$i++){
            $objValidation = $sheet->getDataValidation('D'.$i);
            $objValidation -> setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST)
            -> setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('输入的值有误')
            -> setError('您输入的值不在下拉框列表内')
            -> setPrompt('请选择下拉框列表中的值')
            -> setFormula1('data!$A$3:$A$4');
        }
        
        //语言列
        for($i=2;$i<=$columnNum;$i++){
            $objValidation = $sheet->getDataValidation('E'.$i);
            $objValidation -> setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST)
            -> setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION)
            -> setAllowBlank(false)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('输入的值有误')
            -> setError('您输入的值不在下拉框列表内')
            -> setPrompt('请选择下拉框列表中的值')
            -> setFormula1('data!$B$3:$B$'.(count($languages)+2));
        }
        
        
        
        /*******************************************新增一个活动页，放公共数据*********************************/
        
        $spreadsheet->createSheet();
        $spreadsheet->setActiveSheetIndex(1);
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('data');
        $sheet->getDefaultColumnDimension()->setWidth(25);
        $sheet->mergeCells('A1:C1');
        $styleArray = [
                'alignment' => [
                        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                ],
        ];
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getRowDimension('1')->setRowHeight(35);
        
        $sheet->getStyle('A1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle('A1')->getFill()->getStartColor()->setARGB('8B0000');
        $sheet->getStyle('A1')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
        $sheet->setCellValue('A1', '公共数据（请不要修改）');
        
        $sheet->getStyle('A2:C2')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
        $sheet->getStyle('A2:C2')->getFill()->getStartColor()->setARGB('FF808080');
        $sheet->getStyle('A2:C2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
        
        $sheet->setCellValue('A2', '状态（1=上架|0=下架）');
        $sheet->setCellValue('A3', '0');    
        $sheet->setCellValue('A4', '1');
        
        $sheet->setCellValue('B2', '语言');
        foreach ($languages as $key => $value){
            $sheet->setCellValue('B'.($key+3), $value['name']);
        }
        
        $sheet->setCellValue('C2', '分类名');
        foreach ($categoryNme as $key => $value){
            $sheet->setCellValue('C'.($key+3), $value);
        }
        
        $sheet->setSheetState('hidden');
        
        //设置第一页为活动页，保存
        $spreadsheet->setActiveSheetIndex(0);
        $writer = new Xlsx($spreadsheet);
        $file_name = $this->session->data['site_id'].'.xlsx';
        $file_path = DIR_UPLOAD.'excel_template/'.$file_name;
        $writer->save($file_path);
        
        //释放内存
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
    
        return $file_name;
    }
    
    /**
     * 验证
     * @access private
     * @param array $data
     * @param string $scenario 验证场景 add
     * @return bool
     */
    private function _validate($data, $scenario = '')
    {
    
        if (!isset($data['excel_file']) || empty($data['excel_file'])) {
            $this->addError('excel_file', '没有上传产品Excel文件');
        }
        
        if (!isset($data['images_file']) || empty($data['images_file'])) {
            $this->addError('images_file', '没有上传产品图片文件');
        }
    
        return $this->hasErrors() ? false : true;
    }
    
    /**
     * 获取产品分类名称
     * @access private
     * @param array $data
     * @return array
     */
    private function _getCategoryName($data){
        
        if(!empty($data)){
            
            foreach ($data as $key => $value){
                if(!empty($data['children'])){
                    $this->_getCategoryName($data['children']);
                }else{
                    $retrun[] = $value['name'];
                }
            }
            
        }else{
            $retrun = array();
        }
        
        return $retrun;
        
    }
    
    /**
     * 遍历文件夹
     * @access private
     * @param string $dir
     * @return array
     */
    private function _getDirFile($dir) {
        $files = array();
        if(@$handle = opendir($dir)) { //注意这里要加一个@，不然会有warning错误提示：）
            while(($file = readdir($handle)) !== false) {
                if($file != ".." && $file != ".") { //排除根目录；
                    if(is_dir($dir."/".$file)) { //如果是子文件夹，就进行递归
                        $files[$file] = $this->_getDirFile($dir."/".$file);
                    } else { //不然就将文件的名字存入数组；
                        $files[] = $file;
                    }
    
                }
            }
            closedir($handle);
            return $files;
        }
    }
    
    /**
     * 获取产品图片
     * @access private
     * @param string $dir
     * @return array
     */
    private function _getImage($key,$images_file){

        $aliYunOSS = new AliYunOSS();
        $data = array();
        $arr_image = $this->_getDirFile($images_file);
        
        if(isset($arr_image[$key])){

            foreach ($arr_image[$key] as $k => $v){
                $result = $aliYunOSS->uploadFile($images_file.'/'.$key.'/'.$v, 'upload/images/'.$key.'/'.$v);
                if($result['success']==1){
                    $data[] = array(
                            'name'=>$v,
                            'image'=>$result['info']['url']
                    );
                }
            }
            
        }
        
        return $data;
    }

}