phpSpreadsheet简单使用(包含图片操作)

2022-05-25 11:25:56

1.Composer安装

composer require phpoffice/phpspreadsheet

2.引入

<?php
require ‘vendor/autoload.php‘;

use PhpOffice\PhpSpreadsheet\IOFactory;//用于载入已有的xml文件
use PhpOffice\PhpSpreadsheet\Spreadsheet;//用于新建xml文件
use Phpoffice\PhpSpreadsheet\Shared\Date;//日期操作类 用处不大
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;//单元格格式类型
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;//保存xml文件

3.使用

3.1-excel文档操作

$inputFileName = ‘xabc.xlsx‘;
$spreadSheet = IOFactory::load($inputFileName);//载入xml文件
//$spreadSheet = new Spreadsheet();//新建一个xml文件
//$spreadSheet->getProperties()->setCreator("hellow");//设置xml作者
//$resp = $spreadSheet->getSheetCount();//工作表总数
//$resp = $spreadSheet->getSheetNames();//工作表名数组
//$sheet = $spreadSheet->getSheetByName(‘Sheet1‘);//根据表名获取工作表
//$sheet = $spreadSheet->getSheet(0);//根据表索引获取工作表
//$sheet = $spreadSheet->setActiveSheetIndex(0);//切换当前工作表
$sheet = $spreadSheet->getActiveSheet();

3.2-工作表操作

//常用读取操作
$sheet->setTitle(‘Hello‘);//设置标题
$sheet->mergeCells(‘E7:F10‘);//单元格合并
$sheet->unmergeCells(‘B7:C10‘);//拆分单元格
$cell = $sheet->getCell(‘A1‘);//获取单元格A1的值
$cell = $sheet->getCellByColumnAndRow(2,1);//获取单元格 B1
$data = $sheet->toArray();//获取文档所有值
$resp = $sheet->getHighestRow();//最大行数 19
$resp = $sheet->getHighestColumn();//最大列数 C
//常用写操作
$resp = $sheet->setCellValue(‘A1‘, "hellow\nphp");//设置值
$resp = $sheet->setCellValueByColumnAndRow(2,1,‘6666‘);//设置B2的值
$resp = $sheet->fromArray($arr, null, ‘D5‘);//数组,空值时填充值,开始单元格坐标
//$sheet->getColumnDimension(‘A‘)->setWidth(300);//设置A列的宽度
$sheet->getDefaultColumnDimension()->setWidth(50);//设置列默认宽度
//$sheet->getRowDimension(10)->setRowHeight(300);//设置第一行的宽度
$sheet->getDefaultRowDimension()->setRowHeight(50);//设置行默认高度 但不会修改已设置过高度/已有值的行

$resp = $sheet->setCellValue(‘B2‘,"hellow\nphp");//设置换行 双引号+\n+setWrapText
$resp = $sheet->getStyle(‘B2‘)->getAlignment()->setWrapText(true);//设置换行

