-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathImport1.php
123 lines (116 loc) · 5.6 KB
/
Import1.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
114
115
116
117
118
119
120
121
122
123
<?php
require 'core/init.php';
//require "..pear/PHP/CodeCoverage/autoload.php";
//require 'export.php';
$connect = mysqli_connect("localhost", "root", "", "user");
$output = '';
if(isset($_POST["import"]))
{
$extension = end(explode(".", $_FILES["excel"]["name"])); // For getting Extension of selected file
$allowed_extension = array("xls", "xlsx", "csv"); //allowed extension
if(in_array($extension, $allowed_extension)) //check selected file extension is present in allowed extension array
{
$id = $user_data['id'];
$sql = "DELETE FROM assets WHERE userid='$id'";
mysqli_query($connect, $sql);
$file = $_FILES["excel"]["tmp_name"]; // getting temporary source of excel file
include("PHPExcel/PHPExcel/IOFactory.php"); // Add PHPExcel Library in this code
$objPHPExcel = PHPExcel_IOFactory::load($file); // create object of PHPExcel library by using load() method and in load method define path of selected file
$worksheet = $objPHPExcel->getSheet(0);
$output .= "<label class='text-success'>Data Inserted</label><br /><table class='table table-bordered'>";
$highestRow = $worksheet->getHighestDataRow();
for($row=1; $row<=$highestRow; $row++)
{
$output .= "<tr>";
//$userid = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0, $row)->getValue());
$sno = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0, $row)->getValue());
$user = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());
$department = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(2, $row)->getValue());
$machinetype = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(3, $row)->getValue());
$manufacturer = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(4, $row)->getValue());
$machinesn = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(5, $row)->getValue());
$processor = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(6, $row)->getValue());
$os = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(7, $row)->getValue());
$systemspecs = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(8, $row)->getValue());
$dateofpurchase = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(9, $row)->getFormattedValue());
$warrantystatus = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(10, $row)->getFormattedValue());
$vendor = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(11, $row)->getValue());
$fromyear = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(12, $row)->getValue());
$totalyear = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(13, $row)->getFormattedValue());
$tyear = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(14, $row)->getFormattedValue());
$query = "INSERT INTO assets(sno,user,department,machinetype,manufacturer,machinesn,processor,os,systemspecs,dateofpurchase,warrantystatus,vendor,fromyear,totalyear,tyear) VALUES ('".$sno."', '".$user."','".$department."','".$machinetype."','".$manufacturer."','".$machinesn."','".$processor."','".$os."','".$systemspecs."','".$dateofpurchase."','".$warrantystatus."','".$vendor."','".$fromyear."','".$totalyear."','".$tyear."');";
mysqli_query($connect, $query);
$new="UPDATE assets SET userid = '$id' WHERE userid = 0";
mysqli_multi_query($connect, $new);
$sql1 = "DELETE FROM assets WHERE sno=0";
mysqli_multi_query($connect, $sql1);
$output .= '<td>'.$sno.'</td>';
$output .= '<td>'.$user.'</td>';
$output .= '<td>'.$department.'</td>';
$output .= '<td>'.$machinetype.'</td>';
$output .= '<td>'.$manufacturer.'</td>';
$output .= '<td>'.$machinesn.'</td>';
$output .= '<td>'.$processor.'</td>';
$output .= '<td>'.$os.'</td>';
$output .= '<td>'.$systemspecs.'</td>';
$output .= '<td>'.$dateofpurchase.'</td>';
$output .= '<td>'.$warrantystatus.'</td>';
$output .= '<td>'.$vendor.'</td>';
$output .= '<td>'.$fromyear.'</td>';
$output .= '<td>'.$totalyear.'</td>';
$output .= '<td>'.$tyear.'</td>';
$output .= '</tr>';
}
}
$output .= '</table>';
/*$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$spreadsheet->createSheet();
$spreadsheet->setActiveSheetIndex(1);*/
}
else
{
$output = '<label class="text-danger">Invalid File</label>'; //if non excel file then
}
?>
<html>
<head>
<title>Import Excel to Mysql using PHPExcel in PHP</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:700px;
border:1px solid #ccc;
background-color:#fff;
border-radius:5px;
margin-top:100px;
}
</style>
</head>
<body>
<div class="container box">
<h3 align="center">Import Excel to your Table</h3><br />
<form method="post" enctype="multipart/form-data">
<label>Select Excel File</label>
<input type="file" name="excel" />
<br />
<input type="submit" name="import" class="btn btn-info" value="Import" />
<a href="home.php">View Results</a>
</form>
<br />
<br />
<?php
echo $output;
?>
</div>
</body>
</html>