Export Excel File With PhpSpreadsheet

สวัสดีครับ ต่อจากตอนที่แล้ว ที่เราได้แนะนำการอ่านข้อมูลจากไฟล์ Excel และ Import เข้าฐานข้อมูล MySQL
https://monkeywebstudio.com/read-excel-file-with-phpspreadsheet/

ในตอนนี้เราจะมาทำการ Export ข้อมูลเป็น ไฟล์ Excel กันดูบ้างครับ

สำหรับบทความนี้เราจะใช้ Library phpSpreadsheet กันครับ
สำหรับใครที่ยังไม่ได้ติดตั้งให้ลองดูจากบทความก่อนหน้าได้เลยครับ

ตัวอย่าง Code สำหรับ Export Excel File

<?php

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

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Set document properties
$spreadsheet->getProperties()->setCreator('Maarten Balliauw')
    ->setLastModifiedBy('Maarten Balliauw')
    ->setTitle('Office 2007 XLSX Test Document')
    ->setSubject('Office 2007 XLSX Test Document')
    ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
    ->setKeywords('office 2007 openxml php')
    ->setCategory('Test result file');

// Add some data
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', 'ลำดับ')
    ->setCellValue('B1', 'ชื่อ')
    ->setCellValue('C1', 'เบอร์โทร');

$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A2', '1')
    ->setCellValue('B2', 'วรรณชนก พ่วงทรัพย์')
    ->setCellValue('C2', '0928679411');

$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A3', '2')
    ->setCellValue('B3', 'อรทัย ฤทธิรงค์')
    ->setCellValue('C3', '0925392364');
	
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A4', '3')
    ->setCellValue('B4', 'สมพงษ์ อนุภาพ')
    ->setCellValue('C4', '0914495989');
	
$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A5', '4')
    ->setCellValue('B5', 'ณิชกานต์ สุขแจ่ม')
    ->setCellValue('C5', '0989356019');
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('Simple');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="01simple.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;

<?php

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

use PhpOffice\PhpSpreadsheet\IOFactory;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create new Spreadsheet object

$spreadsheet = new Spreadsheet();

// Set document properties

$spreadsheet->getProperties()->setCreator('Maarten Balliauw')

->setLastModifiedBy('Maarten Balliauw')

->setTitle('Office 2007 XLSX Test Document')

->setSubject('Office 2007 XLSX Test Document')

->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')

->setKeywords('office 2007 openxml php')

->setCategory('Test result file');

// Add some data

$spreadsheet->setActiveSheetIndex(0)

->setCellValue('A1', 'ลำดับ')

->setCellValue('B1', 'ชื่อ')

->setCellValue('C1', 'เบอร์โทร');

$spreadsheet->setActiveSheetIndex(0)

->setCellValue('A2', '1')

->setCellValue('B2', 'วรรณชนก พ่วงทรัพย์')

->setCellValue('C2', '0928679411');

$spreadsheet->setActiveSheetIndex(0)

->setCellValue('A3', '2')

->setCellValue('B3', 'อรทัย ฤทธิรงค์')

->setCellValue('C3', '0925392364');

$spreadsheet->setActiveSheetIndex(0)

->setCellValue('A4', '3')

->setCellValue('B4', 'สมพงษ์ อนุภาพ')

->setCellValue('C4', '0914495989');

$spreadsheet->setActiveSheetIndex(0)

->setCellValue('A5', '4')

->setCellValue('B5', 'ณิชกานต์ สุขแจ่ม')

->setCellValue('C5', '0989356019');

// Rename worksheet

$spreadsheet->getActiveSheet()->setTitle('Simple');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$spreadsheet->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Xlsx)

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="01simple.xlsx"');

header('Cache-Control: max-age=0');

// If you're serving to IE 9, then the following may be needed

header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed

header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past

header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified

header('Cache-Control: cache, must-revalidate'); // HTTP/1.1

header('Pragma: public'); // HTTP/1.0

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

$writer->save('php://output');

exit;

ผลการทำงาน เมื่อรัน Code นี้จะมีการ Download Excel File มาทาง Browser ครับ

การ Export ไฟล์ Excel จาก ฐานข้อมูล MySQL

ตัวอย่างข้อมูลที่ต้องการ Export Excel File

สร้างDatabase ชื่อ demo และ ตารางตัวอย่างสำหรับ Export ข้อมูลดังนี้

CREATE TABLE `customer` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `customer_code` varchar(255) DEFAULT NULL,
 `customer_name` varchar(255) DEFAULT NULL,
 `customer_address` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `customer` (`id`, `customer_code`, `customer_name`, `customer_address`) VALUES
