PHPExcel经典汇出逻辑_phpexcel barcode-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_42433970/article/details/119421304

这段代码实现了一个用于导出商品信息到Excel的功能。它从请求参数中获取产品ID，然后从数据库中查询对应的商品内容，进行数据处理并填充到Excel表格中，包括产品标题、描述、供应商、类型等详细信息。最后，将处理后的数据保存为Excel文件，并通过阿里云上传服务存储，返回文件URL给用户。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

代码code：

public function productExportExcel()
    {
        $params = request()->param();
        $ids = $params['ids'] ?? ApiResponse::error('请选择汇出的产品');
        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->setActiveSheetIndex(0);
        $list = db('shop_product_list')->field('content')->whereIn('id', $ids)->select();

//        d($list);
        foreach ($list as $l => &$ar) {
            $ar['content'] = JD($ar['content']);
            foreach ($ar['content']['variants'] as $c => &$tent) {
                $tent['handle'] = $ar['content']['handle'];
                $tent['title'] = $ar['content']['title'];
                $tent['body_html'] = $ar['content']['body_html'];
                $tent['vendor'] = $ar['content']['vendor'];
                $tent['product_type'] = $ar['content']['product_type'];
                $tent['status'] = $ar['content']['status'];
                $tent['option1_name'] = $ar['content']['options'][0]['name'];
                $tent['option2_name'] = $ar['content']['options'][1]['name'];
                $tent['option3_name'] = $ar['content']['options'][2]['name'];
                $tent['image_src'] = $ar['content']['images'][$c]['src'] ?? '';
                $tent['img_position'] = $ar['content']['images'][$c]['position'] ?? '';
                $tent['variant_image'] = $ar['content']['image']['src'];
                $tent['tags'] = $ar['content']['tags'];
            }
        }
//        d($list);

        foreach ($list as $i => $st) {
            $new_arr[$i] = $st['content']['variants'];
        }

        foreach ($new_arr as $n => $w) {
            foreach ($w as $x => $y) {
                $new_array[] = $y;
            }
        }

        foreach ($new_array as $a => $ry) {
            $new_list_array[$a] = [
                $ry['handle'],
                $ry['title'],
                $ry['body_html'],
                $ry['vendor'],
                $ry['product_type'],
                $ry['tags'],
                $ry['status'],
                $ry['option1_name'],
                $ry['option1'],
                $ry['option2_name'],
                $ry['option2'],
                $ry['option3_name'],
                $ry['option3'],
                $ry['sku'],
                $ry['grams'],
                $ry['inventory_management'],
                $ry['inventory_quantity'],
                $ry['inventory_policy'],
                $ry['fulfillment_service'],
                $ry['price'],
                $ry['compare_at_price'],
                $ry['requires_shipping'],
                $ry['taxable'],
                $ry['barcode'],
                $ry['image_src'],
                $ry['img_position'],
                $ry['img_alt_text'] ?? '',
                $ry['gift_card'] ?? 'FALSE',
                $ry['seo_title'] ?? '',
                $ry['seo_description'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['variant_image'],
                $ry['weight_unit'],
                $ry['gift_card'] ?? '',
                $ry['gift_card'] ?? '',
                $ry['status']

            ];
        }
//        d($new_array);
//        d($new_list_array);

        $titleExcelss = [
            [
                'Handle',
                'Title',
                'Body (HTML)',
                'Vendor',
                'Type',
                'Tags',
                'Published',
                'Option1 Name',
                'Option1 Value',
                'Option2 Name',
                'Option2 Value',
                'Option3 Name',
                'Option3 Value',
                'Variant SKU',
                'Variant Grams',
                'Variant Inventory Tracker',
                'Variant Inventory Qty',
                'Variant Inventory Policy',
                'Variant Fulfillment Service',
                'Variant Price',
                'Variant Compare At Price',
                'Variant Requires Shipping',
                'Variant Taxable',
                'Variant Barcode',
                'Image Src',
                'Image Position',
                'Image Alt Text',
                'Gift Card',
                'SEO Title',
                'SEO Description',
                'Google Shopping / Google Product Category',
                'Google Shopping / Gender',
                'Google Shopping / Age Group',
                'Google Shopping / MPN',
                'Google Shopping / AdWords Grouping',
                'Google Shopping / AdWords Labels',
                'Google Shopping / Condition',
                'Google Shopping / Custom Product',
                'Google Shopping / Custom Label 0',
                'Google Shopping / Custom Label 1',
                'Google Shopping / Custom Label 2',
                'Google Shopping / Custom Label 3',
                'Google Shopping / Custom Label 4',
                'Variant Image',
                'Variant Weight Unit',
                'Variant Tax Code',
                'Cost per item',
                'Status'
            ]
        ];
        $count = count($titleExcelss[0]);
//        d($count);
        $sheet->getStyle('A1:AV1')->getFont()->setSize(12);//字体大小
        foreach ($titleExcelss as $ki => $node) {
            $k = 'A';
            foreach ($node as $one) {
                $sheet->setCellValue($k . strval($ki + 1), $one);
                #$sheet->getStyle('A1:AV1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置文字居中
                $sheet->getColumnDimension($k)->setWidth(25);#设置列的宽度
                $k++;
            }

        }
        $startRow = 1;
        foreach ($new_list_array as $ki => $node) {
            $startRow++;
            $m = 'A';
            for ($i = 0; $i < $count; $i++) {
                $sheet->setCellValue($m . $startRow, $node[$i] ?? 0);
                $m++;
            }
//            $sheet->setCellValueExplicit('B' . ($ki + 2), $node['title'], \PHPExcel_Cell_DataType::TYPE_STRING);
//            $sheet->getStyle('A1:Z' . (count($list) + 1))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置文字居中
//            $sheet->setCellValue('A' . ($ki + 2), $node['handle']);


        }
        $path = ROOT_PATH . 'goods' . rand(1, 3333333333333) . '.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($path);
        $url = uploadAliyunFile($path, 'Excle/' . str_shuffle(md5(time())) . '.xls');
        unlink($path);
        db('shop_product_excel')->insertGetId(['u_id' => $params['u_id'], 'url' => $url]);
        ApiResponse::success('汇出成功', $url);

    }