Di tulisan ini saya akan menjelaskan bagaimana menambahkan data dari ms. excel (.xls atau .xlsx) ke database MySQL dengan menggunakan PHPExcel.
Pernah berfikir anda sudah memiliki banyak baris data namun ingin menginput ke database dalam sekali unggah? Dengan PHPExcel, anda dapat membaca, menampilkan, menulis, membuat, bahkan menginsert data ke database dari sebuah file excel.
Skema yang akan dibuat adalah:
- Mengunggah file excel yang sudah berisikan beberapa contoh data, kemudian
- Membaca banyaknya contoh data, lalu
- Melakukan aksi insert data ke tabel database.
Unduh PHPExcel
Unduh dahulu tools berikut: PHPExcel
Database
Buat contoh database “exceldb”
1 |
CREATE DATABASE exceldb; |
Kemudian buat contoh tabel “user”
1 2 3 4 5 6 7 8 |
CREATE TABLE user ( id int NOT NULL AUTO_INCREMENT, username varchar(50) NOT NULL, password varchar(50), fullname varchar(50), PRIMARY KEY (id) ); |
Kode
Buat file excel.php di htdocs/webdir anda, isikan dengan kode berikut:
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 |
<?PHP // Tangkap data dari post form if (isset($_POST["import"])) { // Koneksi database $host = 'localhost'; $db_user = 'root'; $db_password = ''; $db = 'exceldb'; $conn = mysql_connect($host, $db_user, $db_password) or die(mysql_error()); mysql_select_db($db) or die(mysql_error()); // Include ke path PHPExcel require 'phpexcel/PHPExcel.php'; require_once 'phpexcel/PHPExcel/IOFactory.php'; // Path file upload move_uploaded_file($_FILES['file']['tmp_name'], './' . $_FILES['file']['name']); $path = $_FILES['file']['name']; // Load PHPExcel $objPHPExcel = PHPExcel_IOFactory::load($path); foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) { $worksheetTitle = $worksheet->getTitle(); $highestRow = $worksheet->getHighestRow(); $highestColumn = $worksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); $nrColumns = ord($highestColumn) - 64; echo "<br>Worksheet " . $worksheetTitle . " memiliki "; echo $nrColumns . ' kolom (A-' . $highestColumn . ') '; echo ' dan ' . $highestRow . ' baris.'; echo '<br>Data: <table border="1"><tr>'; for ($row = 1; $row <= $highestRow; ++$row) { echo '<tr>'; for ($col = 0; $col < $highestColumnIndex; ++$col) { $cell = $worksheet->getCellByColumnAndRow($col, $row); $val = $cell->getValue(); $dataType = PHPExcel_Cell_DataType::dataTypeForValue($val); echo '<td>' . $val . '<br>(Typ ' . $dataType . ')</td>'; } echo '</tr>'; } echo '</table>'; } // Proses perulangan baris file excel yang diupload for ($row = 2; $row <= $highestRow; ++$row) { $val = array(); for ($col = 0; $col < $highestColumnIndex; ++$col) { $cell = $worksheet->getCellByColumnAndRow($col, $row); $val[] = $cell->getValue(); } // Skip data jika username sudah ada $findMatch = @mysql_query("SELECT username FROM user WHERE username = '" . $val[0] . "'"); $numMatch = @mysql_num_rows($findMatch); if ($numMatch == 0) { // Buat query insert per-baris data ke tabel user $sql = "INSERT INTO user VALUES ('','" . $val[0] . "','" . $val[1] . "','" . $val[2] . "')"; // Tampikan query echo"<pre>"; print_r($sql); echo"</pre>"; // Querykan mysql_query($sql); } } // Hapus file excel ketika data sudah masuk ke tabel @unlink($_FILES['file']['name']); } ?> <html> <body> <!-- Buat form upload file excel --> <form enctype="multipart/form-data" method="post" role="form" action="excel.php"> <div class="form-group"> <label for="exampleInputFile">Upload File</label> <input type="file" name="file" id="file" size="150"> <p class="help-block">Hanya file Excel/CSV diperbolehkan</p> </div> <button type="submit" class="btn btn-default" name="import" value="Import">Upload</button> </form> </body> </html> |
File Excel
Setelah itu, buat contoh file excel lalu simpan:
Unggah
Unggah file excel yang sudah dibuat sebelumnya ke script PHP yang sudah anda buat di http://localhost/excel.php
Tertarik untuk mencoba? 🙂