DataBase/script/php/importTool/getExcel.php

<?php
require '../../../vendor/autoload.php';
include("../sql.php");

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$ajax = array();
$connectionInfo = array("Database" => "$dbname", "UID" => "$username", "PWD" => "$password", "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect($hostname, $connectionInfo);
if ($conn === false) {
    die(print_r(sqlsrv_errors(), true));
}
$fileinfo = array(array("計畫編號", "工程類別", "此計畫是否匯入過"));

$file = '00000.xlsx';
$Category_List = [];
if (isset($_GET['file'])) {
    $file = "uploads/{$_GET['file']}";
}

$isImport = '是';
$isImportArray = array();
$project_id = $_GET['projectId'];
$category_id = $_GET['categoryId'];
$sql = "SELECT TOP 1 [project_id] FROM [21000X].[dbo].[File_Table] WHERE [project_id] = '{$project_id}'";
$fetchResult = sqlsrv_query($conn, $sql);
$row = sqlsrv_fetch_array($fetchResult);
if (empty($row)) {
    $isImport = '否';
}
$sql = "SELECT [category_name] FROM [Construction_Category] WHERE [category_id] = '{$category_id}'";
$fetchResult = sqlsrv_query($conn, $sql);
$category = sqlsrv_fetch_array($fetchResult)[0];

array_push($fileinfo, array($project_id, $category, $isImport));
$importinfo = array(array("類別", "選擇檔案類型", "是否為新的類型"));

$ajax["0_匯入資訊"] = $fileinfo;

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadDataOnly(true);
$reader->setReadEmptyCells(false);
$spreadsheet = $reader->load($file);
$sheetCount = $spreadsheet->getSheetCount();
$all = $spreadsheet->getSheetNames();
for ($s = 0; $s < $sheetCount; $s++) {
    if (str_contains($all[$s], "_")) {
        $Category = explode("_", $all[$s])[1];
    } else if (str_contains($all[$s], ".")) {
        $Category = explode(".", $all[$s])[1];
    } else {
        $Category =  $all[$s];
    }
    $sql =  "IF NOT EXISTS (SELECT [type_id] FROM [File_Category] WHERE [type_name] = '{$Category}')
            BEGIN
                SELECT '是'
                END
            ELSE
                BEGIN
                    SELECT '否'
                END";
    $fetchResult = sqlsrv_query($conn, $sql);
    while ($row = sqlsrv_fetch_array($fetchResult)) {
        array_push($isImportArray, $row[0]);
    }
    $sql = "SELECT [type_id] FROM [File_Category] WHERE [type_name] = '{$Category}';";

    $fetchResult = sqlsrv_query($conn, $sql);
    $id = 0;
    while ($row = sqlsrv_fetch_array($fetchResult)) {
        $id = $row[0];
    }
    array_push($Category_List, $id);
}
$Category_List_s = implode(",", $Category_List);
$sql = "IF NOT EXISTS (SELECT [list_id] FROM [Category_List] WHERE [category_list] = '{$Category_List_s}')
            BEGIN
                INSERT INTO [Category_List] ([category_list])
                VALUES ('{$Category_List_s}')
            END";
$fetchResult = sqlsrv_query($conn, $sql);

for ($s = 0; $s < $sheetCount; $s++) {
    $normalCheck = "";
    $blueprintCheck = "";
    $cancelCheck = "";
    $sheet = $spreadsheet->getSheet($s);
    $sheetName = $spreadsheet->getSheetNames()[$s];
    if (str_contains($sheetName, '成果圖') || str_contains($sheetName, '設計圖')) {
        $blueprintCheck = "checked='checked'";
    } else {
        $normalCheck = "checked='checked'";
    }
    if (str_contains($sheetName, '目錄') || str_contains($sheetName, '注意')) {
        $cancelCheck = "checked='checked'";
    }

    $cellCollection = $sheet->getCellCollection();
    $column = $cellCollection->getHighestRowAndColumn();
    $data = array();

    for ($i = 1; $i <= $column['row']; $i++) { //行
        $row = array();
        for ($j = 'A'; $j <= $column['column']; $j++) { //列
            $key = $j . $i;
            $value = $sheet->getCell($key)->getValue();
            array_push($row, $value);
        }
        array_push($data, $row);
    }
    $ajax[$sheetName] = $data;
    array_push($importinfo, array($sheetName, "<input type='radio' id='normal{$s}' name='type{$s}' value='[File_table]' {$normalCheck}>
    <label for='normal{$s}'>一般檔案</label><br>
    <input type='radio' id='blueprint{$s}' name='type{$s}' value='[Blueprint]' {$blueprintCheck}>
    <label for='blueprint{$s}'>設計圖或成果圖</label><br>
    <input type='radio' id='canel{$s}' name='type{$s}' value='canel' {$cancelCheck}>
    <label for='canel{$s}'>不進行匯入</label>  ", $isImportArray[$s]));
}
$data["table"] = $ajax;
$data["info"] = $importinfo;

echo json_encode($data, JSON_UNESCAPED_UNICODE);