Skip to main content

Writing Large XLSX Spreadsheet Files with PHP_XLSXWriter

monkeywebstudio PHP

สำหรับบทความนี้ ผู้เขียนจะแนะนำถึงการใช้งาน PHP XLSX Writer ซึ่งเป็น Library สำหรับ Export Excel ที่มีขนาดเล็ก รองรับการ Export ได้หลากหลายรูปแบบ ครับ

มาดูความสามารถ หลัก ๆ กันครับ

* supports PHP 5.2.1+

* takes UTF-8 encoded input

* multiple worksheets

* supports currency/date/numeric cell formatting, simple formulas

* supports basic cell styling

* supports writing huge 100K+ row spreadsheets

สำหรับข้อสุดท้ายอันนี้ถือว่า ช่วยแก้ปัญหา สำหรับการ Export ข้อมูลจำนวน มาก ๆ  เป็น แสน ๆ เร็คคอร์ด เป็น Excel  ซึ่งผู้เขียน ได้ลองใช้ Library หลาย ๆ ตัว พบว่า library ตัวนี้ มีขนาดเล็ก และ ใช้เวลาในการประมวลผล อยู่ในระดับที่ดีครับ ( Export ข้อมูลเป็น  Excel  ข้อมูลจำนวน 100000 ขึ้นไป )

รายละเอียดเพ่ิมเติม https://github.com/mk-j/PHP_XLSXWriter

ในการใช้งาน สามารถใช้งานได้ 2 รูปแบบด้วยกันคือ

1.เรียกใช้งานผ่าน หน้าเว็บ Browser เพื่อ Export File

ตัวอย่าง เมื่อเรียกไฟล์นี้  จะ Download  File Excel ผ่าน Web Browser ให้อัตโนมัติ

