README

使用方法

ZipArchive，基于PHP的 Zip 扩展

简单的XLSX输出生成器

支持PHP 8.0+
接受UTF-8编码的输入
支持多工作表
支持单元格样式
支持合并单元格
支持图像（png, jpg, jpeg）
支持图表（折线图、柱状图、饼图）
支持写入大量行的工作表（查看大量数据示例）
支持行列固定（查看行列固定示例）
支持行筛选（查看筛选示例）
支持单元格宽度和行高（查看行高列宽示例）
支持超链接（查看超链接示例）

安装

composer require pfurak/xlsx-writer

简单的PHP CLI示例

use XLSXWriter\Export;

$sheetName = "sheet1";
$maxRow = 3;
$maxCol = 10;

$export = new Export("export.xlsx");

for($row = 0; $row < $maxRow; $row++) {
    for($col = 0; $col < $maxCol; $col++) {
        $value = $row + $col;
        $export->addField($sheetName, $row, $col, $value);
    }
}

$export->saveOnDisk(__DIR__);
unset($export);

简单的PHP浏览器示例

use XLSXWriter\Export;

$sheetName = "sheet1";
$maxRow = 3;
$maxCol = 10;

$export = new Export("export.xlsx");

for($row = 0; $row < $maxRow; $row++) {
    for($col = 0; $col < $maxCol; $col++) {
        $value = $row + $col;
        $export->addField($sheetName, $row, $col, $value);
    }
}

$export->download();
unset($export);

简单的样式示例

use XLSXWriter\Export;
use XLSXWriter\Fill;
use XLSXWriter\Font;
use XLSXWriter\Border;

$fill = new Fill("solid", ["from" => "string", "value" => "black"]);
$font = new Font([
    "size"      =>  12,
    "name"      =>  "Helvetica",
    "bold"      =>  true,
    "italic"    =>  true,
    "underline" =>  true,
    "color"     =>  [
        "from"  =>  "hex",
        "value" =>  "6EF0EB"
    ]
]);

$alignment = [
    "horizontal"    =>  "center",
    "vertical"      =>  "center",
    "wrapText"      =>  "1",
    "textRotation"  =>  "90"
];

$borderColor = [
    "from" => "rgb",
    "value" => [
        "red" => 0,
        "green" => 125,
        "blue" => 125
    ]
];

$border = new Border([
    "left"  =>  [
        "style" =>  "medium",
        "color" =>  $borderColor
    ],
    "diagonal"  =>  [
        "style" =>  "medium",
        "color" =>  $borderColor
    ],
    "diagonalUp"    =>  true,
    "diagonalDown"  =>  true,
]);

$style = [
    "fill"          =>  $fill,
    "font"          =>  $font,
    "alignment"     =>  $alignment,
    "numberFormat"  =>  "##\%",
    "border"        =>  $border
]

$sheetName = "sheet1";
$maxRow = 3;
$maxCol = 10;

$export = new Export("export.xlsx");

for($row = 0; $row < $maxRow; $row++) {
    for($col = 0; $col < $maxCol; $col++) {
        $value = $row + $col;
        $export->addField($sheetName, $row, $col, $value, $style);
    }
}

$export->saveOnDisk(__DIR__);
unset($export);

简单的图像示例

use XLSXWriter\Export;

$imgDir = "./images/";
$images = [[
    "file"  =>  $imgDir . "img.png",
    "row"   =>  0,
    "col"   =>  0
], [
    "file"  =>  $imgDir . "img_2.png",
    "row"   =>  0,
    "col"   =>  2
], [
    "file"  =>  $imgDir . "img_3.png",
    "row"   =>  0,
    "col"   =>  4
]];

$sheetName = "sheet1";

$export = new Export("export.xlsx");
foreach($images as $image) {
    $export->addImage($sheetName, $image['file'], $image['row'], $image['col']);
}

$export->saveOnDisk(__DIR__);
unset($export);

简单的折线图示例

use XLSXWriter\Export;
use XLSXWriter\Chart\LineChart\LineChart;
use XLSXWriter\Chart\LineChart\Line;

$sheetName = "sheet1";
$style = ["numberFormat" => "#,##0.00\ [\$Ft-hu-HU]"];

