270 lines
9.2 KiB
PHP
270 lines
9.2 KiB
PHP
<?php
|
||
|
||
namespace Leantime\Domain\Bom\Services;
|
||
|
||
/**
|
||
* 轻量 Excel/CSV 解析器，不依赖 PhpSpreadsheet。
|
||
*
|
||
* 支持：
|
||
* - .xlsx（解压 sharedStrings.xml + sheet1.xml，按列字母映射单元格）
|
||
* - .csv（fgetcsv，自动探测 BOM）
|
||
* - .xls 老格式不支持，报错提示另存为 .xlsx 或 .csv
|
||
*
|
||
* 返回：首行为表头，后续行为数据行。
|
||
*/
|
||
class Excel
|
||
{
|
||
/**
|
||
* @return array{header:array<int,string>,rows:array<int,array<int,string>>}
|
||
*/
|
||
public function parse(string $path): array
|
||
{
|
||
$ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));
|
||
|
||
return match ($ext) {
|
||
'xlsx' => $this->parseXlsx($path),
|
||
'csv' => $this->parseCsv($path),
|
||
default => throw new \InvalidArgumentException('仅支持 .xlsx 或 .csv 文件'),
|
||
};
|
||
}
|
||
|
||
private function parseCsv(string $path): array
|
||
{
|
||
$handle = fopen($path, 'r');
|
||
if ($handle === false) {
|
||
throw new \InvalidArgumentException('无法打开 CSV 文件');
|
||
}
|
||
|
||
// 去掉 UTF-8 BOM
|
||
$first = fgets($handle);
|
||
if ($first === false) {
|
||
fclose($handle);
|
||
|
||
return ['header' => [], 'rows' => []];
|
||
}
|
||
if (str_starts_with($first, "\xEF\xBB\xBF")) {
|
||
$first = substr($first, 3);
|
||
}
|
||
|
||
$header = str_getcsv($first);
|
||
$rows = [];
|
||
while (($line = fgetcsv($handle)) !== false) {
|
||
// 跳过空行
|
||
if (count($line) === 1 && trim($line[0]) === '') {
|
||
continue;
|
||
}
|
||
$rows[] = $line;
|
||
}
|
||
fclose($handle);
|
||
|
||
return ['header' => $header, 'rows' => $rows];
|
||
}
|
||
|
||
private function parseXlsx(string $path): array
|
||
{
|
||
$zip = new \ZipArchive;
|
||
if ($zip->open($path) !== true) {
|
||
throw new \InvalidArgumentException('无法打开 .xlsx 文件');
|
||
}
|
||
|
||
// sharedStrings
|
||
$shared = [];
|
||
$ss = $zip->getFromName('xl/sharedStrings.xml');
|
||
if ($ss !== false) {
|
||
$xml = new \SimpleXMLElement($ss);
|
||
foreach ($xml->si as $si) {
|
||
$text = '';
|
||
foreach ($si->t ?? [] as $t) {
|
||
$text .= (string) $t;
|
||
}
|
||
$shared[] = $text;
|
||
}
|
||
}
|
||
|
||
// 第一个工作表
|
||
$sheet = $zip->getFromName('xl/worksheets/sheet1.xml');
|
||
if ($sheet === false) {
|
||
$zip->close();
|
||
throw new \InvalidArgumentException('未找到工作表');
|
||
}
|
||
|
||
$xml = new \SimpleXMLElement($sheet);
|
||
$rows = [];
|
||
$header = [];
|
||
|
||
foreach ($xml->sheetData->row as $rowEl) {
|
||
$cells = [];
|
||
foreach ($rowEl->c as $c) {
|
||
$ref = (string) $c['r']; // 如 "A1", "B2"
|
||
$col = $this->colIndex($ref);
|
||
$type = (string) $c['t'];
|
||
|
||
if ($type === 's') {
|
||
// 共享字符串：v 是 sharedStrings 索引
|
||
$v = (string) ($c->v ?? '');
|
||
$cells[$col] = $shared[(int) $v] ?? '';
|
||
} elseif ($type === 'inlineStr') {
|
||
// 内联字符串（现代 Excel/WPS 常用）：值在 <is><t> 里，富文本可能有多个 <t>
|
||
$text = '';
|
||
foreach ($c->is->t ?? [] as $t) {
|
||
$text .= (string) $t;
|
||
}
|
||
$cells[$col] = $text;
|
||
} else {
|
||
// 数字/布尔/公式结果等
|
||
$cells[$col] = (string) ($c->v ?? '');
|
||
}
|
||
}
|
||
$line = [];
|
||
$max = empty($cells) ? 0 : max(array_keys($cells));
|
||
for ($i = 0; $i <= $max; $i++) {
|
||
$line[$i] = $cells[$i] ?? '';
|
||
}
|
||
$rows[] = $line;
|
||
}
|
||
$zip->close();
|
||
|
||
if (empty($rows)) {
|
||
return ['header' => [], 'rows' => []];
|
||
}
|
||
|
||
$header = array_shift($rows);
|
||
|
||
// 规范化表头：去空白
|
||
$header = array_map(fn ($h) => trim((string) $h), $header);
|
||
|
||
return ['header' => $header, 'rows' => $rows];
|
||
}
|
||
|
||
/** "A"->0, "B"->1, "AA"->26 ... */
|
||
private function colIndex(string $ref): int
|
||
{
|
||
$letters = preg_replace('/\d/', '', $ref);
|
||
$idx = 0;
|
||
foreach (str_split($letters) as $ch) {
|
||
$idx = $idx * 26 + (ord($ch) - ord('A') + 1);
|
||
}
|
||
|
||
return $idx - 1;
|
||
}
|
||
|
||
// ---- 导出（生成真正的 .xlsx，不依赖 PhpSpreadsheet） ----
|
||
|
||
/**
|
||
* 生成 .xlsx 文件，返回临时文件路径。
|
||
*
|
||
* @param array<int,string> $header 表头
|
||
* @param array<int,array<int,string>> $rows 数据行
|
||
*/
|
||
public function exportXlsx(string $sheetName, array $header, array $rows): string
|
||
{
|
||
$sheetName = mb_substr(preg_replace('/[\\/?*\[\]:]/', ' ', $sheetName) ?: 'Sheet1', 0, 31) ?: 'Sheet1';
|
||
|
||
$rowsXml = '';
|
||
$rowNum = 1;
|
||
|
||
$rowsXml .= $this->buildRow($rowNum++, $header);
|
||
|
||
foreach ($rows as $row) {
|
||
$cells = [];
|
||
foreach ($row as $i => $v) {
|
||
$cells[$i] = is_string($v) ? $v : (string) $v;
|
||
}
|
||
$rowsXml .= $this->buildRow($rowNum++, $cells);
|
||
}
|
||
|
||
$zip = new \ZipArchive;
|
||
$tmp = tempnam(sys_get_temp_dir(), 'bomexp');
|
||
@unlink($tmp);
|
||
$path = $tmp.'.xlsx';
|
||
if ($zip->open($path, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) !== true) {
|
||
throw new \RuntimeException('无法创建导出文件');
|
||
}
|
||
|
||
$zip->addFromString('[Content_Types].xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
||
.'<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
|
||
.'<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
|
||
.'<Default Extension="xml" ContentType="application/xml"/>'
|
||
.'<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
|
||
.'<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
|
||
.'</Types>');
|
||
|
||
$zip->addFromString('_rels/.rels', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
||
.'<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
|
||
.'<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
|
||
.'</Relationships>');
|
||
|
||
$zip->addFromString('xl/workbook.xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
||
.'<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
|
||
.'<sheets><sheet name="'.$this->xml($sheetName).'" sheetId="1" r:id="rId1"/></sheets>'
|
||
.'</workbook>');
|
||
|
||
$zip->addFromString('xl/_rels/workbook.xml.rels', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
||
.'<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
|
||
.'<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>'
|
||
.'</Relationships>');
|
||
|
||
$zip->addFromString('xl/worksheets/sheet1.xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
||
.'<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>'
|
||
.$rowsXml
|
||
.'</sheetData></worksheet>');
|
||
|
||
$zip->close();
|
||
|
||
return $path;
|
||
}
|
||
|
||
/**
|
||
* 生成 .csv 文件，返回临时文件路径（带 UTF-8 BOM，Excel 打开不乱码）。
|
||
*/
|
||
public function exportCsv(array $header, array $rows): string
|
||
{
|
||
$tmp = tempnam(sys_get_temp_dir(), 'bomexp');
|
||
$path = $tmp.'.csv';
|
||
$fh = fopen($path, 'w');
|
||
if ($fh === false) {
|
||
throw new \RuntimeException('无法创建导出文件');
|
||
}
|
||
fwrite($fh, "\xEF\xBB\xBF");
|
||
fputcsv($fh, $header);
|
||
foreach ($rows as $row) {
|
||
fputcsv($fh, array_map(fn ($v) => (string) $v, $row));
|
||
}
|
||
fclose($fh);
|
||
|
||
return $path;
|
||
}
|
||
|
||
private function buildRow(int $rowNum, array $cells): string
|
||
{
|
||
$xml = '<row r="'.$rowNum.'">';
|
||
$col = 0;
|
||
foreach ($cells as $v) {
|
||
$ref = $this->colLetter($col).$rowNum;
|
||
$xml .= '<c r="'.$ref.'" t="inlineStr"><is><t>'.$this->xml((string) $v).'</t></is></c>';
|
||
$col++;
|
||
}
|
||
$xml .= '</row>';
|
||
|
||
return $xml;
|
||
}
|
||
|
||
private function colLetter(int $idx): string
|
||
{
|
||
$s = '';
|
||
$idx++;
|
||
while ($idx > 0) {
|
||
$idx--;
|
||
$s = chr(ord('A') + ($idx % 26)).$s;
|
||
$idx = intdiv($idx, 26);
|
||
}
|
||
|
||
return $s;
|
||
}
|
||
|
||
private function xml(string $s): string
|
||
{
|
||
return htmlspecialchars($s, ENT_QUOTES | ENT_XML1, 'UTF-8');
|
||
}
|
||
}
|