php+phpspreadsheet读取Excel数据存入mysql
发布时间：2020-05-25 03:35:22 所属栏目：PHP 来源：互联网
导读：先生成Excel模板，然后导入Excel数据到mysql，每条数据对应图片上传到阿里云1 ?php2 /**3 * Created by PhpStorm.4 * User: Administrator5 * Date: 2018/9/276 * Time: 13:577 */89 namespace siteadminmodel;1011 use PhpOfficePhpSpreadsheetSpreadsheet;1
|
先生成Excel模板，然后导入Excel数据到mysql，每条数据对应图片上传到阿里云 _validate(,'add' = = = = (((['excel_file'],'.'),1)<>'xlsx' ->addError('excel_file','请上传xlsx格式的Excel文件' = ('com/',['excel_file' = PhpOfficePhpSpreadsheetIOFactory::createReader('Xlsx' ->setReadDataOnly( = ->load(DIR_UPLOAD.[1]); = -> = ->getHighestRow(); = ->getHighestColumn(); = PhpOfficePhpSpreadsheetCellCoordinate::columnIndexFromString(); = - 1 ( <= 0 ->addError('Excel','Excel表格中没有数据' = = ( = 2; <= ; ++ = ->getCellByColumnAndRow(1,)-> (( ->addError('model_no'.,'第'..'行产品型号为空' } = ->getProductByModel( (( [] = ; = ->getCellByColumnAndRow(2,)-> (( ->addError('category_name'.,'第'..'行分类名为空' } = ->getCategoryIdByName( (( ->addError('category_name'.,'第'..'行分类名不存在' = ->getCellByColumnAndRow(3,)-> (() || !( ->addError('sort_order'.,'第'..'行排序错误' = ->getCellByColumnAndRow(4,)-> (() || !( ->addError('status'.,'第'..'行状态错误' = ->getCellByColumnAndRow(5,)-> (( ->addError('language_name'.,'第'..'行语言为空' } = ->getSysLanguageByName( (( ->addError('languages'.,'第'..'行语言不存在' = ->getCellByColumnAndRow(6,)-> (( ->addError('name'.,'第'..'行名称为空' = ->getCellByColumnAndRow(7,)-> (( ->addError('keywords'.,'第'..'行关键词为空' = ->getCellByColumnAndRow(10,)-> (!( ((, ->addError('url'.,'第'..'行网址重复出现' } [] = (-> = ( => = ->getCellByColumnAndRow(1,)-> = ->getCellByColumnAndRow(2,)-> = ->getCategoryIdByName( = ['category_id' = ->getCellByColumnAndRow(3,)-> = ->getCellByColumnAndRow(4,)-> = ->getCellByColumnAndRow(5,)-> = ->getSysLanguageByName( = ['language_id' = ->getCellByColumnAndRow(6,)-> = ->getCellByColumnAndRow(7,)-> = ->getCellByColumnAndRow(8,)-> = ->getCellByColumnAndRow(9,)-> = ->getCellByColumnAndRow(10,)-> = ->getCellByColumnAndRow(11,)-> = ->getCellByColumnAndRow(12,)-> = ->getCellByColumnAndRow(13,)-> (!([ [] = 'product_type_id'=>'', 'model_no'=>, 'sort_order'=>, 'status'=>, 'category'=>(), 'trade'=>'', 'producttradeinfo'=> []['desc'][] = 'description'=>, 'name'=>, 'abstract'=>, 'keywords'=>, 'language_name'=>, 'meta_title'=>, 'meta_keyword'=>, 'meta_description'=>, 'url_alias'=> ( => ((['desc']) && !(['desc' (['desc'] => = ->validate(['keyword' => ['url_alias'],'language_id' => ,'query' => ''],'add' (== = DIR_UPLOAD.->session->data['site_id' = .'/'.['images_file' (!() || (((,1)<>'zip' ->addError('imageZip','请上传产品图片压缩包（zip格式）' = = ->unzip(,.'/',, ( (); ( => []['image'] = ->_getImage(,.'/'.(['images_file'],'.zip' ( => ->add( } ( ->addError('add',-> = = -> = = -> = ->_getCategoryName( = 3000 = = -> ->setCellValue('A1','型号（必填）' ->setCellValue('B1','分类名（必填）' ->setCellValue('C1','排序（必填，请输入数字）' ->setCellValue('D1','状态（必填，1=上架|0=下架）' ->setCellValue('E1','语言（必填）' ->setCellValue('F1','名称（必填）' ->setCellValue('G1','关键词（必填）' ->setCellValue('H1','简要描述' ->setCellValue('I1','详细描述' ->setCellValue('J1','网址' ->setCellValue('K1','SEO标题' ->setCellValue('L1','SEO关键词' ->setCellValue('M1','SEO描述' ->setTitle('Product' ->getDefaultColumnDimension()->setWidth(30 ->getStyle('A1:M1')->getFill()->setFillType(PhpOfficePhpSpreadsheetStyleFill:: ->getStyle('A1:M1')->getFill()->getStartColor()->setARGB('FF808080' ->getStyle('A1:M1')->getFont()->getColor()->setARGB(PhpOfficePhpSpreadsheetStyleColor:: (=2;<=;++ = ->getDataValidation('B'. -> (PhpOfficePhpSpreadsheetCellDataValidation:: -> setErrorStyle(PhpOfficePhpSpreadsheetCellDataValidation:: -> setAllowBlank( -> setShowInputMessage( -> setShowErrorMessage( -> setShowDropDown( -> setErrorTitle('输入的值有误' -> setError('您输入的值不在下拉框列表内' -> setPrompt('请选择下拉框列表中的值' -> setFormula1('data!$C$3:$C$'.(()+2 (=2;<=;++ = ->getDataValidation('D'. -> (PhpOfficePhpSpreadsheetCellDataValidation:: -> setErrorStyle(PhpOfficePhpSpreadsheetCellDataValidation:: -> setAllowBlank( -> setShowInputMessage( -> setShowErrorMessage( -> setShowDropDown( -> setErrorTitle('输入的值有误' -> setError('您输入的值不在下拉框列表内' -> setPrompt('请选择下拉框列表中的值' -> setFormula1('data!$A$3:$A$4' (=2;<=;++ = ->getDataValidation('E'. -> (PhpOfficePhpSpreadsheetCellDataValidation:: -> setErrorStyle(PhpOfficePhpSpreadsheetCellDataValidation:: -> setAllowBlank( -> setShowInputMessage( -> setShowErrorMessage( -> setShowDropDown( -> setErrorTitle('输入的值有误' -> setError('您输入的值不在下拉框列表内' -> setPrompt('请选择下拉框列表中的值' -> setFormula1('data!$B$3:$B$'.(()+2 -> ->setActiveSheetIndex(1 = -> ->setTitle('data' ->getDefaultColumnDimension()->setWidth(25 ->mergeCells('A1:C1' = 'alignment' => 'horizontal' => PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER, 'vertical' => PhpOfficePhpSpreadsheetStyleAlignment::VERTICAL_CENTER, ], ->getStyle('A1')->applyFromArray( ->getRowDimension('1')->setRowHeight(35 ->getStyle('A1')->getFill()->setFillType(PhpOfficePhpSpreadsheetStyleFill:: ->getStyle('A1')->getFill()->getStartColor()->setARGB('8B0000' ->getStyle('A1')->getFont()->getColor()->setARGB(PhpOfficePhpSpreadsheetStyleColor:: ->setCellValue('A1','公共数据（请不要修改）' ->getStyle('A2:C2')->getFill()->setFillType(PhpOfficePhpSpreadsheetStyleFill:: ->getStyle('A2:C2')->getFill()->getStartColor()->setARGB('FF808080' ->getStyle('A2:C2')->getFont()->getColor()->setARGB(PhpOfficePhpSpreadsheetStyleColor:: ->setCellValue('A2','状态（1=上架|0=下架）' ->setCellValue('A3','0' ->setCellValue('A4','1' ->setCellValue('B2','语言' ( => ->setCellValue('B'.(+3),['name' ->setCellValue('C2','分类名' ( => ->setCellValue('C'.(+3), ->setSheetState('hidden' ->setActiveSheetIndex(0 = Xlsx( = ->session->data['site_id'].'.xlsx' = DIR_UPLOAD.'excel_template/'. ->save( -> ( _validate(, = '' (!(['excel_file']) || (['excel_file' ->addError('excel_file','没有上传产品Excel文件' (!(['images_file']) || (['images_file' ->addError('images_file','没有上传产品图片文件' ->hasErrors() ? : _getCategoryName( (!( ( => (!(['children' ->_getCategoryName(['children' } [] = ['name' } = _getDirFile( = (@ = ()) { (( = ()) !== ( != ".." && != ".") { ((."/".)) { [] = ->_getDirFile(."/". } { [] = ( _getImage(, = = = ->_getDirFile( (([ ([] => = ->uploadFile(.'/'..'/'.,'upload/images/'..'/'. (['success']==1 [] = 'name'=>, 'image'=>['info']['url' }（编辑：安卓应用网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |