Laravel 的 PhpSpreadsheet 包入门
安装
composer require phpoffice/phpspreadsheet
demo1
<?php
namespace App\Http\Controllers\Admin;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class PhpOfficeController extends Controller
{
//
public function index(){
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();//取得当前的选中的表格页
$sheet->setCellValue('A1', 'Hello World !');//设置表格列的A1内容
$writer = new Xlsx($spreadsheet);//写入类型对象
$filename=to_linux_path(public_path()).'/phpoffice/world.xlsx';//文件路径
$writer->save($filename);//写入
}
}
表格文件设置
文件设置
//文件:\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Document\Properties.php
$spreadsheet->getProperties()
->setCreator('Maarten Balliauw')
->setLastModifiedBy('Maarten Balliauw')
->setTitle('Office 2007 XLSX Test Document')
->setSubject('Office 2007 XLSX Test Document')
->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
->setKeywords('office 2007 openxml php')
->setCategory('Test result file');
默认字体颜色大小
文件路径设置:\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Style\Style.php
$spreadsheet->getDefaultStyle()
->getFont()
->setName('Arial')
->setSize(10);
选择表格的页面
$spreadsheet->setActiveSheetIndex(1);//表示第二个页面,从0开始
工作表标题
$spreadsheet->getActiveSheet()->setTitle('Hello');
表格内容设置参考
使用前配置
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
//文件所在地:\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Worksheet\Worksheet.php
// Set cell A1 with a string value
$spreadsheet->getActiveSheet()->setCellValue('A1', 'PhpSpreadsheet');
// Set cell A2 with a numeric value
$spreadsheet->getActiveSheet()->setCellValue('A2', 12345.6789);
// Set cell A3 with a boolean value
$spreadsheet->getActiveSheet()->setCellValue('A3', TRUE);
// Set cell A4 with a formul 生成一个判断公司,判断A3是否存在,如果存在则组合A1,A2.否则A2,A1
$spreadsheet->getActiveSheet()->setCellValue(
'A4',
'=IF(A3, CONCATENATE(A1," ", A2), CONCATENATE(A2," ", A1))'
);
设置列宽度
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(40);
设置默认列宽度
$spreadsheet->getActiveSheet()->getDefaultColumnDimension('A')->setWidth(20);
//自动
$spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setAutoSize(12);
设置自动列宽度
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
设置行高
$spreadsheet->getActiveSheet()->getRowDimension('10')->setRowHeight(100);
//默认行高
$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
设置日期和/或时间值
$spreadsheet->getActiveSheet()->getStyle('A6')
->getNumberFormat()
->setFormatCode(
\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME
);
//\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME 可以改成你的时间格式
$spreadsheet->getActiveSheet()->getStyle('A6')
->getNumberFormat()->setFormatCode( 'yyyy-mm-dd');
设置前导零的数字,格式为字符串
默认自动转换成数字,去掉0的
$spreadsheet->getActiveSheet()->setCellValueExplicit(
'A8',
"01513789642",
\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
);
取得单元格值
$cellValue = $spreadsheet->getActiveSheet()->getCell('A1')->getValue();
设置单元格值
$spreadsheet->getActiveSheet() ->getCell('C14')->setValue($richText2);
有时候有些带了参数,如果想获得里面的值修改成
$cellValue = $spreadsheet->getActiveSheet()->getCell('A4')->getCalculatedValue();
上面也可以改成
$cellValue = $spreadsheet->getActiveSheet()->getCell('A6')->getFormattedValue();
默认情况下,它会自动转换我们的类型,如果我们的数字会变成科学计数,所以这个时候需要用到格式要求
$spreadsheet->getActiveSheet()->getCell('A6')->setValueExplicit(44565687897566565,DataType::TYPE_STRING);
设置按列和行设置单元格值
$spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 5, 'PhpSpreadsheet');//A5的值
取得按列和行设置单元格值
$cellValue = $spreadsheet->getActiveSheet()->getCellByColumnAndRow(2, 5)->getValue();//第2列,第5行,B5
需要单元格的计算值
$cellValue = $spreadsheet->getActiveSheet()->getCellByColumnAndRow(1, 4)->getCalculatedValue();
从数组中设置单元格范围
$arrayData = [
[NULL, 2010, 2011, 2012],
['Q1', 12, 15, 21],
['Q2', 56, 73, 86],
['Q3', 52, 61, 69],
['Q4', 30, 32, 0],
];
$spreadsheet->getActiveSheet()
->fromArray(
$arrayData, // The data to set
NULL, // Array values with this value will not be set,当为空的返回值
'C3' // Top left coordinate of the worksheet range where
// we want to set these values (default is A1)
);
传递二维数组,则会将其视为一系列行和列。一维数组将被视为一行
$rowArray = ['Value1', 'Value2', 'Value3', 'Value4'];
$spreadsheet->getActiveSheet()
->fromArray(
$rowArray, // The data to set
NULL, // Array values with this value will not be set
'C3' // Top left coordinate of the worksheet range where
// we want to set these values (default is A1)
);
从C3开始,开始写入数据
检索一系列单元格值到数组
$dataArray = $spreadsheet->getActiveSheet()
->rangeToArray(
'C3:E5', // The worksheet range that we want to retrieve
NULL, // Value that should be returned for empty cells,单元格为空的的时候,返回值
TRUE, // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)//是否最后的结果显示
TRUE, // Should values be formatted (the equivalent of getFormattedValue() for each cell)//单页格格式化类型
TRUE // Should the array be indexed by cell row and cell column,是否返回列名的key键
);
单元格字体样式
$richText = new RichText();
$richText->createText('你好');
$payable = $richText->createTextRun('你 好 吗?');
$payable->getFont()->setBold(true);
$payable->getFont()->setItalic(true);
$payable->getFont()->setColor(new Color(Color::COLOR_DARKGREEN));
$richText->createText(', unless specified otherwise on the invoice.');
$spreadsheet->getActiveSheet()
->setCellValue('C13', $richText);
设置单元格自动换行
$spreadsheet->getActiveSheet()
->getStyle('C14')
->getAlignment()->setWrapText(true);
//多行
$spreadsheet->getActiveSheet()->getStyle('A3:A6')->getAlignment()->setWrapText(true);
设置超级链接
$spreadsheet->getActiveSheet()
->setCellValue('C17', '黑白课堂学院');
$spreadsheet->getActiveSheet()
->getCell('C17')
->getHyperlink()
->setUrl('http://www.heibaiketang.com')
->setTooltip('黑白课堂学院官网');
设置计算公式
$spreadsheet->getActiveSheet()->setCellValue('B1', 'Range #1')
->setCellValue('B2', 3)
->setCellValue('B3', 7)
->setCellValue('B4', 13)
->setCellValue('B5', '=SUM(B2:B4)');
设置单页格对齐方式
$spreadsheet->getActiveSheet()->setCellValue('D11', 'Total excl.:');
$spreadsheet->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
或
$styleArray = [
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
$spreadsheet->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
对齐参数参考
// Horizontal alignment styles
const HORIZONTAL_GENERAL = 'general';
const HORIZONTAL_LEFT = 'left';
const HORIZONTAL_RIGHT = 'right';
const HORIZONTAL_CENTER = 'center';
const HORIZONTAL_CENTER_CONTINUOUS = 'centerContinuous';
const HORIZONTAL_JUSTIFY = 'justify';
const HORIZONTAL_FILL = 'fill';
const HORIZONTAL_DISTRIBUTED = 'distributed'; // Excel2007 only
// Vertical alignment styles
const VERTICAL_BOTTOM = 'bottom';
const VERTICAL_TOP = 'top';
const VERTICAL_CENTER = 'center';
const VERTICAL_JUSTIFY = 'justify';
const VERTICAL_DISTRIBUTED = 'distributed'; // Excel2007 only
插入图片
$drawing = new Drawing();
$drawing->setName('Paid');
$drawing->setDescription('Paid');
$drawing->setPath(__DIR__ . '/../images/paid.png');
$drawing->setCoordinates('G2');//放入坐标位置,G2
$drawing->setOffsetX(110);坐标位置,相对于G2位置
$drawing->setRotation(25);//旋转角度
$drawing->getShadow()->setVisible(true);//阴影
$drawing->getShadow()->setDirection(90);//阴影方向
$drawing->setWorksheet($spreadsheet->getActiveSheet());
设置字体部分
文件所在\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Style\Font.php
//取得getFont对象
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');//字体
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);//大小
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);//加粗
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(Font::UNDERLINE_SINGLE);//下划线
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setSuperscript(true);//设置上标
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setSubscript(true);//设置下标
合并
//将A18到E22合并为一个单元格
$spreadsheet->getActiveSheet()->mergeCells('A18:E22');
拆分
$spreadsheet->getActiveSheet()->unmergeCells('A18:E22');
边框
$styleArray = [
'borders' => [
'outline' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
'color' => ['argb' => 'FFFF0000'],
],
],
];
$spreadsheet->getActiveSheet()->getStyle('B2:G8')->applyFromArray($styleArray);
印刷设
设置页眉和页脚
$spreadsheet->getActiveSheet()
->getHeaderFooter()
->setOddHeader('&L&G&C&黑白课堂');
$spreadsheet->getActiveSheet()
->getHeaderFooter()
->setOddFooter('专业php开发');
设置图片页眉
$helper->log('Add a drawing to the header');
$drawing = new HeaderFooterDrawing();
$drawing->setName('PhpSpreadsheet logo');
$file='E:/www/lv56/public/upload/text.png';
$drawing->setPath($file);
$drawing->setHeight(36);
$spreadsheet->getActiveSheet()
->getHeaderFooter()
->addImage($drawing, HeaderFooter::IMAGE_HEADER_LEFT);
设置打印方向和纸张大小
$spreadsheet->getActiveSheet()
->getPageSetup()
->setOrientation(PageSetup::ORIENTATION_LANDSCAPE);
$spreadsheet->getActiveSheet()
->getPageSetup()
->setPaperSize(PageSetup::PAPERSIZE_A4);
通过官方实例学习
php -S localhost:8000 -t vendor/phpoffice/phpspreadsheet/samples
直接进入http:localhost:8000
使用中的问题
- 编码问题,PHP iconv() 或mb_convert_encoding() 函数转换操作
- 内存大小,可以设置php内存脚本,memory_limit 指令值php.ini或ini_set('memory_limit', '128M') 在代码中使用 来增加PHP可用的内存
版权提示
1.除了标识原创之外,其他可能来源于网友的分享,仅供学习使用2.如您发现侵犯了您的权利,请联系我们删除
3.转载必须带本文链接,否则你将侵权
4.关于会员或其发布的相关内容均由会员自行提供,会员依法应对其提供的任何信息承担全部责任,本站不对此承担任何法律责任