How PHP exports large amounts of data in Excel

More commonly used is phpexcel,1 data is still OK, but the data reached about 1 million, Phpexcel is very helpless, search for the next more commonly used is the use of PHP fputcsv, do not know what can be exported to compare a large amount of data excle

Just tested the next fputcsv, found all squeezed into a, unable to change to B

September 2, 2012 added
Version 03 is really limited, the format of PHP CSV is not opened with Excel will only appear on column A?

September 2, 2012 added
I think I really need to say why there is this demand, a big point of the brand company, a year or so of orders, more than 1 million is very normal, at the end of the year when their business staff will be based on orders for various analysis, each company's analysis angle is not the same, So to export all the orders to the business people is the simplest way, why to export excle, because for the business people This is the most common, you can not help so many companies to develop such a report.
So there is this demand, how to export more than 1 million or even 10 million data excle problem

Reply content:

More commonly used is phpexcel,1 data is still OK, but the data reached about 1 million, Phpexcel is very helpless, search for the next more commonly used is the use of PHP fputcsv, do not know what can be exported to compare a large amount of data excle

Just tested the next fputcsv, found all squeezed into a, unable to change to B

September 2, 2012 added
Version 03 is really limited, the format of PHP CSV is not opened with Excel will only appear on column A?

September 2, 2012 added
I think I really need to say why there is this demand, a big point of the brand company, a year or so of orders, more than 1 million is very normal, at the end of the year when their business staff will be based on orders for various analysis, each company's analysis angle is not the same, So to export all the orders to the business people is the simplest way, why to export excle, because for the business people This is the most common, you can not help so many companies to develop such a report.
So there is this demand, how to export more than 1 million or even 10 million data excle problem

1. PHP reads 1 million rows of records from the database multiple times, and writes 1 million rows to a text file.
2. Excel can support 1 million rows of records, Excel 2003 support 65536 lines, starting from 2007 version support 1.04 million lines, the current 2007 of piracy should be more popular-_-! Ask what version of your customer is.
3. The reason you want to export Excel is very full and correct and should continue to persist. Business people are most familiar with Excel, is not familiar with the current learning is also faster than others. Just note that when the amount of data reaches 100,000 lines this level, Excel's formula fill will be very very slow, if there is a lookup () formula, basically 10 minutes to deal with the CPU full process Manager can not kill the state, this time in fact XAMPP + phpMyAdmin is the most balanced choice for ease of use and performance
4. Phpexcel output is the Excel XML format, there is an XML header and tail, the middle is the data body, you need to assign 1 million rows to an array to call Phpexcel->write (), which can easily lead to PHP execution timeout or memory overrun, You may want to adjust the php.ini configuration to change the time-out and memory limits to a large
5. If it is the output CSV format, it is too simple, your problem may be not to quote the field contents, add quotation marks and then call Fputcsv try again? In fact, fputcsv do things very simple, you can even regardless of it, you put the CSV file each line of stitching up and then write with file_put_contents

Finally, I'll show you a sample of phpMyAdmin exported CSV, and look at what's different from the CSV example you generated yourself.

Before I export all with CSV, if Putcsv has a problem, you can try to assemble the CSV file yourself. CSV format is very simple, is a comma-delimited.
Look at you, it is possible that the first line left blank caused by the problem, suggested modifications to try.
---
Another way is to use \ t partition, save into a text file, and then copy paste into Excel

I agree with the 2 floor statement.
Excel itself is not for big data operation, if you really over 100,000 lines, you still do not use Excel to operate, this really will be dead.
Of course, if you really want to do this, you're still in access. Then use Excel to read access data to generate a variety of charts (if your Excel is to be used to generate a chart).
Otherwise, it really doesn't make sense.

If I remember correctly, Excel limit is 65,536.

The 1.Excel itself cannot fit millions of data. Excel is limited by the number of bars. You can refer to the relevant information for the specific number of articles.
2. You can split the data into different Excel files by dividing the number of bars. Generate multiple.
3. Even if you write to the CSV file, you want to open millions of data in Excel is not too realistic

Excel is just a good breakdown.

Can be CSV, not a period of refresh buffer period, to achieve the export of large data streams

$fp = fopen (' php://output ', ' a ');//output Excel column name information $head = Array ("e-mail"); foreach ($head as $i + $v) {    //CSV Excel support GB K Code, be sure to convert, otherwise garbled    $head [$i] = iconv (' utf-8 ', ' GBK ', $v);} Writes the data through Fputcsv to the file handle Fputcsv ($FP, $head);//Counter $cnt = 0;//every $limit line, refresh the output buffer, not too big, not too small $limit = 100000;//row-by-line extraction of data, Do not waste memory $count = count ($email); for ($t =0; $t < $count; $t + +) {    $cnt + +);    if ($limit = = $cnt) {//Refresh output buffer to prevent problems caused by too much data        ob_flush ();        Flush ();        $cnt = 0;    }    $row [] = $email [$t];    foreach ($row as $i = + $v) {        $row [$i] = iconv (' utf-8 ', ' GBK ', $v);    }    Fputcsv ($fp, $row);    Unset ($row);}

Loop to generate CSV data, refresh the buffer period for each generation of 1000

$filename = './file.csv '; $handle =fopen ($filename, "W") fwrite ($handle, Chr (255) Chr (254)); Fwrite ($handle, Iconv (" UTF-8 "," Utf-16le "," Value 1\t value 2\t value 3\t value n\\r\n "); fclose ($handle);

Use this to see, do not check out all the data, you can refer to the following mysql_unbuffered_query

Version 03 is really limited, the format of PHP CSV is not opened with Excel will only appear on column A?

