1.合并单元格
$objPHPExcel->getActiveSheet(0)->mergeCells('A1:B1');
2.给单元格添加背景色
$objPHPExcel->getActiveSheet(0)->getStyle('A1:B1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet(0)->getStyle('A1:B1')->getFill()->getStartColor()->setRGB('CCCCFF');
ps:rgb值转16进制在线转换网址 http://www.sioe.cn/yingyong/yanse-rgb-16/
3.设置单元格默认行高
$objPHPExcel->getActiveSheet(0)->getDefaultRowDimension()->setRowHeight(80);
4.设置单元格默认宽度
$objPHPExcel->getActiveSheet(0)->getDefaultColumnDimension()->setWidth(15);
5.设置某行行高度
$objPHPExcel->getActiveSheet(0)->getRowDimension(1)->setRowHeight(17);
6.设置某列单元格宽度
$objPHPExcel->getActiveSheet(0)->getColumnDimension('B')->setWidth(21)
7.设置水平竖直居中
$objPHPExcel->getActiveSheet(0)->getStyle($i.'1:'.$i.'2')->getAlignment()->
setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//竖直居中
$objPHPExcel->getActiveSheet(0)->getStyle($i.'1:'.$i.'2')->getAlignment()
->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平居中
8.设置边框
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
'color' => array('argb' => '000000'),
),
),
);
$objPHPExcel->getActiveSheet(0)->getStyle('A1:K1')->applyFromArray($styleArray);//从A1到K1设置边框为细边框
9.设置导出文件的名称
header("Content-Disposition: attachment;filename='导出名字.xls'");
10.在导出的excel单元格里添加图片
$objDrawing= new PHPExcel_Worksheet_Drawing();
$objDrawing->setPath($url);//url是图片的绝对路径
// 设置宽度高度
$objDrawing->setHeight(80);//照片高度
$objDrawing->setWidth(80); //照片宽度
/*设置图片要插入的单元格*/
$objDrawing->setCoordinates('N'.$i);
// 图片偏移距离
$objDrawing->setOffsetX(10);
$objDrawing->setOffsetY(10);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
来自 https://blog.csdn.net/qq_27930635/article/details/80102972
phpexcel导出带生成图片完美案例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 | // 导出exl
public function look_down(){
$id = I( 'get.id' );
$m = M ( 'offer_goods' );
$where [ 'offer_id' ] = $id ;
$data = $m ->field( 'goods_id,goods_sn,goods_name,barcode,goods_type,price' )->select();
// 导出Exl
import( "Org.Util.PHPExcel" );
import( "Org.Util.PHPExcel.Worksheet.Drawing" );
import( "Org.Util.PHPExcel.Writer.Excel2007" );
$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( 'B1' )->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);
$objActSheet ->setCellValue( 'A1' , '商品货号' );
$objActSheet ->setCellValue( 'B1' , '商品名称' );
$objActSheet ->setCellValue( 'C1' , '商品图' );
$objActSheet ->setCellValue( 'D1' , '商品条码' );
$objActSheet ->setCellValue( 'E1' , '商品属性' );
$objActSheet ->setCellValue( 'F1' , '报价(港币)' );
// 设置个表格宽度
$objPHPExcel ->getActiveSheet()->getColumnDimension( 'A' )->setWidth(16);
$objPHPExcel ->getActiveSheet()->getColumnDimension( 'B' )->setWidth(80);
$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()->getStyle( 'A' )->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel ->getActiveSheet()->getStyle( 'B' )->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);
foreach ( $data as $k => $v ){
$k +=2;
$objActSheet ->setCellValue( 'A' . $k , $v [ 'goods_sn' ]);
$objActSheet ->setCellValue( 'B' . $k , $v [ 'goods_name' ]);
$img = M( 'goods' )->where( 'goods_id = ' . $v [ 'goods_id' ])->field( 'goods_thumb' )->find();
// 图片生成
$objDrawing [ $k ] = new \PHPExcel_Worksheet_Drawing();
$objDrawing [ $k ]->setPath( './Upload/' . $img [ 'goods_thumb' ]);
// 设置宽度高度
$objDrawing [ $k ]->setHeight(80); //照片高度
$objDrawing [ $k ]->setWidth(80); //照片宽度
/*设置图片要插入的单元格*/
$objDrawing [ $k ]->setCoordinates( 'C' . $k );
// 图片偏移距离
$objDrawing [ $k ]->setOffsetX(12);
$objDrawing [ $k ]->setOffsetY(12);
$objDrawing [ $k ]->setWorksheet( $objPHPExcel ->getActiveSheet());
// 表格内容
$objActSheet ->setCellValue( 'D' . $k , $v [ 'barcode' ]);
$objActSheet ->setCellValue( 'E' . $k , $v [ 'goods_type' ]);
$objActSheet ->setCellValue( 'F' . $k , $v [ 'price' ]);
// 表格高度
$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
}
|
注意事项:
1.phpexcel下载地址:http://phpexcel.codeplex.com/
来自 https://www.cnblogs.com/mracale/p/6743377.html
PHPEXCEL 导出数据加图片
前言
图片对excel来说是一个对象,正常是导出不到单元格里去,所以大家都很困惑到底该怎么使用PHP语言来进行导出呢!下面我就详细的讲解下怎么使用php导出图片到excel(此例是使用CI框架)
开始啦!~~~~
如果你没有PHPEXCEL类库,可以到http://phpexcel.codeplex.com/里去下载!
下载完成后导入到自己框架的第三方类库!~CI示例:
注释:这个是CI项目的哦,别的框架放到类库里就行了
下面开始我们的代码操作!~:
//CI框架引入PHPExcel
$this->load->library('PHPExcel');
//实例化PHPExcel类
$objPHPExcel = new PHPExcel();
重点来了!!!!
//图片处理类,这个才是图片导出的关键哦
$objDrawing = new PHPExcel_Worksheet_Drawing();
//然后正常设置我们导出图片的代码
$objPHPExcel->createSheet(0);
$objPHPExcel->setActiveSheetIndex(0);
$currentSheet = $objPHPExcel->getActiveSheet();
// 水平居中(位置很重要,建议在最初始位置,这里我举例,ABCDEFG,可以按照自己的需求定义!~)
$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->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(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
//设置第一行标题
$currentSheet->setCellValue('A1', "序列号");
$currentSheet->setCellValue('B1', "设备ID");
$currentSheet->setCellValue('C1', "设备密码");
$currentSheet->setCellValue('D1', "设备编码");
$currentSheet->setCellValue('E1', "二维码");
$currentSheet->setCellValue('F1', "二维码");
$currentSheet->setCellValue('G1', "二维码");
//定义变量初始化为2,从第二行开始
$idx = 2;
//开始循环数组数据!~
foreach ($devices as $device) {
//设置数据所在单元格,我这里把图片放在E里
$currentSheet->setCellValue('A' . $idx, $device['sequence']);
$currentSheet->setCellValue('B' . $idx, $device['device_unique']);
$currentSheet->setCellValue('C' . $idx, $device['device_pwd']);
$currentSheet->setCellValue('D' . $idx, $device['device_code']);
//设置单元格高度,这个是重点哦
$currentSheet->getRowDimension($idx) -> setRowHeight(60);
图片操作来了!~~~
//获取图片
$this->load->model('Device_codes_model');
$codes = $this->Device_codes_model->getOneCodes($id);
//开始设置图片啦~~
$objDrawing->setPath($codes['code_file']);
// 设置图片宽度高度
$objDrawing->setHeight(80);//照片高度
$objDrawing->setWidth(80); //照片宽度
/*设置图片要插入的单元格*/
$objDrawing->setCoordinates('E'.$idx);
// 图片偏移距离
$objDrawing->setOffsetX(12);
$objDrawing->setOffsetY(12);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$idx++;
}
//导出excel到表格
$sheetWrite = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
来自 https://www.jianshu.com/p/bd163f2eca27
PHP Excel表格导出图片方法
1、进入PHP Excel官网后,找到右边的download按钮,下载,下载完成的是一个压缩文件,解压放到你的项目目录里。
2、查看解压文件是否有PHPExcel_Worksheet_Drawing图片文件类。
3、创建一个excel.php文件,开始编写PHP Excel到出。
实例代码:
<?php
/**
* PHP Excel表格导出图片方法
* 2015-07-30
*/
function ExcelCustomers(){
/*引入phpexcel核心类文件*/
include ROOT_PATH.'/system/libraries/phpExcel/PHPExcel.php';
/*实例化excel类*/
$excel = new PHPExcel();
/*实例化excel图片处理类*/
$objDrawing = new PHPExcel_Worksheet_Drawing();
/*设置文本对齐方式*/
$excel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objActSheet = $excel->getActiveSheet();
$letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N');
/*设置表头数据*/
$tableheader = array('导出日期','姓名', '身份证号', '民族','性别' ,'地址','房号','入住次数','身份头像');
/* 填充表格表头*/
for($i = 0;$i < count($tableheader);$i++) {
$excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");
/*设置font*/
$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setName(iconv('gbk', 'utf-8', '宋体'));
$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setSize(16);
$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setBold(true);
/*设置下划线*/
//$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
/*设置字体颜色*/
//$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
//$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
/*设置表格宽度*/
$objActSheet->getColumnDimension("$letter[$i]")->setWidth(20);
$objActSheet->getColumnDimension("$letter[2]")->setWidth(30);
$objActSheet->getColumnDimension("$letter[5]")->setWidth(60);
}
$startdate = !empty($_POST['startdate']) ? $_POST['startdate'] : date('Y-m-d',time());
/* 设置表格数据*/
$hotel_customer = M('hotel_customer');
$sql = "SELECT addtime,username,idcard,gender,national,address,photo,photo_s,number,check_num FROM tc_hotel_customer WHERE last_checkin = '".$startdate."'";
$Obj = $hotel_customer ->query($sql);
if(!empty($Obj)){
foreach ($Obj as $k=>$v){
$Obj[$k]['addtime'] = date('Y-m-d',time());
list($Oldrwidth, $Oldheight) = getimagesize($v['photo']);
if($Oldrwidth > 126 && $Oldheight > 102){
$Obj[$k]['photos'] = $v['photo_s'];
}else{
$Obj[$k]['photos'] = $v['photo'];
}
if($v['gender'] == 1){
$Obj[$k]['gender'] = '男';
}else{
$Obj[$k]['gender'] = '女';
}
unset($Obj[$k]['photo']);
unset($Obj[$k]['photo_s']);
}
}else{
echo "<script>alert('亲,今天没人入住哟!');location.href='index.php?ac=hotel_customers';</script>";
exit;
}
/*向每行单元格插入数据*/
for ($i = 0;$i < count($Obj);$i++) {
$j = $i + 2;
/*设置表格高度*/
$excel->getActiveSheet()->getRowDimension($j)->setRowHeight(50);
$excel->getActiveSheet()->getStyle($j)->getFont()->setSize(12);
/*设置表格格式*/
$excel->getActiveSheet()->getStyle("$letter[2]$j")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
for ($row = 0;$row < count($Obj[$i]);$row++) {
$photos = '';
if ($row == (count($Obj[$i]) - 1 )) {
/*实例化excel图片处理类*/
$objDrawing = new PHPExcel_Worksheet_Drawing();
if(!empty($Obj[$i]['photos'])){
$photo = substr($Obj[$i]['photos'],7);
$photoAry = explode('/',$photo);
if(in_array('uploadfiles',$photoAry)){
unset($photoAry[0]);
$photos = implode('/',$photoAry);
//var_dump($photos);exit;
/*设置图片路径 切记:只能是本地图片*/
$objDrawing->setPath($photos);
}
}else{
$objDrawing->setPath($photos,false);
}
/*设置图片高度*/
$objDrawing->setWidth(20);
$objDrawing->setHeight(60);
/*设置图片要插入的单元格*/
$objDrawing->setCoordinates("$letter[$row]$j");
/*设置图片所在单元格的格式*/
$objDrawing->setOffsetX(50);
$objDrawing->setRotation(20);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(50);
$objDrawing->setWorksheet($excel->getActiveSheet());
continue;
}
$excel->getActiveSheet()->setCellValue("$letter[0]$j",$Obj[$i]['addtime']);
$excel->getActiveSheet()->setCellValue("$letter[1]$j",$Obj[$i]['username']);
$excel->getActiveSheet()->setCellValue("$letter[2]$j",$Obj[$i]['idcard']);
$excel->getActiveSheet()->setCellValue("$letter[3]$j",$Obj[$i]['national']);
$excel->getActiveSheet()->setCellValue("$letter[4]$j",$Obj[$i]['gender']);
$excel->getActiveSheet()->setCellValue("$letter[5]$j",$Obj[$i]['address']);
$excel->getActiveSheet()->setCellValue("$letter[6]$j",$Obj[$i]['number']);
$excel->getActiveSheet()->setCellValue("$letter[7]$j",$Obj[$i]['check_num']);
}
}
$date = $startdate."的住客资料";
/*实例化excel输入类并完成输出excel文件*/
$write = new PHPExcel_Writer_Excel5($excel);
//var_dump($write);exit;
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");;
header("Content-Disposition:attachment;filename=$date.xls");
header("Content-Transfer-Encoding:binary");
$write->save('php://output');
//var_dump($row);exit;
}
?>
来自 https://www.cnblogs.com/520fyl/p/5420690.html