Hướng dẫn php read large excel file - php đọc tệp excel lớn

9

Mới! Lưu câu hỏi hoặc câu trả lời và sắp xếp nội dung yêu thích của bạn. Tìm hiểu thêm.
Learn more.

Tôi đang cố đọc một tệp excel lớn hơn 100MB bằng cách sử dụng PHPExcel nhưng nó bị sập trong khi tải tệp. Tôi không cần bất kỳ kiểu dáng nào. Tôi đã thử sử dụng:

$objReader->setReadDataOnly(true);

Nhưng nó vẫn gặp sự cố.

Có cách nào hiệu quả để đọc kích thước này của tệp excel trong PHP không?

Hỏi ngày 13 tháng 10 năm 2015 lúc 6:29Oct 13, 2015 at 6:29

Husnain Alihusnain AliHusnain Ali

2.1302 Huy hiệu vàng18 Huy hiệu bạc25 Huy hiệu Đồng2 gold badges18 silver badges25 bronze badges

7

Hãy thử Spout: https://github.com/box/spout.

Đây là một thư viện PHP được tạo để giải quyết vấn đề của bạn (đọc/viết các tệp lớn). Đây là lý do tại sao nó hoạt động:

Các thư viện khác giữ một đại diện của bảng tính trong bộ nhớ khiến chúng phải chịu các lỗi bộ nhớ. Sử dụng một số chiến lược bộ đệm sẽ giúp với các loại lỗi này nhưng sẽ ảnh hưởng đến hiệu suất khá tệ.

Mặt khác, Spout sử dụng các luồng để đọc hoặc ghi dữ liệu. Điều này có nghĩa là chỉ có một hàng được giữ trong bộ nhớ mọi lúc, tất cả các hàng đọc/viết được giải phóng khỏi bộ nhớ. Điều này cho phép đọc/ghi nhanh của bộ dữ liệu ở bất kỳ kích thước nào! Hãy thử một lần :)

Đã trả lời ngày 13 tháng 10 năm 2015 lúc 15:30Oct 13, 2015 at 15:30

AdrienadrienAdrien

1.8631 Huy hiệu vàng11 Huy hiệu bạc22 Huy hiệu đồng1 gold badge11 silver badges22 bronze badges

1

Spout vừa tiết kiệm thời gian của tôi! Tôi không thể đọc một tệp lớn với phpoffice/phpspreedsheet với nhiều kích thước bộ nhớ lỗi nghiêm trọng và với Spout, nó hoạt động như một sự quyến rũ.

Đã trả lời ngày 13 tháng 10 năm 2021 lúc 6:54Oct 13, 2021 at 6:54

Excel là định dạng tệp được sử dụng nhiều nhất để chia sẻ dữ liệu.

Trong hướng dẫn này, chúng tôi sẽ tìm hiểu cách đọc và ghi các tệp XLSX trong PHP với sự trợ giúp của các ví dụ.

Cuối cùng nhưng không kém phần quan trọng, chúng tôi sẽ phân tích một tệp Excel 1 triệu dòng theo cách rất nhanh và tiết kiệm bộ nhớ.

Toàn bộ mã và ví dụ có sẵn trong kho lưu trữ GitHub; Nó được đóng gói như một dự án Symfony đơn giản và cung cấp hình ảnh Docker.

Thư viện PHP tốt nhất để phân tích và viết các tệp excel

Một số thư viện cho phép làm việc với các tệp Excel; Họ đến với ưu và nhược điểm của họ.

Nổi tiếng nhất là phpoffice/phpspreadsheet; Nó có hơn 10k sao trên GitHub. Thư viện này cho phép đọc, tạo và viết tài liệu bảng tính trong PHP. Thư viện này trước đây được đặt tên là phpoffice/phpexcel, dự án đã được khấu hao vào năm 2017 và

composer require box/spout

composer require box/spout

Người thách thức là

composer require box/spout

Không có cuộc tranh luận nào về sự hỗ trợ của các định dạng tệp khác nhau: phpoffice/phpspreadsheet hỗ trợ nhiều định dạng hơn, đặc biệt là các định dạng cũ nhất rất thuận tiện khi bạn cần phân tích các tệp XLS được tạo bằng Excel 2003 97 hoặc 95.

composer require box/spout

Bảng tính (.xml) Excel 2003

❌

composer require box/spout

Sử dụng trường hợp

Lựa chọn thư viện tốt nhất

Đọc hoặc viết các định dạng Excel hiện đại và cũ

________số 8

composer require box/spout

