";
//        print_r($where);
//        print_r($data);
//        die;
        foreach ($data as $key => $class) {
            $name = $class['className'] ?? '';
            $formatData[] = ['name' => $name, 'price' => '', 'type' => 'className'];
            if (isset($class['child']) && !empty($class['child'])) {
                foreach ($class['child'] as $cKey => $child) {
                    $cName = $child['name'] ?? '';
                    $stock = $child['stock'] ?? 0;
                    $stock = floatval($stock);
                    $formatData[] = [
                        'name' => $cName,
                        'price' => $stock,
                        'type' => 'item',
                    ];
                }
            }
        }

        $map = CustomClass::$nickNameMap;
        $levelName = $map[$level] ?? '';

        $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()->getHeaderFooter()->setOddHeader(date('n月j日') . " " . $levelName . "客户");
        //$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(date('n月j日') . " 编号 " . $level);
        $topLevelName = $levelName;
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(date('n月j日') . " " . $topLevelName);

        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.7);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.5);
        $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';

            //行高14
            $objPHPExcel->getActiveSheet()->getRowDimension($x)->setRowHeight(15);

            //设置表格宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension($before)->setWidth(11);
            $objPHPExcel->getActiveSheet()->getColumnDimension($after)->setWidth(5);

            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()->setSize(9)->setBold(true);
                $objPHPExcel->getActiveSheet()->getStyle($before . $x)->getFont()->setName('微软雅黑');
                //居中
                $objPHPExcel->getActiveSheet()->getStyle($before . $x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                //垂直居中
                $objPHPExcel->getActiveSheet()->getStyle($before . $x)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            } else if (isset($product['type']) && $product['type'] == 'reach') {
                $objPHPExcel->getActiveSheet()->setCellValue($before . $x, $product['name'])->setCellValue($after . $x, $product['price']);
                //字体大小 加粗
                $objPHPExcel->getActiveSheet()->getStyle($before . $x)->getFont()->setSize(9)->setBold(true);
                //红色
                $objPHPExcel->getActiveSheet()->getStyle($before . $x)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
                //垂直居中
                $objPHPExcel->getActiveSheet()->getStyle($before . $x)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                //字体大小 加粗
                $objPHPExcel->getActiveSheet()->getStyle($after . $x)->getFont()->setSize(11)->setBold(true);
                //红色
                $objPHPExcel->getActiveSheet()->getStyle($after . $x)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
            } else {
                $objPHPExcel->getActiveSheet()->setCellValue($before . $x, $product['name'])->setCellValue($after . $x, $product['price']);
                //字体大小
                $objPHPExcel->getActiveSheet()->getStyle($before . $x)->getFont()->setSize(9);
                //垂直居中
                $objPHPExcel->getActiveSheet()->getStyle($before . $x)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            }
            $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);

        $dir = './priceTable/' . $sjId;
        if (file_exists($dir) == false) {
            mkdir($dir, 0777, true);
        }
        $date = date("m-d") . "-剩余库存";

        $file = $date . '.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        if (file_exists($dir . '/' . $file)) {
            unlink($dir . '/' . $file);
        }
        $objWriter->save($dir . '/' . $file);

        return ['file' => '/priceTable/' . $sjId . '/' . $file, 'shortFile' => $file];
    }

    //生成条形码 lqh 20211227
    public static function generateBarcodeTable($sjId, $shopId, $mainId = 0)
    {
        //中央ID
        $classIds = ItemClassClass::getGhsItemClassAll($mainId);
//        $where['sjId'] = $sjId;
//        $where['shopId'] = $shopId;
        $where['mainId'] = $mainId;
        $where['delStatus'] = 0;
        $product = ProductClass::getAllByCondition($where, null, "*");
        $data = [];
        foreach ($product as $v) {
            $tmp = [];
            $tmp['id'] = $v['id'];
            $tmp['name'] = $v['name'];
            $data[$v['classId']][] = $tmp;
        }
        $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");

        //一行五列
        $column_start = "A";
        $column_end = "F";
        $title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q',
            'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH'];
        $i = 1;
        $page = 0;
        $objPHPExcel->setActiveSheetIndex($page);
        foreach ($classIds as $k => $v) {
            $productData = $data[$v['id']] ?? [];
            if (empty($productData)) {
                continue;
            }
            $cate_name = $v['name'] ?? '';
            $objPHPExcel->getActiveSheet()->mergeCells("{$column_start}{$i}:{$column_end}{$i}")->setCellValue("A" . $i, $cate_name);
            //居中
            $objPHPExcel->getActiveSheet()->getStyle("{$column_start}{$i}:{$column_end}{$i}")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //加粗
            $objPHPExcel->getActiveSheet()->getStyle("{$column_start}{$i}:{$column_end}{$i}")->getFont()->setSize(18)->setBold(true);
            $i++;

            if ($productData) {
                $newChunk = array_chunk($productData, 4); //6个一组
                foreach ($newChunk as $c) {

                    foreach ($c as $ck => $cv) {
                        $objPHPExcel->getActiveSheet()->setCellValue($title_array[$ck] . $i, $cv['name']);
                        //居中
                        $objPHPExcel->getActiveSheet()->getStyle("$title_array[$ck]$i")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    }
                    $i++;
                    foreach ($c as $ck => $cv) {
                        $objDrawing = new \PHPExcel_Worksheet_Drawing;
                        $p = dirUtil::getBarcodeDir() . $cv['id'] . ".jpg";
                        if (!file_exists($p)) {
                            //生成条形码
                            barcodeUtil::generatorProductBarcode($cv['id']);
                        }
                        $objDrawing->setPath($p);
                        //$objDrawing->setHeight(130);//设置图片高度
                        $objDrawing->setOffsetX(28);//设置图片水平偏移
                        //$objDrawing->setOffsetY(1);//设置图片垂直偏移
                        $objPHPExcel->getActiveSheet()->getColumnDimension($title_array[$ck])->setWidth(22);
                        $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(66);
                        $objPHPExcel->getActiveSheet()->getStyle("{$title_array[$ck] }{$i}:{$title_array[$ck] }{$i}")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                        $objDrawing->setCoordinates($title_array[$ck] . ($i));
                        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
                        //居中
                        $objPHPExcel->getActiveSheet()->getStyle("$title_array[$ck]$i")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                    }
                    $i++;
                    if ($i >= 18) {
                        $i = 1;
                        $page++;
                        $objPHPExcel->createSheet();
                        $objPHPExcel->setActiveSheetIndex($page);
                    }
                }
            }
        }
        $dir = './barcodeTable/' . $shopId;
        if (file_exists($dir) == false) {
            mkdir($dir, 0777, true);
        }
        $date = "条形码-" . date("m-d");

        $file = $date . '.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        if (file_exists($dir . '/' . $file)) {
            unlink($dir . '/' . $file);
        }
        $objWriter->save($dir . '/' . $file);

        return ['file' => '/barcodeTable/' . $shopId . '/' . $file, 'shortFile' => $file];
    }

}