<?php
public function export_excel(){
if(IS_POST){ //查询导出数据
$where = array();
$where['status'] = 2;
$payList= M('pay')->where($where)->order('payid desc')->select(); $xlsData = array();
foreach ($payList as $key => &$payItem){
$userItem = M('users')->where(array('userid'=>$payItem['userid']) )->find(); $payItem['alipay_nickname'] = $userItem['alipay_nickname'];
$payItem['alipay_account'] = $userItem['alipay_account'];
$payItem['name'] = M('order')->where(array('orderid'=>$payItem['orderid']) )->getField('name');
$payItem['mobile'] = $userItem['mobile']; $domain = strstr($payItem['imgurl'], ',');
if($domain == false){
$payItem['imgurl_arr'][0] = $payItem['imgurl'];
}else{
$aa = explode(",", $payItem['imgurl']); array_pop($aa);
$payItem['imgurl_arr'] = $aa;
}
}
$xlsData = $payList;
/
//开始导出为excel
set_time_limit(0);
require_once APP_PATH.'Extend/PHPExcel/PHPExcel.php';
require_once APP_PATH.'Extend/PHPExcel/PHPExcel/Worksheet/Drawing.php'; $objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$xlsName = "支付列表";
$xlsTitle = iconv('utf-8', 'gb2312', $xlsName);//文件名称
//开始生成excel
//set width
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(80);
//设置默认行高
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(130);
// 设置行高
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20); // 字体和样式
//$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10); //$objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getFont()->setBold(true); //$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
//$objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//$objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//设置单元格为文本
$objPHPExcel->getActiveSheet()->getStyle('A')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('C')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->getStyle('F')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
// 水平居上
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
// 将A1单元格设置为加粗,居中
// $styleArray1 = array(
// 'font' => array('bold' => true,'size'=>12,
// 'color'=>array('argb' => '00000000',),
// ),
// 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,),
// );
// $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray1);
// 设置水平居中
//$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 合并
//$objPHPExcel->getActiveSheet()->mergeCells('A1:I1');
// 表头
$excel_temp = $objPHPExcel->setActiveSheetIndex(0);
$excel_temp = $excel_temp->setCellValue('A1', '序号');
$excel_temp = $excel_temp->setCellValue('B1', '账号');
$excel_temp = $excel_temp->setCellValue('C1', '名字');
$excel_temp = $excel_temp->setCellValue('D1', '手机号');
$excel_temp = $excel_temp->setCellValue('E1', '金额');
$excel_temp = $excel_temp->setCellValue('F1', '图片');
//print_r($xlsData);exit;
//内容
for ($i = 0, $len = count($xlsData); $i < $len; $i++) {
//$objPHPExcel->getActiveSheet(0)->setCellValue('A' .($i + 3), $xlsData[$i]['mobile']); // $xlsData[$i]['mobile']
$objPHPExcel->getActiveSheet(0)->setCellValue('A' .($i + 2), $i+1);
// $xlsData[$i]['mobile']
$objPHPExcel->getActiveSheet(0)->setCellValue('B' .($i + 2), $xlsData[$i]['alipay_account']);
$objPHPExcel->getActiveSheet(0)->setCellValue('C' .($i + 2), $xlsData[$i]['alipay_nickname']);
$objPHPExcel->getActiveSheet(0)->setCellValue('D' .($i + 2), $xlsData[$i]['mobile']);
$amount = sprintf("%.2f",$xlsData[$i]['amount']/100); $objPHPExcel->getActiveSheet(0)->setCellValue('E' .($i + 2), $amount); // 对图片进行判断
foreach ($xlsData[$i]['imgurl_arr'] as $key => $img){ $bb = explode("uploads", $img);
$cc = "./uploads".$bb[1];//拼接图片路径
$objDrawing = new \PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($cc);
// 设置图片的宽度
$objDrawing->setHeight(80);
$objDrawing->setWidth(80);
// 图片偏移距离
$objDrawing->setOffsetX($key*90);
$objDrawing->setOffsetY(2);
$objDrawing->setCoordinates('F' . ($i+2) );
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
continue;
} //$objPHPExcel->getActiveSheet(0)->setCellValue('F' .($i + 2), '');
}//内容==end========
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle($xlsTitle);
$name = "./uploads/kuaijian_excel/Excel_".date("YmdHis",time()).".xls";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
//$objWriter->save('php://output');
$objWriter->save($name);
$ret["code"] = "1";
$ret["fileurl"] = 'http://'.$this->get_current_domain().'/'.__ROOT__.'/'.$name; $this->ajaxReturn($ret);
return;
}
}
PHPExcel导出含图片表格
最新推荐文章于 2023-08-03 11:50:42 发布