Cara menggunakan phpspreadsheet get cell value

Untuk keperluan data science atau yang lainnya, sering kita memerlukan tool untuk mengkonversi data dari format tertentu ke format lainnya. Tidak terkecuali data file Excel atau spreadsheet ke JSON.

Pada tutorial ini, saya akan mencoba memaparkan bagaimana cara membuat script PHP untuk mengkonversi data yang tersimpan di file Excel ke JSON, atau mengenerate JSON dari data yang bersumber dari Excel. Sebenarnya di internet terdapat beberapa situs yang menyediakan tool untuk konversi ini, namun jika kita bisa membuatnya sendiri tentunya akan bisa lakukan customize terhadap struktur JSON yang dihasilkan.

JSON (JavaScript Object Notation) sering digunakan untuk merepresentasikan sebuah entitas data karena memiliki kelebihan antara lain: strukturnya lebih sederhana, file size lebih kecil dibandingkan XML, dan tidak perlu menggunakan parser khusus untuk mengolahnya.

Adapun file data spreadsheet, Excel misalnya, merupakan format data terstruktur (structured data) dalam bentuk tabel yang cukup jamak di sekitar kita.

  • Library PHPSpreadSheet
  • Menyiapkan File Data Excel
  • Ide Pembuatan Script
  • Implementasi ke Script PHP

Library PHPSpreadSheet

Untuk keperluan konversi data dari file Excel ke JSON dengan PHP, kita membutuhkan library untuk membaca data Excelnya. Dalam hal ini, kita akan menggunakan library PHPSpreadSheet, di mana library ini merupakan pengembangan dari PHPExcel yang saat ini sudah discontinued sejak 5 tahun yang lalu.

Library PHPSpreadSheet dapat diinstall ke dalam direktori kerja dengan menggunakan composer, melalui perintah

composer require phpoffice/phpspreadsheet

atau apabila dibutuhkan mengunduh documentationnya juga, bisa ditambahkan parameter seperti berikut ini

composer require phpoffice/phpspreadsheet --prefer-source

Cara menggunakan phpspreadsheet get cell value
Proses instalasi PHPSpreadSheet via composer

Setelah proses instalasi selesai, secara otomatis akan muncul folder ‘vendor‘ di direktori web yang sudah kita siapkan.

Menyiapkan File Data Excel

Selanjutnya, misalkan kita memiliki file data Excel (nama file data.xlsx) seperti pada tampilan di bawah ini.

Cara menggunakan phpspreadsheet get cell value
Contoh data Excel

Dari data file Excel di atas, nantinya kita akan generate JSON dengan struktur sebagai berikut.

Cara menggunakan phpspreadsheet get cell value
Struktur JSON yang akan dihasilkan

Ide Pembuatan Script

Ide dasar membuat script PHP untuk mengenerate JSON dari file Excel adalah sebagai berikut:

  1. Setiap nama kolom tabel dari Excel terlebih dahulu dicollect.

    Kolom tabel Excel ini nanti digunakan mengkonstruksi array asosiatif yang akan dibuat di langkah selanjutnya. Untuk memudahkan komputasi, nama-nama kolom tabel ini nanti disimpan ke dalam sebuah array.

    Sehingga untuk contoh data Excel di atas, dari proses ini akan diperoleh sebuah array:

    Cara menggunakan phpspreadsheet get cell value

  2. Lakukan pembacaan setiap baris/record data Excel.

    Baris/record data Excel ini nantinya akan dikonstruksi menjadi sebuah array asosiatif dengan key-nya berupa nama kolomnya (didapat dari langkah 1).

    Hasil dari langkah ini akan diperoleh sebuah array asosiatif sebagai berikut (untuk data yang diambil dari record pertama):

    Cara menggunakan phpspreadsheet get cell value

  3. Array asosiatif yang dihasilkan dari setiap baris data Excel (dari langkah 2), digabung menjadi satu menjadi sebuah array yang berukuran lebih besar.

    Hasil dari langkah ini akan diperoleh struktur data array berbentuk seperti di bawah ini.

    Cara menggunakan phpspreadsheet get cell value

  4. Array yang dihasilkan dari langkah 3 barulah dikonversi ke JSON menggunakan function json_encode().

Implementasi ke Script PHP

Berdasarkan ide penyelesaian di atas, kita coba implementasikan ke dalam script PHP.

setReadDataOnly(TRUE);

// setting nama file yg akan dibaca
$spreadsheet = $reader->load("data.xlsx");

// data worksheet yg akan dibaca ada di active sheet
$worksheet = $spreadsheet->getActiveSheet();

// mendapatkan maks nomor baris data
$highestRow = $worksheet->getHighestRow();
// mendapatkan maks kolom data
$highestColumn = $worksheet->getHighestColumn();

// mendapatkan nama-nama kolom data 
// membaca value yang ada di cell: A1, B1, ..., E1
// dan simpan ke dalam array $colsName
$colsName = array();
for($col='A'; $col<=$highestColumn; $col++){
	$colsName[] =  $worksheet->getCell($col . 1)->getValue();
}

// inisialisasi array untuk menampung semua data
$dataAll = array();

// proses membaca data baris-perbaris 
// dimulai dari baris ke-2, karena baris ke-1 berisi nama kolom tabel

for($row=2; $row<=$highestRow; $row++){
	// inisialisasi array untuk data perbaris
	$dataRow = array();

	$i = 0;
	// untuk setiap baris data, baca value tiap kolom cell
        // misal untuk baris ke-2, cell yang dibaca: A2, B2, ..., E2
        // misal untuk baris ke-3, cell yang dibaca: A3, B3, ..., E3
        // dst ...
	for($col='A'; $col<=$highestColumn; $col++){
		// setiap value digabung menjadi satu
		// dan tambahkan ke array $dataRow
		$dataRow += array($colsName[$i] => $worksheet->getCell($col . $row)->getValue());
		$i++;
	}
	// setelah didapat data array perbaris
	// tambahkan ke $dataAll 
	$dataAll[] = $dataRow;
}

// convert ke json lalu tampilkan
echo json_encode($dataAll);

?>

Penjelasan script:

  • Perintah $worksheet->getHighestRow() digunakan untuk mendapatkan nomor baris terbesar data sheet yang akan dibaca. Nomor baris terbesar ini nanti akan digunakan untuk proses looping (sebagai batas akhir looping) ketika membaca setiap baris/record data Excel. Dalam contoh di atas, nomor baris terbesar yang didapatkan adalah 10 (lihat data Excelnya)
  • Perintah $worksheet->getHighestColumn() digunakan untuk mendapatkan nama kolom cell terbesar data sheet. Nama kolom cell terbesar ini nantinya akan digunakan untuk proses looping ketika mengcollect seluruh nama kolom dari tabel data. Pada contoh yang diberikan, kolom cell terbesar adalah E (lihat data Excelnya)
  • Perintah $worksheet->getCell(...)->getValue() digunakan untuk membaca value pada cell tertentu. Misalkan untuk membaca value di cell A1, maka perintah yang diberikan adalah:
    $worksheet->getCell('A1')->getValue()

Mudah bukan membuatnya? Dari ide di atas, silakan dikembangkan/ dicustom sendiri sesuai kebutuhan Anda.

Semoga bisa dipahami dan bermanfaat.

Bagikan artikel ini jika bermanfaat !