Tuesday, August 11, 2020

Cara Mudah Export Data Ke Excel Dari Database MySQLi

Assalamualaikum Wr, Wb
Sesuai Judul Postingan kali ini akan membahas cara export data ke excel, tujuannya untuk memudahkan jika ingin mencetak atau untuk laporan. langsung saja ya

Persiapan

  1. Buat file index.html
  2. Buat file export_excel.html
  3. Buat code untuk export data ke Excel
  4. Jalankan File diBrowser, tahukan cara untuk menjalankan dibrowser
File index.html
<html>
<head>
	<title>Export Data Ke Excel Dengan PHP</title>
    <style type="text/css">
	body{
		font-family: sans-serif;
	}
	table{
		margin: 20px auto;
		border-collapse: collapse;
        width: 50%;
	}
	table th,
	table td{
		border: 1px solid #3c3c3c;
		padding: 10px 8px;
        background-color: burlywood;
        text-align: center;
 
	}
	a{
		background-color: burlywood;
		color: #fff;
		padding: 8px 10px;
		text-decoration: none;
		border-radius: 2px;
	}
	</style>
</head>
<body>
<div style="text-align: center;">
	<div><h1>Export Data Ke Excel Dengan PHP</h1></div>
	<div><a target="_blank" href="export_excel.php">EXPORT KE EXCEL</a></div>
 </div>

	<table>
		<tr>
			<th>No</th>
			<th>Nama</th>
			<th>Alamat</th>
			<th>No.Telp</th>
		</tr>
		<tr>
			<td>1</td>
			<td>Sigit</td>
			<td>Kudus</td>
			<td>0829121223</td>
		</tr>
		<tr>
			<td>2</td>
			<td>Joko</td>
			<td>Kudus</td>
			<td>08291212211</td>
		</tr>
		<tr>
			<td>3</td>
			<td>Jimi</td>
			<td>Medan</td>
			<td>0829121223</td>
		</tr>
		<tr>
			<td>4</td>
			<td>Inok</td>
			<td>Kudus</td>
			<td>02133324344</td>
		</tr>
		<tr>
			<td>5</td>
			<td>Muhammad Ulil</td>
			<td>Kudus</td>
			<td>08231111223</td>
		</tr>
		<tr>
			<td>6</td>
			<td>Waloni</td>
			<td>Kudus</td>
			<td>027373733</td>
		</tr>
		<tr>
			<td>7</td>
			<td>Ferdian</td>
			<td>Kudus</td>
			<td>0829121223</td>
		</tr>
		<tr>
			<td>8</td>
			<td>Tanta</td>
			<td>Kudus</td>
			<td>23432423423</td>
		</tr>
		<tr>
			<td>9</td>
			<td>Eko Wibowo</td>
			<td>Kudus</td>
			<td>0829234233</td>
		</tr>
		<tr>
			<td>10</td>
			<td>Ari Untung</td>
			<td>Kudus</td>
			<td>0829239323</td>
		</tr>
	</table>
</body>
</html>
File export_excel.html

<html>
<head>
	<title>Export Data Ke Excel Dengan PHP</title>
    <style type="text/css">
	body{
		font-family: sans-serif;
	}
	table{
		margin: 20px auto;
		border-collapse: collapse;
        width: 50%;
	}
	table th,
	table td{
		border: 1px solid #3c3c3c;
		padding: 10px 8px;
        background-color: burlywood;
        text-align: center;
 
	}
	a{
		background-color: burlywood;
		color: #fff;
		padding: 8px 10px;
		text-decoration: none;
		border-radius: 2px;
	}
	</style>
