Tutorial PHP Part 53 – Cara Membuat Report Excel di PHP menggunakan PhpSpreadsheet

July 2, 2019February 2, 2020 Aris Samsudin 0 Comments

Selamat datang di warung belajar, dalam tutorial kali ini kita akan melanjutkan pembahasan dari seri tutorial PHP.

Part 53 kita akan belajar membuat report excel dengan PHP menggunakan PhpSpreadsheet.

Mengenal PhpSpreadsheet

PhpSpreadsheet adalah library PHP yang dapat digunakan untuk membaca dan menuliskan file Excel, sehingga anda dapat memanfaatkan library ini untuk keperluan membaca ataupun menuliskan file Excel menggunakan perintah PHP.

Pada tutorial ini kita akan menggunakan library ini untuk menuliskan file Excel, yang artinya kita akan gunakan untuk membuat report berupa file excel.

PhpSpreadsheet memiliki dokumentasi resmi di alamat : https://phpspreadsheet.readthedocs.io

Dalam dokumentasi resminya PhpSpreadsheet dapat digunakan di versi PHP 5.6 ataupun yang lebih tinggi

Sebelum kita memulai untuk menggunakan library PhpSpreadsheet, anda harus pastikan dulu composer sudah terinstall di komputer teman – teman, untuk tutorial instalasi composer bisa lihat di tutorial kita sebelumnya : Cara Menggunakan Composer untuk Project PHP

kita asumsikan teman – teman sudah menginstall composer di komputer masing – masing, kita akan langsung memulai pembuatan report excel.

Install Library PhpSpreadsheet

Sebelum kita membuat reportnya kita perlu menginstall library PhpSpreadSheet, tetapi sebelum itu kita buat folder projectnya dulu.

karena dalam contoh ini kita menggunakan xampp, sehingga folder projectnya berada di C:/xampp/htdocs

contohnya kita membuat folder dengan nama reportexcel

Berikutnya silahkan buka terminal/command prompt, dan akses folder tersebut.

untuk menginstall library PhpSpreadsheet silahkan ketikkan perintah :

composer require phpoffice/phpspreadsheet

1	composer require phpoffice/phpspreadsheet

pastikan anda terkoneksi internet, lalu tekan enter.

Tunggu proses instalasinya selesai, seperti gambar diatas.

Membuat Report Excel Sederhana

Baik kita asumsikan teman – teman sudah menginstall library PhpSpreadSheet menggunakan composer.

Langkah berikutnya kita akan coba membuat report excel dengan tampilan yang sederhana dulu, agar kita mudah memahami perintah – perintahnya, baik langkah – langkahnya adalah sebagai berikut :

1. buat file dengan nama report.php di folder reportexcel yang tadi kita buat.

2. lalu kita isi file report.php dengan code seperti berikut ini :

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');
?>

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);

$writer->save('hello world.xlsx');

3. Simpan file tersebut, berikutnya coba akses file tersebut dengan alamat : localhost/reportexcel/report.php

4. Berikunya coba periksa di folder reportexcel, akan terdapat 1 file excel dengan nama hello world.xlsx, file itu adalah hasil export dari file report.php

5. silahkan buka file tesebut maka hasilnya adalah seperti berikut ini :

Keterangan :

Line 2 kita require file autoload.php di dalam folder vendor

Line 3 – 4 kita menggunakan namespace dari PhpSpreadsheet

Line 6 kita membuat object dengan nama $spreadsheet dengan menggunakan class Spreadsheet

Line 7 kita membuat variabel $sheet yang digunakan sebagai activesheet di file excel

Line 8 kita mengisi Cell A1 dengan text “Hello World !”

Line 10 kita render menjadi file Xlsx hasil dari object $spreadsheet dimana didalam object ini kita sudah membuat isi dari file excelnya

Line 11 kita melakukan penyimpanan / Export file excel dengan nama hello world.xlsx sehingga di folder project php kita terdapat file dengan nama hello world.xlsx

Membuat Database Siswa

