php phpoffice/phpspreadsheet 读取 生成 excel文件

利用composer安装PhpSpreadsheet到项目目录

https://packagist.org/packages/phpoffice/phpspreadsheet
// 引入包
composer require phpoffice/phpspreadsheet

导出代码

<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

include_once "./vendor/autoload.php";

/**
 * 数字转字母（类似excel列标）
 * @param $index
 * @param int $start
 * @return string
 */
function intToChr($index, int $start = 65): string
{
    $str = '';
    if (floor($index / 26) > 0) {
        $str .= intToChr(floor($index / 26) - 1);
    }
    return $str . chr($index % 26 + $start);
}

/**
 * 每个sheet设置
 * @param $spreadsheet
 * @param $sheetIndex
 * @param $data
 */
function handlingMultipleSheet($spreadsheet, $sheetIndex, $data)
{
    // 创建sheet
    $spreadsheet->createSheet();
    // 设置当前的活动sheet
    $activeSheet = $spreadsheet->setActiveSheetIndex($sheetIndex);
    // 设置每个sheet中的名称 title
    $activeSheet->setCellValue('A1', $data['title']);
    // 这是你的数据键名
    $rows = $data['rows'][0];
    // 计算你所占的列数
    $columnsCount = count($rows);
    // 求k-v值的所占行数
    $infoNum = ceil(count($data['info']) / 2);
    // 下面的详细信息的开始行数
    $infoStart = $infoNum + 2;
    $cellName = [];
    for ($column = 0; $column < $columnsCount; $column++) {
        array_push($cellName, intToChr($column));
    }
    // 设置标题栏的名称
    $sheet = $spreadsheet->getActiveSheet($sheetIndex)->setTitle($data['info']['姓名:']);
    // 合并标题栏单元格
    $spreadsheet->getActiveSheet($sheetIndex)->mergeCells('A1:' . $cellName[$columnsCount - 1] . '1');
    // 设置标题栏的字体大小
    $spreadsheet->getActiveSheet($sheetIndex)->getStyle('A1')->getFont()->setSize(20);
    // 中部数据居中
    $spreadsheet->getActiveSheet($sheetIndex)->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $spreadsheet->getActiveSheet($sheetIndex)->getStyle('A')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $spreadsheet->getActiveSheet($sheetIndex)->getStyle('B')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $spreadsheet->getActiveSheet($sheetIndex)->getStyle('C')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $spreadsheet->getActiveSheet($sheetIndex)->getStyle('D')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $spreadsheet->getActiveSheet($sheetIndex)->getStyle('E')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $spreadsheet->getActiveSheet($sheetIndex)->getStyle('F')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $spreadsheet->getActiveSheet($sheetIndex)->getStyle('G')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    // 低部数据居中
    $spreadsheet->getActiveSheet($sheetIndex)->getStyle($infoStart)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    // 低部数据标题栏加粗
    $spreadsheet->getActiveSheet($sheetIndex)->getStyle($infoStart)->getFont()->setBold(true);

    // 低部数据信息循环
    foreach ($data['rows'] as $key => $item) {
        //循环设置单元格：
        //$key+$infoStart,因为第一行是表头，所以写到表格时   从第数据行开始写
        for ($i = 0; $i < $columnsCount; $i++) {
            $sheet->setCellValue(intToChr($i) . ($key + $infoStart), $item[$i]);
            // 固定列宽
            $spreadsheet->getActiveSheet($sheetIndex)->getColumnDimension(intToChr($i))->setWidth(20);
        }
    }

    // 中闻数据处理
    $rowNumber = 1;
    $infoIndex = 0;
    foreach ($data['info'] as $key => $value) {
        if ($infoIndex % 2 == 0) {
            $rowNumber++;
            $infoCellName1 = 'A' . $rowNumber;
            $infoCellMegreRange = 'B' . $rowNumber . ':C' . $rowNumber;
            $infoCellName2 = 'B' . $rowNumber;
        } else {
            $infoCellName1 = 'D' . $rowNumber;
            $infoCellMegreRange = 'E' . $rowNumber . ':F' . $rowNumber;
            $infoCellName2 = 'E' . $rowNumber;
        }
        $spreadsheet->setActiveSheetIndex($sheetIndex)->setCellValue($infoCellName1, $key);
        $spreadsheet->getActiveSheet($sheetIndex)->mergeCells($infoCellMegreRange);
        $spreadsheet->setActiveSheetIndex($sheetIndex)->setCellValue($infoCellName2, $value);
        $infoIndex++;
    }
}

