php - how to convert 500,000 rows of data with 39 columns from MySQL into Excel -

i have php web app can convert data excel file using phpexcel. web app running inside linux machine.

these steps involved.

1. i need run several join queries data want.
2. i take data , write them excel file using phpexcel.
3. repeat step 1-2 in batches of ~5000 rows.

i noticed time taken generate ~50000 rows alone (which 10%) 15 minutes long.

i need generate excel file once day.

off top of head, have following options.

a) instead of doing step 1 , 2 repeatedly 10 times, retrieve data need in step 1 , store data separate mysql table.

then try phpexcel , read table once , write excel file once.

at end, wipe table clean again.

b) same a) instead of using phpexcel, find way run mysql excel in cloud windows instance generate excel file.

if need further formatting, use phpexcel format excel file.

the way send data between linux machine , windows cloud instance through email attachments because linux machine within firewall.

i posting question see if has better idea.

in meantime, try option a).

i have no idea how run windows instance , microsoft excel option b).

as me, best way, use mysql select outfile. can export result csv, microsoft excel open correctly

example:

select * outfile '/tmp/result.csv'    fields terminated '\t' optionally enclosed '"' escaped ‘\\’     lines terminated '\n' table 

after that, can use php library phpexcel convertion csv xls

include 'phpexcel/iofactory.php';  $objreader = phpexcel_iofactory::createreader('csv');  // if files uses delimiter other comma (e.g. tab), tell reader $objreader->setdelimiter("\t"); // if files uses encoding other utf-8 or ascii, tell reader $objreader->setinputencoding('utf-16le');  $objphpexcel = $objreader->load('mycsvfile.csv'); $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5'); $objwriter->save('myexcelfile.xls');