php生成/读取excel的PhpSpreadsheet库使用方法

安装

composer require phpoffice/phpspreadsheet

使用文档

https://phpspreadsheet.readthedocs.io/en/latest/

生成excel

conf.php

<?php
//表头样式
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
//use PhpOffice\PhpSpreadsheet\Style\Font;

$alignment=['vertical'=> Alignment::VERTICAL_CENTER, 'rotation'=>0, 'wrap'=>true];
return [
    'head'=>[
        'font'=>['bold' => true],
        'alignment'=>[
            'horizontal'=>Alignment::HORIZONTAL_CENTER,
            'vertical'=>Alignment::VERTICAL_CENTER
        ],
        //'borders'=>[
        //  'outline'=>['borderStyle' => Border::BORDER_MEDIUM, 'color' => ['rgb' => '508630']],
        //  'bottom'=>['borderStyle'=>Border::BORDER_MEDIUM],
        //],
    ],
    'font'=>[ //标题样式
        'font' => [
            'name'=>'微软雅黑',
            'size'=>12,
            'bold'=>true,
            'italic'=>false,
            //'underline'=>Font::UNDERLINE_DOUBLE,
            //'strike'=>false,
            'color'=>['rgb' => 'ff0000']
        ],
    ],
    'alignment_left'=>array_merge($alignment,array('horizontal' => Alignment::HORIZONTAL_LEFT)),
    'alignment_right'=>array_merge($alignment,array('horizontal' => Alignment::HORIZONTAL_RIGHT)),
    'alignment_center'=>array_merge($alignment,array('horizontal' => Alignment::HORIZONTAL_CENTER)),
];

index.php

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;

$conf=include 'conf.php';
$spreadsheet = new Spreadsheet();
$data=[
    ['1', 'cw', '1862080', '2000-01-01'],
    ['2', 'cw', '1862080', '2000-01-01'],
    ['3', 'cw', '1862080', '2000-01-01'],
    ['4', 'cw', '1862080', '2000-01-01'],
];
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A5','ID')
    ->setCellValue('B5','姓名')
    ->setCellValue('C5','电话')
    ->setCellValue('D5','时间');

//设置选定sheet表名
$spreadsheet->getActiveSheet()->setTitle('Sheet1');
//设置字体样式
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setName('Arial')->setSize(25);//->setUnderline(true);->getColor()->setARGB('FFFF0000');->setBold(true);
//合并单元格 给单元格赋值(数值，字符串，公式)
$spreadsheet->getActiveSheet()->mergeCells('A1:D3')->setCellValue('A1', '活动数据列表');
$spreadsheet->getActiveSheet()->mergeCells('A4:D4')->setCellValue('A4', "导表时间：".date("Y-m-d H:i:s"));
$spreadsheet->getActiveSheet()->getStyle('A4')->getAlignment()->applyFromArray($conf['alignment_right']);

//设置单列宽度
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(12);
//$spreadsheet->getActiveSheet()->getColumnDimension('G')->setRowHeight(50);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);

$spreadsheet->getActiveSheet()->getStyle('A1:D3')->applyFromArray($conf['head']);
//->getAlignment()->getHorizontal('');
//->getBorders()->getTop()->setBorderStyle('');
//->setWrapText(true);自动换行

$spreadsheet->getActiveSheet()->getStyle('A5:D5')->getAlignment()->applyFromArray($conf['alignment_center']);
$spreadsheet->getActiveSheet()->getStyle('A5:D5')->applyFromArray($conf['font']);
$spreadsheet->getActiveSheet()->getStyle('A:D')->getAlignment()->applyFromArray($conf['alignment_left']);

//内容部分
foreach($data as $k => $v) {
    $i=$k+6;
    $spreadsheet->setActiveSheetIndex(0)
        ->setCellValue('A'.$i, $v[0])
        ->setCellValue('B'.$i, $v[1])
        ->setCellValue('C'.$i, $v[2])
        ->setCellValue('D'.$i, $v[3]);
}

//设置打印页边距
$spreadsheet->getActiveSheet()->getPageMargins()->setTop(0);
$spreadsheet->getActiveSheet()->getPageMargins()->setRight(0);
$spreadsheet->getActiveSheet()->getPageMargins()->setLeft(0);
$spreadsheet->getActiveSheet()->getPageMargins()->setBottom(0);
//设置纸张类型
$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);
//设置自动筛选
$spreadsheet->getActiveSheet()->setAutoFilter('A5:D5');
//设置自动换行
$spreadsheet->getActiveSheet()->getStyle('B6:B5')->getAlignment()->setWrapText(true);

$writer = new Xlsx($spreadsheet);

$writer->save('x1.xlsx');
echo 'ok';

读取excel

read.php

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = __DIR__ . '/x1.xlsx';
//方法1.1, 1.2, 2都可以

//方法1：自动识别文件类型
//方法1.1
$spreadsheet = IOFactory::load($inputFileName);//自动识别文件类型
//方法1.2
//$spreadsheet= IOFactory::createReaderForFile($inputFileName)->setReadDataOnly(true)->load($inputFileName);

//方法2：指定文件类型
//$spreadsheet = IOFactory::createReader("Xlsx")->load($inputFileName);

$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
var_dump($sheetData);

可能出现的问题

Fatal error: Uncaught Error: Class "ZipArchive" not found

需要安装php zip扩展