Import, Export và xử lý Excel với PHPExcel

Chẳng biết các bạn có làm việc với Excel nhiều không nhưng trong các dự án thương mại điện tử tôi đã từng tham gia thì việc làm việc với các file Excel là cực kì nhiều: từ viết xuất report báo cáo, nhập dữ liệu từ file Excel (ví dụ như cập nhật giá sản phẩm, cập nhật mã EMS cho các đơn hàng gửi bưu điện…) khiến cho việc làm việc với file Excel là vô cùng cần thiết và tôi thường sử dụng PHPExcel.

Trong bài viết này tôi sẽ hướng dẫn mọi người làm việc với file Excel bằng ngôn ngữ PHP.

PHPExcel

Mặc dù bạn hoàn toàn có thể tự mình lập trình PHP toàn bộ các thao tác với Excel nhưng tôi cho rằng việc này là không cần thiết và khá tốn thời gian, đôi khi là trình độ nữa. Cho nên, tôi khuyên các bạn sử dụng một bộ thư viện được xây dựng sẵn để làm việc với file Excel: PHPExcel.

Bộ thư viện này khá đầy đủ và hỗ trợ nhiều định dạng Excel mà bạn thường xuyên sử dụng.

Các định dạng Import cho phép:

1. Excel 2007 (SpreadsheetML)
2. BIFF5 (Excel 5.0 / Excel 95)
3. BIFF8 (Excel 97 and later)
4. PHPExcel Serialized Spreadsheet
5. Symbolic Link (SYLK by Microsoft)
6. CSV (Comma Separated Values)

Và bạn có thể xuất dữ liệu ra các định dạng:

1. Excel 2007 (SpreadsheetML)
2. BIFF8 (Excel 97 and later)
3. PHPExcel Serialized Spreadsheet
4. PDF
5. HTML
6. CSV (Comma Separated Values)

Để cài đặt, bạn cần chắc chắn bạn sử dụng PHP từ phiên bản 5.2 trở lên và đã mở các extension php_zip, php_xml và php_gd2.

Sau khi kiểm tra bạn download bộ thư viện này từ nguồn:

[code]https://phpexcel.codeplex.com/[/code]

Phần document trong nguồn có hướng dẫn đầy đủ cho bạn.

Làm việc với PHPExcel

Sau đây tôi sẽ hướng dẫn bạn một số thao tác và code cơ bản để làm việc với PHPExcel, từ những đoạn mã mẫu này bạn có thể phát triển dự án theo mong muốn của mình.

Tạo một spreadsheet

Sau khi download và giải nén, ngoài bộ thư viện bạn cũng sẽ có thêm một thư mục Test chứa một số file được lập trình sẵn cho bạn những ví dụ sinh động để bạn làm việc với PHPExcel dễ dàng hơn.

Chúng ta xem xét đoạn để tạo một speadshet:

[code language=”php”] require_once ‘PHPExcel.php’;
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("NhanWeb")
->setLastModifiedBy("Nguyễn Duy Nhân")
->setTitle("Demo tạo Excel bằng PHPExcel")
->setSubject("Demo tạo Excel bằng PHPExcel")
->setDescription("Tạo file Excel bằng PHPExcel bởi Nguyễn Duy Nhân")
->setKeywords("office 2007 openxml php")
->setCategory("Demo");
$objPHPExcel->getActiveSheet()->setTitle(‘NhanWeb’);
[/code]

Đoạn mã trên tương đối đơn giản: tạo ra một file Excel với các thông tin cơ bản như người tạo, người chỉnh sửa cuối cùng, tiêu đề… và sau đó chúng ta tạo một ActiveSheet với title là NhanWeb.

Tiếp theo, trong ActiveSheet đang mở, chúng ta đặt một vào dòng dữ liệu vào các cột A1, B1 như sau:

[code language=”php”] $objPHPExcel->setActiveSheetIndex(0)
->setCellValue(‘A1’, ‘Hello’)
->setCellValue(‘B1’, ‘world!’);
[/code]

Đoạn mã trên là tiến hành đặt dữ liệu vào một ô xác định (A1,B1), trường hợp chúng ta cần đặt nhiều dòng dữ liệu được trích xuất từ CSDL MySQL thì không thể dùng cách này được vì chúng ta không biết chính xác số dòng, số cột.

Lúc này chúng ta sử dụng method sau của objPHPExcel:

