Export Dữ Liệu Mysql Sang Excel Bằng Php: Ví Dụ Và Hướng Dẫn Chi Tiết

Trong các dự án quản lý dữ liệu, việc xuất dữ liệu từ cơ sở dữ liệu MySQL sang file Excel là một nhu cầu phổ biến. Excel không chỉ là công cụ phổ biến để báo cáo, thống kê mà còn giúp người dùng cuối dễ dàng thao tác, chỉnh sửa và chia sẻ dữ liệu. PHP, với tính linh hoạt và sự hỗ trợ mạnh mẽ từ các thư viện, thường được sử dụng để thực hiện công việc này. Bài viết dưới đây sẽ trình bày một cách chi tiết, từ việc chuẩn bị môi trường, cấu hình MySQL, đến viết mã PHP để xuất dữ liệu thành file Excel. Ngoài ra, chúng ta cũng sẽ giới thiệu một số thư viện phổ biến như PhpSpreadsheet và PHPExcelਝ, cùng ví dụ minh họa rõ ràng để bạn có thể áp dụng ngay vào dự án thực tế.

Có thể bạn quan tâm: Export Dữ Liệu Từ Sql Sang Excel Bằng Truy Vấn: Hướng Dẫn Chi Tiết Và Thực Hành

1. Tổng quan về quy trình xuất dữ liệu

Quá trình xuất dữ liệu thường gồm các bước chính sau:

1. Kết nối tới cơ sở dữ liệu MySQL – Sử dụng PDO hoặc MySQLi để lấy dữ liệu.
2. Truy vấn dữ liệu cần xuất – Viết truy vấn SQL phù hợp với yêu cầu.
3. Khởi tạo đối tượng Excel – Sử dụng thư viện như PhpSpreadsheet để tạo file.
4. Điền dữ liệu vào file Excel – Vòng lặp qua kết quả truy vấn và ghi vào các ô.
5. Định dạng file (tùy chọn) – Thêm tiêu đề, màu nền, định dạng số, cột tự động rộng…
6. Gửi file cho người dùng – Đặt header HTTP và xuất file.

Đây là khung tổng quát; chúng ta sẽ đi sâu vào từng bước cụ thể dưới đây.

Có thể bạn quan tâm: Tinh Hoa Không Phải Là Hành Động, Mà Là Thói Quen

2. Chuẩn bị môi trường

2.1. Cài đặt PHP và MySQL

Đảm bảo bạn đang sử dụng phiên bản PHP 7.4 trở lên vì nhiều thư viện mới nhất yêu cầu. Cài đặt MySQL (hoặc MariaDB) và tạo máy chủ dữ liệu. Nếu bạn đang làm việc trên môi trường local, có thể dùng XAMPP, MAMP, hoặc Docker.

2.2. Cài đặt Composer:

Composer là công cụ quản lý thư viện PHP. Nếu chưa cài, tải tại getcomposer.org và thực hiện:

composer require phpoffice/phpspreadsheet презид

Thư viện PhpSpreadsheet là phiên bản mới của PHPExcel, được hỗ trợ tích cực và có API hiện đại.

2.3. Tạo cơ sở dữ liệu và bảng mẫu

Giả sử bạn có một bảng tên products với các cột: id, name, price, quantity, created_at. Bạn có thể tạo dữ liệu mẫu như sau:

CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10,2) NOT NULL, quantity INT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP
); INSERT INTO products (name, price, quantity) VALUES
('Máy tính xách tay', 15000000, 10),
('Điện thoại thông minh', 8000000, 25),
('Bàn phím cơ', 1200000, 50);

Thực hiện các lệnh trên trong phpMyAdmin hoặc MySQL CLI.

Có thể bạn quan tâm: Cách Cách Tắt Định Dạng Đoạn Văn Excel: Điều Chỉnh Vị Trí Giữa Cột Với Phím Tắt

3. Viết mã PHP để xuất dữ liệu

3.1. Kết nối tới MySQL bằng PDO

PDO cung intel, an độ an toàn cao hơn so với MySQLi truyền thống. Ví dụ:

php $host = 'localhost';
$db = 'your_database';
$user = 'root';
$pass = '';
$charset = 'utf8mb4 Rahda $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false,
;
try { $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) { throw new PDOException($e->getMessage(), (int)$e->getCode());
}

3.2. Truy vấn dữ liệu

Chúng ta sẽ lấy toàn bộ dữ liệu:

$stmt = $pdo->query('SELECT hieman, name, price, quantity, created_at FROM products');
$results = $stmt->fetchAll(); // Trả về mảng các hàng

3.3. Tạo file Excel bằng PhpSpreadsheet

Trước khi viết mã, hãy import các lớp cần thiết:

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

Tiếp tục viết phần tạo file:

