欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

php导出excel图片格式,PHPExcel API接口用法大全,按模板导入excel,美化excel,导出图片,设置单元格字体颜色背景色边框,合并单元格,设置行高列宽...

程序员文章站 2022-07-13 14:25:37
...

PHPExcel 功能十分强大,但是没有发现有详细的使用说明,经常需要各种百度查找,因此总结一下,希望对大家能有帮助。

读取excel表模板

$phpexcel = new \moonland\phpexcel\Excel();

fileName = '***.xlsx';   //excel模板的地址

$format = \PHPExcel_IOFactory::identify($fileName);

$objectreader = \PHPExcel_IOFactory::createReader($format);

$worksheet = $objectreader->load($fileName);

//获取第一个sheet

$sheet = $worksheet->getSheet(0);

设置单元格值//可连续设置多个值

$sheet->setCellValue("A2","测试A2")

->setCellValue("A3","测试A3");

//方式2

$sheet->setCellValueByColumnAndRow('A',2,"测试A2")

->setCellValueByColumnAndRow('A',3,"测试A3");

//方式3,设置内容的数据类型

$sheet->setCellValueExplicit('A10','100',\PHPExcel_Cell_DataType::TYPE_STRING);

$sheet->setCellValueExplicit('A11','100',\PHPExcel_Cell_DataType::TYPE_NUMERIC);

设置单元格样式

设置字体样式

在getStyle之后调用getFont,然后可以设置字体各个样式//创建颜色对象,设置颜色像css那样简单的传个色值,需要传对象

$color = new \PHPExcel_Style_Color();

$color->setRGB('FF0000');

$sheet->setCellValue("A10","测试A10");

$sheet->getStyle('A10')

->getFont()

->setName('微软雅黑')    //设置字体

->setSize(14)           //设置字体大小

->setColor($color)      //设置字体颜色

->setBold(true)         //是否家加粗

->setItalic(true);      //是否斜体

上面设置颜色比较麻烦,需要传个对象,还可以通过下面的方式,直接传颜色色值

$sheet->getStyle('A10')->getFont()->getColor()->setRGB('0000FF');

设置单元格背景色

$color = new \PHPExcel_Style_Color();

$color->setRGB('FF0000');

$sheet->setCellValue("A10","测试A10");

$sheet->getStyle('A10')

->getFill()

->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)  //设置填充类型

->setStartColor($color);

还可设置为渐变背景色$start_color = new \PHPExcel_Style_Color();

$start_color->setRGB('FF0000');

$end_color = new \PHPExcel_Style_Color();

$end_color->setRGB('00FF00');

$sheet->setCellValue("A10","测试A10");

$sheet->getStyle('A10')

->getFill()

->setFillType(\PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR)  // 设置填充类型为渐变

->setStartColor($start_color)

->setEndColor($end_color);

设置行高、列宽$sheet->setCellValue(“A10”,“测试A10”);

$sheet->getRowDimension(10)->setRowHeight(120);  //设置第10行的行高

$sheet->getColumnDimension('A')->setWidth(10);   //设置A列的宽度

设置单元格边框

getAllBorders设置选中区域所有单元格的边框

$color = new \PHPExcel_Style_Color();

$color->setRGB('FF0000');

$sheet->getStyle('A15:D20')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_DASHDOT);  //设置边框样式

$sheet->getStyle('A15:D20')->getBorders()->getAllBorders()->setColor($color);//设置边框颜色

如果只设置顶部边框,则使用

getTop$color = new \PHPExcel_Style_Color();

$color->setRGB('FF0000');

$sheet->getStyle('A25:D30')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_DASHDOT);

$sheet->getStyle('A25:D30')->getBorders()->getTop()->setColor($color);  //只设置顶部边框颜色

单元格对齐方式

$sheet->getStyle('A10')

->getAlignment()

->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)  //设置水平对齐方式

->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   //设置垂直对齐方式

合并、拆分单元格$sheet->mergeCells('A5:D5');    //合并单元格

$sheet->unmergeCells('B7:D7');  //拆分单元格

导出图片

导出网络图片之前需要先把图片下载到本地

$temp_pic = $this->download($image_url, '/图片存储路径');

$local_pic_path = '/图片存储路径'.$temp_pic;

$objDrawing = new \PHPExcel_Worksheet_Drawing();

$objDrawing->setPath($local_pic_path);

// 设置图片宽度高度

$objDrawing->setHeight(80);//照片高度

$objDrawing->setWidth(80); //照片宽度

/*设置图片要插入的单元格*/

$objDrawing->setCoordinates($position.$row_no);

// 图片偏移距离

$objDrawing->setOffsetX(20);

$objDrawing->setOffsetY(20);

$objDrawing->setWorksheet($sheet);

private function download($url, $path = 'images/'){
$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, $url);

curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);

curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); // 信任任何证书

$file = curl_exec($ch);

curl_close($ch);

$filename = pathinfo($url, PATHINFO_BASENAME);

$resource = fopen($path . $filename, 'a');

fwrite($resource, $file);

fclose($resource);

return $filename;

}

导出之后默认打开第一个sheet

默认情况下,打开导出的excel是默认打开最后一个sheet,我们可以进行设置,如设置为打开第一个sheet$worksheet->setActiveSheetIndex(0);

导出

excelheader("Content-Type: application/force-download");

header("Content-Type: application/octet-stream");

header("Content-Type: application/download");

header('Content-Disposition:inline;filename="filename.text"');

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");

$phpexcel->writeFile($worksheet);