Setelah anda belajar mengenai konsep membuat report excel dengan contoh sederhana di tutorial sebelumnya, berikutnya kita akan belajar untuk export data dari database menjadi bentuk excel.

Untuk langkah awal kita akan membuat database db_siswa, dibawah ini adalah perintah SQL untuk membuat databasenya.

-- phpMyAdmin SQL Dump
-- version 4.8.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Waktu pembuatan: 23 Jun 2019 pada 16.33
-- Versi server: 10.1.37-MariaDB
-- Versi PHP: 7.3.0

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_siswa`
--
CREATE DATABASE IF NOT EXISTS `db_siswa` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `db_siswa`;

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

--
-- Struktur dari tabel `tb_siswa`
--

CREATE TABLE `tb_siswa` (
  `id_siswa` int(11) NOT NULL,
  `nama` varchar(255) NOT NULL,
  `kelas` varchar(100) NOT NULL,
  `alamat` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data untuk tabel `tb_siswa`
--

INSERT INTO `tb_siswa` (`id_siswa`, `nama`, `kelas`, `alamat`) VALUES
(1, 'Budi Susanto', '1MM3', 'Sedati Gede'),
(2, 'Dita Anggraini', '1MM2', 'Rungkut'),
(3, 'Riska Nur Aini', '3MM1', 'Wonocolo');

--
-- Indexes for dumped tables
--

--
-- Indeks untuk tabel `tb_siswa`
--
ALTER TABLE `tb_siswa`
  ADD PRIMARY KEY (`id_siswa`);

--
-- AUTO_INCREMENT untuk tabel yang dibuang
--

--
-- AUTO_INCREMENT untuk tabel `tb_siswa`
--
ALTER TABLE `tb_siswa`
  MODIFY `id_siswa` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
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 */;

-- phpMyAdmin SQL Dump

-- version 4.8.4

-- https://www.phpmyadmin.net/

-- Host: 127.0.0.1

-- Waktu pembuatan: 23 Jun 2019 pada 16.33

-- Versi server: 10.1.37-MariaDB

-- Versi PHP: 7.3.0

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_siswa`

CREATE DATABASE IF NOT EXISTS `db_siswa` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

USE `db_siswa`;

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

-- Struktur dari tabel `tb_siswa`

CREATE TABLE `tb_siswa` (

`id_siswa` int(11) NOT NULL,

`nama` varchar(255) NOT NULL,

`kelas` varchar(100) NOT NULL,

`alamat` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data untuk tabel `tb_siswa`

INSERT INTO `tb_siswa` (`id_siswa`, `nama`, `kelas`, `alamat`) VALUES

(1, 'Budi Susanto', '1MM3', 'Sedati Gede'),

(2, 'Dita Anggraini', '1MM2', 'Rungkut'),

(3, 'Riska Nur Aini', '3MM1', 'Wonocolo');

-- Indexes for dumped tables

-- Indeks untuk tabel `tb_siswa`

ALTER TABLE `tb_siswa`

ADD PRIMARY KEY (`id_siswa`);

-- AUTO_INCREMENT untuk tabel yang dibuang

-- AUTO_INCREMENT untuk tabel `tb_siswa`

ALTER TABLE `tb_siswa`

MODIFY `id_siswa` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

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 */;

didalam database db_siswa, akan terdapat tabel tb_siswa, yang memiliki beberapa kolom, antara lain :

id_siswa (primary key – auto increment)
nama (varchar 255)
kelas (varchar 100)
alamat (varchar 255)

didalam tabel tb_siswa telah memiliki 3 siswa seperti gambar dibawah ini :

Baik kita asumsikan teman – teman sudah memiliki database siswa, dengan menggunakan perintah SQL diatas.

Membuat File Koneksi ke database

Berikutnya kita akan membuat file koneksi.php yang berisi perintah untuk koneksi ke database, untuk codenya adalah sebagai berikut :

<?php 
$host       = "localhost";
$user       = "root";
$password   = "";
$database   = "db_siswa";
$koneksi    = mysqli_connect($host, $user, $password, $database);
?>

<?php

$host = "localhost";

$user = "root";

$password = "";

$database = "db_siswa";

$koneksi = mysqli_connect($host, $user, $password, $database);

Keterangan :

Perintah diatas digunakan untuk koneksi ke database db_siswa yang sebelumnya telah kita buat.
$host dengan nilai “localhost” yang merupakan alamat server databasenya
$user dengan nilai “root” yang merupakan user dari databasenya
$password dengan nilai “” yang merupakan password untuk mengakses database, nilainya kosong karena default dari xampp untuk password user root adalah kosong
$database dengan nilai “db_siswa” yang merupakan nama dari database yang sebelumnya telah kita buat
Baris 6 adalah perintah untuk melakukan koneksi ke database, dan hasilnya disimpan di variabel $koneksi.

Membuat File reportdataexcel.php

Berikutnya kita akan membuat file reportdataexcel.php, yang digunakan untuk pembuatan fitur export data siswa berupa report excel, baik silahkan buat file dengan nama reportdataexcel.php dengan isi code sebagai berikut :

<?php
include('koneksi.php');
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'No');
$sheet->setCellValue('B1', 'Nama');
$sheet->setCellValue('C1', 'Kelas');
$sheet->setCellValue('D1', 'Alamat');

