Aplikasi Sederhana Export PHP Ke Excel

export php ke excel

Kali ini kami akan share bagaimana membuat aplikasi sederhana yaitu export PHP ke Excel. Script dalam file ini akan mengekspor data MySQLi Anda ke dokumen microsoft excel. Script ini menggunakan fungsi konten tajuk untuk menerjemahkan data MySQLi, lalu untuk dapat mengunduh sebagai format excel.

Langsung saja mari kita masuk kedalam pembahasan inti.

1. Buat database dengan nama db_excel kemudian masukkan struktur database di bawah ini.

-- phpMyAdmin SQL Dump
-- version 4.8.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Mar 20, 2019 at 04:08 PM
-- Server version: 10.1.37-MariaDB
-- PHP Version: 7.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `db_excel`
--

-- --------------------------------------------------------

--
-- Table structure for table `student`
--

CREATE TABLE `student` (
  `stud_id` int(11) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `year` varchar(10) NOT NULL,
  `section` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student`
--

INSERT INTO `student` (`stud_id`, `firstname`, `lastname`, `year`, `section`) VALUES
(1, 'Arman', 'Basir', 'II', 'A');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `student`
--
ALTER TABLE `student`
  ADD PRIMARY KEY (`stud_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `student`
--
ALTER TABLE `student`
  MODIFY `stud_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

2. Buat file dengan nama conn.php file ini sebagai koneksi database.

<?php
	$conn = mysqli_connect("localhost", "root", "", "db_excel");
	
	if(!$conn){
		die("Error: Failed to connect to database!");
	}
?>

3. Buat file dengan nama index.php file ini berfungsi sebagai form utama dalam aplikasi ini.

<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="UTF-8" name="viewport" content="width=device-width, initial-scale=1"/>
		<link rel="stylesheet" type="text/css" href="css/bootstrap.css" />
	</head>
<body>
	<nav class="navbar navbar-default">
		<div class="container-fluid">
			<a class="navbar-brand" href="https://jasawebsite.ooo">Jasawebsite</a>
		</div>
	</nav>
	<div class="col-md-3"></div>
	<div class="col-md-6 well">
		<h3 class="text-primary">PHP - Export Data table Ke Excel</h3>
		<hr style="border-top:1px dotted #ccc;"/>
		<button type="button" class="btn btn-success" data-toggle="modal" data-target="#form_modal"><span class="glyphicon glyphicon-plus"></span> Add student</button>
		<br /><br />
		<table class="table table-bordered">
			<thead class="alert-info">
				<tr>
					<th>Firstname</th>
					<th>Lastname</th>
					<th>Year</th>
					<th>Section</th>
				</tr>
			</thead>
			<tbody>
				<?php
					require 'conn.php'; 
					
					$query = mysqli_query($conn, "SELECT * FROM `student`") or die(mysqli_error());
					while($fetch = mysqli_fetch_array($query)){
				?>
				<tr>
					<td><?php echo $fetch['firstname']?></td>
					<td><?php echo $fetch['lastname']?></td>
					<td><?php echo $fetch['year']?></td>
					<td><?php echo $fetch['section']?></td>
				</tr>
				<?php
					}
				?>
			</tbody>
			<tfoot>
				<tr>
					<td><a class="btn btn-info" href="export_excel.php">Save as Excel</a></td>
					<td></td>
					<td></td>
					<td></td>
				</tr>
			</tfoot>
		</table>
	</div>
<div class="modal fade" id="form_modal" aria-hidden="true">
	<div class="modal-dialog">
		<div class="modal-content">
			<form method="POST" action="save_student.php">
				<div class="modal-header">
					<h3 class="modal-title">Add Student</h3>
				</div>
				<div class="modal-body">
					<div class="col-md-2"></div>
					<div class="col-md-8">
						<div class="form-group">
							<label>Firstname</label>
							<input type="text" name="firstname" class="form-control" required="required"/>
						</div>
						<div class="form-group">
							<label>Lastname</label>
							<input type="text" name="lastname" class="form-control" required="required"/>
						</div>
						<div class="form-group">
							<label>Year</label>
							<select name="year" class="form-control" required="required">
								<option value=""></option>
								<option value="I">I</option>
								<option value="II">II</option>
								<option value="III">III</option>
								<option value="IV">IV</option>
							</select>
						</div>
						<div class="form-group">
							<label>Section</label>
							<select name="section" class="form-control" required="required">
								<option value=""></option>
								<option value="A">A</option>
								<option value="B">B</option>
								<option value="C">C</option>
								<option value="D">D</option>
							</select>
						</div>
					</div>
				</div>
				<br style="clear:both;"/>
				<div class="modal-footer">
					<button type="button" class="btn btn-danger" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Close</button>
					<button class="btn btn-primary" name="save"><span class="glyphicon glyphicon-save"></span> Save</button>
				</div>
			</form>
		</div>
	</div>
</div>	
<script src="js/jquery-3.2.1.min.js"></script>	
<script src="js/bootstrap.js"></script>	
</body>	
</html>

4. Buat file dengan nama save_student.php script dalam ini berfungsi untuk menambahkan data kedatabase ketika button save di klik.

<?php
	require_once 'conn.php';
	
	if(ISSET($_POST['save'])){
		$firstname = $_POST['firstname'];
		$lastname = $_POST['lastname'];
		$year = $_POST['year'];
		$section = $_POST['section'];
		
		mysqli_query($conn, "INSERT INTO `student` VALUES('', '$firstname', '$lastname', '$year', '$section')") or die(mysqli_error());
		
		header("location: index.php");
	}
?>

5. Buat file dengan nama export_excel.php file berisi script untuk mengubah tabel html Anda menjadi file excel yang dapat dibaca saat button export diklik.

<?php
	header("Content-Type: application/xls");    
	header("Content-Disposition: attachment; filename=file.xls");  
	header("Pragma: no-cache"); 
	header("Expires: 0");

	require_once 'conn.php';
	
	$output = "";
	
	$output .="
		<table>
			<thead>
				<tr>
					<th>stud_id</th>
					<th>firstname</th>
					<th>lastname</th>
					<th>year</th>
					<th>section</th>
				</tr>
			<tbody>
	";
	
	$query = $conn->query("SELECT * FROM `student`") or die(mysqli_errno());
	while($fetch = $query->fetch_array()){
		
	$output .= "
				<tr>
					<td>".$fetch['stud_id']."</td>
					<td>".$fetch['firstname']."</td>
					<td>".$fetch['lastname']."</td>
					<td>".$fetch['year']."</td>
					<td>".$fetch['section']."</td>
				</tr>
	";
	}
	
	$output .="
			</tbody>
			
		</table>
	";
	
	echo $output;
	
	
?>

Kalian bisa mendownload filenya langsung pada link button dibawah ini.

Sekian share kali ini semoga apa yang kami share bermanfaat bagi anda, jika kalian belum paham silahkan tinggalkan komentar dan mulai berdiskusi dengan kami.

Leave a Reply

Your email address will not be published. Required fields are marked *