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();
$errorLists = 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';
if (isset($_GET['file'])) {
    $file = "uploads/{$_GET['file']}";
}
$test = '';
$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];

$sql = "SELECT [project_name] FROM [Project_Table] WHERE [project_id] = '{$project_id}'";
$fetchResult = sqlsrv_query($conn, $sql);
$project_name = sqlsrv_fetch_array($fetchResult)[0];

array_push($fileinfo, array($project_id, $project_name, $category, $isImport));
$importinfo = array(array("類別", "選擇檔案類型"/*, "是否為新的類型"*/));
$statuses["匯入資訊"] = "info";
$extension = pathinfo($file, PATHINFO_EXTENSION);
$ajax["匯入資訊"] = $fileinfo;
if ('csv' == $extension) {
    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
} else if ('xls' == $extension) {
    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
} else
    $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]);
    }
}

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

    $cellCollection = $sheet->getCellCollection();
    $column = $cellCollection->getHighestRowAndColumn();
    $data = array();
    $error = "";
    for ($i = 1; $i <= $column['row']; $i++) { //行        
        $row = array();
        for ($j = 'A'; $j <= $column['column']; $j++) { //列
            $key = $j . $i;
            $value = $sheet->getCell($key)->getValue();
            if ($value === null & $status != 'error') {
                $status = 'warning';
                //$test = "key: ".$key." value: ".$value;
            }
            if ($fileCol != '0') {
                if ($j == $fileCol & $i > 1) {
                    if (!is_file('../../../assets/建置資料/' . $category . '/' . $project_id . '-' . $project_name . $value)) {
                        $error = $error.','.$i-2; //扣掉欄位名稱及從零開始計算
                        $status = 'error';
                    }
                }
            }

            array_push($row, $value);
        }
        array_push($data, $row);
    }
    $ajax[$sheetName] = $data;
    $statuses[$sheetName] = $status;
    //array_push($statuses, $status);
    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='cancel{$s}' name='type{$s}' value='cancel' {$cancelCheck}>
    <label for='cancel{$s}'>不進行匯入</label>  "/*, $isImportArray[$s]*/));
    $errorLists[$sheetName] = $error;
}
$data["table"] = $ajax;
$data["info"] = $importinfo;
$data["statuses"] = $statuses;
$data["errorLists"] = $errorLists;
echo json_encode($data, JSON_UNESCAPED_UNICODE);
sqlsrv_close($conn);