include_once("xlsxwriter.class.php");
ini_set('display_errors', 0);
ini_set('log_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);

$filename = "example.xlsx";
header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');

$rows = array(
    array('2003','1','-50.5','2010-01-01 23:00:00','2012-12-31 23:00:00'),
    array('2003','=B1', '23.5','2010-01-01 00:00:00','2012-12-31 00:00:00'),
);

$writer = new XLSXWriter();
$writer->setAuthor('Some Author'); 
foreach($rows as $row)
	$writer->writeSheetRow('Sheet1', $row);
$writer->writeToStdOut();

exit(0);

2.ใช้งานผ่าน CLI (Command-line user interface) เพื่อ สร้างไฟล์ Excel ก็ได้

ตัวอย่างการเรียกผ่าน CLI

<?php
include_once("xlsxwriter.class.php");
ini_set('display_errors', 0);
ini_set('log_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);

$filename = "example.xlsx";

$rows = array(
    array('2003','1','-50.5','2010-01-01 23:00:00','2012-12-31 23:00:00'),
    array('2003','=B1', '23.5','2010-01-01 00:00:00','2012-12-31 00:00:00'),
);

$writer = new XLSXWriter();
$writer->setAuthor('Some Author'); 
foreach($rows as $row)
	$writer->writeSheetRow('Sheet1', $row);

$writer->writeToFile('example.xlsx');

จะมีการสร้างไฟล์  Excel ไว้ใน Path ที่ต้องการ เมือรันไฟล์นี้จะมีการสร้างไฟล์ Excel ไว้ที่ C://xampp/htdocs/Demo/example.xlsx (แต่ละเครื่อง path อาจไม่เหมือนกันนะครับ)

มาดูตัวอย่างกันเลยดีกว่า
(จริง ๆ แล้ว ในตัว Library ผู้ที่สร้าง mk-j  เขาได้ทำ ตัวอย่าง (Examples) ไว้ละเอียดอยู่แล้วครับ สามารถเปิดดูและทดลองรันได้เลย แต่ผู้เขียนอยาก บันทึกไว้เผื่อมีโอกาสได้ใช้งานอีกครับ)

หมายเหตุ ตัวอย่างทั้งหมดต่อไปนี้จะเป็นการเรียกใช้งาน แบบ CLI ครับ

 

Example 1  Create Excel File

<?php
set_include_path( get_include_path().PATH_SEPARATOR."..");
include_once("xlsxwriter.class.php");

$header = array(
  'c1-text'=>'string',//text
  'c2-text'=>'@',//text
  'c3-integer'=>'integer',
  'c4-integer'=>'0',
  'c5-price'=>'price',
  'c6-price'=>'#,##0.00',//custom
  'c7-date'=>'date',
  'c8-date'=>'YYYY-MM-DD',
);
$rows = array(
  array('x101',102,103,104,105,106,'2018-01-07','2018-01-08'),
  array('x201',202,203,204,205,206,'2018-02-07','2018-02-08'),
  array('x301',302,303,304,305,306,'2018-03-07','2018-03-08'),
  array('x401',402,403,404,405,406,'2018-04-07','2018-04-08'),
  array('x501',502,503,504,505,506,'2018-05-07','2018-05-08'),
  array('x601',602,603,604,605,606,'2018-06-07','2018-06-08'),
  array('x701',702,703,704,705,706,'2018-07-07','2018-07-08'),
);
$writer = new XLSXWriter();

$writer->writeSheetHeader('Sheet1', $header);
foreach($rows as $row)
	$writer->writeSheetRow('Sheet1', $row);

$writer->writeToFile('xlsx-simple.xlsx');

สังเกตตรง $header เราสามารถกำหนดHeader ของ Cells และ รูปแบบของเซล์ (Format Cells) ให้กับข้อมูลที่ต้องการได้

$header = array(
  'c1-text'=>'string',//text
  'c2-text'=>'@',//text
  'c3-integer'=>'integer',
  'c4-integer'=>'0',
  'c5-price'=>'price',
  'c6-price'=>'#,##0.00',//custom
  'c7-date'=>'date',
  'c8-date'=>'YYYY-MM-DD',
);

ผลการทำงาน  PHP_XLSXWriter จะทำการสร้างไฟล์ xlsx-simple.xlsx  ให้ทันที

Example 2  Create Excel File Multiple Sheets

ในตัวอย่างนี้จะเป็นการ สร้างไฟล์ Excel แบบมีหลาย ๆ Sheet ให้ไฟล์เดียว

<?php
set_include_path( get_include_path().PATH_SEPARATOR."..");
include_once("xlsxwriter.class.php");

//กำหนด Header และ Format Cells
$header = array(
    'year'=>'string',
    'month'=>'string',
    'amount'=>'price',
    'first_event'=>'datetime',
    'second_event'=>'date',
);

//ข้อมูลที่ต้องการแสดงใน Sheet1
$data1 = array(
    array('2003','1','-50.5','2010-01-01 23:00:00','2012-12-31 23:00:00'),
    array('2003','=B2', '23.5','2010-01-01 00:00:00','2012-12-31 00:00:00'),
    array('2003',"'=B2", '23.5','2010-01-01 00:00:00','2012-12-31 00:00:00'),
);

//ข้อมูลที่ต้องการแสดงใน Sheet2
$data2 = array(
    array('2003','01','343.12','4000000000'),
    array('2003','02','345.12','2000000000'),
);

$writer = new XLSXWriter();
$writer->writeSheetHeader('Sheet1', $header);
foreach($data1 as $row)
	$writer->writeSheetRow('Sheet1', $row);
foreach($data2 as $row)
	$writer->writeSheetRow('Sheet2', $row);

$writer->writeToFile('xlsx-sheets.xlsx');

exit(0);

ผลการทำงาน  PHP_XLSXWriter จะทำการสร้างไฟล์ xlsx-sheets.xlsx  และจะมี การแบ่ง Sheet1 และ Sheet2  ภายในไฟล์ให้อีกด้วย

Example 3  Create Excel File Style Text

ในตัวอย่างนี้เป็นการสร้างไฟล์ Excel โดยที่เราสามารถ กำหนด รูปแบบของข้อความ ( Style )ใน cells

ตัวอย่าง Options ที่สามารถกำหนด Style ให้กับข้อความได้

<?php
set_include_path( get_include_path().PATH_SEPARATOR."..");
include_once("xlsxwriter.class.php");

$sheet1header = array(
    'c1-string'=>'string',
    'c2-integer'=>'integer',
    'c3-custom-integer'=>'0',
    'c4-custom-1decimal'=>'0.0',
    'c5-custom-2decimal'=>'0.00',
    'c6-custom-percent'=>'0%',
    'c7-custom-percent1'=>'0.0%',
    'c8-custom-percent2'=>'0.00%',
    'c9-custom-text'=>'@',//text
);
$sheet2header = array(
    'col1-date'=>'date',
    'col2-datetime'=>'datetime',
    'custom-date1'=>'YYYY-MM-DD',
    'custom-date2'=>'MM/DD/YYYY',
    'custom-date3'=>'DD-MMM-YYYY HH:MM AM/PM',
    'custom-date4'=>'MM/DD/YYYY HH:MM:SS',
    'custom-date5'=>'YYYY-MM-DD HH:MM:SS',
    'custom-date6'=>'YY MMMM',
    'custom-date7'=>'QQ YYYY',
);
$sheet3header = array(
    'col1-dollar'=>'dollar',
    'col2-euro'=>'euro',
    'custom-amount1'=>'0',
    'custom-amount2'=>'0.0',//1 decimal place
    'custom-amount3'=>'0.00',//2 decimal places
    'custom-currency1'=>'#,##0.00',//currency 2 decimal places, no currency/dollar sign
    'custom-currency2'=>'[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',//w/dollar sign
    'custom-currency3'=>'#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]',//w/euro sign
    'custom-currency4'=>'[$￥-411]#,##0;[RED]-[$￥-411]#,##0', //japanese yen
    'custom-scientific'=>'0.00E+000',//-1.23E+003 scientific notation
);
$pi = 3.14159;
$date = '2018-12-31 23:59:59';
$amount = '5120.5';

$writer = new XLSXWriter();
$writer->setAuthor('Some Author');
$writer->writeSheetHeader('BasicFormats',$sheet1header);
$writer->writeSheetRow('BasicFormats',array($pi,$pi,$pi,$pi,$pi,$pi,$pi,$pi,$pi) );
$writer->writeSheetHeader('Dates',$sheet2header);
$writer->writeSheetRow('Dates',array($date,$date,$date,$date,$date,$date,$date,$date,$date) );
$writer->writeSheetHeader('Currencies',$sheet3header);
$writer->writeSheetRow('Currencies',array($amount,$amount,$amount,$amount,$amount,$amount,$amount,$amount,$amount) );
$writer->writeToFile('xlsx-formats.xlsx');

exit(0);

ผลการทำงาน จะเห็นว่าเราสามารถกำหนดสี หรือ รูปแบบ ต่าง ๆ ให้กับข้อความต่าง ๆ ใน Excel ตามต้องการได้

Example 4  Create Excel File 250000 Rows

ตัวอย่างนี้จะเป็นการสร้างไฟล์ Excel ที่มีจำนวน Rows มากถึง 250000 (สองแสนห้าหมื่น)

โดยในหน้า Github ของ PHP_XLSXWriter  ได้ให้ข้อมูลไว้ว่าจะใช้เวลาประมวณผล ข้อมูล 2500000 ประมาณ 7 วินาที

ส่วนจะเป็นตามนั้นหรือไม่ต้องไปลองทดสอบกันเองครับ แต่การนำไปใช้งานจริงผู้เขียนคิดว่า อาจต้องใช้เวลามากกว่านี้ครับ ในส่วนของการไปดึงข้อมูลมาจาก Database  ก่อนที่ Library จะนำไปสร้างเป็นไฟล์ Excel

<?php
set_include_path( get_include_path().PATH_SEPARATOR."..");
include_once("xlsxwriter.class.php");

$writer = new XLSXWriter();
$writer->writeSheetHeader('Sheet1', array('c1'=>'integer','c2'=>'integer','c3'=>'integer','c4'=>'integer') );//optional
for($i=0; $i<250000; $i++)
{
    $writer->writeSheetRow('Sheet1', array(rand()%10000,rand()%10000,rand()%10000,rand()%10000) );
}
$writer->writeToFile('xlsx-numbers-250k.xlsx');
echo '#'.floor((memory_get_peak_usage())/1024/1024)."MB"."\n";

ตัวอย่างไฟล์ที่ได้

ตัวอย่างการ Export เป็นไฟล์ Excel โดยใช้ ข้อมูลจาก Database  MySQL

ก่อนอื่นเรามาสร้างฐานข้อมูลชื่อ Demo กันก่อนครับ

ตัวอย่าง Database SQL ที่ใช้ในตัวอย่างนี้

create table PHP_XLSXWriter (
	id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(50),
	gender VARCHAR(50),
	ip_address VARCHAR(20)
);
insert into PHP_XLSXWriter (id, first_name, last_name, email, gender, ip_address) values (1, 'Claudianus', 'Kruszelnicki', 'ckruszelnicki0@sakura.ne.jp', 'Male', '173.239.182.8');
insert into PHP_XLSXWriter (id, first_name, last_name, email, gender, ip_address) values (2, 'Dionisio', 'Andreopolos', 'dandreopolos1@discuz.net', 'Male', '85.54.3.9');
insert into PHP_XLSXWriter (id, first_name, last_name, email, gender, ip_address) values (3, 'Niki', 'Novotne', 'nnovotne2@hugedomains.com', 'Male', '63.56.39.217');
insert into PHP_XLSXWriter (id, first_name, last_name, email, gender, ip_address) values (4, 'Billie', 'Arnau', 'barnau3@state.gov', 'Male', '131.135.104.206');
insert into PHP_XLSXWriter (id, first_name, last_name, email, gender, ip_address) values (5, 'Garner', 'Campaigne', 'gcampaigne4@geocities.com', 'Male', '148.45.186.137');
insert into PHP_XLSXWriter (id, first_name, last_name, email, gender, ip_address) values (6, 'Marleen', 'Carabine', 'mcarabine5@domainmarket.com', 'Female', '116.61.153.192');
insert into PHP_XLSXWriter (id, first_name, last_name, email, gender, ip_address) values (7, 'Quintina', 'Stilgoe', 'qstilgoe6@e-recht24.de', 'Female', '92.105.23.198');
insert into PHP_XLSXWriter (id, first_name, last_name, email, gender, ip_address) values (8, 'Damaris', 'Luberto', 'dluberto7@blogtalkradio.com', 'Female', '206.111.63.21');
insert into PHP_XLSXWriter (id, first_name, last_name, email, gender, ip_address) values (9, 'Giffer', 'Dubbin', 'gdubbin8@dailymotion.com', 'Male', '231.2.171.18');
insert into PHP_XLSXWriter (id, first_name, last_name, email, gender, ip_address) values (10, 'Holly', 'Brogan', 'hbrogan9@homestead.com', 'Female', '30.54.82.70');

 

ตัวอย่างที่ 1  PHP_XLSXWriter Download File Excel With Web Browser

<?php
include_once("xlsxwriter.class.php");
ini_set('display_errors', 0);
ini_set('log_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);

$filename = "example.xlsx";
header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');

$servername = "localhost";
$username = "root";
$password = "";
$db_name = "demo";

// สร้างการเชื่อมต่อฐานข้อมูล
$conn = mysqli_connect($servername, $username, $password,$db_name);

//กำหนด charset ให้เป็น utf8 เพื่อรองรับภาษาไทย
mysqli_set_charset($conn,"utf8");

// ตรวจสอบการเชื่อมต่อฐานข้อมูล
if (!$conn) {
	
	//กรณีเชื่อมต่อไม่ได้
	die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT * FROM php_xlsxwriter";

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

if (mysqli_num_rows($result) > 0) {
	
	$rows[] = [
		'id',
		'first_name',
		'last_name',
		'email',
		'gender',
		'ip_address',
	];

	while ($r = mysqli_fetch_assoc($result)) {
		
		$rows[] = [
			$r['id'],
			$r['first_name'],
			$r['last_name'],
			$r['email'],
			$r['gender'],
			$r['ip_address'],
		];
	}
	
	$writer = new XLSXWriter();
	$writer->setAuthor('Some Author'); 
	foreach($rows as $row)
	$writer->writeSheetRow('Sheet1', $row);
	$writer->writeToStdOut();
}

mysqli_close($conn);

exit(0);

ผลการทำงาน เมื่อ เปิดไฟล์นี้ Web Browser จะมีการ Download ไฟล์ Excel ให้ อัตโนมัติครับ

 

ตัวอย่างที่ 2  PHP_XLSXWriter Create File Excel With CLI

<?php
include_once("xlsxwriter.class.php");
ini_set('display_errors', 0);
ini_set('log_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);

$servername = "localhost";
$username = "root";
$password = "";
$db_name = "demo";

// สร้างการเชื่อมต่อฐานข้อมูล
$conn = mysqli_connect($servername, $username, $password,$db_name);

//กำหนด charset ให้เป็น utf8 เพื่อรองรับภาษาไทย
mysqli_set_charset($conn,"utf8");

// ตรวจสอบการเชื่อมต่อฐานข้อมูล
if (!$conn) {
	
	//กรณีเชื่อมต่อไม่ได้
	die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT * FROM php_xlsxwriter";

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

if (mysqli_num_rows($result) > 0) {
	
	$rows[] = [
		'id',
		'first_name',
		'last_name',
		'email',
		'gender',
		'ip_address',
	];

	while ($r = mysqli_fetch_assoc($result)) {
		
		$rows[] = [
			$r['id'],
			$r['first_name'],
			$r['last_name'],
			$r['email'],
			$r['gender'],
			$r['ip_address'],
		];
	}
	
	$writer = new XLSXWriter();
	$writer->setAuthor('Some Author'); 
	foreach($rows as $row)
	$writer->writeSheetRow('Sheet1', $row);
	$writer->writeToFile('example_cli.xlsx');
}

mysqli_close($conn);

exit(0);

ผลการทำงาน เมื่อรันไฟล์นี้ ผ่านหน้า Web Browser จะไม่มีอะไรเกิดขึ้นครับ แต่ จะมีการสร้างไฟล์ Excel ที่ชื่อว่า example_cli.xlsx ตาม path (ตัวอย่าง C://xampp/htdocs/Demo/) ให้ทันทีครับ

ผู้เขียนหวังว่าบทความนี้จะเป็นประโยชน์ต่อเพื่อน ๆ ที่กำลังหาข้อมูลการ Export Excel อยู่นะครับ

ตัวอย่าง Code ประกอบบทความ

PHP_XLSXWriter

ขอขอบคุณข้อมูลจาก
https://github.com/mk-j/PHP_XLSXWriter

 


Deprecated: Function create_function() is deprecated in /home/service1/domains/monkeywebstudio.com/public_html/wp-content/plugins/simple-lightbox/controller.php on line 1642