huahuibao/app-ghs/controllers/CsController.php

<?php

namespace ghs\controllers;

use biz\admin\classes\AdminRoleClass;
use bizGhs\item\classes\ItemClassClass;
use bizGhs\merchant\classes\ShopClass;
use bizGhs\product\classes\ProductClass;
use bizGhs\product\models\Product;
use bizGhs\product\services\ProductService;
use common\components\stringUtil;
use common\components\util;
use Yii;

class CsController extends PublicController
{

    public $sjId = 12568, $shopId = 214;

    public function actionItem()
    {

        $shopId = 214;

        if (getenv('YII_ENV', 'local') != 'production') {
            $shopId = 36225;
            $this->shopId = 36225;
            $this->sjId = 12715;
        }

        $py = Yii::$app->request->get('py', '');
        $type = Yii::$app->request->get('type', '');
        //默认只显示上架的商品
        $status = Yii::$app->request->get('status', 1);

        //获取所有当前供货商的分类 （全部、常用）
        //根据分类组装分类下的花材信息
        $classIds = ItemClassClass::getGhsItemClassAll($this->sjId);

        $where['sjId'] = $this->sjId;
        if ($py) {
            $pyName = strtolower($py);
            $where['py'] = $pyName;
        }


        $where['shopId'] = $shopId;
        if (!empty($status)) {
            $where['status'] = $status;
        }
        $where['delStatus'] = 0;

        $itemInfoData = ProductClass::getAllByCondition($where, ['inTurn' => SORT_DESC, 'actualSold' => SORT_DESC], "*");
        $itemInfoData = ProductClass::groupProductInfo($itemInfoData);
        $data = ProductService::assembleData($classIds, $itemInfoData );

        $formatData = [];

        foreach ($data as $key => $class) {
            $name = $class['className'] ?? '';
            $formatData[] = ['name' => $name, 'price' => ''];
            if (isset($class['child']) && !empty($class['child'])) {
                foreach ($class['child'] as $cKey => $child) {
                    $cName = $child['name'] ?? '';
                    $price = $child['price'] ?? '';
                    $formatData[] = [
                        'name' => $cName,
                        'price' => $price,
                    ];
                }
            }
        }

        $phpExcelFile = Yii::getAlias("@vendor/phpoffice/phpexcel/");
        require_once($phpExcelFile . 'Classes/PHPExcel.php');

        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");


        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(4);

        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(date('n月j日')." 价格表");

        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.4);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setHeader(0.1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setFooter(0);

        $i = 1;
        $letter = [1=>['A','B'],2=>['C','D'],3=>['E','F'],4=>['G','H'],5=>['I','J'],6=>['K','L'],7=>['M','N'],
            8=>['O','P'],9=>['Q','R'],10=>['S','T'],11=>['U','V']];
        $lastCell = '';
        //最大行
        $max = 50;
        foreach ($formatData as $key => $product) {
            $currentPage = ceil($i/$max);
            $x = $i%$max == 0 ? $max : $i%$max;
            $before = $letter[$currentPage][0] ?? 'A';
            $after = $letter[$currentPage][1] ?? 'B';
            if(isset($product['price']) == false || empty($product['price'])){
                //没有价格的合并单元格
                $objPHPExcel->getActiveSheet()->mergeCells("{$before}{$x}:{$after}{$x}")->setCellValue($before . $x, $product['name']);
                //加粗
                $objPHPExcel->getActiveSheet()->getStyle($before . $x)->getFont()->setBold(true);
                //居中
                $objPHPExcel->getActiveSheet()->getStyle($before. $x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            }else{
                $objPHPExcel->getActiveSheet()->setCellValue($before. $x, $product['name'])->setCellValue($after . $x, $product['price']);
                //字体大小
                $objPHPExcel->getActiveSheet()->getStyle($before. $x)->getFont()->setSize(10);
            }
            $lastCell = $after.$max;
            $i++;
        }

        //设置边框
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.$lastCell)->applyFromArray(array('borders' => array('allborders' => array('style' => \PHPExcel_Style_Border::BORDER_THIN),)));

        $objPHPExcel->getActiveSheet()->setTitle('价格表');

        $objPHPExcel->setActiveSheetIndex(0);


        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="0922.xls"');
        header('Cache-Control: max-age=0');

        header('Cache-Control: max-age=1');

        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    //下载文件 ssh 20210922
    public function actionExcel()
    {
        $filePath = './priceTable/12715/09-22.xls';
        $file=fopen($filePath,"r");
        header("Content-Type: application/vnd.ms-excel");
        header("Accept-Ranges: bytes");
        header("Accept-Length: ".filesize($filePath));
        header("Content-Disposition: attachment; filename=2.xls");
        echo fread($file,filesize($filePath));
        fclose($file);
    }

}