[CakePHP] Xuất dữ liệu ra Excel sử dụng thư viện PHPExcel (p3)

Tiếp phần 2, ở bài này tôi sẽ trình bày thêm một số nội dung về PHPExcel để hoàn thiện demo của mình.

Chèn ảnh

Ảnh là một đối tượng khác nên cần phải khởi tạo đối tượng PHPExcel_Worksheet_Drawing trước khi đưa vào một trang excel, đối tượng sau khi được khởi tạo thì có thể thiết lập những thuộc tính cho nó :

$image = new PHPExcel_Worksheet_Drawing();
$image->setName('Company seal');
$image->setDescription('ABC company seal');
$image->setPath('./images/seal.jpg');
$image->setHeight(100);
$image->setWidth(100);

Và để chèn đối tượng ảnh trên thì dùng đoạn code như sau, lúc này PHPExcel sẽ tạo một liên kết giữa đối tượng ảnh và excel :

$image->setWorksheet($this->PhpExcel->getActiveSheet());

Style ảnh

Mặc định ảnh khi được chèn sẽ vào ô đầu tiên dòng thứ nhất, bạn muốn chèn vào một cell cố định nào đó thì dùng setCoordinates(), chẳng hạn tôi muốn con dấu công ty được chèn vào vị trí D1 :

$image->setCoordinates('D1');
$image->setOffsetX(40);

setOffsetX() là hàm hỗ trợ giúp chúng ta có thể di chuyển lệch ảnh sang bên phải một lượng pixcel nhất định, như trên tôi đã thiết lập để ảnh được chèn vào ô D1 nhưng lệch phải một chút để con dấu đúng vị trí giữa cột D và E. Tiếp đến, tôi muốn con dấu được chèn vào phải xoay một góc 45 độ về bên phải thì tôi sẽ cần dùng hàm setRotation() hỗ trợ việc này :

$image->setRotation(45);

TIP : nếu bạn muốn quay con dấu sang trái thì có thể truyền vào số âm

Khi chúng ta mở các file excel đã xuất ra lên sẽ thấy các con số hoàn toàn chưa được định dạng vì thế nhìn sẽ khá rối mắt. PHPExcel cung cấp hàm setFormatCode() với một số hằng định nghĩa sẵn giúp việc định dạng các ô chứa dữ liệu số được tiện lợi hơn. Ví dụ chúng ta sẽ định dạng các con số là tiền trong demo này với kiểu 3 số phân cách bằng dấu phẩy :

$this->PhpExcel->getActiveSheet()->getStyle('A1')->getNumberFormat() ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

TIP : Bạn có thể tham khảo những hằng định sẵn trong file Vendor/PHPExcel/Style/NumberFormat.php

Hằng được tôi sử dụng trên thực chất có giá trị '#,##0.00' nên bạn hoàn toàn có thể tự định nghĩa cho mình định dạng hợp lý cho ứng dụng của bạn, ví dụ tôi chỉ muốn 1 số sau dấu thập phân chứ không phải 2 như định nghĩa của hằng trên :

$this->PhpExcel->getActiveSheet()->getStyle('A1')->getNumberFormat() ->setFormatCode('#,##0.0'); // kết quả dạng 36,774.2

Nhóm dòng

Trong excel, với những sheet có số lượng dòng dữ liệu quá lớn hoặc khi chúng ta có nhu cầu nhóm những dòng lại với nhau, ví dụ trong demo này tôi muốn nhóm những dữ liệu trong bảng invoice :

// group data row
$this->PhpExcel->getActiveSheet()->getRowDimension(7 + $i)->setOutlineLevel(1);
$this->PhpExcel->getActiveSheet()->getRowDimension(7 + $i)->setCollapsed(true);

Vì header của bảng từ dòng 7 nên tôi sẽ bắt đầu nhóm từ dòng 8 đến khi hết dữ liệu (phần getRowDimension(7 + $$)). setOutlineLevel() cho phép tạo ra phần outline bên trái để có thể click vào làm ẩn hiện các dòng đã nhóm lại với nhau, nó nhận vào khoảng từ 0 đến 7, với 0 là không có outline. Sau đó, tôi muốn ẩn những dòng đã nhóm làm view mặc định nên tôi setCollapsed(true). Vậy là đã đủ nhưng với Excel 2007 thì bạn cần thêm dòng code sau, bởi vì setCollapsed() sẽ không được thực thi.

