DataBase/script/php/importTool/insertExcel.php

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

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

$type = array();
$i = 0;
while (isset($_POST["type{$i}"])) {
    $type[$i] = $_POST["type{$i}"];
    $i++;
}


$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));
}

$Category_List = [];
if (isset($_POST['file'])) {
    $file = $_POST['file'];
    $project_id = $_POST['projectId'];
    $project_name = $_POST['projectName'];
    $category_id = $_POST['categoryId'];
} else {
    header("Location: ../../../Upload.php#error");
}

$filename = "uploads/{$file}";
$importinfo = array();

$extension = pathinfo($file,PATHINFO_EXTENSION);
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($filename);
$sheetCount = $spreadsheet->getSheetCount();
$all = $spreadsheet->getSheetNames();

$sql = "DELETE FROM [Blueprint] WHERE [project_id] = ?";
$stmt = sqlsrv_query($conn, $sql, array($project_id));
$sql = "DELETE FROM [File_table] WHERE [project_id] = ?";
$stmt = sqlsrv_query($conn, $sql, array($project_id));

$sql =  "IF NOT EXISTS (SELECT [project_id] FROM [Project_Table] WHERE [project_id] = '{$project_id}')
            BEGIN
                INSERT INTO [dbo].[Project_Table] ([project_id],[project_name],[category_id])
                VALUES
                (?,?,?)
            END";
$stmt = sqlsrv_query($conn, $sql, array($project_id,$project_name,$category_id));

$sql = "SELECT [category_list] FROM [Category_List] WHERE [list_id] = (SELECT [list_id] FROM [Construction_Category] WHERE [category_id] = '{$category_id}');";
$fetchResult = sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array($fetchResult)) {
    $category_list = explode(",", $row[0]);
}

for ($s = 0; $s < $sheetCount; $s++) {
    if ($type[$s] != 'cancel') {

        // if (str_contains($all[$s], "_")) {
        //     $Category = explode("_", $all[$s])[1];
        // } else 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 =  "DECLARE @filetype_id INT;
        //         IF NOT EXISTS (SELECT [type_id] FROM [File_Category] WHERE [type_name] = '{$Category}')
        //         BEGIN
        //             SET @filetype_id = (SELECT [filetype_id] FROM [21000X].[dbo].[File_Type_Table] WHERE [filetype_name] = '{$type[$s]}')
        //             INSERT INTO [File_Category] ([type_name],[filetype_id]) VALUES ('{$Category}',@filetype_id);
        //         END";
        // $fetchResult = sqlsrv_query($conn, $sql);

        // $sql = "SELECT [type_id] FROM [File_Category] WHERE [type_name] = '{$Category}';";

        
        $type_id = $category_list[$s-1];
       
        // array_push($Category_List, $type_id);

        $sheet = $spreadsheet->getSheet($s);
        $cellCollection = $sheet->getCellCollection();
        $column = $cellCollection->getHighestRowAndColumn();
        

        for ($i = 2; $i <= $column['row']; $i++) { //行
            $row = array($project_id, $type_id);
            for ($j = 'A'; $j <= $column['column']; $j++) { //列
                $key = $j . $i;
                $value = $sheet->getCell($key)->getValue();
                array_push($row, $value);
            }
            if ($type[$s] == '[Blueprint]') {
                $sql = "INSERT INTO [Blueprint]
                            ([project_id]
                            ,[type_id]
                            ,[SN]
                            ,[blueprint_id]
                            ,[original_name]
                            ,[keyword]
                            ,[notes]
                            ,[filename])
                        VALUES
                            (?,?,?,?,?,?,?,?)";
            } else if ($type[$s] == '[File_Table]') {
                $sql = "INSERT INTO [File_Table]
                            ([project_id]
                            ,[type_id]
                            ,[SN]
                            ,[original_name]
                            ,[filename]
                            ,[keyword])
                        VALUES
                            (?,?,?,?,?,?)";
                array_push($row, '');
            }
            $stmt = sqlsrv_query($conn, $sql, $row);
            // echo "<br>";
        }
        // echo "<br>";
        // echo "<br>";
    }
}

// $Category_List_s = implode(",", $Category_List);
$sql = "INSERT INTO [Import_Log] ([UserID],[project_id]) VALUES (?,?)";
$fetchResult = sqlsrv_query($conn, $sql,array($_SESSION['UserID'],$project_id));
sqlsrv_close($conn);
header("Location: ../../../Upload.php?status=success");