Đọc hoặc ghi các tệp XLSX một cách hiệu quả hoặc phân tích các tệp lớn.

composer require box/spout2

Có các thư viện khác, nhưng chúng không mang lại bất cứ thứ gì nhiều hơn hoặc rất khác nhau.

composer require box/spout

use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;

$path = 'data/movies-100.xlsx';
# open the file
$reader = ReaderEntityFactory::createXLSXReader();
$reader->open($path);
# read each cell of each row of each sheet
foreach ($reader->getSheetIterator() as $sheet) {
    foreach ($sheet->getRowIterator() as $row) {
        foreach ($row->getCells() as $cell) {
            var_dump($cell->getValue());
        }
    }
}
$reader->close();

composer require box/spout

Đọc tệp Excel (XLSX)

Chúng tôi phân tích tệp XLSX hiện tại của chúng tôi:

1. Tạo đầu đọc Excel bằng cách sử dụng

   use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
   
   $path = 'data/movies-100.xlsx';
   # open the file
   $reader = ReaderEntityFactory::createXLSXReader();
   $reader->open($path);
   # read each cell of each row of each sheet
   foreach ($reader->getSheetIterator() as $sheet) {
       foreach ($sheet->getRowIterator() as $row) {
           foreach ($row->getCells() as $cell) {
               var_dump($cell->getValue());
           }
       }
   }
   $reader->close();
   

   3
2. Mở tệp XSLX bằng

   use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
   
   $path = 'data/movies-100.xlsx';
   # open the file
   $reader = ReaderEntityFactory::createXLSXReader();
   $reader->open($path);
   # read each cell of each row of each sheet
   foreach ($reader->getSheetIterator() as $sheet) {
       foreach ($sheet->getRowIterator() as $row) {
           foreach ($row->getCells() as $cell) {
               var_dump($cell->getValue());
           }
       }
   }
   $reader->close();
   

   4
3. Duyệt từng tờ của bảng tính với

   use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
   
   $path = 'data/movies-100.xlsx';
   # open the file
   $reader = ReaderEntityFactory::createXLSXReader();
   $reader->open($path);
   # read each cell of each row of each sheet
   foreach ($reader->getSheetIterator() as $sheet) {
       foreach ($sheet->getRowIterator() as $row) {
           foreach ($row->getCells() as $cell) {
               var_dump($cell->getValue());
           }
       }
   }
   $reader->close();
   

   5
4. Duyệt từng hàng của trang tính với

   use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
   
   $path = 'data/movies-100.xlsx';
   # open the file
   $reader = ReaderEntityFactory::createXLSXReader();
   $reader->open($path);
   # read each cell of each row of each sheet
   foreach ($reader->getSheetIterator() as $sheet) {
       foreach ($sheet->getRowIterator() as $row) {
           foreach ($row->getCells() as $cell) {
               var_dump($cell->getValue());
           }
       }
   }
   $reader->close();
   

   6
5. Duyệt từng ô của hàng với

   use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
   
   $path = 'data/movies-100.xlsx';
   # open the file
   $reader = ReaderEntityFactory::createXLSXReader();
   $reader->open($path);
   # read each cell of each row of each sheet
   foreach ($reader->getSheetIterator() as $sheet) {
       foreach ($sheet->getRowIterator() as $row) {
           foreach ($row->getCells() as $cell) {
               var_dump($cell->getValue());
           }
       }
   }
   $reader->close();
   

   7
6. Truy cập từng giá trị của ô với

   use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
   
   $path = 'data/movies-100.xlsx';
   # open the file
   $reader = ReaderEntityFactory::createXLSXReader();
   $reader->open($path);
   # read each cell of each row of each sheet
   foreach ($reader->getSheetIterator() as $sheet) {
       foreach ($sheet->getRowIterator() as $row) {
           foreach ($row->getCells() as $cell) {
               var_dump($cell->getValue());
           }
       }
   }
   $reader->close();
   

   8
7. Đóng trình đọc excel (và tệp) với

   use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
   
   $path = 'data/movies-100.xlsx';
   # open the file
   $reader = ReaderEntityFactory::createXLSXReader();
   $reader->open($path);
   # read each cell of each row of each sheet
   foreach ($reader->getSheetIterator() as $sheet) {
       foreach ($sheet->getRowIterator() as $row) {
           foreach ($row->getCells() as $cell) {
               var_dump($cell->getValue());
           }
       }
   }
   $reader->close();
   

   9

use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;