$resp = $sheet->setCellValue(‘B2‘,"百度");//设置A链接
$resp = $sheet->getCell(‘B3‘)->getHyperlink()->setUrl(‘https://www.baidu.com‘);//设置换行

$resp = $sheet->getStyle(‘A1‘)->getFont()->setBold(true)->setName(‘Arial‘)->setSize(10);//设置字体加粗大小
$resp = $sheet->getStyle(‘A1‘)->getFont()->getName();//字体名
$resp = $sheet->getStyle(‘B2‘)->getFont()->getColor()->setRGB(‘#AEEEEE‘);//设置颜色
$resp = $sheet->getStyle(‘A1‘)->getFont()->getColor()->getRGB();//获取颜色值
$sheet->getCell(‘D1‘)->setValue(‘2021-03-27 23:22:59‘);
$sheet->getCell(‘D2‘)->setValue(Date::PHPToExcel(time()));//设置日期
//$sheet->getStyle(‘D2‘)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);//设置日期格式 感觉用处不大
$sheet->getStyle(‘D2‘)->getNumberFormat()->setFormatCode(‘dd/mm/yyyy‘);//设置日期格式 与上文相同

3.3-单元格操作

//常用读
$cell = $sheet->getCell(‘A1‘);//获取单元格A1的值
$resp = $cell->getValue();//获取单元个值
$resp = $cell->getCoordinate();//获取行列信息 A1
$column = $cell->getColumn();//获取列信息 A
$row   = $cell->getRow();//获取行信息 1
list($column, $row) = PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($coordi);//拆分成 [‘A‘,‘1‘];
$resp = $cell->getDataType();//获取数据类型
//常用写
$resp = $cell->setValue(‘8888‘);//设置值
$cell->getStyle()->getFont()->getName();//单元个样式设置 参考工作表设置 只是不需要设置单元格位置而已
//设置A标签与换行参考工作表设置

4.图像操作

//图片读取与存储
$draws = $sheet->getDrawingCollection();//获取所有图像
$imageFilePath = ‘uploads/‘;
foreach ($draws as $drawing) {//$drawing 为 PhpOffice\PhpSpreadsheet\Worksheet\Drawing类的实例;
    $coordi = $drawing->getCoordinates();//获取图像坐标 eg A4
    list($startColumn, $startRow) = PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($coordi);//拆分成 [‘A‘,‘4‘];
    $imageFileName = $imageFilePath.$coordi.‘_‘. mt_rand(1000, 9999);
    switch ($drawing->getExtension()) {
        case ‘jpg‘:
        case ‘jpeg‘:
            $imageFileName .= ‘.jpg‘;
            $source = imagecreatefromjpeg($drawing->getPath());
            $res = imagejpeg($source,  $imageFileName);
            break;
        case ‘gif‘:
            $imageFileName .= ‘.gif‘;
            $source = imagecreatefromgif($drawing->getPath());
            imagegif($source,  $imageFileName);
            break;
        case ‘png‘:
            $imageFileName .= ‘.png‘;
            $source = imagecreatefrompng($drawing->getPath());
            $res = imagepng($source, $imageFileName);
            break;
    }
}

//图像写入操作
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setWorksheet($sheet);
$drawing->setPath(dirname(__FILE__).‘/‘.$imageFileName);
$drawing->setWidth(200);//图片宽
$drawing->setHeight(200);//图片高
$drawing->setOffsetX(100);//设置图片偏移量
$drawing->setCoordinates(‘A5‘);//将图片放置于单元格

5.保存excel

$writer = new Xlsx($spreadSheet);
$resp = $writer->save(‘ok/wm3.xlsx‘);

6.完整实例

<?php
require ‘vendor/autoload.php‘;

use PhpOffice\PhpSpreadsheet\IOFactory;//用于载入已有的xml文件
use PhpOffice\PhpSpreadsheet\Spreadsheet;//用于新建xml文件
use Phpoffice\PhpSpreadsheet\Shared\Date;//日期操作类 用处不大
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;//单元格格式类型
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;//保存xml文件

$arr = [
    [‘a1‘, ‘b1‘, ‘c‘],
    [‘a2‘, ‘b2‘, ‘c2‘]
];
$inputFileName = ‘xabc2.xlsx‘;//一个空的excel文件
$spreadSheet = IOFactory::load($inputFileName);//载入xml文件
//$spreadSheet = new Spreadsheet();//新建一个xml文件
//$spreadSheet->getProperties()->setCreator("hellow");//设置xml作者
//$resp = $spreadSheet->getSheetCount();//工作表总数
//$resp = $spreadSheet->getSheetNames();//工作表名数组
//$sheet = $spreadSheet->getSheetByName(‘Sheet1‘);//根据表名获取工作表
//$sheet = $spreadSheet->getSheet(0);//根据表索引获取工作表
//$sheet = $spreadSheet->setActiveSheetIndex(0);//切换当前工作表
$sheet = $spreadSheet->getActiveSheet();
$sheet->setTitle(‘Hello‘);//设置标题
$sheet->mergeCells(‘E7:F10‘);//单元格合并
//$sheet->unmergeCells(‘B7:C10‘);
//读：
//--工作表读
$cell = $sheet->getCell(‘A1‘);//获取单元格A1的值
//$data = $sheet->toArray();//获取文档所有值
//$cell = $sheet->getCellByColumnAndRow(2,1);//获取单元格 B1
//$resp = $sheet->getHighestRow();//最大行数 19
//$resp = $sheet->getHighestColumn();//最大列数 C

//--单元格读
//$resp = $cell->getValue();//获取单元个值
//$resp = $cell->getCoordinate();//获取行列信息 A1
//$resp = $cell->getColumn();//获取列信息 A
//$resp = $cell->getRow();//获取行信息 1
//$resp = $cell->getDataType();//获取数据类型

//写
//--工作表设置
$resp = $sheet->setCellValue(‘A1‘, "hellow\nphp");//设置值
$resp = $sheet->setCellValueByColumnAndRow(2,1,‘6666‘);//设置B2的值
$resp = $sheet->fromArray($arr, null, ‘D5‘);//数组,空值时填充值,开始单元格坐标
//$sheet->getColumnDimension(‘A‘)->setWidth(300);//设置A列的宽度
$sheet->getDefaultColumnDimension()->setWidth(50);//设置列默认宽度
//$sheet->getRowDimension(10)->setRowHeight(300);//设置第一行的宽度
$sheet->getDefaultRowDimension()->setRowHeight(50);//设置行默认高度 但不会修改已设置过高度/已有值的行
$resp = $sheet->setCellValue(‘B2‘,"hellow\nphp");//设置换行 双引号+\n+setWrapText
$resp = $sheet->getStyle(‘B2‘)->getAlignment()->setWrapText(true);//设置换行
$resp = $sheet->setCellValue(‘B2‘,"百度");//设置A链接
$resp = $sheet->getCell(‘B3‘)->getHyperlink()->setUrl(‘https://www.baidu.com‘);//设置换行

$resp = $sheet->getStyle(‘A1‘)->getFont()->setBold(true)->setName(‘Arial‘)->setSize(10);//设置字体
$resp = $sheet->getStyle(‘A1‘)->getFont()->getName();//字体名
$resp = $sheet->getStyle(‘B2‘)->getFont()->getColor()->setRGB(‘#AEEEEE‘);//设置颜色
$resp = $sheet->getStyle(‘A1‘)->getFont()->getColor()->getRGB();//颜色值
$sheet->getCell(‘D1‘)->setValue(‘2021-03-27 23:22:59‘);
$sheet->getCell(‘D2‘)->setValue(Date::PHPToExcel(time()));//设置日期
//$sheet->getStyle(‘D2‘)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_DDMMYYYY);//设置日期格式 感觉用处不大
$sheet->getStyle(‘D2‘)->getNumberFormat()->setFormatCode(‘dd/mm/yyyy‘);//设置日期格式 与上文相同

//--单元格设置
//$resp = $cell->setValue(‘8888‘);//设置值
$cell->getStyle()->getFont()->getName();//单元个样式设置 参考工作表设置 只是不需要设置单元格位置而已
//图像读取操作
$draws = $sheet->getDrawingCollection();//获取所有图像
$imageFilePath = ‘uploads/‘;
foreach ($draws as $drawing) {//$drawing 为 PhpOffice\PhpSpreadsheet\Worksheet\Drawing类的实例;
    $coordi = $drawing->getCoordinates();//获取图像坐标 eg A4
    list($startColumn, $startRow) = PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($coordi);//拆分成 [‘A‘,‘4‘];
    $imageFileName = $imageFilePath.$coordi.‘_‘. mt_rand(1000, 9999);
    switch ($drawing->getExtension()) {
        case ‘jpg‘:
        case ‘jpeg‘:
            $imageFileName .= ‘.jpg‘;
            $source = imagecreatefromjpeg($drawing->getPath());
            $res = imagejpeg($source,  $imageFileName);
            break;
        case ‘gif‘:
            $imageFileName .= ‘.gif‘;
            $source = imagecreatefromgif($drawing->getPath());
            imagegif($source,  $imageFileName);
            break;
        case ‘png‘:
            $imageFileName .= ‘.png‘;
            $source = imagecreatefrompng($drawing->getPath());
            $res = imagepng($source, $imageFileName);
            break;
    }
}
//图像写入操作
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setWorksheet($sheet);
$drawing->setPath(dirname(__FILE__).‘/‘.$imageFileName);
$drawing->setWidth(200);//图片宽
$drawing->setHeight(200);//图片高
//$drawing->setOffsetX(100);//设置图片偏移量
$drawing->setCoordinates(‘A5‘);//将图片放置于单元格


$writer = new Xlsx($spreadSheet);
$resp = $writer->save(‘ok/wm3.xlsx‘);
var_dump($resp);

 

phpSpreadsheet简单使用(包含图片操作)

上一篇：Apache Shiro简介


下一篇：微信小程序 scroll-view 横向滚动条 隐藏无效