PHPexcel导出赋值

/*导出excel*/
	public function download(){
		error_reporting(E_ALL);
		date_default_timezone_set('Asia/Shanghai');
		ini_set('memory_limit','300M');
		set_time_limit(0);
		ob_end_clean();
		$type=request()->post('type');
		$tj=json_decode(urldecode(request()->post('tj')),true);
		$order=request()->post('order');
		$order=$this->outOrder($order,1);
//		if(empty($order)){
//			$order= 'time desc ';
//		}
		$where=$this->outSearch1($tj,$str);
		$objPHPExcel = new \PHPExcel();
		$objSheet = $objPHPExcel->getActiveSheet();
		/*如果是正品*/
		if($type==1){
			$objSheet->getStyle('A:M')->getFont()->setName("宋体")->setSize(11);
			$objSheet->getStyle('A1:M1')->getFont()->setBold(true);
			$objSheet->getStyle('A1:M1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$objSheet->getStyle('A1:M1')->getFont()->getColor()->setRGB('333399');
			$objSheet->getColumnDimension( 'A')->setWidth(20);
			$objSheet->getColumnDimension( 'B')->setWidth(20);
			$objSheet->getColumnDimension( 'C')->setWidth(15);
			$objSheet->getColumnDimension( 'D')->setWidth(20);
			$objSheet->getColumnDimension( 'E')->setWidth(10);
			$objSheet->getColumnDimension( 'F')->setWidth(10);
            $objSheet->getColumnDimension( 'G')->setWidth(15);
			$objSheet->getColumnDimension( 'H')->setWidth(15);
			$objSheet->getColumnDimension( 'I')->setWidth(50);
			$objSheet->getColumnDimension( 'J')->setWidth(15);
			$objSheet->getColumnDimension( 'K')->setWidth(10);
			$objSheet->getColumnDimension( 'L')->setWidth(30);
			$objSheet->getColumnDimension( 'M')->setWidth(15);
            $objSheet->getStyle('A')->getNumberFormat()
                ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
			$objSheet->getStyle('L')->getNumberFormat()
					->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
			$objPHPExcel->setActiveSheetIndex(0)
					->setCellValue('A1','订单号')
					->setCellValue('B1','审核时间')
					->setCellValue('C1','经销商姓名')
					->setCellValue('D1', '产品名称')
					->setCellValue('E1', '产品数量')
					->setCellValue('F1', '出库单位')
					->setCellValue('G1', '收件人')
					->setCellValue('H1', '联系电话')
					->setCellValue('I1', '收货地址')
					->setCellValue('J1', '寄送方式')
					->setCellValue('K1', '邮费(元)')
					->setCellValue('L1', '单号')
					->setCellValue('M1', '备注');
			$sql="select id,uid,real_name,order_code,goods_name,postage_type_id,postage_express_name,postage_money,consignee_name,consignee_phone,take_address,FROM_UNIXTIME(deal_check_time,'%Y-%m-%d %H:%i:%S') as time,CONCAT(".$order[0].") as orderstr from jm_user_order_out where $where and status=1 order by orderstr {$order[1]}";
			$num=2;
			for($i=0;$i<10;++$i){
				$limit=($i*2000).','.'2000';
				$data=DB::query($sql." limit {$limit}");
				if(!empty($data)){
					$param=$i*2000;
					foreach ($data as $k => $v) {
						//$v['out_unit']=GetUnit($v['out_unit']);
						$detail_obj=new UserOrderOutDetail($v['uid']);
						$detail=$detail_obj->listone($v['uid'],$v['id'],'out_unit,out_numbers');
						$detail['out_unit']=GetUnit($detail['out_unit']);
						//$num = $param +$k+2;
                        if($detail['out_numbers']>1 && $v['postage_type_id']!=4) {
                            for ($a = 0; $a < $detail['out_numbers']; ++$a) {
                                $objPHPExcel->setActiveSheetIndex(0)
                                    //->setCellValue('A' . $num, ' ' . $v['order_code'])
                                    ->setCellValueExplicit('A' . $num,$v['order_code'],\PHPExcel_Cell_DataType::TYPE_STRING)
                                    ->setCellValue('B' . $num, ' ' . $v['time'])
                                    ->setCellValue('C' . $num, $v['real_name'])
                                    ->setCellValue('D' . $num, $v['goods_name'])
                                    ->setCellValue('E' . $num, 1)
                                    ->setCellValue('F' . $num, $detail['out_unit'])
                                    ->setCellValue('G' . $num, $v['consignee_name'])
                                    ->setCellValue('H' . $num, ' ' . $v['consignee_phone'])
                                    ->setCellValue('I' . $num, $v['take_address'])
                                    ->setCellValue('J' . $num, $v['postage_express_name'])
                                    ->setCellValue('K' . $num, $v['postage_money'])
                                    ->setCellValue('L' . $num, '')
                                    ->setCellValue('M' . $num, '');
                                $num++;
                            }
                        }else{
                            $objPHPExcel->setActiveSheetIndex(0)
                                //->setCellValue('A' . $num, ' ' . $v['order_code'])
                                ->setCellValueExplicit('A' . $num,$v['order_code'],\PHPExcel_Cell_DataType::TYPE_STRING)
                                ->setCellValue('B' . $num, ' ' . $v['time'])
                                ->setCellValue('C' . $num, $v['real_name'])
                                ->setCellValue('D' . $num, $v['goods_name'])
                                ->setCellValue('E' . $num, $detail['out_numbers'])
                                ->setCellValue('F' . $num, $detail['out_unit'])
                                ->setCellValue('G' . $num, $v['consignee_name'])
                                ->setCellValue('H' . $num, ' ' . $v['consignee_phone'])
                                ->setCellValue('I' . $num, $v['take_address'])
                                ->setCellValue('J' . $num, $v['postage_express_name'])
                                ->setCellValue('K' . $num, $v['postage_money'])
                                ->setCellValue('L' . $num, '')
                                ->setCellValue('M' . $num, '');
                            $num++;
                        }
					}
				}else{
					break;
				}
			}
		}

  

上一篇:如何在phpexcel中设置自动高度(自动换行)?


下一篇:php 导出导入excel