Recently in the operation of PHP export Excel, synchronous generation at the beginning, and then directly to an asynchronous generation. Of course, the most involved in this is the optimization of time. This article summarizes my suggestions for selection and data processing optimization in the process of using PHP to generate Excel

  1. PHP generates Excel with a ready-made class library

    1. phpexcelBut no longer maintain support for PHP5.3 to PHP5.6;
    2. phpspreadsheetThis is still very good, but I found in the use of large data volume is not available, PHP5.6 can be used above
    3. fputcsv()A format in Excel is a good solution when it has a large amount of data

  2. The data can be generated according to the above three. In general, there are two kinds of data. How do we choose

    1. If you are using the PHP class library, it is recommended that you upgrade PHP to the latest VERSION of PHP7 to save memory and make calculations faster
    2. Use PHP Spreadsheet, which is officially up to date
    3. If you’re not using the PHP class library, thenfputcsv()Is the best way
  3. Why did I choose CSV
    1. CSV file is small
    2. The amount of CSV file data can be large or small
    3. CSV generation does not take a lot of CPU and time, while Excel generation time increases exponentially as the amount of file data increases. The time I’m referring to has excluded data processing time. I made a roughly calculation on my server (ALI Cloud ECS 2 core CPU PHP has reached 100% CPU) to generate about 29900 Excel pieces, which took nearly 30 minutes after the data processing. CSV generation goes along with the data processing, and files are generated after the data processing.

  4. How to optimize data processing time

    1. Reduce nested loops when data loops
    2. Generate CSV or Excel data objects at a time to reduce the number of database queries. If you need to use other data, generate data of the corresponding data structure in advance

  5. Data processing in CSV to ensure Chinese display, numbers are not displayed as scientific notation

    1. How to handle Chinesemb_convert_encoding($str, 'gbk', 'utf8')This ensures that CSV can display Chinese characters properly when opened in Excel
    2. How to deal with numbers such as mobile phone number and ID number'shenfenzheng' => '="300000000000003333"',through=Enforces the contents of a cell to be a string