First of all, there are some libraries in PHP that manipulate exported data, which are also very common.

Such as:

  1. phpexcel

  2. phpspreadsheet


phpexcel

​

The library is no longer up to date, it supports PHP5.3 to PHP5.6, and performance is improving as the PHP version is upgraded.


phpspreadsheet


Phpexcel is a replacement, support php7 version, in performance is certainly greater than PHPExcel. I still recommend using this library if you use it to export


Back to the main body, today we are using CSV to export.


What is CSV, and what is it good for?


Comma-separated Values (CSV, sometimes called character Separated Values because the characters can also not be commas) are files that store table data (numeric and text) in plain text. Plain text means that the file is a sequence of characters and contains no data that must be interpreted as binary numbers. A CSV file consists of any number of records separated by a newline character. Each record consists of fields separated by other characters or strings, most commonly commas or tabs. Typically, all records have exactly the same sequence of fields. They are usually plain text files.


Its advantages are as follows:

  1. CSV file is small

  2. CSV file data can be stored in a large or small amount

  3. CSV generation does not need to occupy a lot of CPU resources and time, while excel generation time and consumption of file data on the server increased exponentially. If you have used Excel export, you will find that the PHP server memory is exhausted or the export time is too long.


Random data generation (the actual situation should be batch calls to the database to fetch data)


$arr = [];
for ($i = 0; $i < 2080000; $i++) {     
     $arr[] = [          
        Test '1'.'test 2'.'test 3'      
    ];  
}Copy the code


Write PHP core export code


We’re going to use PHP coroutines and CSV functions when we write them, and that’s the problem again.


What is a coroutine?



Coroutine is the name of a Coroutine, which is a unit of execution smaller than a thread. Another way to think about it is a lightweight version of threads. So coroutines occupy fewer stacks than threads. As we all know, thread calls are system-level, while coroutines are user-level calls. So there is a big performance improvement at this level, and there is no need for thread switching to consume resources.



How do we use coroutines in PHP, the way we use coroutines in PHP is we use yield to do it, the point is, yield doesn’t mean coroutines, but we use it to implement the properties of coroutines.


The PHP code:

set_time_limit(0);// Set no timeout
$output = fopen('xxx.csv'.'w');
Fputcsv ($output, ["1","2","3"]);
// Use yield to schedule data sources
$data = foreach ($dataList as $k => $v) {    
    yield $v;
}
// Write the file using the final returned scheduler
foreach ($data as $value) { 
   fputcsv($output, $value);
}
// Close file fclose($output);Copy the code


However, when using this code, we need to consider the limit on the number of CSV files that Excel can open.

Excel2003, can display only 65536 lines.

Excel2007 and above can display 1,048,576 lines.

Careful students will find out how to view it if you export more than millions or even tens of millions of data. In fact, from another point of view, we can use the form of subcontracting to divide 104W data sets into a batch package into ZIP and send to users.


And then maybe you’ll see why I opened CSV in Excel since it’s all garbled. In fact, Excel needs to deal with BOM head, but our export does not have BOM head. Add the following code when exporting.


$bom = chr(0xEF) . chr(0xBB) . chr(0xBF);
// The bom header can be written before the heading
fputcsv($output, [$bom]);Copy the code


If Chinese characters are garbled, you can try the following code


mb_convert_encoding($str, 'gbk'.'utf8')Copy the code


If you don’t want numbers to become scientific notation, try the following schemes


Use the = character to make the contents of a cell a string, as in'= "10000000000000000"'Copy the code


The final code:

/** * Coroutines export a large amount of data * @param array$dataListData source * @param string$pathFile path * @param string$filenameThe file name is [department, name] * @param callable$callbackThe custom callback returns an array * @param array$config     
 * @return bool     
 * @throws \Exception    
 */    
public static function createMoreDataToCsvFile( array $dataList,string $path, string $filename,allable $callback,array $config) { set_time_limit(0); // set no timeout ini_set('memory_limit'.'1024M');​        
    $zipFilename = $path . $filename;​        
    $fileInfoArr = explode('. '.$filename);​        
    $zip = new \ZipArchive();​        
    $opened = $zip->open($zipFilename, ZIPARCHIVE::CREATE);​        
    if ($opened! = =true) {           
         throw new \Exception('[Zip Error Code]: ' . $opened);        
    }​        
    $newFilename = $fileInfoArr[0].'_' . $config['num'].'.csv';        
    $newPathFilename = $path . $newFilename; // Buffer writes temporary files$output = fopen($newPathFilename.'w'); / / write headif(! empty($config['headerRow'])) {// Add a bom header to Excel$bom = chr(0xEF) . chr(0xBB) . chr(0xBF);​            
        foreach ($config['headerRow'] as $key= >$value) {                
            $config['headerRow'] [$key] = (string)$value;            
        }​            
        fputcsv($output[$bom]);​           
        fputcsv($output.$config['headerRow']); } // Execute the coroutine$data = (            
        function () use ($dataList.$callback) {                
            foreach ($dataList as $k= >$v) {                   
                 yield $callback($v);               
             }            
        })($dataList.$callback);​        

    foreach ($data as $value) {           
         fputcsv($output.$value);        
    }​       

     $newFileSize = filesize($newPathFilename);​        
     $status = $zip->addFile(           
         $newPathFilename.$newFilename,            
        0,            
        $newFileSize        
    );​        

    if ($status= = =false) {           
         $zip->close();            
        @unlink($newPathFilename);            
        throw new \RuntimeException(                
            sprintf('Error adding file to package :%s:%s'.$filename.$newPathFilename)); }$zip->close();        
    @unlink($newPathFilename);        
    fclose($output);​        
    return true;    
}Copy the code

Final demo operation code:


$stime = microtime(true);
$arr = [];
$num = 0;
for ($i = 0; $i < 2080000; $i++) {  
    $arr[] = [     
         Test '1'.'test 2'.'test 3'  
    ];​  
    if (count($arr) === 1040000) {​   
         $num++;​    
         ExportHelper::createMoreDataToCsvFile(       
             $arr,        
            './csv/'.'test. Zip'.function ($arr) {            
                return$arr; },'headerRow'= > ['Test column number 1'.'Test column number 2'.'Test column number 3',].'num' => $num,        
            ]    
    );   
     $arr = [];    
    } 
}​ $
etime = microtime(true);
Echo $etime - $stime;
// Output time
// 208W is about 32sCopy the code


If you are interested in these, welcome to comment, favorites, forward to give support! Word-wrap: break-word! Important; “> < p style =” max-width: 100%;