Cara menggunakan export database ke excel

PHP

Cara Export Data dari Database ke Excel dengan PhpSpreadsheet

Rizaldi Maulidia Achmad

| Aug 7, 2021

ShareTweet

Halo teman-teman, lagi-lagi kita akan membahas yang namanya Export Excel. Kan sudah ada tutorial export excel ini? tentunya tutorial yang akan kita bahas memang sama tapi ada bedanya. Kali ini kita akan menggunakan librari PhpSpreadsheet. Alasan saya buat tutorial dengan librari ini, karena ada beberapa pengunjung blog ini yang mengeluhkan tutorial export excel sebelumnya error ketika di jalankan di PHP versi 7.3 ke atas. Maka dari itu tutorial ini lah jawabannya, silahkan simak step by stepnya.

PENTING, MOHON DIBACA TERLEBIH DAHULU
Tutorial ini menggunakan librari PhpSpreadsheet untuk proses export excelnya. Librari ini hanya mendukung PHP versi 7.2 ke atas. Bagi teman-teman yang menggunakan PHP versi 7.2 ke bawah, bisa baca tutorial ini : Cara Membuat Export Data Dari Database ke Excel dengan PHPExcel.

DEMO
Sebelum masuk ke tutorialnya. Mungkin ada yang mau lihat demonya terlebih dahulu. Silahkan klik link berikut untuk melihat demonya : Lihat Demo.

STEP 1 – PERSIAPAN
Untuk mempersiapkan tutorial ini, kita buat dulu sebuah folder baru dengan nama export_excel, lalu simpan pada folder xampp/htdocs/.

STEP 2 – Download PhpSpreadsheet
Pada langkah ini kita akan coba download librari PhpSpreadsheet nya dengan menggunakan Composer. Belum tau apa itu Composer? Cara installnya gimana? silahkan mampir dulu ke tutorial cara install composer berikut : Cara Install di Windows / Cara Install di Linux.

Setelah memastikan teman-teman sudah menginstall composernya, silahkan ikuti langkah berikut :

Buka Command Prompt atau CMD.
Ketik dan hit perintah berikut : cd C:\xampp\htdocs\export_excel.
Silahkan sesuaikan path tempat teman-teman menyimpan xampp nya.
Ketik dan hit perintah berikut : composer require phpoffice/phpspreadsheet. Tunggu proses download sampai selesai.
Setelah teman-teman mengeksekusi perintah composer diatas, maka seharusnya di folder export_excel nya terdapat file/folder baru berikut : folder vendor, file composer.json, file composer.lock.

Baca Juga:

Tutorial Codeigniter Bahasa Indonesia Lengkap Plus Source Code
Tutorial PHP Bahasa Indonesia Lengkap Plus Source Code
Cara Membuat Pencarian Data dan Menampilkannya ke Textbox dengan PHP AJAX dan MySQL
Cara Install Laravel dengan Composer di Ubuntu

STEP 3 – DATABASE
Pada step ini, kita akan membuat databasenya. ikuti langkah-langkah berikut ini :

Buat sebuah database dengan nama mynotescode.

Buat sebuah tabel dengan nama siswa. Struktur tabelnya sebagai berikut :

