BIM-Monitor/script/php/read_excel.php

<?php
include("./API/equipment.php");
include("./connect_sql.php");
session_start();
if (0 < $_FILES['excel']['error']) {
    echo 'Error: ' . $_FILES['excel']['error'] . '<br>';
} else {
    $filepath = 'uploads/' . $_FILES['excel']['name'];
    move_uploaded_file($_FILES['excel']['tmp_name'], $filepath);
    $projectName = $_POST['projectName'];
    $constructionName = $_POST['constructionName'];
}

require '../../assets/vendor/autoload.php';

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

$excelData = array();
//$file = "./assets/excels/測試匯入.xlsx";
//$file = "./assets/excels/1110801-580C-(車站區).xls";
//$file = "./assets/excels/1110804-580C-(車站區)_unmerge.xls";
$file = $filepath;


$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(true);
$spreadsheet = $reader->load($file);
$sheetCount = $spreadsheet->getSheetCount();

$header = $spreadsheet->getSheetByName("表頭");
$date_value = $header->getCell('J2')->getValue();
$timestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($date_value);
$date = date('Y/m/d', $timestamp);

for ($s = 0; $s < $sheetCount; $s++) {
    $data = array();
    $sheet = $spreadsheet->getSheet($s);
    $sheetName = $spreadsheet->getSheetNames()[$s];
    if (array_search($sheetName, $not_read) === false) {
        //echo ("資料表:" . $sheetName . "   ");
        $cellCollection = $sheet->getCellCollection();
        $column = $cellCollection->getHighestRowAndColumn();
        $index_key = check_sheet($sheetName, $equipment_data);
        $type = $equipment_data[$index_key]["type"];    //儀器類型
        $startC = $equipment_data[$index_key]["startC"];    //起始欄位 EX:A
        $endC = $equipment_data[$index_key]["endC"];    //結尾欄位 EX:H
        $skipC = $equipment_data[$index_key]["skipC"];  //跳過欄位 EX:G
        $startR = $equipment_data[$index_key]["startR"];    //起始列數 EX:10
        $endR = $equipment_data[$index_key]["endR"] !== 0 ? $equipment_data[$index_key]["endR"] : $column['row'];   //結尾列數 若為0則總列數
        for ($i = $startR; $i <= $endR; $i++) {
            $skip = false;
            $row = array();
            $col_count = 0;
            array_push($row, $date);
            if (str_contains($sheetName, "SIS") || str_contains($sheetName, "SID")) {   //若為SIS取出儀器名稱
                $equipment_name = explode("區", $sheetName)[1];
                $equipment_name = str_replace(" ", "", $equipment_name);
                $col_count++;
                array_push($row, $equipment_name);
            }
            for ($j = $startC; $j <= $endC; $j++) {
                if ($skipC != $j) {
                    $col_count++;
                    $key = $j . $i;
                    $value = $sheet->getCell($key)->getCalculatedValue();
                    if ((str_contains($sheetName, "SIS") || str_contains($sheetName, "SID")) && $j == 'C' && $value == null) {  //若為SIS且C行為空，則跳過
                        $skip = true;
                        break;
                    } else if ($j == 'A' && (!str_contains($value, "-") || str_contains($value, "說明"))) { //若儀器資料不含'-'且含'說明'則跳過
                        $skip = true;
                        break;
                    } else if ($j == 'A' && str_contains($sheetName, "TI")) {
                        $value = str_replace(".", "", $value);
                    }
                    array_push($row, $value);
                }
            }
            if (!$skip) {
                array_push($data, $row);
                $row_merge = str_repeat(", ?", $col_count);
                $sql = "INSERT INTO [{$type}_Data] VALUES (? {$row_merge});";
                $stmt = sqlsrv_query($conn, $sql, $row);
                if ($stmt === false) {
                    if (($errors = sqlsrv_errors()) != null) {
                        foreach ($errors as $error) {
                            echo "SQLSTATE: " . $error['SQLSTATE'] . "<br />";
                            echo "code: " . $error['code'] . "<br />";
                            echo "message: " . $error['message'] . "<br />";
                            echo ($sql . "<br />");
                            var_dump($row);
                        }
                    }
                }
            }
        }



        $excelData[$sheetName] = $data;
    }
}
if (isset($_SESSION['UserID'])) {
    $user_id = $_SESSION['UserID'];
} else {
    $user_id = "0000";
}
$sql = "INSERT INTO [Update_Time] ([UserID]) VALUES (?);";
$stmt = sqlsrv_query($conn, $sql, array($_SESSION['UserID']));
if ($stmt === false) {
    if (($errors = sqlsrv_errors()) != null) {
        foreach ($errors as $error) {
            echo "SQLSTATE: " . $error['SQLSTATE'] . "<br />";
            echo "code: " . $error['code'] . "<br />";
            echo "message: " . $error['message'] . "<br />";
            echo ($sql . "<br />");
            var_dump($row);
        }
    }
}
header("Location: ../../Upload.php?status=success");

function check_sheet($sheetName, $equipment_data)
{
    $equipment_types = ["SIS", "SID", "SB", "SM", "TI", "VG", "RB", "OW", "SP", "EP", "SS", "EXM", "ETi", "BS"];
    foreach ($equipment_types as $type) {
        if (str_contains($sheetName, $type)) {
            $key = array_search($type, array_column($equipment_data, 'type'));
            return $key;
        }
    }
    return null;
}