$EURToHUF = ["EUR TO HUF", 380.71, 381.92, 382.10, 382.56, 378.65];
$USDToHUF = ["USD TO HUF", 362.15, 362.15, 362.15, 365.00, 360.87]; 
$dates = ["Date", "2022.05.06.", "2022.05.07.", "2022.05.08.", "2022.05.09.", "2022.05.10."];

$data = [$EURToHUF, $USDToHUF, $dates];
$maxRow = count($data);

$export = new Export("export.xlsx");

$chart = new LineChart();
$chart->setTitle("EUR (€) & USD ($) to HUF (Ft)");
$chart->setPosition($maxRow, 0);
$chart->setXAxis($sheetName, $maxRow - 1, 1, count($data[$maxRow - 1]) - 1);
$chart->setShowLegend(true);
$chart->setLegendPosition('b'); // bottom

for($row = 0; $row < $maxRow; $row++) {
    $dataRow = $data[$row];
    $maxData = count($dataRow);
    for($col = 0; $col < $maxData; $col++) {
        $value = $dataRow[$col];
        $s = $col > 0 && $row < $maxRow - 1 ? $style : [];
        $export->addField($sheetName, $row, $col, $value, $s);
    }

    if($row < $maxRow - 1) {
        $line = new Line();
        $line->setTitle($sheetName, $row, 0);
        $line->setData($sheetName, $row, 1, $maxData - 1);
        $chart->addLine($line);
    }
}

$export->addChart($sheetName, $chart);
$export->saveOnDisk(__DIR__);
unset($export);

简单的柱状图示例

use XLSXWriter\Export;
use XLSXWriter\Chart\BarChart\BarChart;
use XLSXWriter\Chart\BarChart\Bar;

$sheetName = "sheet1";
$style = ["numberFormat" => "#,##0.00\ [\$Ft-hu-HU]"];

$EURToHUF = ["EUR TO HUF", 380.71, 381.92, 382.10, 382.56, 378.65];
$USDToHUF = ["USD TO HUF", 362.15, 362.15, 362.15, 365.00, 360.87]; 
$dates = ["Date", "2022.05.06.", "2022.05.07.", "2022.05.08.", "2022.05.09.", "2022.05.10."];

$data = [$EURToHUF, $USDToHUF, $dates];
$maxRow = count($data);

$export = new Export($output);

$chart = new BarChart();
$chart->setTitle("EUR (€) & USD ($) to HUF (Ft)");
$chart->setPosition($maxRow, 0);
$chart->setXAxis($sheetName, $maxRow - 1, 1, count($data[$maxRow - 1]) - 1);
$chart->setShowLegend(true);
$chart->setLegendPosition('r'); // right

for($row = 0; $row < $maxRow; $row++) {
    $dataRow = $data[$row];
    $maxData = count($dataRow);
    for($col = 0; $col < $maxData; $col++) {
        $value = $dataRow[$col];
        $s = $col > 0 && $row < $maxRow - 1 ? $style : [];
        $export->addField($sheetName, $row, $col, $value, $s);
    }

    if($row < $maxRow - 1) {
        $bar = new Bar();
        $bar->setTitle($sheetName, $row, 0);
        $bar->setData($sheetName, $row, 1, $maxData - 1);
        $chart->addBar($bar);
    }
}

$export->addChart($sheetName, $chart);
$export->saveOnDisk(__DIR__);
unset($export);

简单的饼图示例

use XLSXWriter\Export;
use XLSXWriter\Chart\PieChart\PieChart;

$sheetName = "sheet_1";
$legends = ["PHP", "React", "nodeJS", "TypeScript", "vue.js", "next.js"];
$data    = [   45,      40,       35,           30,       25,        20];

$export = new Export($output);

$row = 0;
for($i = 0; $i < count($legends); $i++) {
    $col = 0;
    $legend = $legends[$i];
    $legendData = $data[$i] ?? 0;

    $export->addField($sheetName, $row, $col++, $legend);
    $export->addField($sheetName, $row, $col, $legendData);

    $row++;
}