$query = mysqli_query($koneksi,"select * from tb_siswa");
$i = 2;
$no = 1;
while($row = mysqli_fetch_array($query))
{
	$sheet->setCellValue('A'.$i, $no++);
	$sheet->setCellValue('B'.$i, $row['nama']);
	$sheet->setCellValue('C'.$i, $row['kelas']);
	$sheet->setCellValue('D'.$i, $row['alamat']);	
	$i++;
}

$styleArray = [
			'borders' => [
				'allBorders' => [
					'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
				],
			],
		];
$i = $i - 1;
$sheet->getStyle('A1:D'.$i)->applyFromArray($styleArray);


$writer = new Xlsx($spreadsheet);
$writer->save('Report Data Siswa.xlsx');
?>

<?php

include('koneksi.php');

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'No');

$sheet->setCellValue('B1', 'Nama');

$sheet->setCellValue('C1', 'Kelas');

$sheet->setCellValue('D1', 'Alamat');

$query = mysqli_query($koneksi,"select * from tb_siswa");

$i = 2;

$no = 1;

while($row = mysqli_fetch_array($query))

{

$sheet->setCellValue('A'.$i, $no++);

$sheet->setCellValue('B'.$i, $row['nama']);

$sheet->setCellValue('C'.$i, $row['kelas']);

$sheet->setCellValue('D'.$i, $row['alamat']);

$i++;

}