/**
 * 多sheet的导出
 * @param $datas
 * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
 */
function exportExecl($datas)
{
    $name = '完成情况-' . date("Y-m-d", time());
    $spreadsheet = new Spreadsheet();
    foreach ($datas as $key => $data) {
        handlingMultipleSheet($spreadsheet, $key, $data);
    }
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    //删除清空：
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
    exit;
}

// 要导出的数据
$datas = [
    [
        'title' => '张三完成情况',
        'info'  => [
            '姓名:'    => '张三',
            '所在地：' => '北京市',
            '收货地址：'  => '河北',
            '联系方式：'  => '13900000000',
            '日期：'    => '2022-04-20',
            '备注1：'   => '优秀',
            '备注2：'   => '100分'
        ],
        'rows'  => [
            [
                '编号', '姓名', '性别', '手机', '备注3', '备注4', '备注5'
            ],
            [
                "AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG"
            ],
            [
                "AA", "BB", "CC", "DD", "EE", "FF", "GG"
            ]
        ]
    ], [
        'title' => '李四完成情况',
        'info'  => [
            '姓名:'    => '李四',
            '所在地：' => '北京市',
            '收货地址：'  => '河北',
            '联系方式：'  => '13900000000',
            '日期：'    => '2022-04-20',
            '备注1：'   => '优秀',
            '备注2：'   => '100分'
        ],
        'rows'  => [
            [
                '编号', '姓名', '性别', '手机', '备注3', '备注4', '备注5'
            ],
            [
                "1", "2", "3", "4", "5", "6", "7"
            ],
            [
                "11", "22", "33", "44", "55", "66", "77"
            ]
        ]
    ]
];
exportExecl($datas);

效果

其它样式设置，参考

// 创建一个新的工作表
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); 
$worksheet = $spreadsheet->getActiveSheet();

// 样式设置 - 字体
$worksheet -> getStyle('A1:G10') -> getFont()
    -> setBold(true) -> setName('Arial')
    -> setSize(10); //设置单元格A7:G10的字体样式
// 设置单元格A1的字体颜色
$worksheet -> getStyle('A1') -> getFont()
    -> getColor() -> setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); 

$spreadsheet -> getDefaultStyle()->getFont()->setName('微软雅黑'); //修改默认字体

// 样式设置 - 列宽
$worksheet -> getColumnDimension('A') -> setWidth(30); //设置A列宽度为30
$worksheet -> getColumnDimension('B') -> setAutoSize(true); //自动设置B列宽度
$worksheet -> getDefaultColumnDimension() -> setWidth(12); //设置默认列宽为12

//样式设置 - 行高
$worksheet -> getRowDimension('10') -> setRowHeight(100); //设置第10行高度为100
$worksheet -> getDefaultRowDimension() -> setRowHeight(15); //设置默认行高为15

//样式设置 - 水平、垂直居中
$styleArray = [
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
    ],
];
$worksheet -> getStyle('A1:G10') -> applyFromArray($styleArray);

//样式设置 - 边框
$styleArray = [
    'borders' => [
        'outline' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
            'color' => ['argb' => 'FF0000'],
        ],
        'inside' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
        ]
    ],
];
$worksheet -> getStyle('A1:G10') -> applyFromArray($styleArray);

//样式设置 - 单元格背景颜色
$worksheet -> getStyle('A1:G10') -> getFill()
    -> setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    -> getStartColor() -> setARGB('FF9800');

//样式设置 - 合并和拆分
$worksheet -> mergeCells('C3:G3'); //合并单元格
$worksheet -> unmergeCells('C3:G3'); //拆分单元格

//内容设置
$worksheet -> setCellValue('A1', 'xmsb'); //设置A1单元格内容为xmsb

//插入图片
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing -> setName('tupian');
$drawing -> setDescription('tupian');
$drawing -> setPath('./1.jpg');
$drawing -> setHeight(125);
$drawing -> setWorksheet($worksheet);
$drawing -> setCoordinates('A1');

读取sheet数据

// 实例化 Xlsx，如果想要对 Xls 文件进行操作，这里 new Xls() 即可

$reader = new PhpOffice\PhpSpreadsheet\Reader\Xlsx();

$spreadsheet = $reader->load("物料.xlsx");

$sheetCount = $spreadsheet->getSheetCount();

// 将Excel文件的每个sheet的数据转化为数组
$datas = [];
for($j = 0;$j<$sheetCount;$j++){
    $datas[] = $spreadsheet->getSheet($j)->toArray();
}

echo "<pre>";
var_export($datas);