[code language=”php”] setCellValueByColumnAndRow($column, $row, $value)
[/code]

Lưu ý: Dòng bắt đầu từ 1 và Cột bắt đầu từ 0. Nghĩa là nếu bạn muốn sử dụng cột A1 chúng ta sẽ có cặp giá trị $column, $row tương ứng là 0,1.

Cuối cùng, để lưu dữ liệu lại thành 1 file Excel chúng ta sử dụng PHPExcel_IOFactory như sau:

[code language=”php”] require_once ‘PHPExcel/IOFactory.php’;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’);
// If you want to output e.g. a PDF file, simply do:
//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘PDF’);
$objWriter->save(‘MyExcel.xslx);
[/code]

Đọc dữ liệu từ một spreadsheet

PHPExcel có khả năng đọc các tệp tin Excel và trích xuất dữ liệu đó ra PHP để bạn có thể xử lý một cách dễ dàng. Chúng đọc được nhiều định dạng dữ liệu như tôi đã nói ở phần đầu.

Điều này rất tuyệt vời ! Ví dụ như bạn có thể tải lên một file Excel có sẵn từ máy tính của mình và tiến hành đọc dữ liệu từ file tải lên đó, nhập dữ liệu vào CSDL của MySQL hoặc xử lý dữ liệu đó bằng PHP.

Để đọc dữ liệu, bạn có thể làm như sau:

[code language=”php”] require_once ‘PHPExcel/IOFactory.php’;
$objPHPExcel = PHPExcel_IOFactory::load("MyExcel.xlsx");
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g ‘F’
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) – 64;
echo "<br>The worksheet ".$worksheetTitle";
echo $nrColumns . ‘ columns (A-‘ . $highestColumn . ‘) ‘;
echo ‘ and ‘ . $highestRow . ‘ row.’;
echo ‘<br>Data: <table border="1"><tr>’;
for ($row = 1; $row <= $highestRow; ++ $row) {
echo ‘<tr>’;
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
echo ‘<td>’ . $val . ‘<br>(Typ ‘ . $dataType . ‘)</td>’;
}
echo ‘</tr>’;
}
echo ‘</table>’;
}
[/code]

Định dạng file Excel khi xuất ra

Thông thường tôi không làm công việc định dạng dữ liệu nhưng có đôi lúc cần phải thực hiện nó như tạo ra các dòng tiêu đề có màu sắc và kích cỡ lớn hơn.

Trong trường hợp này tôi hay áp dụng một phương pháp đơn giản là định dạng sẵn một file Excel làm mẫu, sau đó tôi sẽ định dạng sẵn file này rồi đọc nó như một template, đưa dữ liệu vào và lưu lại.

Tôi cho rằng phương pháp này khá đơn giản và dễ thực hiện lại hiệu qua cực kì.

Nếu bạn có nhu cầu định dạng dữ liệu, bạn hãy thử áp dụng cách của tôi xem.

Chuyển định dạng file với PHPExcel

Không cần rườm rà, với PHPExcel, bạn chỉ cần 3 dòng mã để làm ba việc: đọc file -> tạo Writer -> Lưu xuống file mới.

Cụ thể như thế này:

[code language=”php”] $objPHPExcel = PHPExcel_IOFactory::load("XMLTest.xml");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’);
$objWriter->save(‘covertedXml2Xlsx.xlsx’);
[/code]

Lưu file Excel xuống máy tính cá nhân

Nếu bạn không muốn lưu file Excel trên server và cung cấp cho người dùng một đường dẫn để download, bạn có thể chỉ định file được tải về ngay khi tạo bằng cách bổ sung một header trước khi save. Đây là cách tôi dùng để thực hiện chức năng đó:

[code language=”php”] header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
header(‘Content-Disposition: attachment;filename="ten_file_se_duoc_tai_ve.xlsx"’);
header(‘Cache-Control: max-age=0’);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’);
$objWriter->save(‘php://output’);
[/code]

Hạn chế

Hạn chế lớn nhất của thư viện này theo tôi đó là không thể vẽ các đồ thị hoặc các biểu đồ. Tuy nhiên, tôi cho rằng thư viện này là một phương pháp tuyệt vời khi bạn làm việc với Excel bằng ngôn ngữ PHP.

Tôi đã sử dụng nó và cảm thấy việc làm việc với Excel bằng ngôn ngữ PHP đơn giản hơn rất nhiều.