$styleArray = [

'borders' => [

'allBorders' => [

'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,

];

$i = $i - 1;

$sheet->getStyle('A1:D'.$i)->applyFromArray($styleArray);

$writer = new Xlsx($spreadsheet);

$writer->save('Report Data Siswa.xlsx');

Setelah anda menyimpan file reportdataexcel.php, anda bisa mencoba untuk mengaksesnya menggunakan alamat : localhost/reportexcel/reportdataexcel.php, maka hasilnya akan mengexport file excel dengan nama Report Data Siswa.xlsx didalam folder reportexcel, jika dibuka hasilnya adalah seperti berikut ini :

Keterangan :

Line 2 kita include file koneksi.php yang berisi koneksi ke database

Line 3 kita require file autoload.php di dalam folder vendor

Line 4 – 5 kita menggunakan namespace dari PhpSpreadsheet

$spreadsheet = new Spreadsheet();

1	$spreadsheet = new Spreadsheet();

Line 7 kita membuat object dengan nama $spreadsheet dengan menggunakan class Spreadsheet

$sheet = $spreadsheet->getActiveSheet();

1	$sheet = $spreadsheet->getActiveSheet();

Line 8 kita membuat variabel $sheet yang digunakan sebagai activesheet di file excel

$sheet->setCellValue('A1', 'No');
$sheet->setCellValue('B1', 'Nama');
$sheet->setCellValue('C1', 'Kelas');
$sheet->setCellValue('D1', 'Alamat');

$sheet->setCellValue('A1', 'No');

$sheet->setCellValue('B1', 'Nama');

$sheet->setCellValue('C1', 'Kelas');

$sheet->setCellValue('D1', 'Alamat');

Line 9 – 12 kita membuat heading dari tabel dengan kolom No, Nama, Kelas, Alamat, header ini ditampilkan di cell A1,B1,C1,D1

$query = mysqli_query($koneksi,"select * from tb_siswa");

1	$query = mysqli_query($koneksi,"select * from tb_siswa");

Line 14 kita membuat query untuk mengambil data di tabel tb_siswa, dan hasilnya disimpan di variabel $query

$i = 2;

$i = 2;

Line 15 kita membuat variabel $i yang digunakan untuk menyimpan nomor awal cell, digunakan untuk awal menampilkan data di cell pada tabel, variabel $i bernilai 2, yang artinya data dari tabel tb_siswa nanti akan ditampilkan dimulai dari baris 2

$no = 1;

$no = 1;

Line 16 variabel $no digunakan untuk memberikan urutan nomor pada data yang ditampilkan

while($row = mysqli_fetch_array($query))

1	while($row = mysqli_fetch_array($query))

Line 17 kita extract hasil query menggunakan perintah while, dan disetiap perulangan datanya akan disimpan di variabel $row

$sheet->setCellValue('A'.$i, $no++);
$sheet->setCellValue('B'.$i, $row['nama']);
$sheet->setCellValue('C'.$i, $row['kelas']);
$sheet->setCellValue('D'.$i, $row['alamat']);	
$i++;

$sheet->setCellValue('A'.$i, $no++);

$sheet->setCellValue('B'.$i, $row['nama']);

$sheet->setCellValue('C'.$i, $row['kelas']);

$sheet->setCellValue('D'.$i, $row['alamat']);

$i++;

Line 19 – 22 kita menuliskan perintah untuk menuliskan data dari hasil query, dimulai dari Kolom A (untuk nomor), Kolom B (untuk nama), Kolom C (untuk kelas), Kolom D (untuk alamat), kolom akan dipadukan dengan nilai pada vaiabel $i, sehingga setiap data akan ditampilkan berurutan secara vertical, karena diakhir perulangan nilai variabel $i akan diincrement

$styleArray = [
			'borders' => [
				'allBorders' => [
					'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
				],
			],
		];

$styleArray = [

'borders' => [

'allBorders' => [

'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,

];

Line 26 – 32 kita membuat array $styleArray dimana didalamnya terdapat settingan border untuk cell.

Line 33 kita mengurangi nilai variabel $i dengan angka 1, agar border nanti akan digunakan hingga akhir perulangan data.

$sheet->getStyle('A1:D'.$i)->applyFromArray($styleArray);

1	$sheet->getStyle('A1:D'.$i)->applyFromArray($styleArray);

Line 34 kita menggunakan hasil array di Line 26 yaitu $styleArray yang berisi settingan border, agar digunakan dari Cell A1 hingga kolom D dengan baris diakhir perulangan data, dalam contoh diatas hingga D4.

$writer = new Xlsx($spreadsheet);

1	$writer = new Xlsx($spreadsheet);

Line 37 kita render menjadi file Xlsx hasil dari object $spreadsheet dimana didalam object ini kita sudah membuat isi dari file excelnya

$writer->save('Report Data Siswa.xlsx');

1	$writer->save('Report Data Siswa.xlsx');

Line 38 kita melakukan penyimpanan / Export file excel dengan nama Report Data Siswa.xlsx sehingga di folder project php kita terdapat file dengan nama Report Data Siswa.xlsx

Baik sekian dulu teman – teman tutorial kali ini, kita sudah belajar untuk membuat report excel dengan menggunakan PhpSpreadSheet, jika ada yang ingin didiskusikan silahkan di kolom komentar, sekian dulu sampai jumpa di tutorial selanjutnya.