Đọc & xuất file Excel với thư viên PHPExcel

Thổ lộ tâm tình: Thực ra thì bài viết dạng như này thì các bạn chỉ cấn Search Google là đã có rất nhiều rồi. Nhưng cá nhân mình thấy các bài viết đó chưa trình bày đầy đủ & tổng quan về PHPExcel. Trải qua một số năm làm việc với thư viện này mình sẽ chia sẻ với các bạn một số kinh nghiệm khó khăn mà mình đã gặp phải và cách giải quyết.

PHPExcel là gì?

PHPExcel là một thư viện mở rộng được viết bằng ngôn ngữ PHP thuần và cung cấp một tập các lớp cho phép thao tác đọc và ghi các định dạng tập tin dạnh bảng tính như Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre / OpenOffice Calc .ods, Gnumeric, PDF, HTML, ... Thư viện này được xây dựng dựa trên chuẩn OpenXML của Microsoft và PHP.

1.1 Định dạng hỗ trợ.

Về khả năng đọc.

• BIFF 5-8 (.xls) Excel 95 trở nên
• Office Open XML (.xlsx) Excel 2007 trở nên
• SpreadsheetML (.xml) Excel 2003
• Open Document Format/OASIS (.ods)
• Gnumeric
• HTML
• SYLK
• CSV

Về khả năng ghi.

• BIFF 8 (.xls) Excel 95 trở lên
• Office Open XML (.xlsx) Excel 2007 trở lên
• HTML
• CSV
• PDF (sử dụng thư viện tcPDF, DomPDF hoặc mPDF, cần cài đặt riêng)

1.2 Yêu cầu hệ thống.

• PHP phiên bản từ 5.2.0 trở lên.
• PHP extension php_zip enabled [^phpzip_footnote].
• PHP extension php_xml enabled.
• PHP extension php_gd2 enabled (if not compiled in).

Sau khi đã đáp ứng đủ các yêu cầu trên thì tiếp theo chúng ta cần là download thư viện PHPexcel và Import nó vào Project của bạn để thực hiện các bước tiếp theo. Các bạn nhấn vào link để download.

2. Các hàm được sử dụng thông dụng trong PHPExcel.

Có thể nói PHPExcel rất dộng lớn nếu bạn có ý định tìm hiểu hết. Trong phần này mình sẽ chỉ liệt kê một số hàm hay được sử dụng nhất trong PHPExcel.

Lưu ý: Khi bạn muốn làm việc với PHPExcel thì bạn cần Import vào Project & Source code nơi bạn muốn sử dụng thư viện nhé.

