php使用PhpSpreadsheet导出Excel表格的实例详解

这篇文章将为大家详细讲解有关php使用PhpSpreadsheet导出Excel表格的实例详解，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

PHP使用PhpSpreadsheet导出Excel表格实例详解

引入必要库

使用PhpSpreadsheet库导出Excel表格，首先需要引入该库。可以通过Composer依赖管理器：

composer require phpoffice/phpspreadsheet

实例化PhpSpreadsheet对象

引入库后，实例化PhpSpreadsheet对象：

use PhpOfficePhpSpreadsheetSpreadsheet;
$spreadsheet = new Spreadsheet();

创建表格数据

接下来，创建要导出的表格数据数组：

$data = [
    ["Name", "Age", "Address"],
    ["John", 25, "123 Main Street"],
    ["Mary", 30, "456 Elm Street"],
];

设置表头

设置表格表头：

foreach ($data[0] as $key => $value) {
    $spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($key + 1, 1, $value);
}

插入数据行

依次插入数据行：

for ($i = 1; $i < count($data); $i++) {
    foreach ($data[$i] as $key => $value) {
        $spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($key + 1, $i + 1, $value);
    }
}

设置列宽

根据内容自动调整列宽：

foreach (range("A", "C") as $columnID) {
    $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}

设置字体和边框

为表格设置字体和边框样式：

$spreadsheet->getActiveSheet()->getStyle("A1:C" . (count($data) + 1))->applyFromArray([
    "font" => [
        "bold" => true
    ],
    "borders" => [
        "allBorders" => [
            "borderStyle" => PhpOfficePhpSpreadsheetStyleBorder::BORDER_THIN
        ]
    ]
]);

设置和页眉页脚

添加和页眉页脚：

$spreadsheet->getActiveSheet()->setTitle("My Data");
$spreadsheet->getProperties()->setCreator("PHP Expert");
$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddHeader("Custom Header");
$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddFooter("Page &P of &N");

设置打印选项

设置打印选项，如纸张方向、缩放比例等：

$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PhpOfficePhpSpreadsheetWorksheetPageSetup::ORIENTATION_LANDSCAPE);
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);

导出Excel文件

导出Excel文件：

$writer = new PhpOfficePhpSpreadsheetWriterXlsx($spreadsheet);
$writer->save("my_data.xlsx");

完整示例代码

<?php
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;

// 引入库
require __DIR__ . "/vendor/autoload.php";

// 实例化对象
$spreadsheet = new Spreadsheet();

// 创建数据
$data = [
    ["Name", "Age", "Address"],
    ["John", 25, "123 Main Street"],
    ["Mary", 30, "456 Elm Street"]
];

// 设置表头
foreach ($data[0] as $key => $value) {
    $spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($key + 1, 1, $value);
}

// 插入数据行
for ($i = 1; $i < count($data); $i++) {
    foreach ($data[$i] as $key => $value) {
        $spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($key + 1, $i + 1, $value);
    }
}

// 设置列宽
foreach (range("A", "C") as $columnID) {
    $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}

// 设置字体和边框
$spreadsheet->getActiveSheet()->getStyle("A1:C" . (count($data) + 1))->applyFromArray([
    "font" => [
        "bold" => true
    ],
    "borders" => [
        "allBorders" => [
            "borderStyle" => PhpOfficePhpSpreadsheetStyleBorder::BORDER_THIN
        ]
    ]
]);

// 设置和页眉页脚
$spreadsheet->getActiveSheet()->setTitle("My Data");
$spreadsheet->getProperties()->setCreator("PHP Expert");
$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddHeader("Custom Header");
$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddFooter("Page &P of &N");

// 设置打印选项
$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PhpOfficePhpSpreadsheetWorksheetPageSetup::ORIENTATION_LANDSCAPE);
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);

// 导出文件
$writer = new Xlsx($spreadsheet);
$writer->save("my_data.xlsx");

以上就是php使用PhpSpreadsheet导出Excel表格的实例详解的详细内容，更多请关注编程学习网其它相关文章！