[开发测试]php excel导出

 public function exportOrderExcel(int $bom_id, array $data)
    {

        // 二次处理数据
        // 取出 规格 工艺 价钱
        $bomGoods = $this->bomGoodsModel->where('bom_id', $bom_id)->select();
        if (!$bomGoods) $this->error();
        $bomGoods = Collection($bomGoods)->toArray();
        $cateName = $this->bomService->getCateName();
        $total = 0; // 总计
        foreach ($data as $key => $value) {
            foreach ($value['goods'] as $k => $val) {
                $data[$key]['goods'][$k]['header_no'] = substr($val['no'], 0, 2); // 设计编号
                $data[$key]['goods'][$k]['cateColor'] = array_key_exists($val['catecolor_id'], $cateName['colorName']) ? $cateName['colorName'][$val['catecolor_id']] : '未设置'; // 产品颜色
                foreach ($bomGoods as $bg) { // 归类 规格消息
                    if ($val['g_id'] == $bg['goods_id']) {
                        if (empty($bg['spec']) || empty($bg['craft']) || empty($bg['price'])) $this->error(); //存在商品未设置规格消息
                        $data[$key]['goods'][$k]['specification'] = array_key_exists($bg['spec'], ['specName']) ? $cateName['specName'][$bg['spec']] : '未设置'; //设计规格
                        $data[$key]['goods'][$k]['specCraft'] = array_key_exists($bg['craft'], $cateName['craftName']) ? $cateName['craftName'][$bg['craft']] : '未设置'; //工艺要求
                        $data[$key]['goods'][$k]['specPrice'] = $bg['price']; //单价(元/㎡)
                        $data[$key]['goods'][$k]['subtotal'] = is_numeric($bg['usable_area']) ? ceil($bg['usable_area'] * $bg['price']) : 0; //小计
                        $total += $data[$key]['goods'][$k]['subtotal'];
                    }
                }
                unset($data[$key]['goods'][$k]['catecolor_id'], $data[$key]['goods'][$k]['g_id'], $data[$key]['goods'][$k]['cate_id'], $data[$key]['goods'][$k]['id'], $data[$key]['goods'][$k]['pid']);
            }

        }


        取项目信 息
        $bomInfo = $this->bomModel->field(['name', 'project_address', 'project_schedule'])->where('id', $bom_id)->find();
        if (!$bomInfo) $this->error();
        $bomInfo = $bomInfo->toArray(); // 清单项目信息


//        开始编辑表格
        $objPHPExcel = new \PHPExcel();

        // 设置文档的相关信息
        $objPHPExcel->getProperties()->setCreator("楼主666")/*设置作者*/
        ->setLastModifiedBy("选材")/*最后修改*/
        ->setTitle("选材")/*题目*/
        ->setSubject("选材")/*主题*/
        ->setDescription("选材")/*描述*/
        ->setKeywords("选材")/*关键词*/
        ->setCategory("选材");/*类别*/
        $objPHPExcel->getDefaultStyle()->getFont()->setName('等线');//字体

        // 合并单元格
        $objPHPExcel = $this->mergeCells($objPHPExcel, ['A1:I1', 'A2:A4', 'C2:I2', 'C3:I3', 'C4:I4', 'E5:H5']);

        // 设置列表宽
        $objPHPExcel = $this->setWidth($objPHPExcel, [
            ['line' => 'A', 'width' => 10], ['line' => 'B', 'width' => 20], ['line' => 'C', 'width' => 10], ['line' => 'D', 'width' => 10], ['line' => 'E', 'width' => 10],
            ['line' => 'F', 'width' => 10], ['line' => 'G', 'width' => 10], ['line' => 'H', 'width' => 10], ['line' => 'I', 'width' => 10], ['line' => 'J', 'width' => 10],
            ['line' => 'K', 'width' => 10], ['line' => 'L', 'width' => 10],
        ]);

        //设置表格开始部分内容
        $objPHPExcel = $this->setValue($objPHPExcel, [
            ['key' => 'A1', 'value' => '物料清单'], ['key' => 'A2', 'value' => '项目信息'], ['key' => 'B2', 'value' => '项目名称'], ['key' => 'B3', 'value' => '项目地点'], ['key' => 'B4', 'value' => '清单时间'],
            ['key' => 'A5', 'value' => '序号'], ['key' => 'B5', 'value' => '产品图片'], ['key' => 'C5', 'value' => '产品名称'], ['key' => 'D5', 'value' => '使用空间'], ['key' => 'E5', 'value' => '基本信息'],
            ['key' => 'I5', 'value' => '备注'], ['key' => 'C2', 'value' => $bomInfo['name']], ['key' => 'C3', 'value' => $bomInfo['project_address']], ['key' => 'C4', 'value' => $bomInfo['project_schedule']]
        ]);
        /*表头数据填充*/

        //设置内容文字大小
        $objPHPExcel = $this->setContentSize($objPHPExcel, [
            ['key' => 'A1', 'value' => 15], ['key' => 'A2', 'value' => 10], ['key' => 'B2', 'value' => 10], ['key' => 'B3', 'value' => 10], ['key' => 'B4', 'value' => 10], ['key' => 'A5', 'value' => 12],
            ['key' => 'C2', 'value' => 12], ['key' => 'C3', 'value' => 12], ['key' => 'C4', 'value' => 12],
        ]);


        // 第一行背景颜色
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('969696');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true); //标题 物料清单 加粗
        // 第二行背景颜色
        $objPHPExcel->getActiveSheet()->getStyle('A2:I4')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('C0C0C0');