(1, '789-62-9574', 'Devpoint', '538 Nova Drive'),
(2, '413-60-4947', 'Jaxnation', '15293 Kennedy Alley'),
(3, '101-31-2038', 'Thoughtblab', '850 Darwin Junction'),
(4, '658-24-9441', 'Jabberstorm', '6 Tomscot Terrace'),
(5, '745-20-6332', 'Cogibox', '529 Acker Hill');

CREATE TABLE `customer` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`customer_code` varchar(255) DEFAULT NULL,

`customer_name` varchar(255) DEFAULT NULL,

`customer_address` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `customer` (`id`, `customer_code`, `customer_name`, `customer_address`) VALUES

(1, '789-62-9574', 'Devpoint', '538 Nova Drive'),

(2, '413-60-4947', 'Jaxnation', '15293 Kennedy Alley'),

(3, '101-31-2038', 'Thoughtblab', '850 Darwin Junction'),

(4, '658-24-9441', 'Jabberstorm', '6 Tomscot Terrace'),

(5, '745-20-6332', 'Cogibox', '529 Acker Hill');

เพิ่ม Code การเชื่อมต่อฐานข้อมูลและ การ Export Excel ดังนี้

<?php

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

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Set document properties
$spreadsheet->getProperties()->setCreator('Maarten Balliauw')
    ->setLastModifiedBy('Maarten Balliauw')
    ->setTitle('Office 2007 XLSX Test Document')
    ->setSubject('Office 2007 XLSX Test Document')
    ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
    ->setKeywords('office 2007 openxml php')
    ->setCategory('Test result file');

$spreadsheet->setActiveSheetIndex(0)
    ->setCellValue('A1', 'Customer Code')
    ->setCellValue('B1', 'Customer Name')
    ->setCellValue('C1', 'Customer Address');
	
//Connect To Database MySQL
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demo";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
mysqli_set_charset($conn,"utf8");

		$sql = "SELECT * FROM customer";
		$result1 = mysqli_query($conn, $sql);
		
		$rows = mysqli_num_rows($result1);
		
		if ($rows > 0) {
			$i = 2;
			while ($r = mysqli_fetch_assoc($result1)) {
				
				$spreadsheet->setActiveSheetIndex(0)
					->setCellValue("A$i", $r['customer_code'])
					->setCellValue("B$i", $r['customer_name'])
					->setCellValue("C$i", $r['customer_address']);
			 $i++;
			}
		}
		
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('Simple');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="01simple.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;

<?php

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

use PhpOffice\PhpSpreadsheet\IOFactory;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create new Spreadsheet object

$spreadsheet = new Spreadsheet();

// Set document properties

$spreadsheet->getProperties()->setCreator('Maarten Balliauw')

->setLastModifiedBy('Maarten Balliauw')

->setTitle('Office 2007 XLSX Test Document')

->setSubject('Office 2007 XLSX Test Document')

->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')

->setKeywords('office 2007 openxml php')

->setCategory('Test result file');

$spreadsheet->setActiveSheetIndex(0)

->setCellValue('A1', 'Customer Code')

->setCellValue('B1', 'Customer Name')

->setCellValue('C1', 'Customer Address');

//Connect To Database MySQL

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "demo";

// Create connection

$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection

if (!$conn) {

die("Connection failed: " . mysqli_connect_error());

}

mysqli_set_charset($conn,"utf8");

$sql = "SELECT * FROM customer";

$result1 = mysqli_query($conn, $sql);

$rows = mysqli_num_rows($result1);

if ($rows > 0) {

$i = 2;

while ($r = mysqli_fetch_assoc($result1)) {

$spreadsheet->setActiveSheetIndex(0)

->setCellValue("A$i", $r['customer_code'])

->setCellValue("B$i", $r['customer_name'])

->setCellValue("C$i", $r['customer_address']);

$i++;

}

// Rename worksheet

$spreadsheet->getActiveSheet()->setTitle('Simple');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$spreadsheet->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Xlsx)

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="01simple.xlsx"');

header('Cache-Control: max-age=0');

// If you're serving to IE 9, then the following may be needed

header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed

header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past

header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified

header('Cache-Control: cache, must-revalidate'); // HTTP/1.1

header('Pragma: public'); // HTTP/1.0

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

$writer->save('php://output');

exit;

ผลการทำงาน จะได้ไฟล์ excel ที่มีข้อมูลจาก ฐานข้อมูล MySQL ดังนี้

ขอขอบคุณข้อมูลจาก
https://phpspreadsheet.readthedocs.io/en/latest/

การ Export ไฟล์ Excel จาก ฐานข้อมูล MySQL

Popular Posts