Home > Article > Backend Development > PHPExcel 向单元格插入图片就换行的问题

PHPExcel 向单元格插入图片就换行的问题

WBOYOriginal: 2016-06-23 13:59:121808browse

要用phpexcel从数据库中将表导出为excel格式，表中存放有图片路径现在我的代码如下：

<?php    require_once 'PHPExcel.php';    require_once 'PHPExcel/Writer/Excel5.php'; include_once 'DB_connmssql.php';$sql="select.....";$query = mssql_query($sql);$items = array();// 创建一个处理对象实例    $objExcel = new PHPExcel();       // 创建文件格式写入对象实例, uncomment    $objWriter = new PHPExcel_Writer_Excel5($objExcel);    //设置文档基本属性    $objProps = $objExcel->getProperties();    $objProps->setCreator("tt");    $objProps->setLastModifiedBy("tt");    $objProps->setTitle("tt");    $objProps->setSubject("tt ");    $objProps->setDescription("tt ");    $objProps->setKeywords("tt ");    $objProps->setCategory("变动报表");       //*************************************    //设置当前的sheet索引，用于后续的内容操作。    //一般只有在使用多个sheet的时候才需要显示调用。    //缺省情况下，PHPExcel会自动创建第一个sheet被设置SheetIndex=0    $objExcel->setActiveSheetIndex(0);    $objActSheet = $objExcel->getActiveSheet();       //设置当前活动sheet的名称    $objActSheet->setTitle('tttt');       //*************************************    //    //设置宽度，这个值和EXCEL里的不同，不知道是什么单位，略小于EXCEL中的宽度$objActSheet->getColumnDimension('A')->setWidth(20); $objActSheet->getColumnDimension('B')->setWidth(15); $objActSheet->getRowDimension(1)->setRowHeight(30); $objActSheet->getRowDimension(2)->setRowHeight(27); $objActSheet->getRowDimension(3)->setRowHeight(16);  //设置单元格的值  $objActSheet->setCellValue('A1', 'Titile'); //合并单元格$objActSheet->mergeCells('A1:N1'); //设置样式$objStyleA1 = $objActSheet->getStyle('A1');    $objStyleA1->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$objFontA1 = $objStyleA1->getFont();    $objFontA1->setName('宋体');    $objFontA1->setSize(18);  $objFontA1->setBold(true);    //设置居中对齐$objActSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$objActSheet->getStyle('B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$objActSheet->setCellValue('A2', 'Shoes Picture'); $objActSheet->setCellValue('B2', 'Product code'); //设置边框$objActSheet->getStyle('A2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('A2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('A2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('A2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('B2')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('B2')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('B2')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('B2')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$i=1;$query = mssql_query($sql);$query1 = mssql_query($sql);$items=array();$sales_sum=$sales_sum+$row1["total_sales"];while($row=mssql_fetch_array($query)){	$row["Photo"]="../picture/".$row["Photo"];	$n=$i+2;	$objActSheet->getStyle('B'.$n)->getNumberFormat()->setFormatCode('@');	$objActSheet->getStyle('E'.$n)->getNumberFormat()->setFormatCode('@');	$objActSheet->getRowDimension($n)->setRowHeight(16);	$objActSheet->getStyle('A'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('A'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('A'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('A'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('B'.$n)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('B'.$n)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('B'.$n)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->getStyle('B'.$n)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );	$objActSheet->setCellValue('A'.$n, $row["Photo"]);	$objActSheet->setCellValue('B'.$n, $row["Article_No"]);		$i++;} $outputFileName = "tables addminus1.xls";    header("Content-Type: application/force-download");header("Content-Type: application/octet-stream");header("Content-Type: application/download");header('Content-Disposition:inline;filename="'.$outputFileName.'"');header("Content-Transfer-Encoding: binary");header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");header("Cache-Control: must-revalidate, post-check=0, pre-check=0");header("Pragma: no-cache");$objWriter->save('php://output');  ?>

导出的excel如下

请教下要把图片的路径换成真实的图片输出代码该怎么修改？还有怎么让excel的标题分成两行显示并加粗，代码该如何修改？