require_once "phpexcel/Classes/PHPExcel.php";
define('EOL', (PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

2.1. Hàm khởi tạo PHPExcel.

$objPHPExcel = new PHPExcel();

Hàm khởi tạo PHPExcel là hàm tạo mới một đối tượng thể hiện cho thư viện PHPExcel. Từ đối tượng thể hiện này bạn có thể gọi các hàm hay truy cập tới các thuộc tính của PHPExcel.

2. Hàm setActiveSheetIndex().

$objPHPExcel->setActiveSheetIndex(n);
// Trong đó:
// n: là một số có giá trị từ 0->n

Như các bạn đã biết cấu trúc của một File Excel bao gồm:

• Bảng tính(Sheet)  e.g Sheet1, Sheet2,Sheet3...
• Các hàng(Row) được đanh số từ 1->n
• Các cột(Column) được đánh theo vần chữ cái A,B,C....AA,AB.
• Các ô(Cell) được xác định bằng giao của dòng và cột e.g: A1 = Dòng 1 x Cột A, B2 = Dòng 2 x Cột B

Lưu ý: Số thứ tự của các Sheet và cột được bắt đầu từ số 0. Khi làm việc với PHPExcel thì bạn phải chỉ rõ bạn cần làm việc với Sheet nào.

Ví dụ 01: Câu lệnh sau báo cho PHPExcel biết rằng mình cầm làm với Sheet1

$objPHPExcel->setActiveSheetIndex(0);

2.3. Hàm getActiveSheet().

Hàm này sẽ trả về sheet hiện tại bạn đang làm việc mà trước đó bạn đã thiết lập bằng hàm setActiveSheetIndex()

$objPHPExcel->getActiveSheet();

2.4 Hàm getDefaultStyle() & getStyle().

Hàm này sẽ trả về một đối tượng mặc định chứa các thuộc tính và phương thức xử lý về mặt giao diện font, background, border, padding....của toàn bộ cả worksheet.

Hàm getStyle() cũng giống với hàm getDefaultStyle() nhưng có phạm vị chỉ trong worksheet hiện hành.

$objPHPExcel->getDefaultStyle();

Hoặc

$objPHPExcel->getActiveSheet()->getStyle();

Từ đối tượng cơ bản này chúng ta mới truy xuất  các thuộc tính và phương thức xử lý style của bảng tính. Thiết lập mới các giá trị mới.

Ví dụ 02: Chúng ta sẽ thiết lập font-size của cả bảng tính là 9px.

$objPHPExcel->getDefaultStyle()->getFont()->setSize(9);

Hoặc bạn muốn thiết lập @font-face và @font-size cho toàn bộ worksheet.

$objPHPExcel->setDefaultFont('Arial', 12);

Hoặc chỉ muốn thiết lập cho hai ô A1 & B1 của worksheet hiện hành.

$style = array(
	'font' => array(
		'size' => 10,
		'bold' => true,
		'color' => array('rgb' => 'ff0000')
	)
);
$objPHPExcel->getActiveSheet()->getStyle('A1:B1')->applyFromArray($style);

Ví dụ 03: Thiết lập border cho hai ô A1 & B1 trong sheet hiện tại.

$objPHPExcel->getActiveSheet()->getStyle('A1:B1')->applyFromArray(
	array(
		'borders' => array(
			'allborders' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
				'color' => array('argb' => '000000'),
			)
		)
	)
);

Lưu ý: Để border left, right, bottom, top, outline bạn cần thay thế từ allborders bằng các giá trị left, right, bottom, top, outline

Để border cho cả dòng thì bạn phải xác định được cột bắt đầu và cột kết thúc.

$objPHPExcel->getActiveSheet()->getStyle('A1:Z1')->....

Để border cho cả cột A bạn sử dụng:

$objPHPExcel->getActiveSheet()->getStyle('A1')->....

Ví dụ 04: Thiết lập background cho hai ô là A1 & B1.

$objPHPExcel->getActiveSheet()->getStyle('A1:B1')->applyFromArray(
	array(
		'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID,
			'color' => array('rgb' => 'deebf6;')
		)
	)
);

Hoặc bạn có thể thiết lập nhiều thuộc tính như sau:

$objPHPExcel->getActiveSheet()->getStyle('A1:B1')->applyFromArray(
	array(
		'fill' => array(
			'type' => PHPExcel_Style_Fill::FILL_SOLID,
			'color' => array('rgb' => 'deebf6;')
		),
		'borders' => array(
			'allborders' => array(
				'style' => PHPExcel_Style_Border::BORDER_THIN,
				'color' => array('argb' => '000000'),
			)
		)
	)
);

2.5 Hàm setCellValue().

Hàm này được sử dụng để thiết lập(set) giá trị cho một cell xác đinh.

Cú pháp: setCellValue(cell, value);

Trong đó:

• cell: Vị trí ô cần set giá trị
• value: Giá trị cần thiết lập e.g: string, số, date, công thức tính

Ví dụ 05: Thiết lập giá trị cho ô A1 & A2.

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Học PHPExcel với www.chiasephp.net');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'Học MySQL với www.chiasephp.net');

2.6 Hàm setRow() & getRow()

Hàm setRow(n) sẽ thiết lập lại row hiện hành của con trỏ. Và hàm getRow() trả về số số Row tiếp theo kể từ vị trí Row không trống cuối cùng.

Cú pháp: setRow(n);

Trong đó:

• n: Là số row cần set.

Ví dụ 06: Set dòng hiện hành của worksheet là = 5; 

$objPHPExcel->setRow(5);
$nextRow = $objPHPExcel->getRow();

2.7 addTableHeader().

Hàm hepper này cho phép bạn tạo tiêu đề cột cho worksheet hiện hành.

Cú pháp: addTableHeader(array columns, array params)

Trong đó:

• columns: Mảng giá trị các giá trị column header
• params: Mảng các giá trị để định đạng header

