PHP Script Export Data MySQL to Excel Multiple Sheet

For the purposes of export data to multiple excel sheet, I am using Spreadsheet_WriteExcel class that is already available. Please you downloadi class here.
However, unfortunately the manual or instructions for using the class is minimal, so I had to dissect the class for any method or facility that can be used in this class.
In this article, I will try to peel a way to export data in MySQL into Excel file using Spreadsheet_WriteExcel class. For example, suppose we have a student data in MySQL as shown below

Next we want to export student data into Excel file with 2 sheets, the first sheet to display data male students, and the second sheet for female student data, as shown in the second image below.

Here is a PHP script that would generate an Excel file with 2 sheets when the script is executed in the browser.

export.php

<?php
  require_once('Worksheet.php');
  require_once('Workbook.php');

  // koneksi ke mysql
  mysql_connect('dbhost', 'dbuser', 'dbpass');
  mysql_select_db('dbname');

  // function untuk membuat header file excel
  function HeaderingExcel($filename) {
      header("Content-type: application/vnd.ms-excel");
      header("Content-Disposition: attachment; filename=$filename" );
      header("Expires: 0");
      header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
      header("Pragma: public");
      }

  // membuat header file excel dan nama filenya
  HeaderingExcel('mhs.xls');

  // membuat workbook baru
  $workbook = new Workbook("");
  // membuat worksheet ke-1 (data laki-laki)
  $worksheet1 =& $workbook->add_worksheet('Laki-laki');

  // setting format header tabel data
  $format =& $workbook->add_format();
  $format->set_align('vcenter');
  $format->set_align('center');
  $format->set_color('white');
  $format->set_bold();
  $format->set_italic();
  $format->set_pattern();
  $format->set_fg_color('red');

  // membuat header tabel dengan format
  $worksheet1->set_row(0, 15);
  $worksheet1->set_column(0, 0, 10);
  $worksheet1->write_string(0, 0, "NIM", $format);
  $worksheet1->set_column(0, 1, 30);
  $worksheet1->write_string(0, 1, "NAMA", $format);
  $worksheet1->set_column(0, 2, 20);
  $worksheet1->write_string(0, 2, "TINGGI BADAN (cm)", $format);

  // menampilkan data mhasiswa laki-laki

  $query = "SELECT * FROM mhs WHERE jns_kelamin = 'L'";
  $hasil = mysql_query($query);
  $baris = 1;
  while ($data = mysql_fetch_array($hasil))
  {
        $worksheet1->write_string($baris, 0, $data['nim']);
        $worksheet1->write_string($baris, 1, $data['nama']);
        $worksheet1->write_number($baris, 2, $data['tinggi']);
        $baris++;
  }

  // membuat worksheet ke-2 untuk data mhs perempuan
  $worksheet2 =& $workbook->add_worksheet('Perempuan');

  // membuat header tabel
  $worksheet2->set_row(0, 15);
  $worksheet2->set_column(0, 0, 10);
  $worksheet2->write_string(0, 0, "NIM", $format);
  $worksheet2->set_column(0, 1, 30);
  $worksheet2->write_string(0, 1, "NAMA", $format);
  $worksheet2->set_column(0, 2, 20);
  $worksheet2->write_string(0, 2, "TINGGI BADAN (cm)", $format);

  // menampilkan data mhasiswa perempuan

  $query = "SELECT * FROM mhs WHERE jns_kelamin = 'P'";
  $hasil = mysql_query($query);
  $baris = 1;
  while ($data = mysql_fetch_array($hasil))
  {
        $worksheet2->write_string($baris, 0, $data['nim']);
        $worksheet2->write_string($baris, 1, $data['nama']);
        $worksheet2->write_number($baris, 2, $data['tinggi']);
        $baris++;
  }

  $workbook->close();
?>

In the script above, there are a few commands that I need to explain here, namely:

$worksheet1 =& $workbook->add_worksheet('Laki-laki');

The above command is used to create a sheet or a worksheet with the name 'man' (worksheet1). Further orders associated with $ worksheet1 is always preceded by a $ worksheet1-> ... Similarly, when making a second worksheet (worksheet2).
The advantages of this class is able to format the text display and cellnya. In the above example, given the following command:

$format =& $workbook->add_format();
$format->set_align('vcenter');
$format->set_align('center');
$format->set_color('white');
$format->set_bold();
$format->set_italic();
$format->set_pattern();
$format->set_fg_color('red'); 

with the above command, we will make the text formatting properties: bold, italic, red background, centered vertical, horizontal centerd, and the font color white. The format will be given in the header of the cell which is the student data table. For example, suppose we want to format the cell header table 'NAME' with the above property on sheet 2, then the command:

$ worksheet2-> write_string (0, 1, "NAME", $ format);

where the parameter 0 and 1 are the coordinates of his cell.

The method set_row (x, y) is used to set the line width in the column x, y pixel wide. While the method set_column (x, y, z) to set the column width in pixels by z-y column. To write data into a cell, can use the method write_string () or write_number (). The difference between the two is if the method write_string () will be generated string in the cell, whereas write_number () data is written in the form of cell