CREATE TABLE IF NOT EXISTS `siswa` (
  `nis` varchar(11) NOT NULL,
  `nama` varchar(50) NOT NULL,
  `jenis_kelamin` varchar(10) NOT NULL,
  `telp` varchar(15) NOT NULL,
  `alamat` text NOT NULL,
  PRIMARY KEY (`nis`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

STEP 4 – KONEKSI DATABASE
Pada step ini, kita akan membuat file php yang berfungsi untuk menghubungkan dengan database MySQL. Silahkan buat file koneksi.php, lalu simpan di folder xampp/htdocs/export_excel/. Berikut ini kodenya :

<?php
$host = "localhost"; // Nama hostnya
$username = "root"; // Username
$password = ""; // Password (Isi jika menggunakan password)
$database = "mynotescode"; // Nama databasenya

$connect = mysqli_connect($host, $username, $password, $database); // Koneksi ke MySQL
?>

STEP 5 – VIEW DATA
Langkah selanjutnya adalah membuat halaman untuk view data siswa dari database. Buat sebuah file dengan nama index.php, lalu simpan di folder xampp/htdocs/export_excel/. Berikut ini tampilan dan kodenya :

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    
    <title>Export Data ke Excel dengan PhpSpreadsheet</title>

  </head>
  <body>
    <h3>Data Siswa</h3>
    
    <a href="proses.php">Export ke Excel</a><br><br>
    
    <table border="1" cellpadding="5">
      <tr>
        <th>No</th>
        <th>NIS</th>
        <th>Nama</th>
        <th>Jenis Kelamin</th>
        <th>Telepon</th>
        <th>Alamat</th>
      </tr>
      <?php
      // Load file koneksi.php
      include "koneksi.php";

      // Buat query untuk menampilkan semua data siswa
      $sql = mysqli_query($connect, "SELECT * FROM siswa");

      $no = 1; // Untuk penomoran tabel, di awal set dengan 1
      while ($data = mysqli_fetch_array($sql)) { // Ambil semua data dari hasil eksekusi $sql
        echo "<tr>";
        echo "<td>".$no."</td>";
        echo "<td>".$data['nis']."</td>";
        echo "<td>".$data['nama']."</td>";
        echo "<td>".$data['jenis_kelamin']."</td>";
        echo "<td>".$data['telp']."</td>";
        echo "<td>".$data['alamat']."</td>";
        echo "</tr>";
        
        $no++; // Tambah 1 setiap kali looping
      }
      ?>
    </table>
  </body>
</html>

Saya akan coba jelaskan sedikit mengenai kode diatas.

include “koneksi.php”;
Kode tersebut berfungsi untuk me-load file koneksi.php.

$sql = mysqli_query($connect, “SELECT * FROM siswa”);
Kode tersebut berfungsi untuk melakukan query ke database dan mengeksekusinya. Pada cotoh diatas, kita akan melakukan query untuk menampilkan semua data dari tabel siswa.

$data = mysqli_fetch_array($sql)

Kode tersebut berfungsi untuk mengambil semua data hasil query dan menampung data-data tersebut di dalam sebuah array lalu menyimpannya ke dalam variabel $data. Kode tersebut saya simpan di dalam sebuah kode while(….) yang berfungsi untuk melakukan proses perulangan sampai data terkahir dengan tujuan agar kita bisa menampilkan datanya satu per satu.

echo "<tr>";
echo "<td>".$no."</td>";
echo "<td>".$data['nis']."</td>";
echo "<td>".$data['nama']."</td>";
echo "<td>".$data['jenis_kelamin']."</td>";
echo "<td>".$data['telp']."</td>";
echo "<td>".$data['alamat']."</td>";
echo "</tr>";

Pada kode diatas terdapat variabel $data digunakan untuk mengambil isi dari field-field yang ada pada tabel siswa di database mynotescode. Pada kode diatas juga ada kode yang berada pada tanda [‘……’], kode tersebut harus sama dengan nama field yang ada pada tabel siswa.

STEP 6 – EXPORT EXCEL
Terakhir, kita akan membuat fitur untuk export ke excel. Buat sebuah file dengan nama proses.php, lalu simpan pada folder xampp/htdocs/export_excel/. Berikut ini kodenya :

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

// Load file autoload.php
require 'vendor/autoload.php';

// Include librari PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Buat sebuah variabel untuk menampung pengaturan style dari header tabel
$style_col = [
    'font' => ['bold' => true], // Set font nya jadi bold
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
    ],
    'borders' => [
        'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border top dengan garis tipis
        'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  // Set border right dengan garis tipis
        'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border bottom dengan garis tipis
        'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] // Set border left dengan garis tipis
    ]
];

// Buat sebuah variabel untuk menampung pengaturan style dari isi tabel
$style_row = [
    'alignment' => [
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
    ],
    'borders' => [
        'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border top dengan garis tipis
        'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  // Set border right dengan garis tipis
        'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border bottom dengan garis tipis
        'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] // Set border left dengan garis tipis
    ]
];

$sheet->setCellValue('A1', "DATA SISWA"); // Set kolom A1 dengan tulisan "DATA SISWA"
$sheet->mergeCells('A1:F1'); // Set Merge Cell pada kolom A1 sampai F1
$sheet->getStyle('A1')->getFont()->setBold(true); // Set bold kolom A1
$sheet->getStyle('A1')->getFont()->setSize(15); // Set font size 15 untuk kolom A1

// Buat header tabel nya pada baris ke 3
$sheet->setCellValue('A3', "NO"); // Set kolom A3 dengan tulisan "NO"
$sheet->setCellValue('B3', "NIS"); // Set kolom B3 dengan tulisan "NIS"
$sheet->setCellValue('C3', "NAMA"); // Set kolom C3 dengan tulisan "NAMA"
$sheet->setCellValue('D3', "JENIS KELAMIN"); // Set kolom D3 dengan tulisan "JENIS KELAMIN"
$sheet->setCellValue('E3', "TELEPON"); // Set kolom E3 dengan tulisan "TELEPON"
$sheet->setCellValue('F3', "ALAMAT"); // Set kolom F3 dengan tulisan "ALAMAT"

// Apply style header yang telah kita buat tadi ke masing-masing kolom header
$sheet->getStyle('A3')->applyFromArray($style_col);
$sheet->getStyle('B3')->applyFromArray($style_col);
$sheet->getStyle('C3')->applyFromArray($style_col);
$sheet->getStyle('D3')->applyFromArray($style_col);
$sheet->getStyle('E3')->applyFromArray($style_col);
$sheet->getStyle('F3')->applyFromArray($style_col);

// Set height baris ke 1, 2 dan 3
$sheet->getRowDimension('1')->setRowHeight(20);
$sheet->getRowDimension('2')->setRowHeight(20);
$sheet->getRowDimension('3')->setRowHeight(20);

// Buat query untuk menampilkan semua data siswa
$sql = mysqli_query($connect, "SELECT * FROM siswa");

$no = 1; // Untuk penomoran tabel, di awal set dengan 1
$row = 4; // Set baris pertama untuk isi tabel adalah baris ke 4
while ($data = mysqli_fetch_array($sql)) { // Ambil semua data dari hasil eksekusi $sql
    $sheet->setCellValue('A' . $row, $no);
    $sheet->setCellValue('B' . $row, $data['nis']);
    $sheet->setCellValue('C' . $row, $data['nama']);
    $sheet->setCellValue('D' . $row, $data['jenis_kelamin']);

    // Khusus untuk no telepon. kita set type kolom nya jadi STRING
    $sheet->setCellValueExplicit('E' . $row, $data['telp'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

    $sheet->setCellValue('F' . $row, $data['alamat']);

    // Apply style row yang telah kita buat tadi ke masing-masing baris (isi tabel)
    $sheet->getStyle('A' . $row)->applyFromArray($style_row);
    $sheet->getStyle('B' . $row)->applyFromArray($style_row);
    $sheet->getStyle('C' . $row)->applyFromArray($style_row);
    $sheet->getStyle('D' . $row)->applyFromArray($style_row);
    $sheet->getStyle('E' . $row)->applyFromArray($style_row);
    $sheet->getStyle('F' . $row)->applyFromArray($style_row);

    $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); // Set text center untuk kolom No
    $sheet->getStyle('B' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT); // Set text left untuk kolom NIS

    $sheet->getRowDimension($row)->setRowHeight(20); // Set height tiap row

    $no++; // Tambah 1 setiap kali looping
    $row++; // Tambah 1 setiap kali looping
}

// Set width kolom
$sheet->getColumnDimension('A')->setWidth(5); // Set width kolom A
$sheet->getColumnDimension('B')->setWidth(15); // Set width kolom B
$sheet->getColumnDimension('C')->setWidth(25); // Set width kolom C
$sheet->getColumnDimension('D')->setWidth(20); // Set width kolom D
$sheet->getColumnDimension('E')->setWidth(15); // Set width kolom E
$sheet->getColumnDimension('F')->setWidth(30); // Set width kolom F

// Set orientasi kertas jadi LANDSCAPE
$sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);

// Set judul file excel nya
$sheet->setTitle("Laporan Data Siswa");

// Proses file excel
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="Data Siswa.xlsx"'); // Set nama file excel nya
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
?>

Saya akan menjelaskan sedikit mengenai kode diatas karena sebagian besar sudah saya jelaskan di setiap baris skrip diatas dengan komentar.

$sheet->setCellValue(‘A1’, “DATA SISWA”);
Seperti yang sudah saya jelaskan lewat komentar, skrip diatas berfungsi untuk menset kolom A1 dengan tulisan DATA SISWA. Mungkin ada yang bingung maksud dari A1 disini apa? lihat gambar berikut ini :

Yang saya beri tanda panah itulah yang dimaksud dengan kolom A1. Saya harap Anda paham maksud saya.

$sheet->mergeCells(‘A1:F1’);
Fungsi dari skrip diatas adalah untuk membuat “Merge Cells”. Dan arti dari ‘A1:F1’ itu adalah buat merge kolom dari kolom A1 sampai dengan kolom F1. Untuk lebih jelasnya lihat gambar dibawah :

Setelah di merge akan menghasilkan seperti gambar berikut :

Mungkin itu tambahan penjelasan dari skrip proses excel nya. untuk lebih lengkapnya mengenai fungsi apa saja yang disediakan oleh PhpSpreadsheet, Anda bisa membaca dokumentasinya langsung dengan mengklik link dokumentasi phpspreadsheet pada bagian Sumber Refrensi diakhir tutorial ini.

Apa itu export database?

Export Database Proses export adalah proses menyimpan database menjadi sebuah file dengan format sql. Proses ini juga bisa disebut proses backup database.

Bagaimana langkah langkah dalam export data dari Access ke format excel?

Persiapkan data atau tabel Access yang akan di Export..

klik menu External Data >> Export >> Excel..

Pilih file formatnya, spesifinya ceklis export data with format and layout supaya layot dan formatnya akan disesuaikan dengan data awal, ceklis open supaya terbuka otomatis ketika selesai di backup, selanjutnya klik >> Ok..