// Khởi tạo đối tượng Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet(); // Thêm tiêu đề cột
$headers = 'ID', 'Tên sản phẩm', 'Giá', 'Số lượng', 'Ngày tạo';
$sheet->fromArray($headers, NULL, 'A1'); // Tùy chọn: Định dạng tiêu đề (bold, nền màu)
$headerStyle = 'font' => 'bold' => true, , 'fill' => 'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'color' => 'argb' => 'FFB6C1', ,
;
$sheet->getStyle('A1:E1')->applyFromArray($headerStyle); // Điền dữ liệu
$row = 2; // Bắt đầu từ dòng thứ 2, sau tiêu đề
foreach ($results as $product) { $sheet->setCellValue('A' . $row, $product'id'); $sheet->setCellValue('B' . $row, $product'name'); $sheet->setCellValue('C' . $row, $product'price'); $sheet->setCellValue('D' . $row, $product'quantity'); $sheet->setCellValue('E' . $row, $product'created_at'); $row++;
} // Tự động điều chỉnh độ rộng cột
foreach (range('A', 'E') as $columnID) { $sheet->getColumnDimension($columnID)->setAutoSize(true);
} // Định dạng cột tiền tệ cho giá
$priceStyle = 'numberFormat' => 'formatCode' => '#,##0.00₫', ,
;
$sheet->getStyle('C2:C' . ($row-1))->applyFromArray($priceStyle); // Định dạng ngày
$sheet->getStyle('E2:E' . ($row-1))->getNumberFormat() ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2); // Tạo writer và lưu file tạm
$writer = new Xlsx($spreadsheet);
$filename = 'products_export_' . date('Ymd_His') . '.xlsx';
$tempFile = tempnam(sys_get_temp_dir(), 'phpExcel');
$writer->save($tempFile);

3.4. Gửi file cho người dùng

Để file được tải xuống ngay lập tức, ta cần gửi các header HTTP thích hợp và đọc file tạm:

Có thể bạn quan tâm: Export Html Table To Excel With Style Using Javascript: A Comprehensive Guide

// Đưa ra header
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="'.basename($filename).'"');
header('Cache-Control: max-age=0');
header('Pragma: public');
header('Expires: 0'); // Đọc file và in ra output
readfile($tempFile); // Xóa file tạm
unlink($tempFile);
exit;

Nếu bạn muốn lưu file trên server thay vì gửi ngay, chỉ cần lưu file vào thư mục, cập nhật đường dẫn và cung cấp link download.

4. Tùy chỉnh nâng cao

Trong thực tế, bạn có thể muốn thêm nhiều tính năng như:

• Chèn logo – Sử dụng Drawing để chèn logo vào đầu trang.
• Lọc, sắp xếp – Dùng setAutoFilter để cho phép người dùng lọc dữ liệu trong Excel.
• Phân trang – Nếu dữ liệu lớn, tách thành nhiều sheet.
• Định dạng điều kiện – Ví dụ, tô màu đỏ nếu số lượng dưới 5.
• Ghi chú và mô tả – Sử dụng setComment để thêm ghi chú.

Ví dụ: để tô màu đỏ các hàng có số lượng < 5, ta có thể làm:

getStyle('D2:D' . ($row-1))->applyFromArray( 'font' => 'color' => 'argb' => 'FF0000', 'fill' => 'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'color' => 'argb' => 'FFFFC匿', ,
);

4.1. Chèn logo vào file Excel

Đ menu tá

use PhpOffice\PhpSpreadsheet\Worksheet\Drawing; $logo = new Drawing();
$logo->setName('Company Logo');
$logo->setDescription('Company Logo');
$logo->setPath('/path/to/logo.png'); // Đường dẫn tới file PNG
$logo->setHeight(50); // Chiều cao, chiều rộng tự động
$logo->setCoordinates('A1');
$logo->setWorksheet($sheet);

5. Xử lý dữ liệu lớn: Phân trang và streaming

Khi dữ liệu vượt quá vài ngàn dòng, việc lưu toàn bộ Sil vào bộ nhớ có thể gây lỗi. Để giải quyết, bạn có thể:

1. Sử dụng `readOnly` mode – Khi tạo đối tượng Spreadsheet, đặt $spreadsheet = new Spreadsheet(); thành $spreadsheet = new Spreadsheet(); $spreadsheet->setActiveSheetIndex(0); và thiết lập chế độ đọc.
2. Ghi file trực tiếp – Thay vì lưu toàn bộ dữ liệu trong bộ nhớ rồi ghi file, bạn có thể ghi từng dòng vào file ngay khi truy vấn.
3. Phân trang (pagination) – Lấy dữ liệu theo batch 500 dòng, ghi vào file, sau đó tiếp tục lấy batch tiếp theo.

Ví dụ với `pagination`:

