-
Notifications
You must be signed in to change notification settings - Fork 0
/
TSEUploadHandler.php
90 lines (78 loc) · 2.89 KB
/
TSEUploadHandler.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Move uploaded file into 'uploads' folder
if (isset($_FILES['Data'])) {
move_uploaded_file($_FILES['Data']['tmp_name'], "uploads/".$_FILES['Data']['name']);
$fileName = $_FILES['Data']['name'];
echo "Uploaded";
connectToDB($fileName);
} else{
echo "Failed upload";
}
function connectToDB($fileName){
//Connect to database
$servername = "localhost";
$username = "arcelormittal_arcelormittal";
$myfile = fopen("login.txt", "r") or die("Unable to open file!");
$password = fgets($myfile,filesize("login.txt") + 1);
fclose($myfile);
try{
$conn = new PDO("mysql:host=$servername; dbname=arcelormittal_arcelormittal", $username,
$password);
$conn -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
ClearTable($conn);
ReadData($conn,$fileName);
} catch (PDOException $e){
echo "Connection failed:" . $e->getMessage();
}
}
function ClearTable($conn) {
try{
$getInfo = $conn->prepare("DELETE FROM TSELocations");
$getInfo->execute();
} catch (PDOException $e){
echo "Error: " . $e->getMessage();
}
}
function ReadData($conn,$fileName){
// Get the spreadsheet from the uploads folder
$filePath = "uploads/" . $fileName;
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filePath);
// Select the sheet TSEInfo from the spreadsheet
$spreadsheet -> setActiveSheetIndexByName("TSEInfo");
// Find the number of rows in the sheet
$rows = $spreadsheet -> getActiveSheet() -> getHighestDataRow();
// Select a cell range from within the sheet
$dataArray = $spreadsheet -> getActiveSheet()
-> rangeToArray(
'A4:A'.($rows - 6),
NULL,
FALSE,
FALSE,
FALSE
);
// Iterate through rows containing data
for($row = 0; $row <= $rows - 10; $row++) {
$data = array();
// TSE
array_push($data, $dataArray[$row][0]);
// City
array_push($data, NULL);
// State
array_push($data, NULL);
// Latitude
array_push($data, NULL);
// Longitude
array_push($data, NULL);
// Insert into database
try{
$insertData = $conn->prepare("Insert TSELocations (TSE, City, State, Latitude, Longitude) values (?,?,?,?,?)");
$insertData->execute($data);
} catch(PDOException $e){
throw new Error($e->getMessage());
}
}
}
?>