php使用PhpSpreadsheet导出Excel表格的实例详解
编程狂想者
2024-04-02 17:21
短信预约 PHP-IT技能 免费直播动态提醒
这篇文章将为大家详细讲解有关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表格的实例详解的详细内容,更多请关注编程学习网其它相关文章!
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341