How to use phpspreadsheet to cut large excel files (with code)

This article introduces to you how to use phpspreadsheet to cut large excel files (with code). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Background:

Using phpspreadsheet can easily parse excel files, but the memory consumption of phpspreadsheet is also relatively large. I have tried parsing nearly 5M of pure text and the memory usage of excel will exceed that of php. The default maximum memory is 128M.
Of course this can be solved by adjusting the memory size, but it is more dangerous when the amount of concurrency is large. So today I will introduce the second method, using phpspreadsheet to cut the excel file. This is a method of exchanging time for space, so it can generally be used for low timeliness requirements.

Method:

First put a function readCell provided by the phpspreadsheet official website, and we can use this function to perform cutting.

First pre-read the excel file, mainly to obtain all worksheets and the number of data rows under the worksheet. At this stage, the readCell method always returns false. We only need to record the worksheet that readCell comes in. and the number of data rows.

The next step is to analyze the obtained records and determine how many rows of original excel data need to be loaded into each part of the data. It should be noted that in order to avoid content confusion, do not cut the contents of the two worksheets together.

The last step is to loop through the analyzed data and use readCell again to obtain each part of the data. Note that each time you read the file, you must use the disconnectWorksheets method to clean up the memory of phpspreadsheet.

After my own testing, I found that using this method to parse a 5M excel file only requires an average of 21M of memory!

Code:

　https://github.com/wangyelou/Tools/tree/master/CutExcel

Recommended related articles:

How to use PHP to read the contents of excel files and obtain cell data

Steps to implement verification code in PHP and server-side verification code

The above is the detailed content of How to use phpspreadsheet to cut large excel files (with code). For more information, please follow other related articles on the PHP Chinese website!