$this->PhpExcel->getActiveSheet()->getRowDimension(7 + $i)->setVisible(false);

Nhóm cột

Việc nhóm cột là tương tự như nhóm dòng với nhau, chỉ khác là bạn cần sử dụng getColumnDimension() thay vì getRowDimension() mà thôi. Trong demo này không có chỗ sthích hợp để nhóm cột nhưng code sẽ dạng như dưới :

// group data column
$this->PhpExcel->getActiveSheet()->_getColumnDimension('A')->setOutlineLevel(1);
$this->PhpExcel->getActiveSheet()->_getColumnDimension('A')->setCollapsed(true);
$this->PhpExcel->getActiveSheet()->_getColumnDimension('A')->setVisible(false);

Merging

PHPExel cũng hỗ trợ chúng ta việc 'trộn' hoặc 'bỏ trộn' các ô với lần lượt 2 hàm mergeCells() và unmergeCells(). Chúng đều nhận vào đối số là vùng các cell, ví dụ tôi sẽ merge các cột từ A đến F cho tất cả 5 dòng đầu tiên chứa thông tin của khách hàng :

/*
* Merging from column A -> F for 5 first rows
*/
for($row = 1; $row <= 5; $row++) {
$this->PhpExcel->getActiveSheet()->mergeCells('A' . (string)($row) . ':' . 'F' . (string)($row)); // for example, mergeCells('A1:F1')
}

Trong demo này không thích hợp nhưng trong trường hợp cần 'bỏ trộn' cột, ví dụ như bạn đọc 1 file có sẵn lên và chỉnh sửa nội dung của nó chẳng hạn thì có thể dùng unmergeCells() với cú pháp hoàn toàn tương tự như trên.

Inserting

Trong demo này tôi sẽ không dùng đến nhưng sẽ đề cập đến khả năng hỗ trợ việc chèn vào một số dòng/ cột hoặc loại bỏ đi từ một vị trí nhất định.

Chèn dòng/cột

PHPExcel hỗ trợ hàm insertNewRowBefore(x, y) để chèn vào trước dòng x với y dòng. Và hàm insertNewColumnBefore(a, b) để chèn vào trước cột a với b cột.

$this->PhpExcel->getActiveSheet()->insertNewRowBefore(6, 2);
$this->PhpExcel->getActiveSheet()->insertNewColumnBefore('C', 5);

Xoá dòng/cột

Khi load một file lên để chỉnh sửa nội dung, có thể chúng ta cần loại bỏ những dòng/cột không mong muốn. PHPExcel hoàn toàn hỗ trợ tốt cho việc này bằng 2 hàm removeRow(x, y) để xoá y dòng kể từ dòng x và removewColumn(a, b) để xoá b cột kể từ cột a.

PHP hỗ trợ việc thiết lập các thông số in ấn file Excel rất đầy đủ, nhưng trong phần này tôi sẽ trình bày một số thông số chung và hay sử dụng nhất, những phần khác bạn có thể tham khảo trên document.

Chiều & cỡ in

Việc này rất đơn giản, tôi sẽ dùng getPageSetup() để thiết lập in invoice của mình theo chiều dọc và cỡ in là khổ A4 như sau :

$this->PhpExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$this->PhpExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

TIP : bạn có tìm các cỡ in mà PHPExcel đã định nghĩa sẵn ở Vendor/PHPExe/Worksheet/PageSetup.php

Scaling

Bạn có thể thiết lập tỷ lệ scaling cho trang excel của mình giống như trên Excel bằng cách sử dụng các hàm sau :

• setFitToPage() : Nhận vào true hoặc false tương ứng với cho phép fit tỷ lệ của trang in hoặc sẽ theo %.
• setScale() : nhận đối số là tỷ lệ tính theo % khi in ra. Nếu được gọi thì mặc định setFitToPage() sẽ được truyền vào false.
• setFitToWidth() và setFitToHeight() : Khi được gọi đến thì mặc định setFitToPage() sẽ được truyền vào true. Đối số 2 hàm này nhận vào là số trang sẽ fit tỷ lệ theo chiều rộng hay chiều cao. Còn nếu truyền vào 0 thì sẽ ko fit tỷ lệ theo chiều rộng hay chiều cao.

