利用composer安装PhpSpreadsheet到项目目录
https:
composer require phpoffice/phpspreadsheet
导出代码
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
include_once "./vendor/autoload.php";
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);
}
function handlingMultipleSheet($spreadsheet, $sheetIndex, $data)
{
$spreadsheet->createSheet();
$activeSheet = $spreadsheet->setActiveSheetIndex($sheetIndex);
$activeSheet->setCellValue('A1', $data['title']);
$rows = $data['rows'][0];
$columnsCount = count($rows);
$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) {
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++;
}
}
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);
$worksheet -> getStyle('A1') -> getFont()
-> getColor() -> setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
$spreadsheet -> getDefaultStyle()->getFont()->setName('微软雅黑');
$worksheet -> getColumnDimension('A') -> setWidth(30);
$worksheet -> getColumnDimension('B') -> setAutoSize(true);
$worksheet -> getDefaultColumnDimension() -> setWidth(12);
$worksheet -> getRowDimension('10') -> setRowHeight(100);
$worksheet -> getDefaultRowDimension() -> setRowHeight(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');
$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数据
$reader = new PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load("物料.xlsx");
$sheetCount = $spreadsheet->getSheetCount();
$datas = [];
for($j = 0;$j<$sheetCount;$j++){
$datas[] = $spreadsheet->getSheet($j)->toArray();
}
echo "<pre>";
var_export($datas);
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· .NET10 - 预览版1新功能体验(一)