Ở phần 1 tôi đã trình bày sơ qua về cách tích hợp PHPExcel vào CakePHP. Bài này tôi xin chia sẻ thêm một vài tính năng khác như cách thiết lập width, height của dòng cột, cố định vị trí dòng cột, hay style cho cell dữ liệu để nội dung được thân thiện hơn, hay phục vụ cho việc in ấn file.

Width & Height

Việc thiết lập độ rộng cột hay chiều cao hàng thực hiện rất đơn giản, chỉ việc gọi đến các hàm tương ứng rồi cho đối số vào là hoàn thành, đó chính là setWidth() và setRowHeight(), cách dùng như đoạn code này ví dụ bạn chắc chắn ko có sản phẩm nào có giá từ chục triệu trở lên thì tối đa số các con số là 7, vậy hãy set là 10 cho rộng rãi cửa nhà :

/*
 * now i will set width for Price column to 10 (can fill 8 number characters) * or you can set auto width for it by using setAutoSize(true)
*/
$this->PhpExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);

Vậy còn height? trong demo này có thể lấy minh hoạ rằng dòng trống sau bảng dữ liệu phải rộng hơn để cho nhìn cảm giác thoải mái hơn chút :

// set that blank row's height to 30pt by using setRowHeight()
$this->PhpExcel->getActiveSheet()->getRowDimension($next2Row - 1)->setRowHeight(25);

Style

Căn lề dữ liệu

Đầu tiên là header của bảng dữ liệu, tôi sẽ thiết lập để nó được căn lề giữa cho cả 2 chiều ngang (dùng setHorizontal) và dọc (dùng setVertical). Nhưng trước đó, phải cần xem là căn cho cell nào hay là một dải các cell, vùng các cell bằng getStyle(), rồi sẽ getAlignment() để cho PHPExcel biết ta sẽ style về lề dữ liệu :

// then align heading to center
$this->PhpExcel->getActiveSheet()->getStyle('A7:F7')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->PhpExcel->getActiveSheet()->getStyle('A7:F7')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

Như bạn thấy chỉ cần truyền hằng số về căn lề đã đươc định sẵn thì cell đã được style lề, ngoài căn giữa thì còn có thể chọn right hay left theo đúng cú pháp trên.

Tiếp theo là style cho bảng dữ liệu sau header, trong demo này thì ta biết chắc dữ liệu sẽ được fill vào từ dòng 8 còn dòng cuối của bảng có thể lấy qua biến $next2Row trừ đi 2 sẽ ra kết quả. Hai cột đầu tiên là số thứ tự (No) và mã sản phẩm (Code) sẽ canh giữa theo cả 2 chiều :

