-1

I have a big combination of data when i have generate a excel file using php excel the page going to be hang and excel file not generating. what i will do? i have increased memory time, execution time but still same issue i have also tried buffering methods in php like ob_start, ob_flush, ob_end_flush etc but still same issue.

i have more than 100 Keyword, 500 categories, 8000 locations means 100*500*8000 combinations are there. when we generate excel the memory execution not supported and page are showing blank.

i have already increased memory time using .htaccess in GB but still same issue.

thanks.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Durgesh Narayan
  • 96
  • 1
  • 11
  • You haven't given much information about how this data is currently stored. It might be that you need to adopt a different approach: get MySQL to do most of the work, for example (if MySQL is what you're using). –  Jan 16 '15 at 02:08
  • Thanks Hobo, I have all data in json format. I'm converting all data json to array and then creating all combinations. – Durgesh Narayan Jan 16 '15 at 03:09
  • So you're probably holding the json string plus an array of combinations in memory, even before you factor PHPExcel into the equation – Mark Baker Jan 16 '15 at 07:59
  • Thanks mark, yes combinations are not fixed. It's dependent on user's selection. – Durgesh Narayan Jan 16 '15 at 08:41

2 Answers2

2

As per your combination 100*500*8000 = 400000000

You will have 400000000 records and we can never store so much rows into single xls file. Our xls Worksheet size is 65,536 rows by 256 columns

http://office.microsoft.com/en-in/excel-help/excel-specifications-and-limits-HP005199291.aspx

So first we will have to caluclate the number of files in which this data can be stored and if we can save 65,536 rows in single sheet then in three sheets we can save upto 65,536*3 = 196608 data records. It means in current case you will have to create at-least 400000000/196608 = 2034.5 Means total we will have to create 2035 files to store so much data in xls files so you can trigger this script by ajax and can get created file by ajax and this ajax call will happen 2035 times (you may reduce this by handling number of files creation at-least five file on single ajax call so this ajax will return 5 files link on completion and you will have to call this ajax 407 times instead of 2035) and in such a way you will never get memory error and execution time error (please try to fix execution time max and memory limit max to make this robust).

So like this way you can accomplish your task.

Adal Singh
  • 106
  • 2
0

Another way to solve that problem is to create multiple sheets in the same file. In your situation, this will create a LOT of sheets though... I don't think this will be a relevant report anymore.

Otherwise, you can move to XLSX. The size limit for this format is 1,048,576 rows per sheet so you'll end up creating way less. There are some libraries out there to help you do that.

Adrien
  • 1,929
  • 1
  • 13
  • 23