Untitled

<?php

require_once __DIR__.'/vendor/autoload.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['file']['tmp_name'])) {
    $file = $_FILES['file']['tmp_name'];
    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
    $spreadsheet = $reader->load($file);
    $names = $spreadsheet->getSheetNames();
    foreach ($names as $name) {
        $sheet = $spreadsheet->getSheetByName($name);
        $rowArray = $sheet->toArray();
        foreach ($rowArray as $c => $col) {
            foreach ($col as $r => $value) {
                update($c + 1, $r + 1, $value, $sheet);
            }
        }
    }
    unlink($file);
    header(sprintf('Content-Disposition: attachment; filename="%s"', date('YmdHis-').(float)microtime().'.xlsx'));
    header('Content-Type: application/octet-stream');
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}

function update($c, $r, $value, $sheet)
{
    $cellName = getCell($c, $r);
    $clean = replace($value);

    if ($clean !== $value) {
        $type = getExcelType($clean);
        $cell = $sheet->getCell($cellName);

        $type = $type ?: $cell->getDataType();
        $cell->setValueExplicit($clean, $type);
    }
}

function getExcelType($value)
{
    if (preg_match('/^[1-9][0-9]*$/', $value)) {
        return \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC;
    } elseif (preg_match('/^0/', $value)) {
        return \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING;
    }

    return null;
}

function replace($value)
{
    return (string) preg_replace('/^\s*/', '', $value);
}

function getCell($col, $row)
{
    $second = (int)floor(($row-1)/26);
    $secChr = '';
    if ($second >= 1) {
        $secChr = chr(64 + $second);
    }

    $first = $row % 26;
    $firChr = chr(64 + (0 === $first ? 26 : $first));

    return $secChr . $firChr . $col;

    // php > echo chr(64+1);
    // A
    // php > echo chr(64+26);
    // Z
    // 1 -> A
    // 26 -> Z
    // 27 -> AA
    // 52 -> AZ
    // 53 -> BA
    // 78 -> BZ
}

?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>excel 不要文字削除</title>
</head>

<body>
<h1>不要文字削除フォーム</h1>
<p>ここからエクセルをアップロードすると不要な先頭の空白文字が削除できます<br>簡易スクリプトなのでファイルが大きすぎると動かないかもしれませんTT</p>
<form action="./index.php" method="post" enctype="multipart/form-data">
<input type="file" name="file" />
<input type="submit" />
</form>
</body>

</html>