/**
* 商品导入
* @return void
*/
public function import()
{
$file = $this->request->file('file');
if (!$file) return app('json')->fail('请上传EXCEL文件');
$file = is_array($file) ? $file[0] : $file;
validate(["file|文件" => ['fileExt' => 'xlsx,xls',]])->check(['file' => $file]);
$upload = UploadService::create(1);
$ret = $upload->to('excel')->move('file');
if ($ret === false) return app('json')->fail($upload->getError());
$res = $upload->getUploadInfo();
$path = rtrim(public_path(),'/').$res['dir'];
$field_list = ['store_name', 'cate_id', 'sh_cate_id', 'brand_id', 'unit_name',
'keyword', 'store_info',
'price', 'cost','one_price', 'two_price', 'stock', 'product_num', 'volume', 'weight', 'sort', 'slider_image', 'content', 'content_text'];
//'spec_type', 'attr_duo', 'attr_value',
// $file_path = '/www/wwwroot/www.lzt666.com/public/1.xlsx'; //包含图片的Excel文件
$file_path = $path; //包含图片的Excel文件
//导入数据库
$this->readexcel1($field_list, $file_path);
return app('json')->success('导入成功');
}
// 获取excel表格的图片
public function readexcel1($field_list, $file)
{
$merId = $this->request->merId();
$imageFilePath = '/uploads/imgsss'; //图片本地存储的路径
$path = public_path($imageFilePath);
if (!is_dir($path)) { //如果目录不存在则递归创建
mkdir($path, 0777, true);
}
try {
if (!$file) return false;
$pathInfo = pathinfo($file, PATHINFO_EXTENSION);
if (!$pathInfo || $pathInfo != "xlsx") throw new ValidateException('必须上传xlsx格式文件');
$objRead = IOFactory::createReader('Xlsx');
$objSpreadsheet = $objRead->load($file);
$objWorksheet = $objSpreadsheet->getSheet(0);
$data = $objWorksheet->toArray();
Db::startTrans();
try {
$all_data = [];
//获取表格里的图片 这个方法只要图上超过2M就上传不了
/*foreach ($objWorksheet->getDrawingCollection() as $drawing) {
list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());//获取图片所在的列和行
$imageFileName = $drawing->getCoordinates() . mt_rand(1000, 9999);
switch ($drawing->getExtension()) {
case 'jpg':
case 'jpeg':
$imageFileName .= '.jpg';
$source = imagecreatefromjpeg($drawing->getPath());
imagejpeg($source, $path . $imageFileName);
break;
case 'gif':
$imageFileName .= '.gif';
$source = imagecreatefromgif($drawing->getPath());
imagegif($source, $path . $imageFileName);
break;
case 'png':
$imageFileName .= '.png';
$source = imagecreatefrompng($drawing->getPath());
imagepng($source, $path . $imageFileName);
break;
}
$startColumn = $this->ABC2decimal($startColumn);//由于图片所在位置的列号为字母,转化为数字
$data[$startRow - 1][$startColumn] .= 'https://www.lzt666.com/uploads/imgss/' . $imageFileName . ',';//把图片插入到数组中
}*/
foreach ($objSpreadsheet->getWorksheetIterator() as $worksheet) {
foreach ($worksheet->getDrawingCollection() as $drawing) {
list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());//获取图片所在行和列
$imagePath = $drawing->getPath();
$md5 = md5(uniqid()) . '.' . $drawing->getExtension();
$newImagePath = $path . $md5;
copy($imagePath, $newImagePath);
$startColumn = $this->ABC2decimal($startColumn);//由于图片所在位置的列号为字母,转化为数字
$data[$startRow - 1][$startColumn] .= 'https://www.lzt666.com/uploads/imgsss/' . $md5 . ',';//把图片插入到数组中
}
}
foreach ($data as $key => &$val) {
$val[16] = rtrim($val[16], ',');
$val[17] = rtrim($val[17], ',');
}
foreach ($data as $key => $value) {
$data_arr = [];
foreach ($field_list as $field_key => $field_value) {
$data_arr[$field_value] = trim($value[$field_key]);
}
$all_data[] = $data_arr;
}
unset($all_data[0]);
foreach ($all_data as $key => &$value) {
//商品详情拼接
$content = explode(',', $value['content']);
$content_detail = '<p>';
foreach ($content as $k => $v) {
$content_detail .= '<img src="' . $v . '"/>';
}
$content_detail .= $value['content_text'];
$content_detail .= '</p>';
$value['content'] = $content_detail;
//查找商品分类id
$value['cate_id'] = $this->getCateId($value['cate_id']);
//查找商品品牌id
$cate_id = (new StoreBrand())->where('brand_name', '=', $value['brand_id'])->value('brand_id');
$value['brand_id'] = $cate_id;
//TODO::查找商户商品分类
$value['store_product_cate'] = $this->getProductCateId($value['sh_cate_id']);
//商品轮播
//取第一张照片为商品封面
$value['image'] = '';
if ($value['slider_image']) {
$value['image'] = explode(',', $value['slider_image'])[0];
}
unset($all_data[$key]['sh_cate_id']);
}
$store_spu = [];
$store_product_content = [];
$store_product_attr_value = [];
$store_product_cate = [];
foreach ($all_data as $k => $v) {
//产品规格详情表
// if ($v['spec_type'] == 0) {
// }
//产品表
$id = Db::name('store_product')->insertGetId([
'store_name' => $v['store_name'],
'cate_id' => $v['cate_id'],
'brand_id' => $v['brand_id'],
'unit_name' => $v['unit_name'],
'keyword' => $v['keyword'],
'store_info' => $v['store_info'],
'spec_type' => 0,
'sort' => $v['sort'],
'slider_image' => $v['slider_image'],
'price' => $v['price'],
'cost' => $v['cost'],
'one_price' => $v['one_price'],
'two_price' => $v['two_price'],
'stock' => $v['stock'],
'image' => $v['image'],
'mer_id' => $merId,
'is_show' => 0,
'status' => 1,
'temp_id' => 2,
'delivery_free' => 0,
'delivery_way' => 2,
]);
//产品搜索信息表
$store_spu[] = [
'mer_id' => $merId,
'product_id' => $id,
'store_name' => $v['store_name'],
'keyword' => $v['keyword'],
'price' => $v['price'],
'rank' => $v['sort'],
'image' => $v['image'],
];
$store_product_attr_value[] = [
'product_id' => $id,
'stock' => $v['stock'],
'image' => $v['image'],
'bar_code' => $v['product_num'],
'cost'=>$v['cost'],
'one_price' => $v['one_price'],
'two_price' => $v['two_price'],
'price' => $v['price'],
'volume' => $v['volume'],
'weight' => $v['weight'],
'unique' => substr(md5('' . $id), 12, 11) . 0,
];
//产品详情表
$store_product_content[] = [
'product_id' => $id,
'content' => $v['content'],
];
foreach ($v['store_product_cate'] as $key => $item) {
//产品平台分类表
$store_product_cate[] = [
'product_id' => $id,
'mer_cate_id' => $item,
'mer_id' => $merId
];
}
}
(new Spu())->saveAll($store_spu);
(new ProductContent())->saveAll($store_product_content);
(new ProductAttrValue())->saveAll($store_product_attr_value);
(new ProductCate())->saveAll($store_product_cate);
Db::commit();
$result = true;
} catch (Exception $exception) {
Db::rollback();
$result = '导入失败,信息:' . $exception->getMessage();
}
} catch (\Exception $e) {
throw $e;
}
return $result;
}
function ABC2decimal($abc)
{
$ten = 0;
$len = strlen($abc);
for ($i = 1; $i <= $len; $i++) {
$char = substr($abc, 0 - $i, 1);//反向获取单个字符
$int = ord($char);
$ten += ($int - 65) * pow(26, $i - 1);
}
return $ten;
}
xlsx文档上传带图片
于 2023-09-14 15:40:17 首次发布