$chart = new PieChart();
$chart->setTitle("Lovers of the languages");
$chart->setPosition($row, 0);
$chart->setShowLegend(true);
$chart->setLegendPosition('b');
$chart->setLegend($sheetName, 0, $row - 1, 0, 0);
$chart->setPies($sheetName, 0, $row - 1, 1, 1);

$export->addChart($sheetName, $chart);

$sheetName = "sheet_2";
$row = 0;
$col = 0;
foreach($legends as $legend) {
    $export->addField($sheetName, $row, $col++, $legend);
}

$row++;
$col = 0;
foreach($data as $legendData) {
    $export->addField($sheetName, $row, $col++, $legendData);
}
$row++;

$chart = new PieChart();
$chart->setTitle("Lovers of the languages");
$chart->setPosition($row, 0);
$chart->setShowLegend(true);
$chart->setLegendPosition('r');

$chart->setLegend($sheetName, 0, 0, 0, count($legends) - 1);
$chart->setPies($sheetName, 1, 1, 0, count($data) - 1);

$export->addChart($sheetName, $chart);

$export->saveOnDisk(__DIR__);
unset($export);

支持的样式

字体

关键属性	默认值	描述
`size`	`12`	字体大小（以PT为单位）
`name`	`Calibri`	字体名称
`color`		字体颜色（见颜色）
`vertalign`		`"superscript"` 或 `"subscript"`
`bold`	`false`	`true` 或 `false`
`italic`	`false`	`true` 或 `false`
`underline`	`false`	`true` 或 `false`
`strike`	`false`	`true` 或 `false`

Alignment

关键属性	默认值	描述
`horizontal`	`left`	`"left"` 或 `"center"` 或 `"right"`
`vertical`	`bottom`	`"top"` 或 `"center"` 或 `"bottom"`
`wrapText`	`false`	`true` 或 `false`
`textRotation`	`0`	Integer

Fill

关键属性	默认值	描述
`pattern`	`solid`	见支持的填充图案类型
`color`		填充颜色（见颜色）

支持的填充图案类型

none
gray125
solid
darkGray
mediumGray
lightGray
gray0625
darkHorizontal
darkVertical
darkDown
darkUp
darkGrid
darkTrellis
lightHorizontal
lightVertical
lightDown
lightUp
lightGrid
lightTrellis

Border

关键属性	描述
`top`	`[ "style" => 见支持的边框样式, "color" => see colors ]`
`bottom`	`[ "style" => 见支持的边框样式, "color" => see colors ]`
`left`	`[ "style" => 见支持的边框样式, "color" => see colors ]`
`right`	`[ "style" => 见支持的边框样式, "color" => see colors ]`
`diagonal`	`[ "style" => 见支持的边框样式, "color" => see colors ]`
`diagonalUp`	`true` 或 `false` if diagonal key setted
`diagonalDown`	`true` 或 `false` if diagonal key setted

支持的边框样式

dashDotDot
dashDot
dashed
dotted
hair
mediumDashDotDot
mediumDashDot
mediumDashed
medium
slantDashDot
thick
thin

数字格式

关键属性	描述
`numberFormat`	自定义数字格式

颜色

关键属性	描述
`from`	`string` 或 `hex` 或 `rgb`
`value`	`string` if `from` is `string` or `hex`
	`array` if `from` is `rgb`

颜色示例

$red = [
    "from"  =>  "hex",
    "value" =>  "FF0000"
];

$green = [
    "from"  =>  "rgb",
    "value" => [
        "red"   =>  0,
        "green" =>  255,
        "blue"  =>  0
    ]
];

$blue = [
    "from"  =>  "string",
    "value" =>  "blue"
];

支持的图像扩展

jpg
jpeg
png

支持的图表

折线图
柱状图
饼图

pfurak / xlsx-writer

维护者

详细信息

README

使用方法

简单的XLSX输出生成器

安装

简单的PHP CLI示例

简单的PHP浏览器示例

简单的样式示例

简单的图像示例

简单的折线图示例

简单的柱状图示例

简单的饼图示例

支持的样式

字体

Alignment

Fill

支持的填充图案类型

Border

支持的边框样式

数字格式

颜色

颜色示例

支持的图像扩展

支持的图表