Easily solve PHPExcel export 10W line timeout and memory overflow problems

Easily solve PHPExcel export 10W line timeout and memory overflow problems

Topic 1: Break through the intractable diseases of PHPExcel

This article uses a lightweight PHP Excel operation library-PHP_XLSXWriter

1. Solve the problem

  • It only takes 5.26 seconds to export 10w rows of excel data, so there is no need to worry about timeout when the excel export exceeds 1w rows (max execution time in php.ini )
  • The memory stack overflows. Students who have used PHPExcel should be aware that an error is reported before the exported file line reaches 1w line. The reason is that a single PHP process exceeds the memory_limit configured by php.ini
  • Solve the problem that the style is lost when csv is exported, long numbers become scientific notation, and the problem of garbled characters
#Use the lightweight excel library PHP_XLSXWriter
$timeStart = microtime(true);
require_once __DIR__."/../PHP_XLSXWriter/vendor/autoload.php";

$writer = new XLSXWriter();
$sheetHeader = [
    'Product id'=>'string',
    'Inventory quantity'=>'string',
    'Unit price'=>'string',
    'Seller contact number'=>'string'//Long numbers no longer have to worry about becoming scientific notation

$writer->writeSheetHeader('Sheet1', $sheetHeader);//optional
for($i=0; $i<100000; $i++)
    $s1 = $i+1;
    $s2 = mt_rand(0,1000);
    $s3 = mt_rand(100,999)/10;
    $s4 = "Product".$s1;
    $s5 = "13713147601";//Just type in
    $writer->writeSheetRow('Sheet1', array($s1, $s2, $s3, $s4, $s5) );
echo floor((memory_get_peak_usage())/1024/1024)."MB";
echo "";
echo microtime(true) -$timeStart;

Source address: https://gitee.com/nodestudy/The-PHP-Best-Practice

File directory structure:

1. Solve the problem of the column

Recently, there is a demand. The data is exported in THINKPHP3.2. There are 43 data items. When exporting, it will be wrong Invalid cell coordinate [1

Because phpexcel's cell.php has 26 columns by default, and in the judgment method, the default is from az and then the next column is aa1, but in fact, in excel, z should be followed by AA, AB, AC, AD, AE, AF In this way, the judgment method needs to be changed. I wrote an excel export before. Just change one of the methods. The idea of ​​the change is that when there are more than 26 columns, the next column name should be AA, AB, not aa1, AA2 , The improvement method is as follows

1. Convert the letters in the column to a numeric serial number for use, the code is as follows:

$phpExcel = new PHPExcel();
//$array needs to export the array
foreach($array as $k => $value){
$title_col = PHPExcel_Cell::stringFromColumnIndex($k);//start from 0 AZ, AA, AB...
$phpExcel->getActiveSheet()->setCellValue($title_col."1",$value);//title row A1-Z1,AA1,AB1...

2. Convert the numeric serial number of the column to letters for use, the code is as follows:

Reference: https://cloud.tencent.com/developer/article/1480133 Easily solve PHPExcel exporting 10W line timeout and memory overflow problems-Cloud + Community-Tencent Cloud