// ok, we have $next2Row so we know current row.
// we will style from row 8 for cell table
// firstly, column A (No) will be centered
$this->PhpExcel->getActiveSheet()->getStyle('A8:A' . (string)($next2Row - 2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->PhpExcel->getActiveSheet()->getStyle('A8:A' . (string)($next2Row - 2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

Mặc định cột Product được căn lề trái nên nếu cần thiết thì chỉ set lề chiều dọc nữa mà thôi

$this->PhpExcel->getActiveSheet()->getStyle('C8:C' . (string)($next2Row - 2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

Cuối cùng là 3 cột cuối Price, Quantity và Sub Total đều chứa dữ liệu kiểu số nên ta sẽ để căn lề phải chiều ngang và căn giữa chiều dọc :

$this->PhpExcel->getActiveSheet()->getStyle('D8:F' . (string)($next2Row - 2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$this->PhpExcel->getActiveSheet()->getStyle('D8:F' . (string)($next2Row - 2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

Bạn hẳn nhận thấy rằng đang thừa code một chút, bởi vì dải A8:F(cuối) đều căn lề giữa chiều dọc nên cần thu gọn bớt như sau là đủ :

$this->PhpExcel->getActiveSheet()->getStyle('A8:F' . (string)($next2Row - 2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

Chú ý :
+ bạn để ý rằng phải ép kiểu string vì getStyle() nhận vào đối số dạng chuỗi
+ nếu dùng bản dưới 1.7.0 thì sẽ chỉ set được style cho 1 cell

Border

Giờ đến lúc thiết lập các border cần thiết để khi in ra nhìn sẽ đẹp hơn, rõ ràng với khung viền bao quanh bảng dữ liệu. Để thiết lập thì bạn cần gọi getBorders() trước tiên, sau đó là border nào bạn định format (trái, phải, trên dưới hay tất cả) và cuối cùng sẽ là loại border nào bạn muốn. Các loại border được hỗ trợ rất nhiều, cơ bản có THIN, THICK, DOTTED, DASHED, DOUBLE, DASHDOT ...

// header's borders should be bold so i use BORDER_THICK for all borders
$this->PhpExcel->getActiveSheet()->getStyle('A7:F7')
->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
// and data table should be THIN
$this->PhpExcel->getActiveSheet()->getStyle('A8:F' . (string)($next2Row - 2))
->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
// but outside border should be THICK
$this->PhpExcel->getActiveSheet()->getStyle('A8:F' . (string)($next2Row - 2))
->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$this->PhpExcel->getActiveSheet()->getStyle('A8:F' . (string)($next2Row - 2))
->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$this->PhpExcel->getActiveSheet()->getStyle('A8:F' . (string)($next2Row - 2))
->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);

Hyperlink

Trong demo này chỗ thích hợp để link nhất chắc là tên khách hàng, chẳng hạn gắn vào đó email của khách hàng và khi click vào đó sẽ khởi động trình soạn thảo mail gửi cho khách hàng. Hoặc là URL dẫn đến chính invoice đó trên giao diện web

// make customer name become clickable cell, for example a email address or URL
$this->PhpExcel->getActiveSheet()->getCell('A2')->getHyperlink()->setUrl('http://localhost/blog/invoices/viewInvoiceDetail/1');

Cố định hàng cột khi view

Với những file có số lượng hàng cột nhiều mà không thể xem hết trong màn hình, đôi khi ta sẽ cần cố định những phần nội dung thường là tiêu đề để khi kéo view sang các hàng cột bị khuất thì sẽ vẫn biết được đang aview phần nội dung của tiêu đề nào. Ở demo này dữ liệu là không nhiều nhưng tôi sẽ cố định lại phần thông tin về khách hàng và cột A :

$this->PhpExcel->getActiveSheet()->freezePane('B6');

Hàm freezePane() làm việc giống như Excel của bạn, như trên khi chỉ định B6 làm đối số thì PHPExcel sẽ hiểu là bạn muốn cố định từ dòng 1 đến 5 và các cột trước B (tức cột A). Rất đơn giản phải không !?

Metadata

Mỗi file khi được xuất ra từ hệ thống, để đảm bảo tính sở hữu cá nhân hay bản quyền, hoặc để quản lý tìm kiếm file được dễ dàng hơn ... nhiều khi bạn cần đưa cả những thông tin cần thiết (như tên người tạo, subject của file, mô tả về file đó ...) vào metadata. PHPExcel hỗ trợ các thông tin như sau :

// set metadata
$this->PhpExcel->getProperties()
	->setCreator("PHPExcel")
	->setLastModifiedBy("Nguyen Van Huong");
	->setTitle("This is title")
	->setSubject("This is subject")
	->setDescription("demo for PHPExcel")
	->setKeywords("invoice phpexcel")
	->setCategory("Invoice");

Comment và data validation

Comment

Với nhiều ứng dụng, đôi khi những nội dung trên file có chỗ sẽ cần phải note lại cho người đọc dễ hiểu hơn như chú thích về cách tính của một cell, hay bất kì nội dung chú thích nào khác thì giải pháp là để lại comment cho cell dữ liệu đó. PHPExcel hỗ trợ rất nhiều và chức năng này cũng không là ngoại lệ. Bạn sẽ dùng hàm getComment() với đối số là địa chỉ cell dữ liệu muốn để comment rồi dùng getText() và createTextRUn('content here') để fill nội dung comment vào cell đã định. Giả sử comment cell đầu tiên là phải thanh toán trước, hãy cùng xem đoạn code demo sau :

// leave a comment in file
$this->PhpExcel->getActiveSheet()->getComment('A1')->setAuthor('HuongNV');
$this->PhpExcel->getActiveSheet()->getComment('A1')->getText()->createTextRun('You must pay for us in 5 days, thank you !');

Data validation

Cuối cùng trong bài này, tôi sẽ viết về cách tạo ra một data validation, vì có nhiều loại nên tôi chỉ chọn một loại là list để trình bày, các loại khác cũng gần tương tự nên có dịp tôi sẽ trình bày sau :

// đầu tiên là gán đối tượng data validation thành 1 biến cho dễ sử dụng
$dataValidation = $this->PhpExcel->getActiveSheet()->getCell('E2')->getDataValidation();
// sau đó là khai báo xem loại validation là gì, ở đây tôi chọn TYPE_LIST
$dataValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
// nếu user nhập sai thì sẽ hiển thị lỗi dạng info thay vì error
$dataValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
// cái này cho phép thiết lập cell có được để trống hay ko
$dataValidation->setAllowBlank(false);
// lựa chọn hiển thị input message khi chọn từ list hay ko, ở đây là có
$dataValidation->setShowInputMessage(true);
// trường hợp có lỗi thì hiển thị message ko? ở đây là có
$dataValidation->setShowErrorMessage(true);
// ta phải hiển thị dropdown để user còn chọn item trong đó
$dataValidation->setShowDropDown(true);
// thiết lập tiêu đề và nội dung message khi có lỗi
$dataValidation->setErrorTitle('Error');
$dataValidation->setError('Invalid value');
// thiết lập tiêu đề và nội dung message khi user focus vào cell
$dataValidation->setPromptTitle('Choose value');
$dataValidation->setPrompt('Please choose a value from list.');
// cuối cùng là cần khai báo những item có thể chọn từ list, phân cách bằng dấu phẩy
$dataValidation->setFormula1('"Paid,Not paid"');

Chú ý : phải để các item trong dấu ngoặc kép "" như code trên

Trong một vài trường hợp, có thể list dữ liệu của bạn lấy luôn từ các giá trị có trong worksheet đó và bạn có thể xem đoạn code như sau :

$dataValidation->setFormula1('Sheet!$D$1:$D$5');.

Để tạo ra một chuỗi những drop-down list tương tự có cùng nội dung thì bạn có thể sử dụng vòng lặp tạo ra hàng loạt hoặc đôi khi bạn chỉ muốn sao chép từ cell này sang cell nào khác trong sheet, lúc này bạn dùng clone :

$this->PhpExcel->getActiveSheet()->getCell('E3')->setDataValidation(clone $dataValidation);

Lời kết

Đến đây demo đã tương đối hoàn chỉnh, ở phần tiếp tôi sẽ trình bày tiếp về những tính năng khác mà PHPExcel cung cấp và hoàn thiện demo này,ví dụ như format số tiền theo định dạng mong muốn, tạo thiết lập bảo vệ file, thiết lập các thông số cho in ấn ...

code on github