//     设置边框线
        $style_array = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN
                )
            ));


        /*设置表相关的信息*/
        $objPHPExcel->getActiveSheet()->setTitle('选材助手'); //活动表的名称
        $objPHPExcel->setActiveSheetIndex(0);//设置第一张表为活动表
        //纸张方向和大小 为A4横向
        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);


        // 设置清单内容
        $key = 1;
        $tempCateKey = 6; //行数
        $rowNum = 5;
        /**
         * 先循环分类名- 合并赋值，计算行数 针对行数赋值
         * 再 循环商品
         */
        foreach ($data as $v) {

            // 分类名（设置值、合并）
            $objPHPExcel->getActiveSheet()->mergeCells('A' . $tempCateKey . ':I' . $tempCateKey)->setCellValue('A' . $tempCateKey, $v['cate_name']);
            $objPHPExcel->getActiveSheet()->getStyle('A' . $tempCateKey)->getFont()->setBold(true);//加粗
            $objPHPExcel = $this->setContentSize($objPHPExcel, [['key' => 'A' . $tempCateKey, 'value' => 12]]);//设置字体大小
            foreach ($v['goods'] as $item) {// epd($item);
                $itemNeedMergeCellKeyStart = $tempCateKey + 1;// 记录每商品开始行数
                $itemNeedMergeCellKeyEnd = $tempCateKey + 7;//记录每商品结束行数

                $objPHPExcel->getActiveSheet()
                    // 分类下具体的项进行合并
                    ->mergeCells('A' . $itemNeedMergeCellKeyStart . ':A' . $itemNeedMergeCellKeyEnd)
                    ->mergeCells('B' . $itemNeedMergeCellKeyStart . ':B' . $itemNeedMergeCellKeyEnd)
                    ->mergeCells('C' . $itemNeedMergeCellKeyStart . ':C' . $itemNeedMergeCellKeyEnd)
                    ->mergeCells('D' . $itemNeedMergeCellKeyStart . ':D' . $itemNeedMergeCellKeyEnd)
                    ->mergeCells('I' . $itemNeedMergeCellKeyStart . ':I' . $itemNeedMergeCellKeyEnd)
                    // 分类下具体的项进行赋值
                    ->setCellValue('A' . $itemNeedMergeCellKeyStart, $key)
                    //->setCellValue('B' . $itemNeedMergeCellKeyStart, $item['img'])
                    ->setCellValue('C' . $itemNeedMergeCellKeyStart, $item['g_name']); // 产品名称
                $objPHPExcel->getActiveSheet()->getStyle('C' . $itemNeedMergeCellKeyStart)->getFont()->setSize(8); // 商品标题
                $objPHPExcel->getActiveSheet()->setCellValue('D' . $itemNeedMergeCellKeyStart, $item['used_space']) // 使用空间
                // 设置属性名

                //设置固定属性名
                ->setCellValue('E' . ($itemNeedMergeCellKeyStart), '设置编号')->setCellValue('E' . ($itemNeedMergeCellKeyStart + 1), '设计类别')->setCellValue('E' . ($itemNeedMergeCellKeyStart + 2), '设计规格')->setCellValue('E' . ($itemNeedMergeCellKeyStart + 3), '产品颜色')->setCellValue('E' . ($itemNeedMergeCellKeyStart + 4), '防火等级')->setCellValue('E' . ($itemNeedMergeCellKeyStart + 5), '工艺要求')->setCellValue('E' . ($itemNeedMergeCellKeyStart + 6), '原产地')
                    ->setCellValue('G' . ($itemNeedMergeCellKeyStart), '供应厂商')->setCellValue('G' . ($itemNeedMergeCellKeyStart + 1), '产品编号')->setCellValue('G' . ($itemNeedMergeCellKeyStart + 2), '产品规格')->setCellValue('G' . ($itemNeedMergeCellKeyStart + 3), '供货周期')->setCellValue('G' . ($itemNeedMergeCellKeyStart + 4), '单价(元/㎡)')->setCellValue('G' . ($itemNeedMergeCellKeyStart + 5), '面积(㎡)')->setCellValue('G' . ($itemNeedMergeCellKeyStart + 6), '小计')
                    // 设置 属性值
                    ->setCellValue('F' . ($itemNeedMergeCellKeyStart), $item['header_no'])//设计编号
                    ->setCellValue('F' . ($itemNeedMergeCellKeyStart + 1), $item['c_name'])// 设计类别
                    ->setCellValue('F' . ($itemNeedMergeCellKeyStart + 2), $item['specification'])// 设计规格
                    ->setCellValue('F' . ($itemNeedMergeCellKeyStart + 3), $item['cateColor'])// 产品颜色
                    ->setCellValue('F' . ($itemNeedMergeCellKeyStart + 4), $item['fire_rating'])// 防火等级
                    ->setCellValue('F' . ($itemNeedMergeCellKeyStart + 5), $item['specCraft'])// 工艺要求
                    ->setCellValue('F' . ($itemNeedMergeCellKeyStart + 6), $item['ship_address'])// 原产地

                    ->setCellValue('H' . ($itemNeedMergeCellKeyStart), $item['supplier'])// 工艺厂商
                    ->setCellValue('H' . ($itemNeedMergeCellKeyStart + 1), $item['no'])// 产品编号
                    ->setCellValue('H' . ($itemNeedMergeCellKeyStart + 2), $item['size'])// 产品规格
                    ->setCellValue('H' . ($itemNeedMergeCellKeyStart + 3), $item['period'])// 供货周期
                    ->setCellValue('H' . ($itemNeedMergeCellKeyStart + 4), $item['specPrice'])// 单价(元/㎡)
                    ->setCellValue('H' . ($itemNeedMergeCellKeyStart + 5), $item['usable_area'])// 面积
                    ->setCellValue('H' . ($itemNeedMergeCellKeyStart + 6), $item['subtotal'])//小计

                    ->setCellValue('I' . ($itemNeedMergeCellKeyStart), $item['remark'])//备注
                ;

                $objPHPExcel = $this->setContentSize($objPHPExcel, [
                    ['key' => 'E' . ($itemNeedMergeCellKeyStart), 'value' => 8], ['key' => 'E' . ($itemNeedMergeCellKeyStart + 1), 'value' => 8], ['key' => 'E' . ($itemNeedMergeCellKeyStart + 2), 'value' => 8], ['key' => 'E' . ($itemNeedMergeCellKeyStart + 3), 'value' => 8], ['key' => 'E' . ($itemNeedMergeCellKeyStart + 4), 'value' => 8], ['key' => 'E' . ($itemNeedMergeCellKeyStart + 5), 'value' => 8], ['key' => 'E' . ($itemNeedMergeCellKeyStart + 6), 'value' => 8],
                    ['key' => 'G' . ($itemNeedMergeCellKeyStart), 'value' => 8], ['key' => 'G' . ($itemNeedMergeCellKeyStart + 1), 'value' => 8], ['key' => 'G' . ($itemNeedMergeCellKeyStart + 2), 'value' => 8], ['key' => 'G' . ($itemNeedMergeCellKeyStart + 3), 'value' => 8], ['key' => 'G' . ($itemNeedMergeCellKeyStart + 4), 'value' => 8], ['key' => 'G', ($itemNeedMergeCellKeyStart + 5), 'value' => 8], ['key' => 'G' . ($itemNeedMergeCellKeyStart + 6), 'value' => 8],
                    ['key' => 'F' . ($itemNeedMergeCellKeyStart), 'value' => 8], ['key' => 'F' . ($itemNeedMergeCellKeyStart + 1), 'value' => 8], ['key' => 'F' . ($itemNeedMergeCellKeyStart + 2), 'value' => 8], ['key' => 'F' . ($itemNeedMergeCellKeyStart + 3), 'value' => 8], ['key' => 'F' . ($itemNeedMergeCellKeyStart + 4), 'value' => 8], ['key' => 'F' . ($itemNeedMergeCellKeyStart + 5), 'value' => 8], ['key' => 'F' . ($itemNeedMergeCellKeyStart + 6), 'value' => 8],
                    ['key' => 'H' . ($itemNeedMergeCellKeyStart), 'value' => 8], ['key' => 'H' . ($itemNeedMergeCellKeyStart + 1), 'value' => 8], ['key' => 'H' . ($itemNeedMergeCellKeyStart + 2), 'value' => 8], ['key' => 'H' . ($itemNeedMergeCellKeyStart + 3), 'value' => 8], ['key' => 'H' . ($itemNeedMergeCellKeyStart + 4), 'value' => 8], ['key' => 'H' . ($itemNeedMergeCellKeyStart + 5), 'value' => 8], ['key' => 'H' . ($itemNeedMergeCellKeyStart + 6), 'value' => 8],

                ]);

                //封面图片 操作
                if (!empty($item['cover_image'])) {

                    $imgPath = $_SERVER['DOCUMENT_ROOT'] . $item['cover_image'];

                    $objDrawing = new \PHPExcel_Worksheet_Drawing();
                    $objDrawing->setResizeProportional(false); // 此处顺序不可调，因为导出默认是按原图像缩放的，设置成false才可以设置成可控制的宽度，要注意哦！

                    $objDrawing->setPath($imgPath);

                    $objDrawing->setWidthAndHeight(159, 100);
                    $objDrawing->setOffsetY(15);
                    $objDrawing->setOffsetX(1);
//

                    $objDrawing->setCoordinates('B' . $itemNeedMergeCellKeyStart);
                    $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
                }

                // 设置背景色
                $objPHPExcel->getActiveSheet()->getStyle('E' . $itemNeedMergeCellKeyStart . ':E' . $itemNeedMergeCellKeyEnd)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('C0C0C0');
                $objPHPExcel->getActiveSheet()->getStyle('G' . $itemNeedMergeCellKeyStart . ':G' . $itemNeedMergeCellKeyEnd)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('C0C0C0');


                // 数据序号递增
                $key += 1;
                // 每个分类递增
                $tempCateKey = $tempCateKey + 7; // 每循环一个商品 行数累加7行(追加)
                // 累加数据行数
                $rowNum += 7;// 商品中累加行数
            }

            $tempCateKey += 1;
            // 累加数据行数
            $rowNum += 1;
        }

        // 最后一行 设计总计
        $objPHPExcel->getActiveSheet()->setCellValue('G' . ($rowNum + 1), '总计');
        $objPHPExcel = $this->setContentSize($objPHPExcel, [['key' => 'G' . ($rowNum + 1), 'value' => 8]]);//设置字体大小
        // 合并最后一行单元格
        $objPHPExcel = $this->mergeCells($objPHPExcel, ['A' . ($rowNum + 1) . ':' . 'F' . ($rowNum + 1)]);
        //计算总计
        $objPHPExcel->getActiveSheet()->setCellValue('H' . ($rowNum + 1), $total);//备注
        $objPHPExcel = $this->setContentSize($objPHPExcel, [['key' => 'H' . ($rowNum + 1), 'value' => 8]]);//设置字体大小
        // 设置边框线
        $objPHPExcel->getActiveSheet()->getStyle('A1:I' . ($rowNum + 1))
            ->applyFromArray($style_array);
        $objPHPExcel->getActiveSheet()->getStyle('A1:I' . ($rowNum + 1))->getAlignment()
            ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER) // 水平居中
            ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 垂直居中

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

        $fileName = ROOT_PATH . 'public' . DIRECTORY_SEPARATOR . 'excel' . DIRECTORY_SEPARATOR . 'inventory.xlsx';

        $objWriter->save($fileName); //保存文件


        $xlsx = $this->baseUrl . DIRECTORY_SEPARATOR . 'excel' . DIRECTORY_SEPARATOR . 'inventory.xlsx';

        $this->success(__('successful'), [$xlsx]);

    }