</head>
<body>
<div style="text-align: center;">
	<div><h1>Export Data Ke Excel Dengan PHP</h1></div>
	<div><a target="_blank" href="export_excel.php">EXPORT KE EXCEL</a></div>
 </div>

	<!-- Kode untuk export data ke Exel -->
	<?php
	header("Content-type: application/vnd-ms-excel");
	header("Content-Disposition: attachment; filename=Data_pegawai.xls");
	?>


	<table>
		<tr>
			<th>No</th>
			<th>Nama</th>
			<th>Alamat</th>
			<th>No.Telp</th>
		</tr>
		<tr>
			<td>1</td>
			<td>Sigit</td>
			<td>Kudus</td>
			<td>0829121223</td>
		</tr>
		<tr>
			<td>2</td>
			<td>Joko</td>
			<td>Kudus</td>
			<td>08291212211</td>
		</tr>
		<tr>
			<td>3</td>
			<td>Jimi</td>
			<td>Medan</td>
			<td>0829121223</td>
		</tr>
		<tr>
			<td>4</td>
			<td>Inok</td>
			<td>Kudus</td>
			<td>02133324344</td>
		</tr>
		<tr>
			<td>5</td>
			<td>Muhammad Ulil</td>
			<td>Kudus</td>
			<td>08231111223</td>
		</tr>
		<tr>
			<td>6</td>
			<td>Waloni</td>
			<td>Kudus</td>
			<td>027373733</td>
		</tr>
		<tr>
			<td>7</td>
			<td>Ferdian</td>
			<td>Kudus</td>
			<td>0829121223</td>
		</tr>
		<tr>
			<td>8</td>
			<td>Tanta</td>
			<td>Kudus</td>
			<td>23432423423</td>
		</tr>
		<tr>
			<td>9</td>
			<td>Eko Wibowo</td>
			<td>Kudus</td>
			<td>0829234233</td>
		</tr>
		<tr>
			<td>10</td>
			<td>Ari Untung</td>
			<td>Kudus</td>
			<td>0829239323</td>
		</tr>
	</table>
</body>
</html>
Code Untuk Export data Ke Excel

<?php
header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=Data_pegawai.xls");
?>
Hasil Bila di jalankan di browser

Bagaimana untuk implementasi dengan mysqli? langsung saja

Persiapan
  1. Buat Database baru untuk nama bebas
  2. Buat Tabel tb_aset
  3. Buat folder project baru masukan folder kedalam folder xampp/htdocs
  4. Buat File Koneksi.php didalam folder project
  5. Includkan File koneksi.php ke folder index.php
  6. Buat File index.php didalam folder project
  7. Buat File export_excel.php didalam folder project
Code Tabel tb_aset

CREATE TABLE `tb_aset` (
  `id` int(11) NOT NULL,
  `kode_qr` varchar(10) NOT NULL,
  `nama` varchar(100) NOT NULL,
  `nama_barang` varchar(100) NOT NULL,
  `tgl_pakai` date NOT NULL,
  `kondisi` varchar(100) NOT NULL,
  `status` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
File koneksi.php

<?php
//sesuaikan dengan confiq database 
$konek = mysqli_connect("localhost","root","","db_anda");

if(!$konek){
	echo "Koneksi Database Gagal...!!!";
}
File index.php

<!-- memanggil file koneksi -->
<?php include "koneksi.php" ?>

<div><a target="_blank" href="export_excel.php">EXPORT KE EXCEL</a></div>
<table class="table table-bordered table-striped " id="data">
    <tr>
        <th>No</th>
        <th>Nama Pegawai</th>
        <th>Nama Barang</th>
        <th>Status Pegawai</th>
        <th>Tanggal Pakai</th>
        <th>Kondisi</th>
    </tr>
    <?php

    $sql = mysqli_query($konek, "SELECT * FROM tb_aset");
    $no = 1;
    while ($d = mysqli_fetch_array($sql)) {
        echo "<tr>
            <td width='40px' align='center'>$no</td>
            <td><a href='data_aset.php?id=$d[id]&view=edit'>$d[nama]</a></td>
            <td>$d[nama_barang]</td>
            <td>$d[status]</td>
            <td>$d[tgl_pakai]</td>
            <td>$d[kondisi]</td>
        </tr>";
        $no++;
    }
    ?>
</table>
File export_excel.php

<?php
// memanggil file koneksi
include "koneksi.php";

// code untuk export ke excel
header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=Data_Pemakaiaset.xls");
?>

<table class="table table-bordered table-striped">
    <tr>
        <th>No</th>
        <th>Nama Pegawai</th>
        <th>Nama Barang</th>
        <th>Status Pegawai</th>
        <th>Tanggal Pakai</th>
        <th>Kondisi</th>
    </tr>
    <?php

    $sql = mysqli_query($konek, "SELECT * FROM tb_aset order by id asc");
    $no = 1;

    while ($d = mysqli_fetch_array($sql)) {
        echo "<tr>
                <td width='40px' align='center'>$no</td>
                <td>$d[nama]</td>
                <td>$d[nama_barang]</td>
                <td>$d[status]</td>
                <td>$d[tgl_pakai]</td>
                <td>$d[kondisi]</td>
            </tr>";
        $no++;
    }
    ?>
</table>

Silahkan Klik Button Export ke Excel, otomatis data akan terdonwload dengan format excel, Nah selesai sudah postingan cara export data ke excel, mudah bukan? sekian dulu ya mudahan mudahan bermanfaat 😀

Download Code Lengkapnya

No comments:
Tulis komentar

Category