function excel_down(){ //导入谁就去查谁 $data=Db::name('order_xueyou')->select(); // 导出Exl // import("./PHPExcel"); // import("./PHPExcel/Worksheet/Drawing"); // import("./PHPExcel/Writer/Excel2007"); include("./PHPExcel/PHPExcel.php"); include("./PHPExcel/Drawing.php"); $objPHPExcel = new \PHPExcel(); $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel); $objActSheet = $objPHPExcel->getActiveSheet(); // 水平居中(位置很重要,建议在最初始位置) $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('K')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('L')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('M')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('N')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('O')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('P')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('Q')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('R')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('S')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('T')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('U')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('V')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('W')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('X')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('Y')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet->setCellValue('A1', 'id'); $objActSheet->setCellValue('B1', 'openid'); $objActSheet->setCellValue('C1', 'nickName'); $objActSheet->setCellValue('D1', 'buyer'); $objActSheet->setCellValue('E1', 'ordertime'); $objActSheet->setCellValue('F1', 'orderdate'); $objActSheet->setCellValue('G1', 'address'); $objActSheet->setCellValue('H1', 'name'); $objActSheet->setCellValue('I1', 'goodid'); $objActSheet->setCellValue('J1', 'img'); $objActSheet->setCellValue('K1', 'oneprice'); $objActSheet->setCellValue('L1', 'num'); $objActSheet->setCellValue('M1', 'color'); $objActSheet->setCellValue('N1', 'sex'); $objActSheet->setCellValue('O1', 'size'); $objActSheet->setCellValue('P1', 'buynum'); $objActSheet->setCellValue('Q1', 'buy'); $objActSheet->setCellValue('R1', 'price'); $objActSheet->setCellValue('S1', 'status'); $objActSheet->setCellValue('T1', 'formkorea'); $objActSheet->setCellValue('U1', 'saddress'); $objActSheet->setCellValue('V1', 'remark'); $objActSheet->setCellValue('W1', 'formchina'); $objActSheet->setCellValue('X1', 'subremark'); $objActSheet->setCellValue('Y1', 'ddh'); // 设置个表格宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(16); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(12); // 垂直居中 $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('K')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('L')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('M')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('N')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('O')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('P')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('Q')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('R')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('S')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('T')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('U')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('V')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('W')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('X')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('Y')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); foreach($data as $k=>$v){ $k +=2; $objActSheet->setCellValue('A'.$k, $v['id']); $objActSheet->setCellValue('B'.$k, $v['openid']); $objActSheet->setCellValue('C'.$k, $v['nickName']); $objActSheet->setCellValue('D'.$k, $v['buyer']); $objActSheet->setCellValue('E'.$k, $v['ordertime']); $objActSheet->setCellValue('F'.$k, $v['orderdate']); $objActSheet->setCellValue('G'.$k, $v['address']); $objActSheet->setCellValue('H'.$k, $v['name']); $objActSheet->setCellValue('I'.$k, $v['goodid']); //获取到图片信息 $img = Db::name('order_xueyou')->where('id = '.$v['id'])->field('img')->find(); //dump($img); // die(); // 图片生成 $objDrawing[$k] = new \PHPExcel_Worksheet_Drawing(); $objDrawing[$k]->setPath('.'.$img['img']);//这里拼接 . 是因为要在根目录下获取 // 设置宽度高度 $objDrawing[$k]->setHeight(80);//照片高度 $objDrawing[$k]->setWidth(80); //照片宽度 /*设置图片要插入的单元格*/ $objDrawing[$k]->setCoordinates('J'.$k); // 图片偏移距离 $objDrawing[$k]->setOffsetX(12); $objDrawing[$k]->setOffsetY(12); $objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet()); // 表格内容 $objActSheet->setCellValue('K'.$k, $v['oneprice']); $objActSheet->setCellValue('L'.$k, $v['num']); $objActSheet->setCellValue('M'.$k, $v['color']); $objActSheet->setCellValue('N'.$k, $v['sex']); $objActSheet->setCellValue('O'.$k, $v['size']); $objActSheet->setCellValue('P'.$k, $v['buynum']); $objActSheet->setCellValue('Q'.$k, $v['buy']); $objActSheet->setCellValue('R'.$k, $v['price']); $objActSheet->setCellValue('S'.$k, $v['status']); $objActSheet->setCellValue('T'.$k, $v['formkorea']); $objActSheet->setCellValue('U'.$k, $v['saddress']); $objActSheet->setCellValue('V'.$k, $v['remark']); $objActSheet->setCellValue('W'.$k, $v['formchina']); $objActSheet->setCellValue('X'.$k, $v['subremark']); $objActSheet->setCellValue('Y'.$k, $v['ddh']); // 表格高度 $objActSheet->getRowDimension($k)->setRowHeight(80); } $fileName = '报价表'; $date = date("Y-m-d",time()); $fileName .= "_{$date}.xls"; $fileName = iconv("utf-8", "gb2312", $fileName); //重命名表 // $objPHPExcel->getActiveSheet()->setTitle('test'); //设置活动单指数到第一个表,所以Excel打开这是第一个表 $objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename=\"$fileName\""); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); //文件通过浏览器下载 // END }