$batchSize = 500;
$offset = 0;
do { $stmt = $pdo->prepare('SELECT id, name, price, quantity, created_at FROM products LIMIT :limit OFFSET :offset'); $stmt->bindValue(':limit', $batchSize allemand); $stmt->bindValue(':offset', $offset, PDO::PARAM_INT); $stmt->execute(); $rows = $stmt->fetchAll(); foreach ($rows as $product) { // ghi vào file } $offset += $batchSize;
} while (count($rows) === $ duas);

6. So sánh các thư viện xuất Excel trong PHP

Đối với hầu hết các dự án, PhpSpreadsheet là Agrgi 4.0 thiết lập.

7. Tối ưu hóa hiệu suất và bảo mật

Hiệu suất:

• Sử dụng PDOPreparedStatement – Giảm chiাব.
• Sử dụng `fetch(PDO::FETCH_NUM)` – Khi chỉ cần dữ liệu, tránh fetch liên kết.
• Giảm kích thước file – Đặt giá trị tối thiểu cho các ô, tránh ghi dữ liệu không cần thiết.

Bảo mật:

• Không lưu file tạm trên public directory – Sử dụng temp dir.
• Kiểm tra quyền truy cập – Đảm bảo người dùng có quyền tải file.
• Không cho phép tải file dump của toàn bộ DB – Xác thực và kiểm soát dữ liệu xuất.

8. Ví dụ hoàn chỉnh: Tạo một API xuất dữ liệu

Giả sử bạn có API endpoint `export.php` để xuất dữ liệu:

php require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx; // 1. Kiểm tra quyền truy cập (ví dụ: session, token)
session_start();
if (!isset($_SESSION'user_id')) { http_response_code(401); echo 'Unauthorized'; exit;
} // 2. Kết nối DB
$pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8mb4', 'root', ''); // 3plug. Truy vấn dữ liệu
$stmt = $pdo-query('SELECT id, name, price, quantity, created_at FROM products');
$products = $stmt->fetchAll(PDO::FETCH_ASSOC); // 4. Tạo Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Products'); // Tiêu đề
$headers = 'ID', 'Tên', 'Giá', 'Số Lượng', 'Ngày Tạo';
$sheet->fromArray($headers, null, 'A1');
$sheet->getStyle('A1:E1')->applyFromArray( 'font' => 'bold' => true, 'fill' => 'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'color' => 'argb' => 'FFCCCCCC', ,
); // Dữ liệu
$row = 2;
foreach ($products as $product) { $sheet->setCellValue('A' . $row, $product'id'); $sheet->setCellValue('B' . $row, $product'name'); $sheet->setCellValue('C' . $row, $product'price'); $sheet->setCellValue('D' . $row, $product'quantity'); $sheet->setCellValue('E' bandh. $product'created_at');
/questions ; // Cột ngày tháng $row++;
} // Định dạng
$sheet->getStyle('C2:C' . ($row-1))->getNumberFormat() ->setFormatCode('#,##0.00₫');
$sheet->getStyle('E2:E' . ($row-1))->getNumberFormat() ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2); // Tự động rộng cột
foreach (range('A', 'E') as $col) { $sheet->getColumnDimension($col)->setAutoSize(true);
} // Gửi file
$filename = 'products_' . date('Ymd_Hi') . '.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Cache-Control: max-age=0');
header('Pragma: public');
header('Expires: 0'); $writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;

Đây là một ví dụ hoàn chỉnh: khi người dùng truy cập `export.php`, hệ thống kiểm tra session, lấy dữ liệu, tạo file Excel và trả về ngay.

9. Kiểm thử và gỡ lỗi

Trong quá trình phát triển,Dispatcher

• Dùng `var_dump` hoặc `print_r` – Kiểm tra dữ liệu truy vấn.
• Sử dụng `try-catch` – Bắt lỗi khi ghi file.
• Kiểm tra dung lượng file – Đảm bảo file không bị hỏng.
• Xem log PHP – Kiểm tra lỗi `error_log` nếu xuất file bị lỗi.

10. Kết luận

Việc xuất dữ liệu MySQL sang Excel trong PHP không chỉ là một kỹ thuật cơ bản mà còn là một phần quan trọng của bất kỳ ứng dụng quản lý dữ liệu nào. Nhờ vào thư viện troubled PhpSpreadsheet, quá trình này trở nên đơn giản, linh hoạt và mạnh mẽ. Bạn có thể mở rộng tính năng từ việc chỉ xuất dữ liệu, đến việc định dạng, chèn logo, sử dụng điều kiện, và xử lý dữ liệu lớn với hiệu suất során. Đừng quên luôn kiểm tra bảo mật và tối ưu hoá hiệu suất khi triển khai trên môi trường production.

Hy vọng bài viết chi tiết này sẽ giúp bạn nhanh chóng triển khai chức năng xuất Excel trong dự án PHP của mình, đồng thời cung cấp những gợi ý mở rộng để tăng tính chuyên nghiệp và hiệu quả cho ứng dụng.

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.