$path = 'data/movies-100.xlsx';
# open the file
$reader = ReaderEntityFactory::createXLSXReader();
$reader->open($path);
# read each cell of each row of each sheet
foreach ($reader->getSheetIterator() as $sheet) {
    foreach ($sheet->getRowIterator() as $row) {
        foreach ($row->getCells() as $cell) {
            var_dump($cell->getValue());
        }
    }
}
$reader->close();

Chúng ta có thể sử dụng đoạn trích này để nhập các hàng excel vào cơ sở dữ liệu. Trong trường hợp đó, chúng tôi cần chèn hoặc cập nhật dữ liệu cho mỗi hàng phân tích cú pháp.

Viết tệp Excel (XLSX)

Chúng tôi viết trong một tệp XLSX:

1. Tạo người viết excel bằng cách sử dụng

   use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
   
   $rows = [
       ['id', 'title', 'poster', 'overview', 'release_date', 'genres'],
       [181808, "Star Wars: The Last Jedi", "https://image.tmdb.org/t/p/w500/kOVEVeg59E0wsnXmF9nrh6OmWII.jpg", "Rey develops her newly discovered abilities with the guidance of Luke Skywalker, who is unsettled by the strength of her powers. Meanwhile, the Resistance prepares to do battle with the First Order.", 1513123200, "Documentary"],
       [383498, "Deadpool 2", "https://image.tmdb.org/t/p/w500/to0spRl1CMDvyUbOnbb4fTk3VAd.jpg", "Wisecracking mercenary Deadpool battles the evil and powerful Cable and other bad guys to save a boy's life.", 1526346000, "Action, Comedy, Adventure"],
       [157336, "Interstellar", "https://image.tmdb.org/t/p/w500/gEU2QniE6E77NI6lCU6MxlNBvIx.jpg", "Interstellar chronicles the adventures of a group of explorers who make use of a newly discovered wormhole to surpass the limitations on human space travel and conquer the vast distances involved in an interstellar voyage.",1415145600,"Adventure, Drama, Science Fiction"]
   ];
   $path = 'data/new-file.xlsx';
   $writer = WriterEntityFactory::createXLSXWriter();
   $writer->openToFile($path);
   foreach ($rows as $row) {
       $rowFromValues = WriterEntityFactory::createRowFromArray($row);
       $writer->addRow($rowFromValues);
   }
   $writer->close();
   

   0
2. Mở tệp XSLX bằng

   use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
   
   $rows = [
       ['id', 'title', 'poster', 'overview', 'release_date', 'genres'],
       [181808, "Star Wars: The Last Jedi", "https://image.tmdb.org/t/p/w500/kOVEVeg59E0wsnXmF9nrh6OmWII.jpg", "Rey develops her newly discovered abilities with the guidance of Luke Skywalker, who is unsettled by the strength of her powers. Meanwhile, the Resistance prepares to do battle with the First Order.", 1513123200, "Documentary"],
       [383498, "Deadpool 2", "https://image.tmdb.org/t/p/w500/to0spRl1CMDvyUbOnbb4fTk3VAd.jpg", "Wisecracking mercenary Deadpool battles the evil and powerful Cable and other bad guys to save a boy's life.", 1526346000, "Action, Comedy, Adventure"],
       [157336, "Interstellar", "https://image.tmdb.org/t/p/w500/gEU2QniE6E77NI6lCU6MxlNBvIx.jpg", "Interstellar chronicles the adventures of a group of explorers who make use of a newly discovered wormhole to surpass the limitations on human space travel and conquer the vast distances involved in an interstellar voyage.",1415145600,"Adventure, Drama, Science Fiction"]
   ];
   $path = 'data/new-file.xlsx';
   $writer = WriterEntityFactory::createXLSXWriter();
   $writer->openToFile($path);
   foreach ($rows as $row) {
       $rowFromValues = WriterEntityFactory::createRowFromArray($row);
       $writer->addRow($rowFromValues);
   }
   $writer->close();
   

   1 cho phép tạo tệp excel
