Export large data to excel in php

For such a large amount of data I would not recommend tools like PHPExcel or ApachePOI [for Java] because of their memory requirements. I have struggled with similar task recently and I have found convenient [but maybe little bit fiddly] way to inject data into spreadsheets. Serverside generation or updating of Excel spreadsheets can be achieved thus simple XML editing. I have XLSX spreadsheet sitting on the server and every time data is gathered from dB, I unzip it using php. Then I access specific XML files that are holding contents of worksheets that need to be injected and insert data manually. Afterwards, I compress spreadsheet folder in order to distribute it as an regular XLSX file. Whole process is quite fast and reliable. Obviously, there are few issues and glitches related to inner organisation of XLSX/Open XML file [e. g. Excel tend to store all strings in separate table and use references to this table in worksheets]. But when injecting only data like numbers and strings, it is not that hard. If anyone is interested, I can provide some code.

Okay, here goes sample code for this. I have tried to comment what it does, but feel free to ask for further explanation.

Chủ Đề