Hãy xem ví dụ sau, do demo sẽ chỉ in 1 trang nhưng để dễ hiểu tôi sẽ thiết lập 2 trang in ra sẽ fit tỷ lệ theo nội dung. Lúc này bạn không cần phải thiết lập setFitToPage() nữa :

$this->PhpExcel->getActiveSheet()->getPageSetup()->setFitToWidth(2);
$this->PhpExcel->getActiveSheet()->getPageSetup()->setFitToHeight(2);

Căn lề trang in

Sau đây là các hàm mà PHPExcel cung cấp, chúng là tất cả các thuộc tính margins có thể thiết lập trong Excel. Chú ý là gía trị nhận vào của PHPExcel là inch.

$this->PhpExcel->getActiveSheet()->getPageMargins()->setTop(0.7); // ~ 1.78cm
$this->PhpExcel->getActiveSheet()->getPageMargins()->setHeader(0.4); // ~1.02cm
$this->PhpExcel->getActiveSheet()->getPageMargins()->setRight(0.68); // ~
$this->PhpExcel->getActiveSheet()->getPageMargins()->setLeft(0.7); // ~1.78cm
$this->PhpExcel->getActiveSheet()->getPageMargins()->setBottom(0.68); // ~1.73cm
$this->PhpExcel->getActiveSheet()->getPageMargins()->setFooter(0.4); // ~1.02cm

Ngoài ra, với một trang in thì chúng ta cũng có thể căn/không căn giữa nó theo chiều ngang hoặc chiều dọc bằng cách truyền vào true/false vào hàm setHorizontalCentered() hoặc setVerticalCentered()

// center a page horizontally/vertically
$this->PhpExcel->getActiveSheet()->getPageSetup()->setHorizontalCentered(true);
$this->PhpExcel->getActiveSheet()->getPageSetup()->setVerticalCentered(true);

Header & Footer

Bạn có thể thiết lập nội dung, định dạng header và footer cho file của mình theo trang chẵn lẻ. Demo này chỉ có 1 trang nên tôi sẽ làm cho trang lẻ như sau :

$this->PhpExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&C&BThis is a centered header with PHPExcel');
$this->PhpExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

• &L, &C, &R : là những mã code tượng trưng cho bên trái, giữa và phải của header.
• &B : là mã định dạng cho chữ đậm lên
• &P : là trang in hiện tại khi in đến nó
• &N : là tổng số trang in

Còn có một số mã code mà PHPExcel hỗ trợ cho việc thiết lập header và footer, bạn có thể tham khảo thêm ở trên document

Có 3 mức bảo vệ là thiết lập mật khẩu cho một file; đối với một trang trong 1 file thì bạn có thể không cho phép chèn dòng vào, không cho sắp xếp ...; còn bảo vệ đến một ô cho phép khoá hay không khoá, ẩn hiện công thức tính ...

Bảo vệ một file như dưới :

$this->PhpExcel->getSecurity()->setLockWindows(true);
$this->PhpExcel->getSecurity()->setLockStructure(true);
$this->PhpExcel->getSecurity()->setWorkbookPassword("123456");

Bảo vệ một sheet, tôi sẽ set mật khẩu cho cả sheet trong file, không cho edit, không cho chèn thêm dòng mới, không cho định dạng lại dữ liệu :

// protect current sheet
$this->PhpExcel->getActiveSheet()->getProtection()->setPassword('123456');
$this->PhpExcel->getActiveSheet()->getProtection()->setSheet(true); // can not edit without password
$this->PhpExcel->getActiveSheet()->getProtection()->setInsertRows(true); // can not insert new row
$this->PhpExcel->getActiveSheet()->getProtection()->setFormatCells(true); // can not use Format cells

Ngoài ra, bạn có thể thiết lập những bảo vệ khác như không cho xoá dòng/cột, không cho chèn links, không cho sort ... cũng như là thiết lập bảo vệ đến từng cell. Những phần này có thể tìm thấy trong Vendor/PHPExe/Worksheet/Protection.php

Đến đây tôi xin kết thúc phần 3 của chuỗi bài tìm hiểu những tính năng mà PHPExcel cung cấp để kết hợp với CakePHP thực thi những công việc liên quan đến Excel. Ba phần này chỉ đi vào những cái chung hay dùng, ngoài ra các nội dung khác bạn có thể tham khảo trên document hoặc trang chủ và source code.

code on github ...