3. Lặp lại trên các hàng chúng tôi muốn chèn
4. Tạo từng hàng Excel với

   use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
   
   $rows = [
       ['id', 'title', 'poster', 'overview', 'release_date', 'genres'],
       [181808, "Star Wars: The Last Jedi", "https://image.tmdb.org/t/p/w500/kOVEVeg59E0wsnXmF9nrh6OmWII.jpg", "Rey develops her newly discovered abilities with the guidance of Luke Skywalker, who is unsettled by the strength of her powers. Meanwhile, the Resistance prepares to do battle with the First Order.", 1513123200, "Documentary"],
       [383498, "Deadpool 2", "https://image.tmdb.org/t/p/w500/to0spRl1CMDvyUbOnbb4fTk3VAd.jpg", "Wisecracking mercenary Deadpool battles the evil and powerful Cable and other bad guys to save a boy's life.", 1526346000, "Action, Comedy, Adventure"],
       [157336, "Interstellar", "https://image.tmdb.org/t/p/w500/gEU2QniE6E77NI6lCU6MxlNBvIx.jpg", "Interstellar chronicles the adventures of a group of explorers who make use of a newly discovered wormhole to surpass the limitations on human space travel and conquer the vast distances involved in an interstellar voyage.",1415145600,"Adventure, Drama, Science Fiction"]
   ];
   $path = 'data/new-file.xlsx';
   $writer = WriterEntityFactory::createXLSXWriter();
   $writer->openToFile($path);
   foreach ($rows as $row) {
       $rowFromValues = WriterEntityFactory::createRowFromArray($row);
       $writer->addRow($rowFromValues);
   }
   $writer->close();
   

   2
5. Chèn từng hàng trong excel với

   use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
   
   $rows = [
       ['id', 'title', 'poster', 'overview', 'release_date', 'genres'],
       [181808, "Star Wars: The Last Jedi", "https://image.tmdb.org/t/p/w500/kOVEVeg59E0wsnXmF9nrh6OmWII.jpg", "Rey develops her newly discovered abilities with the guidance of Luke Skywalker, who is unsettled by the strength of her powers. Meanwhile, the Resistance prepares to do battle with the First Order.", 1513123200, "Documentary"],
       [383498, "Deadpool 2", "https://image.tmdb.org/t/p/w500/to0spRl1CMDvyUbOnbb4fTk3VAd.jpg", "Wisecracking mercenary Deadpool battles the evil and powerful Cable and other bad guys to save a boy's life.", 1526346000, "Action, Comedy, Adventure"],
       [157336, "Interstellar", "https://image.tmdb.org/t/p/w500/gEU2QniE6E77NI6lCU6MxlNBvIx.jpg", "Interstellar chronicles the adventures of a group of explorers who make use of a newly discovered wormhole to surpass the limitations on human space travel and conquer the vast distances involved in an interstellar voyage.",1415145600,"Adventure, Drama, Science Fiction"]
   ];
   $path = 'data/new-file.xlsx';
   $writer = WriterEntityFactory::createXLSXWriter();
   $writer->openToFile($path);
   foreach ($rows as $row) {
       $rowFromValues = WriterEntityFactory::createRowFromArray($row);
       $writer->addRow($rowFromValues);
   }
   $writer->close();
   

   3
6. Đóng người viết Excel (và tệp) với

   use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
   
   $rows = [
       ['id', 'title', 'poster', 'overview', 'release_date', 'genres'],
       [181808, "Star Wars: The Last Jedi", "https://image.tmdb.org/t/p/w500/kOVEVeg59E0wsnXmF9nrh6OmWII.jpg", "Rey develops her newly discovered abilities with the guidance of Luke Skywalker, who is unsettled by the strength of her powers. Meanwhile, the Resistance prepares to do battle with the First Order.", 1513123200, "Documentary"],
       [383498, "Deadpool 2", "https://image.tmdb.org/t/p/w500/to0spRl1CMDvyUbOnbb4fTk3VAd.jpg", "Wisecracking mercenary Deadpool battles the evil and powerful Cable and other bad guys to save a boy's life.", 1526346000, "Action, Comedy, Adventure"],
       [157336, "Interstellar", "https://image.tmdb.org/t/p/w500/gEU2QniE6E77NI6lCU6MxlNBvIx.jpg", "Interstellar chronicles the adventures of a group of explorers who make use of a newly discovered wormhole to surpass the limitations on human space travel and conquer the vast distances involved in an interstellar voyage.",1415145600,"Adventure, Drama, Science Fiction"]
   ];
   $path = 'data/new-file.xlsx';
   $writer = WriterEntityFactory::createXLSXWriter();
   $writer->openToFile($path);
   foreach ($rows as $row) {
       $rowFromValues = WriterEntityFactory::createRowFromArray($row);
       $writer->addRow($rowFromValues);
   }
   $writer->close();
   

   4

use Box\Spout\Writer\Common\Creator\WriterEntityFactory;

