Plugin: PHPExcel, Export data into Excel

PHPExcel adalah php code untuk read, write dan create Excel document. Code nya boleh download di https://github.com/PHPOffice/PHPExcel. File yang penting nya di dalam folder Classes.

Permulaan untuk hasilkan file Excel.

// Include PHPExcel
require_once 'PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

Set properties.

// Set document properties
$objPHPExcel->getProperties()
            ->setCreator("Shahrul Nizam")
            ->setLastModifiedBy("Shahrul Nizam")
            ->setTitle("Senarai Harga")
            ->setSubject("Projek di http://shahrulnizam.com/project/")
            ->setDescription("Senarai Harga projek yang terdapat di shahrulnizam.com")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Projek PIC, Arduino, VB, Android, Web");
 
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Harga');

Masukkan data ke dalam Excel.

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', "No")
            ->setCellValue('B1', "Project No")
            ->setCellValue('C1', "Title")
            ->setCellValue('D1', "Price (RM)");
$objPHPExcel->getActiveSheet()
            ->getStyle('A1')
            ->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
$objPHPExcel->getActiveSheet()
            ->getStyle('B1')
            ->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()
            ->getStyle('C1')
            ->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()
            ->getStyle('D1')
            ->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

Masukkan data dari MySQL ke dalam Excel.

$no = 0; 
while($row = mysqli_fetch_array($result))
{
$no=$no+1;
$nom=$no+1;
$objPHPExcel->getActiveSheet()
            ->setCellValue('A'.$nom, $no)
            ->setCellValue('B'.$nom, $row["projectno"])
            ->setCellValue('C'.$nom, $row["title"])
            ->setCellValue('D'.$nom, $row["price"]);
$objPHPExcel->getActiveSheet()
            ->getStyle('A'.$nom)
            ->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 
$objPHPExcel->getActiveSheet()
            ->getStyle('B'.$nom)
            ->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()
            ->getStyle('D'.$nom)
            ->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}

Set Column Auto Size.

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);

Save file Excel.

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Senarai Harga Projek shahrulnizam.com.xlsx"');
header('Cache-Control: max-age=0');

Contoh link yang mempunyai fungsi Export data ke Excel adalah shahrulnizam.com/web/senarai-harga.php

Contoh video Export data ke file Excel.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.