<?phpnamespace app\admin\controller;use app\common\controller\Backend;use think\Loader;Loader::import('phpexcel.Classes.PHPExcel.IOFactory',EXTEND_PATH,'.php');/** * * * @icon fa fa-circle-o */class Order extends Backend{ /** * Order模型对象 * @var \app\admin\model\Order */ protected $model = null; protected $dataLimit = 'auth'; public function _initialize() { parent::_initialize(); $this->model = new \app\admin\model\Order; } function excel_handle($ids){ $this->success('解决胜利，正在生成报表！',null,$ids); } function excout($ids=null){ $info = db('order')->find($ids); if(empty($info)){ $this->error('订单不存在'); } $list = db('order_details') ->alias('details') ->join('goods','goods.id = details.goods_id') ->join('goods_sku','goods_sku.id = details.sku_id') ->field('details.*,goods.name as goods_name,goods_sku.name as suk_name') ->where('order_id',$info['id']) ->select(); //订单信息 error_reporting(E_ALL); ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); ini_set("error_reporting","E_ALL & ~E_NOTICE"); define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />'); date_default_timezone_set('Europe/London'); include 'extend/phpexcel/Classes/PHPExcel/IOFactory.php'; //设置模板文件 $objPHPExcel = \PHPExcel_IOFactory::load("PI-larkkey.xls"); //将json字符串转换为数组 //数据 //$billData = json_decode($_REQUEST["billData"],true); //月份表 $months = array( 1 => 'Jan', 2 => 'Feb', 3 => 'Mar', 4 => 'Apr', 5 => 'May', 6 => 'Jun', 7 => 'Jul', 8 => 'Aug', 9 => 'Sep', 10 => 'Oct', 11 => 'Nov', 12 => 'Dec' ); //根底信息 $basic_info = array( 'consignee_company_name' => $info['contactname'], 'consignee_address' => $info['address'], 'consignee_tel' => $info['tel'], 'loading_port' => $info['loading'], 'destination_port' => $info['destination'], 'shippment_way' => $info['shippment'], 'invoice_number' => 'INVOICE NO:'.$info['invoiceno'] ); //根底信息 日期 $basic_info['date'] = $months[(int)Date('m')]." ".Date('D').",".Date("Y"); //根底信息填充单元格 $objPHPExcel->getActiveSheet()->setCellValue('B7', $basic_info['consignee_company_name']) ->setCellValue('B8', $basic_info['consignee_address']) ->setCellValue('B9', $basic_info['consignee_tel']) ->setCellValue('B10', 'Ningbo') ->setCellValue('B11', $basic_info['destination_port']) ->setCellValue('B12', 'by express') ->setCellValue('G7', $basic_info['invoice_number']) ->setCellValue('G8', $basic_info['date']); //设置B10 $objValidation_ = $objPHPExcel->getActiveSheet()->getCell("B10")->getDataValidation(); $objValidation_ -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST) -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION) -> setAllowBlank(false) -> setShowInputMessage(true) -> setShowErrorMessage(true) -> setShowDropDown(true) -> setErrorTitle('输出的值有误') -> setError('您输出的值不在下拉框列表内.') -> setPromptTitle('设施类型') -> setFormula1('"Ningbo,Shengzhen,guangzhou"'); //设置B12 $objValidation_ = $objPHPExcel->getActiveSheet()->getCell("B12")->getDataValidation(); $objValidation_ -> setType(\PHPExcel_Cell_DataValidation::TYPE_LIST) -> setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION) -> setAllowBlank(false) -> setShowInputMessage(true) -> setShowErrorMessage(true) -> setShowDropDown(true) -> setErrorTitle('输出的值有误') -> setError('您输出的值不在下拉框列表内.') -> setPromptTitle('设施类型') -> setFormula1('"by express,by air,by express"'); //列表信息// $list_data = array(// '0' => array(// 'pic' => '',//图片// 'item_no' => '001',//货号// 'description' => 'wife',//形容// 'protocol' => 'sex',//属性// 'color' => 'green',//色彩// 'qty_pcs' => '100', //数量/个数 -装箱量// 'usd_pcs' => '50',//美金/个数// 'amount' => '100' //总计价格// )// ); $list_data = $list;// $PHPSheet->getColumnDimension('C')->setWidth(70);// $PHPSheet->getRowDimension(21)->setRowHeight(75);//高度 //循环填充雷同题目单元格 $row=14; foreach($list_data as $r => $dataRow) { $this->excelPic($dataRow['image'],'A'.$row,$objPHPExcel->getActiveSheet()); $objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(100); $objPHPExcel->getActiveSheet()// ->setCellValue('A'.$row, $dataRow['image']) ->setCellValue('B'.$row, $dataRow['model_no']) ->setCellValue('c'.$row, $dataRow['goods_name']) ->setCellValue('D'.$row, $dataRow['suk_name']) ->setCellValue('E'.$row, $dataRow['mode']==1?'Read To Sheep':'Customization') ->setCellValue('F'.$row, $dataRow['num']) ->setCellValue('G'.$row, $dataRow['price']) ->setCellValue('H'.$row, $dataRow['num']*$dataRow['price']); $row++; } //显示总价 $objPHPExcel->getActiveSheet()->setCellValue('A'.$row, 'Total：'."") ->setCellValue('H'.$row, $info['amount']) ->setCellValue('A'.($row+2), "Note:") //上面是固定文本信息 ->setCellValue('A'.($row+3), "1,Price Term:Fob Ningbo") ->setCellValue('A'.($row+4), "2,Price Valid:30 days") ->setCellValue('A'.($row+5), "3,Payment Term:30% T/T,70% balance before shippment or L/C or Paypal") ->setCellValue('A'.($row+6), "4,Packing:white netural packing or our logo packing,") ->setCellValue('A'.($row+7), " If need customized packing,need MOQ.And packing cost need to be adjusted.") ->setCellValue('A'.($row+10), "Bank Detail:"); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); ob_end_clean(); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename=larkkey_'.time().'.xlsx'); header('Cache-Control: max-age=0'); $objWriter->save("php://output"); exit; //让拜访浏览器间接下载文件流// Header("Location: /uploads/larkkey_".time().".xls"); } function excelPic($url,$seat,$obj){ if(empty($url)){ return $obj; } $temp_pic = $this->download($url,'../public/uploads/temp/'); $temp[] =$local_pic_path = '../public/uploads/temp/'.$temp_pic; if (file_exists($local_pic_path)) { $img = new \PHPExcel_Worksheet_Drawing(); $img->setPath($local_pic_path);//写入图片门路 // $img->setHeight(100);//写入图片高度 $img->setWidth(100);//写入图片宽度// $img->setOffsetX(105);//写入图片在指定格中的X坐标值// $img->setOffsetY(5);//写入图片在指定格中的Y坐标值// $img->setRotation(1);//设置旋转角度// $img->getShadow()->setVisible(true);// $img->getShadow()->setDirection(50); $img->setCoordinates($seat);//设置图片所在表格地位 $img->setWorksheet($obj);//把图片写到以后的表格中 } return $obj; } function download($url, $path = 'images/') { $ch = curl_init(); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30); curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); // 信赖任何证书 $file = curl_exec($ch); curl_close($ch); $filename = pathinfo($url, PATHINFO_BASENAME); $resource = fopen($path . $filename, 'a'); fwrite($resource, $file); fclose($resource); return $filename; }}文件