$rows = [
    ['id', 'title', 'poster', 'overview', 'release_date', 'genres'],
    [181808, "Star Wars: The Last Jedi", "https://image.tmdb.org/t/p/w500/kOVEVeg59E0wsnXmF9nrh6OmWII.jpg", "Rey develops her newly discovered abilities with the guidance of Luke Skywalker, who is unsettled by the strength of her powers. Meanwhile, the Resistance prepares to do battle with the First Order.", 1513123200, "Documentary"],
    [383498, "Deadpool 2", "https://image.tmdb.org/t/p/w500/to0spRl1CMDvyUbOnbb4fTk3VAd.jpg", "Wisecracking mercenary Deadpool battles the evil and powerful Cable and other bad guys to save a boy's life.", 1526346000, "Action, Comedy, Adventure"],
    [157336, "Interstellar", "https://image.tmdb.org/t/p/w500/gEU2QniE6E77NI6lCU6MxlNBvIx.jpg", "Interstellar chronicles the adventures of a group of explorers who make use of a newly discovered wormhole to surpass the limitations on human space travel and conquer the vast distances involved in an interstellar voyage.",1415145600,"Adventure, Drama, Science Fiction"]
];
$path = 'data/new-file.xlsx';
$writer = WriterEntityFactory::createXLSXWriter();
$writer->openToFile($path);
foreach ($rows as $row) {
    $rowFromValues = WriterEntityFactory::createRowFromArray($row);
    $writer->addRow($rowFromValues);
}
$writer->close();

Chúng ta có thể sử dụng đoạn trích này để xuất dữ liệu để Excel. Chẳng hạn, bằng cách đọc các hàng từ bảng cơ sở dữ liệu để ghi từng hàng trong tệp XLSX.

Bên cạnh Microsoft Excel, Google Sheets đang nhận được rất nhiều lực kéo từ những người dùng cần cộng tác gần thời gian thực trên dữ liệu kinh doanh. Google cung cấp API để tạo, đọc và cập nhật dữ liệu Google Sheets bằng PHP.

Đọc một tệp excel lớn một cách hiệu quả trong khi sử dụng bộ nhớ thấp

Một vấn đề phổ biến trong khi thao túng các tệp lớn là tiêu thụ bộ nhớ và đọc hoặc viết hiệu quả.

Tin tuyệt vời là Spout tính đến thử thách này và cho phép duyệt nội dung tệp mà không tải mọi thứ trong bộ nhớ.

Hãy lấy một ví dụ về tệp XLSX chứa 1M dòng; Tệp này trọng lượng 200mb.

-rw-rw-rw- 1 nico nico 205M mars 12 10:10 data/movies-1000000.xlsx

Hãy tải tất cả nội dung của nó:

// I'm using here the Symfony Console & StopWatch components
$section = 'read_excel_file';
$this->stopwatch->start($section);
$path = 'data/movies-1000000.xlsx';
$reader = ReaderEntityFactory::createXLSXReader();
$reader->open($path);
# read each cell of each row of each sheet
foreach ($reader->getSheetIterator() as $sheet) {
    foreach ($sheet->getRowIterator() as $row) {
        $cells = $row->getCells();
        foreach ($cells as $cell) {
            // we do nothing, but we want to ensure we browse each cell
        }
    }
}
$this->stopwatch->stop($section);
$output->writeln("I read 1.000.000 rows from the Excel File ".$path);
$output->writeln((string) $this->stopwatch->getEvent($section));

Và đây là kết quả:

I read 1.000.000 rows from the Excel File data/movies-1000000.xlsx
default/read_excel_file: 8.00 MiB - 104666 ms

Chúng tôi tải các dòng 1M của chúng tôi chỉ bằng 8MB bộ nhớ (tôi đang sử dụng PHP8). (I'm using PHP8).

Khi phát trực tuyến đọc và xử lý dữ liệu, bạn có thể áp dụng các phép biến đổi phong phú trong khi giữ cho việc sử dụng bộ nhớ thấp và thời gian thực hiện nhanh.

Tải xuống mã và ví dụ

Bạn có thể tìm thấy tất cả các mã và ví dụ trong kho GitHub này.

Nó được đóng gói như một dự án Symfony đơn giản, một bộ lệnh, nó cũng đi kèm với hình ảnh Docker. 🐋

Làm cách nào để xử lý một tệp excel lớn?

Làm thế nào để bạn đọc và viết tệp excel trong PHP?

Làm cách nào để nhận tệp XLSX trong PHP?

Làm thế nào để bạn đọc dữ liệu từ bảng Excel và chèn vào bảng cơ sở dữ liệu trong PHP?