回复讨论(解决方案)

图片如下

标题换行只需在需要换行的地方加上换行符
比如 "Product \ncode"

其他的明天说

$objActSheet->setCellValue('A2', 'Shoes Picture'); $objActSheet->setCellValue('B2', 'Product code'); $objActSheet->setCellValue('C2', 'Original Retail  Price (VAT)'); $objActSheet->setCellValue('D2', '  Total \nOrdered Qty'); $objActSheet->setCellValue('E2', '  Total 		Sales Qty'); $objActSheet->setCellValue('F2', 'Sellthru'); $objActSheet->setCellValue('G2', 'Total Sales'); $objActSheet->setCellValue('H2', 'Cumulated Qty'); $objActSheet->setCellValue('I2', 'Cumulated  /nQty %');

/n \n都试过了...都不行。

加粗范例
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);

添加图片  范例
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setCoordinates('B15');
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objDrawing = new PHPExcel_Worksheet_Drawing();while($row=mssql_fetch_array($query)){	if(''!==$row["Photo"]){	$row["Photo"]="../picture/".$row["Photo"];	$objDrawing->setName('Logo');	$objDrawing->setDescription('Logo');	$objDrawing->setPath($row["Photo"]);	$objDrawing->setHeight(36);	$objDrawing->setCoordinates('B15');	$objDrawing->setOffsetX(110);	$objDrawing->setRotation(25);	$objDrawing->getShadow()->setVisible(true);	$objDrawing->getShadow()->setDirection(45);	$objDrawing->setWorksheet($objExcel->getActiveSheet());	}

$objDrawing = new PHPExcel_Worksheet_Drawing();
要放在循环内，即每个图片需要一个 PHPExcel_Worksheet_Drawing 实例

setHeight 图片高
setCoordinates 所在单元格
setOffsetX 左上角相对单元格的x偏移
setRotation 旋转角
更多的方法请见文档

我 setCoordinates('B15') （15行B列）
你也在 setCoordinates('B15') 吗？

我 setCoordinates('B15') （15行B列）
你也在 setCoordinates('B15') 吗？
果然这样，但又有个问题，我把图片设置到了图片名称出现的位置。

while($row=mssql_fetch_array($query)){	$objDrawing = new PHPExcel_Worksheet_Drawing();	if(''!==$row["Photo"]){	$row["Photo"]="picture/".$row["Photo"];	$objDrawing->setName('Logo');	$objDrawing->setDescription('Logo');	$objDrawing->setPath($row["Photo"]);	$objDrawing->setHeight(36);	$objDrawing->setCoordinates('A'.$aa);//图片名称所在的单元格	$objDrawing->setOffsetX(80);	$objDrawing->setRotation(25);	$objDrawing->getShadow()->setVisible(true);	$objDrawing->getShadow()->setDirection(45);	$objDrawing->setWorksheet($objExcel->getActiveSheet());	$aa++;	}

出来效果是这样的

图片只是出现在单元格的位置，并不是单元格的值
要达到下面的效果该怎么设置单元格？

不是说了吗？
$objDrawing->setOffsetX(80); //表示图片左边距是 80
$objDrawing->setRotation(25); //表示旋转 25 度
显然都不是你需要的
你只需设置单元格居中排列

批量设置，我一般用 PHPExcel_Style 来写，如

	$style_obj = new PHPExcel_Style(); 	$style_array = array( 		'borders' => array( 			'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),			'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),			'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),			//'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN) 			),		'alignment' => array(			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,			'vertical'	=> PHPExcel_Style_Alignment::VERTICAL_CENTER,			'wrap'	=> true			)		);	$style_obj->applyFromArray($style_array); 	$sheet->setSharedStyle($style_obj, "B$y:$x$y");

当然一个个的设置也是没有问题的

	$style_obj = new PHPExcel_Style(); 	$style_array = array( 		'borders' => array( 			'top' => array('style' => PHPExcel_Style_Border::BORDER_THIN),			'left' => array('style' => PHPExcel_Style_Border::BORDER_THIN),			'bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN),			//'right' => array('style' => PHPExcel_Style_Border::BORDER_THIN) 			),		'alignment' => array(			'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,			'vertical'	=> PHPExcel_Style_Alignment::VERTICAL_CENTER,			'wrap'	=> true			)		);	$style_obj->applyFromArray($style_array); 	$sheet->setSharedStyle($style_obj, "B$y:$x$y");

当然一个个的设置也是没有问题的
格式搞定了现在就剩两个问题了：
1、导出图片到cell里面。
2、页面上有导出excel按钮，怎么通过点击按钮，将页面的参数传到后台（前台页面不跳转）以导出对应的excel。
大师把这个分数一起拿走吧小弟分数不多... http://bbs.csdn.net/topics/390730241

1、导出图片上面已经说了，你也做到了。只是要注意调整格式
2、导出excel按钮可以是表单的提交按钮，设置表单的 target 指向一个隐藏的 iframe 就不会跳转了

<form method=post action=excel.php target=down><input type=submit value='导出excel'></form><iframe name=down style='display:none'></iframe>

<form method=post action=excel.php target=down><input type=submit value='导出excel'></form><iframe name=down style='display:none'></iframe>

大师再请教个问题，向excel中写入图片时，当图片不存在，phpexcel会抛出异常不能形成excel文件，于是我捕获异常（被注释掉的代码）

if(''!==$row["Photo"]){	//	$row["Photo"]="picture/".$row["Photo"];	//try{		$objDrawing->setName('Logo');		$objDrawing->setDescription('Logo');		$objDrawing->setPath($row["Photo"]);		$objDrawing->setHeight(36);		$objDrawing->setCoordinates('A'.$n);//图片名称所在的单元格		$objDrawing->setOffsetX(15);		$objDrawing->setRotation(25);		$objDrawing->getShadow()->setVisible(true);		$objDrawing->getShadow()->setDirection(45);		$objDrawing->setWorksheet($objExcel->getActiveSheet());// 	}catch (Exception $e){// 		$objDrawing->setName('Logo1');// 		$objDrawing->setDescription('Logo1');// 		$objDrawing->setPath("notf.jpg");// 		$objDrawing->setHeight(36);// 		$objDrawing->setCoordinates('A'.$n);//图片名称所在的单元格// 		$objDrawing->setOffsetX(15);// 		$objDrawing->setRotation(25);// 		$objDrawing->getShadow()->setVisible(true);// 		$objDrawing->getShadow()->setDirection(45);// 		$objDrawing->setWorksheet($objExcel->getActiveSheet());// 	}	}

这样就算图片不存在也能导出excel，但导出的excel就是打不开，请问如何解决？

if(file_exists("picture/".$row["Photo"])) {   .....  $objDrawing->setWorksheet($objExcel->getActiveSheet());}

if(file_exists("picture/".$row["Photo"])) {   .....  $objDrawing->setWorksheet($objExcel->getActiveSheet());}

请问大师输出pdf文档该怎么输出呢？
我这样输出文件很小很小，而且打不开

/输出内容    // if($outtype=="pdf"){// header('Content-Type: application/pdf');// header('Content-Disposition: attachment;filename="Global Network.pdf"');// header('Cache-Control: max-age=0');// $objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'PDF');// $objWriter->save('php://output');// exit;//}ELSE{$outputFileName = "Global Network.xls";    //到文件    //$objWriter->save($outputFileName);    //到浏览器header("Content-Type: application/force-download");header("Content-Type: application/octet-stream");header("Content-Type: application/download");header('Content-Disposition:inline;filename="'.$outputFileName.'"');header("Content-Transfer-Encoding: binary");header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");// header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");header("Cache-Control: must-revalidate, post-check=0, pre-check=0");header("Pragma: no-cache");$objWriter->save('php://output');  //exit;//}

我没做过，刚才试了一下
不但样式难看，而且不能显示中文

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：关于mysql的查询和页面的显示Next article：代码求简化

See more

PHPExcel 向单元格插入图片就换行的问题

回复讨论(解决方案)

Related articles