PHPExcel 向单元格插入图片就换行的问题-PHP Tutorial-php.cn

Home

Backend Development

PHP Tutorial

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 23, 2016 pm 01:59 PM

要用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

Working with Flash Session Data in LaravelMar 12, 2025 pm 05:08 PM

Laravel simplifies handling temporary session data using its intuitive flash methods. This is perfect for displaying brief messages, alerts, or notifications within your application. Data persists only for the subsequent request by default: $request-

cURL in PHP: How to Use the PHP cURL Extension in REST APIsMar 14, 2025 am 11:42 AM

The PHP Client URL (cURL) extension is a powerful tool for developers, enabling seamless interaction with remote servers and REST APIs. By leveraging libcurl, a well-respected multi-protocol file transfer library, PHP cURL facilitates efficient execution of various network protocols, including HTTP, HTTPS, and FTP. This extension offers granular control over HTTP requests, supports multiple concurrent operations, and provides built-in security features.

Build a React App With a Laravel Back End: Part 2, ReactMar 04, 2025 am 09:33 AM

This is the second and final part of the series on building a React application with a Laravel back-end. In the first part of the series, we created a RESTful API using Laravel for a basic product-listing application. In this tutorial, we will be dev

Simplified HTTP Response Mocking in Laravel TestsMar 12, 2025 pm 05:09 PM

Laravel provides concise HTTP response simulation syntax, simplifying HTTP interaction testing. This approach significantly reduces code redundancy while making your test simulation more intuitive. The basic implementation provides a variety of response type shortcuts: use Illuminate\Support\Facades\Http; Http::fake([ 'google.com' => 'Hello World', 'github.com' => ['foo' => 'bar'], 'forge.laravel.com' =>

12 Best PHP Chat Scripts on CodeCanyonMar 13, 2025 pm 12:08 PM

Do you want to provide real-time, instant solutions to your customers' most pressing problems? Live chat lets you have real-time conversations with customers and resolve their problems instantly. It allows you to provide faster service to your custom

Notifications in LaravelMar 04, 2025 am 09:22 AM

In this article, we're going to explore the notification system in the Laravel web framework. The notification system in Laravel allows you to send notifications to users over different channels. Today, we'll discuss how you can send notifications ov

Explain the concept of late static binding in PHP.Mar 21, 2025 pm 01:33 PM

Article discusses late static binding (LSB) in PHP, introduced in PHP 5.3, allowing runtime resolution of static method calls for more flexible inheritance.Main issue: LSB vs. traditional polymorphism; LSB's practical applications and potential perfo

PHP Logging: Best Practices for PHP Log AnalysisMar 10, 2025 pm 02:32 PM

PHP logging is essential for monitoring and debugging web applications, as well as capturing critical events, errors, and runtime behavior. It provides valuable insights into system performance, helps identify issues, and supports faster troubleshoot

See all articles

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

Repo: How To Revive Teammates

1 months agoBy尊渡假赌尊渡假赌尊渡假赌

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hello Kitty Island Adventure: How To Get Giant Seeds

1 months agoBy尊渡假赌尊渡假赌尊渡假赌

How Long Does It Take To Beat Split Fiction?

4 weeks agoByDDD

R.E.P.O. Save File Location: Where Is It & How to Protect It?

4 weeks agoByDDD

Hot Tools

SublimeText3 English version

Recommended: Win version, supports code prompts!

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Zend Studio 13.0.1

Powerful PHP integrated development environment

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Hot Topics

Where is the login entrance for gmail email?

7369

1628

1354

1266

1214