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

使用中的问题

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