/**
     * 设置单元格内容 字体大小
     * @param $objPHPExcel
     * @param array $data
     * @return mixed
     */
    protected function setContentSize($objPHPExcel, array $data)
    {

        foreach ($data as $v) {
            $objPHPExcel->getActiveSheet()->getStyle($v['key'])->getFont()->setSize($v['value']);
        }

        return $objPHPExcel;
    }


    /**
     * 设置单元格内容
     * @param $objPHPExcel
     * @param array $data
     * @return mixed
     */
    protected function setValue($objPHPExcel, array $data)
    {

        foreach ($data as $v) {
            $objPHPExcel->setActiveSheetIndex()->setCellValue($v['key'], $v['value']);
        }

        return $objPHPExcel;
    }


    /**
     * 合并单元格
     * @param $objPHPExcel
     * @param array $data
     * @return mixed
     */
    protected function mergeCells($objPHPExcel, array $data)
    {

        foreach ($data as $v) {
            $objPHPExcel->getActiveSheet()->mergeCells($v);
        }

        return $objPHPExcel;
    }

    /**
     * 设置列宽
     * @param $objPHPExcel
     * @param array $data
     */
    protected function setWidth($objPHPExcel, array $data)
    {
        foreach ($data as $value) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($value['line'])->setWidth($value['width']);
        }

        return $objPHPExcel;
    }