-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathIndex.php
114 lines (93 loc) · 4.24 KB
/
Index.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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
<!DOCTYPE html>
<html>
<head>
<title>Excel Upload and Download</title>
</head>
<body>
<h2>Excel Upload and Download</h2>
<!-- Form for uploading Excel file -->
<form method="POST" enctype="multipart/form-data">
<input type="file" name="excel_file" required>
<input type="submit" name="upload" value="Upload">
</form>
<!-- Form for downloading filtered data -->
<form method="POST" action="download.php">
<h3>Filter Options</h3>
<label for="start_date">Start Date:</label>
<input type="date" name="start_date">
<br>
<label for="end_date">End Date:</label>
<input type="date" name="end_date">
<br>
<label for="employee_id">Employee ID:</label>
<input type="text" name="employee_id">
<br>
<input type="submit" name="filter" value="Download Filtered Data">
</form>
<?php
// Check if the form is submitted
if (isset($_POST['upload'])) {
$targetDir = "uploads/"; // Directory to store uploaded files
// Check if the uploads directory exists, if not, create it
if (!file_exists($targetDir)) {
mkdir($targetDir, 0777, true);
}
// Generate a unique filename
$targetFile = $targetDir . uniqid() . '_' . basename($_FILES['excel_file']['name']);
$uploadOk = 1;
$fileType = strtolower(pathinfo($targetFile, PATHINFO_EXTENSION));
// Check if the uploaded file is a valid Excel file
if ($fileType != "xls" && $fileType != "xlsx") {
echo "<p>Only Excel files are allowed.</p>";
$uploadOk = 0;
}
// Move the uploaded file to the target directory
if ($uploadOk) {
if (move_uploaded_file($_FILES['excel_file']['tmp_name'], $targetFile)) {
echo "<p>File uploaded successfully.</p>";
// Load the Excel file
require 'path_to_phpspreadsheet_library/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
$spreadsheet = IOFactory::load($targetFile);
$worksheet = $spreadsheet->getActiveSheet();
// Establish MySQL database connection
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Retrieve the highest row and column numbers
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
// Loop through each row of the worksheet
for ($row = 1; $row <= $highestRow; $row++) {
$rowData = $worksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, false);
// Prepare the values for MySQL insertion
$values = implode("','", $rowData[0]);
$values = "'" . $values . "'";
// Build the MySQL query to check if the row exists
$checkQuery = "SELECT * FROM your_table_name WHERE column1 = '{$rowData[0][0]}'";
// Execute the query
$result = $conn->query($checkQuery);
// If the row exists, update the existing row
if ($result->num_rows > 0) {
$updateQuery = "UPDATE your_table_name SET column2 = '{$rowData[0][1]}', column3 = '{$rowData[0][2]}' WHERE column1 = '{$rowData[0][0]}'";
$conn->query($updateQuery);
} else {
// If the row doesn't exist, insert a new row
$insertQuery = "INSERT INTO your_table_name (column1, column2, column3) VALUES (" . $values . ")";
$conn->query($insertQuery);
}
}
// Close the MySQL connection
$conn->close();
echo "<p>Excel data appended successfully to the MySQL table.</p>";
// Provide a download link to retrieve the table data in Excel format
echo "<p><a href='download.php'>Download Excel File</a></p>";
}
?>
</body>
</html>