Ví dụ 07:

$table = array(
	array('label' => __('No'), 'awidth' => 5),
	array('label' => __('Code'), 'awidth' => 15),
	array('label' => __('Product'), 'awidth' => 30, 'wrap' => true),
	array('label' => __('Price')),
	array('label' => __('Quantity')),
	array('label' => __('Sub total'))
);
$objPHPExcel->addTableHeader($table, array('bold' => true));

2.8 Hàm mergeCells()

Hàm mergeCells() có chức năng là gộp nhiều cột và hàng lền kề nhau thành một hàng và cột.

Cú pháp: mergeCells(start, end);

Trong đó: 

• start: Ví trí bắt đầu.
• end: Vị trí kết thúc.

Ví dụ 08: MergeCells trong một hàng từ A1->C1

$objPHPExcel->getActiveSheet()->mergeCells('A1:C1');

Ví dụ 09: Gộp nhiều hàng và cột từ A1 -> C3

$objPHPExcel->getActiveSheet()->mergeCells('A1:C3');

2.9 Hàm setTitle()

Hàm setTitle được sử dụng để thiết lập tên của worksheet hiện hành.

Cú pháp: setTitle(string title);

$titlePage = 'PHPExcel Title';
$objPHPExcel->getActiveSheet()->setTitle($titlePage);

2.10 Hàm setWrapText()

Hàm này sử dụng để thiết lập chế độ wrap cho một cell xác định.

Cú pháp: setWrapText(TRUE/FALSE);

Ví dụ 10:

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
//Hoặc
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(false);

Lưu ý: Bạn không thể set warp cho một ô dạng mergeCells.

$objPHPExcel->getActiveSheet()->mergeCells('A1:B1');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);

2.11 Hàm setWidth()

Hàm này được sử dụng để thiết lập độ rộng cho cột. 

Cú pháp: setWidth(n);

Ví dụ 11: Thiết lập awidth cột A với awidth = 15.

$objPHPExcel->getActiveSheet()->getColumnDimension("A")->setWidth(15);

Hoặc thiết lập awidth cho các cột A,B,C

$objPHPExcel->getActiveSheet()->getColumnDimension("A:C")->setWidth(15);

2.12 Thiết lập công thức tính.

Có thể nói Excel có rất nhiều điểm mạnh. Một trong số đó là nó cung cấp nhiều hàm(SUM,IF,AVG,MIN,MAX) cho phép người tạo thiết lập các công thức tính toán lấy số liệu.

Ví dụ:

$objPHPExcel->getActiveSheet()->setCellValue('A3','=SUM(A1+A2));
// Hoặc
$objPHPExcel->getActiveSheet()->setCellValue('A4','=(A1*100)');

[Còn tiếp tục...]

3. Tạo - Ghi - Download file

Sau khi việc build hoàn thành File Excel này bạn sẽ phải ghi lại hoặc download về thiết bị lưu trữ. 

Để bắt đầu một quá trình ghi và lưu file bạn làm như sau:

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $filetype); // $filetype: Excel5,CSV
$objWriter->save('./exports/test.xls');

Hoặc bạn cần download về thiết bị lưu trữ:

$objWriteuô'ư';//r = PHPExcel_IOFactory::createWriter($objPHPExcel, $filetype); // $filetype: Excel5,CSV
$objWriter->save('php://output');

Trong một số trường hơp khi mình tạo & ghi file download về máy tính. Khi mở file thì nó bị lỗi không mở được file. Mĩnh cũng không rõ nó bị nguyên nhân gì nhưng để khắc phục nó thì mình sử dụng thêm lệnh sau: ob_end_clean()

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $filetype); // $filetype: Excel5,CSV
ob_end_clean();
$objWriter->save('php://output');
exit();

[Hoàn chỉnh]:

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($titlePage);
// Redirect output to a client's web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="$filename.xls"');
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
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
ob_end_clean();
$objWriter->save('php://output');
exit;

4. Kết luân

Như vậy trong bài này mình đã giới thiệu với các bạn một số hàm thông dụng được sử dụng phố biến trong PHPExcel. Trong bài tiếp theo mình sẽ hướng dẫn các sử dụng PHPExcel để đọc và xuất file. [Bài tiếp theo]