Export records in excel file

Question

I created a web application and I want to add a feature like user can export selected records...

Check Box   Name    Email

            Sumit   sumit@gmail.com
            Karan   karan@gmail.com

I want to export this records into an excel file, but only the selected records should be exported.

Thanks in advance

You can use PHPExcel http://phpexcel.codeplex.com/ , but your question is bit too open ... — Molochdaa, Nov 10 '11 at 16:10
possible duplicate of [export mysql result to excel](http://stackoverflow.com/questions/1475441/export-mysql-result-to-excel) — hakre, Nov 10 '11 at 16:24
@hakre - Don't duplicate to that question. It's for a particular script that is overly complicated. — evan, Nov 10 '11 at 16:33
@hakre - I can't vote to close questions yet, not enough rep. Simply stating that if you're going to vote to close a question, you should at least do your due diligence to find an actual duplicate. Preferably with a good answer so that others that come to the duplicate will be helped as well. — evan, Nov 10 '11 at 17:02
Please look at the many duplicates: http://stackoverflow.com/search?q=[php]+mysql+export+excel — hakre, Nov 10 '11 at 17:03

evan · Accepted Answer · 2011-11-10T16:29:39.753

There's no need to download external libraries - everything you need for this is already built into PHP.

Steps:

Do query in php to get the rows you want to output
You can use SELECT * FROM table WHERE id IN (1,2,...)
Use mysql_fetch_array() or mysql_fetch_assoc() to get the rows one at a time
Use fputcsv() to output them to a file ending in csv - this will properly escape your data

http://www.php.net/manual/en/function.mysql-fetch-array.php
http://php.net/manual/en/function.fputcsv.php

Excel will be able to read the file.

Override the defaults for fputcsv to use tabs for delimiters and Excel will have an even easier time reading the file. If you use commas (the default) you may need to pick commas as the delimiter on Excel import.

Here's a working example assuming you already have rows set up:

$rows; // predefined
$filename = 'webdata_' . date('Ymd') . '.csv';

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/octet-stream"); 
// that indicates it is binary so the OS won't mess with the filename
// should work for all attachments, not just excel

$out = fopen("php://output", 'w');  // write directly to php output, not to a file
foreach($rows as $row)
{
  fputcsv($out, $row);
}
fclose($out);

In my years of PHP, I've never actually used fputcsv -- usually because I generally avoid creating physical files, but this is perhaps a good solution. However, I don't think excel will have any problem using tabs as opposed to commas.. it should automatically detect the delimiter, so I don't anticipate configuration changes needing to be made. But again, mileage may vary. Very good answer though :-) — WhiskeyTangoFoxtrot, Nov 10 '11 at 16:24
Excel does BETTER with tabs. A bit odd since csv stands for Comma Separated Values. See the example, no need to output to a file first. Just output straight to php standard out. — evan, Nov 10 '11 at 16:28
@evan, ah it didn't even occur to me that you were going to use stdout. I just generally juggle around strings. But since you're using it as a file context, it makes perfect sense. Thanks for ideas on how to better some stuff I've been working on. :-) — WhiskeyTangoFoxtrot, Nov 10 '11 at 16:35

score 1 · Answer 2 · answered Nov 10 '11 at 16:13

1

You can install and use the very nice PHP exel library to import exel files as well as export data in exel.

More info here : http://phpexcel.codeplex.com/

Otherwise the question itself is a bit vague so that's all I can say. :/

answered Nov 10 '11 at 16:13

CAP

317
1
6

score 0 · Answer 3 · answered Nov 10 '11 at 16:14

If you send the data as CSV to the browser (i.e. implode() each entry in the result set array with \t and separate each line with \n), Excel can easily convert it to something it can use when the document is loaded.

But note, you must tell the browser that the document it's loading is an excel file so it knows to use excel to load the file. Do so by providing a content type and content disposition header like so:

header('Content-type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=exportExcel.xls");

Please make sure you call the header() functions before you send the CSV'd text.

Note: I chose \t (tabs) to separate each field rather than commas because in my experience, they've caused issues as it might be common for a field to contain commas in it... but perhaps not in your particular case.

As a note, this won't have any fancy formatting or table-boxing. Camille's library might provide that. My method will just do a simple straight-up export. — WhiskeyTangoFoxtrot, Nov 10 '11 at 16:15

score 0 · Answer 4 · edited May 23 '17 at 12:15

Assuming that your mysql database is on the same server as your web host, then you should be able to use the "select into outfile" method in mysql. http://dev.mysql.com/doc/refman/5.1/en/select-into.html

You can create a csv file (which opens in excel) using this kind of syntax:

SELECT * FROM people INTO OUTFILE '/www/your/output/directory/output.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

And then you can let people download the file using php like this:

header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=/www/your/output/directory/output.csv");
header("Pragma: no-cache");
header("Expires: 0");

this last part coming from other post -> create csv file

Export records in excel file

4 Answers4

Linked