phpexcel 的简单用法

     $data = DB::table('goods')->where([['sale','>',0]])->get();
            $data = json_decode($data,true);
            // 文件名和文件类型
            $fileName = "goods";
            $fileType = "xlsx";

            $obj = new \PHPExcel();

            // 以下内容是excel文件的信息描述信息
            $obj->getProperties()->setCreator(''); //设置创建者
            $obj->getProperties()->setLastModifiedBy(''); //设置修改者
            $obj->getProperties()->setTitle(''); //设置标题
            $obj->getProperties()->setSubject(''); //设置主题
            $obj->getProperties()->setDescription(''); //设置描述
            $obj->getProperties()->setKeywords('');//设置关键词
            $obj->getProperties()->setCategory('');//设置类型

            // 设置当前sheet
            $obj->setActiveSheetIndex(0);

            // 设置当前sheet的名称
            $obj->getActiveSheet()->setTitle('student');

            // 列标
            $list = ['A', 'B', 'C','D', 'E', 'F','G', 'H', 'L','J', 'K', 'M','N', 'O', 'P','Q', 'R', 'S','T', 'U', 'V','W'];

            // 填充第一行数据
            $obj->getActiveSheet()
                ->setCellValue($list[0] . '1', '常规利润率（（指导价-协议价）/协议价 *100）（百分比）')
                ->setCellValue($list[1] . '1', '协议价（单位：分）')
                ->setCellValue($list[2] . '1', '商家id')
                ->setCellValue($list[3] . '1', '三方商品id（可忽略）')
                ->setCellValue($list[4] . '1', '来源1自营2京东6阿里巴巴7天猫来源')
                ->setCellValue($list[5] . '1', '三方分类名称')
                ->setCellValue($list[6] . '1', '三方品牌名称')
                ->setCellValue($list[7] . '1', '商品名称')
                ->setCellValue($list[8] . '1', '市场价')
                ->setCellValue($list[9] . '1', '指导价')
                ->setCellValue($list[10] . '1', '销售价')
                ->setCellValue($list[11] . '1', '成本价')
                ->setCellValue($list[12] . '1', '主图')
                ->setCellValue($list[13] . '1', '库存')
                ->setCellValue($list[14] . '1', '总库存')
                ->setCellValue($list[15] . '1', '状态1上架0下架')
                ->setCellValue($list[16] . '1', '销量')
                ->setCellValue($list[17] . '1', '1包邮0不包邮')
                ->setCellValue($list[18] . '1', '营销利润率')
                ->setCellValue($list[19] . '1', '实际月销量')
                ->setCellValue($list[20] . '1', '产品id')
                ->setCellValue($list[21] . '1', '营销价');

            // 填充第n(n>=2, n∈N*)行数据
            $length = count($data);
            $cell_counter = 1;
            for ($i = 0; $i < $length; $i++) {
                $obj->getActiveSheet()->setCellValue($list[0] . ($i + 2), $data[$i]['rate'], \PHPExcel_Cell_DataType::TYPE_STRING);//将其设置为文本格式
                $obj->getActiveSheet()->setCellValue($list[1] . ($i + 2), $data[$i]['agreement_price']);
                $obj->getActiveSheet()->setCellValue($list[2] . ($i + 2), $data[$i]['third_id']);
                $obj->getActiveSheet()->setCellValue($list[3] . ($i + 2), $data[$i]['shop_id']);
                $obj->getActiveSheet()->setCellValue($list[4] . ($i + 2), $data[$i]['source']);
                $obj->getActiveSheet()->setCellValue($list[5] . ($i + 2), $data[$i]['third_category_name']);
                $obj->getActiveSheet()->setCellValue($list[6] . ($i + 2), $data[$i]['third_brand_name']);
                $obj->getActiveSheet()->setCellValue($list[7] . ($i + 2), $data[$i]['title']);
                $obj->getActiveSheet()->setCellValue($list[8] . ($i + 2), $data[$i]['market_price']);
                $obj->getActiveSheet()->setCellValue($list[9] . ($i + 2), $data[$i]['guide_price']);
                $obj->getActiveSheet()->setCellValue($list[10] . ($i + 2), $data[$i]['sale_price']);
                $obj->getActiveSheet()->setCellValue($list[11] . ($i + 2), $data[$i]['cost_price']);
                $cell_counter++;
                $objDrawing[$cell_counter] = new \PHPExcel_Worksheet_MemoryDrawing();
                // 截取图片的格式，用不同的方法
                $end =[];
                $end[$cell_counter] = substr($data[$i]['cover'], -3);
                info("==============". $end[$cell_counter]."=======".$data[$i]['id']."==========");
                if ($end[$cell_counter] == 'jpg' || $end[$cell_counter] == 'peg') {
                    $img[$cell_counter] = @imagecreatefromjpeg($data[$i]['cover']);
                    $objDrawing[$cell_counter]->setImageResource($img[$cell_counter]);
                    $objDrawing[$cell_counter]->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT);//渲染方法

                    $objDrawing[$cell_counter]->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
                    // // 设置宽度高度
                    $objDrawing[$cell_counter]->setHeight(40);//照片高度
                    $objDrawing[$cell_counter]->setWidth(60); //照片宽度
                    // /*设置图片要插入的单元格*/
                    $objDrawing[$cell_counter]->setCoordinates($list[12].$cell_counter);
                    // // 图片偏移距离
                    $objDrawing[$cell_counter]->setOffsetX(8);
                    $objDrawing[$cell_counter]->setOffsetY(8);
                    $objDrawing[$cell_counter]->setWorksheet($obj->getActiveSheet());
                    // 表格高度
                    $obj->getActiveSheet()->getRowDimension($cell_counter)->setRowHeight(80);
                }
                else if ($end[$cell_counter] == 'png') {
                    $img[$cell_counter] = @imagecreatefrompng($data[$i]['cover']);
                    $objDrawing[$cell_counter]->setImageResource($img[$cell_counter]);
                    $objDrawing[$cell_counter]->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT);//渲染方法

                    $objDrawing[$cell_counter]->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
                    // // 设置宽度高度
                    $objDrawing[$cell_counter]->setHeight(40);//照片高度
                    $objDrawing[$cell_counter]->setWidth(60); //照片宽度
                    // /*设置图片要插入的单元格*/
                    $objDrawing[$cell_counter]->setCoordinates($list[12].$cell_counter);
                    // // 图片偏移距离
                    $objDrawing[$cell_counter]->setOffsetX(8);
                    $objDrawing[$cell_counter]->setOffsetY(8);
                    $objDrawing[$cell_counter]->setWorksheet($obj->getActiveSheet());
                    // 表格高度
                    $obj->getActiveSheet()->getRowDimension($cell_counter)->setRowHeight(80);
                }else if ($end[$cell_counter] == 'gif') {
                    $img[$cell_counter] = @imagecreatefromgif($data[$i]['cover']);
                    $objDrawing[$cell_counter]->setImageResource($img[$cell_counter]);
                    $objDrawing[$cell_counter]->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT);//渲染方法

                    $objDrawing[$cell_counter]->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
                    // // 设置宽度高度
                    $objDrawing[$cell_counter]->setHeight(40);//照片高度
                    $objDrawing[$cell_counter]->setWidth(60); //照片宽度
                    // /*设置图片要插入的单元格*/
                    $objDrawing[$cell_counter]->setCoordinates($list[12].$cell_counter);
                    // // 图片偏移距离
                    $objDrawing[$cell_counter]->setOffsetX(8);
                    $objDrawing[$cell_counter]->setOffsetY(8);
                    $objDrawing[$cell_counter]->setWorksheet($obj->getActiveSheet());
                    // 表格高度
                    $obj->getActiveSheet()->getRowDimension($cell_counter)->setRowHeight(80);
                }else{
                    $img[$cell_counter] = $data[$i]['cover'];
                    $ch = curl_init ();
                    curl_setopt ( $ch, CURLOPT_CUSTOMREQUEST, 'GET' );
                    curl_setopt ( $ch, CURLOPT_SSL_VERIFYPEER, false );
                    curl_setopt ( $ch, CURLOPT_URL, $img[$cell_counter] );
                    ob_start ();
                    curl_exec ( $ch );
                    $return_content = ob_get_contents ();
                    ob_end_clean ();

                    $return_code = curl_getinfo ( $ch, CURLINFO_HTTP_CODE );
                    $filename = $data[$i]['id'].'jpg';
                    file_put_contents("upload/pdf/".$filename, $return_content);

                    $img[$cell_counter] = @imagecreatefromjpeg("http://qianliyan.cn/upload/pdf/".$filename);
                    $objDrawing[$cell_counter]->setImageResource($img[$cell_counter]);
                    $objDrawing[$cell_counter]->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_DEFAULT);//渲染方法
                    $objDrawing[$cell_counter]->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
                    // // 设置宽度高度
                    $objDrawing[$cell_counter]->setHeight(40);//照片高度
                    $objDrawing[$cell_counter]->setWidth(60); //照片宽度
                    // /*设置图片要插入的单元格*/
                    $objDrawing[$cell_counter]->setCoordinates($list[12].$cell_counter);
                    // // 图片偏移距离
                    $objDrawing[$cell_counter]->setOffsetX(8);
                    $objDrawing[$cell_counter]->setOffsetY(8);
                    $objDrawing[$cell_counter]->setWorksheet($obj->getActiveSheet());
                    // 表格高度
                    $obj->getActiveSheet()->getRowDimension($cell_counter)->setRowHeight(80);
                }

                $obj->getActiveSheet()->setCellValue($list[13] . ($i + 2), $data[$i]['stock']);
                $obj->getActiveSheet()->setCellValue($list[14] . ($i + 2), $data[$i]['total_stock']);
                $obj->getActiveSheet()->setCellValue($list[15] . ($i + 2), $data[$i]['status']);
                $obj->getActiveSheet()->setCellValue($list[16] . ($i + 2), $data[$i]['sale']);
                $obj->getActiveSheet()->setCellValue($list[17] . ($i + 2), $data[$i]['is_free_shipping']);
                $obj->getActiveSheet()->setCellValue($list[18] . ($i + 2), $data[$i]['activity_rate']);
                $obj->getActiveSheet()->setCellValue($list[19] . ($i + 2), $data[$i]['real_month_sale']);
                $obj->getActiveSheet()->setCellValue($list[20] . ($i + 2), $data[$i]['goods_id']);
                $obj->getActiveSheet()->setCellValue($list[21] . ($i + 2), $data[$i]['activity_price']);
            }

            // 设置加粗和左对齐
            foreach ($list as $col) {
                // 设置第一行加粗
                $obj->getActiveSheet()->getStyle($col . '1')->getFont()->setBold(true);
                // 设置第1-n行，左对齐
                for ($i = 1; $i <= $length + 1; $i++) {
                    $obj->getActiveSheet()->getStyle($col . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                }
            }

            // 设置列宽
            $obj->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $obj->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $obj->getActiveSheet()->getColumnDimension('C')->setWidth(15);

            // 导出
            ob_clean();
            if ($fileType == 'xls') {
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="' . $fileName . '.xls');
                header('Cache-Control: max-age=1');
                $objWriter = new \PHPExcel_Writer_Excel5($obj);
                $objWriter->save('php://output');
                exit();
            } elseif ($fileType == 'xlsx') {
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx');
                header('Cache-Control: max-age=1');
                $objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
                $objWriter->save('php://output');exit();
            }