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开始

表格内容设置参考

//文件所在地:\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))'
);

a2.jpg
a1.jpg

设置日期和/或时间值

$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()->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)
    );

07-simple-example-2.png

传递二维数组,则会将其视为一系列行和列。一维数组将被视为一行

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

keyon.png

从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键
    );

key.jpg

单元格字体样式

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

对齐参数参考

 // 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);//设置下标

印刷设

设置页眉和页脚

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

QQ截图20181018150305.jpg

通过官方实例学习

php -S localhost:8000 -t vendor/phpoffice/phpspreadsheet/samples

QQ截图20181018112756.jpg

直接进入http:localhost:8000
office.jpg

使用中的问题

  1. 编码问题,PHP iconv()或mb_convert_encoding()函数转换操作
  2. 内存大小,可以设置php内存脚本,memory_limit 指令值php.ini或ini_set('memory_limit', '128M')在代码中使用 来增加PHP可用的内存

提示

评论区 (0)

没有记录
支持 markdown,图片截图粘贴拖拽都可以自动上传。
黑白课堂

黑白课堂

混元大罗金仙 站长创业者玉树凌风每天醒来0收入

查看更多

最新视频课程

Laravel 的 PhpSpreadsheet 包入门

ace.js 打造一款属于你的 Web 编辑器,入门文档。

wap2App 入门讲解,100%速成,全面为你讲解。

Laravel Permission 中文文档

解释 OAuth 2.0 认证 和使用场景说明

Laravel 之 horizon 队列管理界面系统

钻级赞助商