skip to content

PHP: Exporting Data to Excel

After putting so much effort into importing your data into an SQL database and connecting it to your website, how do you get it back out and into Excel in order to keep your off-line and on-line systems synchronised?

The following article presents a simple method for downloading any data from PHP into an Excel spreadsheet - or at least something that looks like one.

Preparing the data

The following examples use the dataset created for Sorting Arrays of Arrays which is defined as follows:

<?PHP $data = array( array("firstname" => "Mary", "lastname" => "Johnson", "age" => 25), array("firstname" => "Amanda", "lastname" => "Miller", "age" => 18), array("firstname" => "James", "lastname" => "Brown", "age" => 31), array("firstname" => "Patricia", "lastname" => "Williams", "age" => 7), array("firstname" => "Michael", "lastname" => "Davis", "age" => 43), array("firstname" => "Sarah", "lastname" => "Miller", "age" => 24), array("firstname" => "Patrick", "lastname" => "Miller", "age" => 27) ); ?>

Note: Further down this page you can find an example on creating an export from an SQL query.

The first step is to output the data in a tab-delimited format (CSV can also be used but is slightly more complicated). To achieve this we use the following code:

<?PHP header("Content-Type: text/plain"); $flag = false; foreach($data as $row) { if(!$flag) { // display field/column names as first row echo implode("\t", array_keys($row)) . "\r\n"; $flag = true; } echo implode("\t", array_values($row)) . "\r\n"; } exit; ?>

We set the content type to text/plain so that the output can more easily be viewed in the browser. Otherwise, because there is no HTML formatting, the output would appear as a single line of text.

The first line of output will be the column headings (in this case the field names are used). Values are separated with a tab \t and rows with a line break \n. The output should look something like the following:

firstname lastname age Mary Johnson 25 Amanda Miller 18 James Brown 31 Patricia Williams 7 Michael Davis 43 Sarah Miller 24 Patrick Miller 27

There's already a weakness in this code that may not be immediately obvious. What if one of the fields to be ouput already contains one or more tab characters, or worse, a newline? That's going to throw the whole process out as we rely on those characters to indicate column- and line-breaks.

The solution is to 'escape' the tab characters. In this case we're going to replace tabs with a literal \t and line breaks with a literal \n so they don't affect the formatting:

<?PHP function cleanData(&$str) { $str = preg_replace("/\t/", "\\t", $str); $str = preg_replace("/\r?\n/", "\\n", $str); } header("Content-Type: text/plain"); $flag = false; foreach($data as $row) { if(!$flag) { // display field/column names as first row echo implode("\t", array_keys($row)) . "\r\n"; $flag = true; } array_walk($row, 'cleanData'); echo implode("\t", array_values($row)) . "\r\n"; } exit; ?>

Now, before each row is echoed any tab characters are replaced "\t" so that our columns aren't broken up. Also any line breaks within the data are replaced with "\n". Now, how to set this up as a download...

Triggering a download

What many programmers don't realise is that you don't have to create a file, even a temporary one, in order for one to be downloaded. It's sufficient to 'mimic' a download by passing the equivalent HTTP headers followed by the data.

If we create a PHP file with the following code then when it's called a file will be downloaded which can be opened directly using Excel.

<?PHP function cleanData(&$str) { $str = preg_replace("/\t/", "\\t", $str); $str = preg_replace("/\r?\n/", "\\n", $str); if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; } // filename for download $filename = "website_data_" . date('Ymd') . ".xls"; header("Content-Disposition: attachment; filename=\"$filename\""); header("Content-Type: application/vnd.ms-excel"); $flag = false; foreach($data as $row) { if(!$flag) { // display field/column names as first row echo implode("\t", array_keys($row)) . "\r\n"; $flag = true; } array_walk($row, 'cleanData'); echo implode("\t", array_values($row)) . "\r\n"; } exit; ?>

Note that we've added an extra line to the cleanData function to detect double-quotes and escape any value that contains them. Without this an uneven number of quotes in a string can confuse Excel.

» click here to trigger the download «

This should result in a file being downloaded and saved to your computer. If all goes well then the filename will be named "website_data_20140420.xls" and will open in Excel looking something like this:

screenshot showing data in Excel colums

How does it work? Setting the headers tells the browser to expect a file with a given name and type. The data is then echoed, but instead of appearing on the page it becomes the downloaded file.

Because of the .xls extension and the ms-excel file type, most computers will associate it with Excel and double-clicking will cause that program to open. You could also modify the file name and mime type to indicate a different spreadsheet package or database application.

There is no way to specify data or cell formatting, column widths, etc, using this method. We are only passing a tab-delimited text file. To include formatting try generating HTML code or a script that actually builds an Excel file. Or create your own macro in Excel that applies formatting after the import.

A similar technique can be used to allow users to download files that have been uploaded previously using PHP and stored with different names. More on that later...

Exporting from an SQL database

If your goal is to allow data to be exported from a query result then the changes are relatively simple:

<?PHP // Original PHP code by Chirp Internet: www.chirp.com.au // Please acknowledge use of this code by including this header. function cleanData(&$str) { $str = preg_replace("/\t/", "\\t", $str); $str = preg_replace("/\r?\n/", "\\n", $str); if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; } // filename for download $filename = "website_data_" . date('Ymd') . ".xls"; header("Content-Disposition: attachment; filename=\"$filename\""); header("Content-Type: application/vnd.ms-excel"); $flag = false; $result = pg_query("SELECT * FROM table ORDER BY field") or die('Query failed!'); while(false !== ($row = pg_fetch_assoc($result))) { if(!$flag) { // display field/column names as first row echo implode("\t", array_keys($row)) . "\r\n"; $flag = true; } array_walk($row, 'cleanData'); echo implode("\t", array_values($row)) . "\r\n"; } exit; ?>

This would be the entire file required to query the database and trigger the file download. The database functions need to match the database you're using. MySQL users for example will need to use mysql_query and either mysql_fetch_assoc or mysqli_fetch_assoc in place of the PostgreSQL functions.

For other databases see under User Comments below or check the PHP documentation. If you are seeing duplicate columns (numbered as well as labeled) you need to change the fetch call to return only the associative (ASSOC) array.

If you're having trouble at this stage, remove the Content-Disposition header and change the Content-Type back to text/plain. This makes debugging a lot easier as you can see the output in your browser rather than having to download and open the generated file every time you edit the script.

Preventing Excel's ridiculous auto-format

When importing from a text file as we're essentially doing here, Excel has a nasty habit of mangling dates, timestamps, phone numbers and similar input values.

For our purposes, some simple additions to the cleanData function take care of most of the problems:

<?PHP // Original PHP code by Chirp Internet: www.chirp.com.au // Please acknowledge use of this code by including this header. function cleanData(&$str) { // escape tab characters $str = preg_replace("/\t/", "\\t", $str); // escape new lines $str = preg_replace("/\r?\n/", "\\n", $str); // convert 't' and 'f' to boolean values if($str == 't') $str = 'TRUE'; if($str == 'f') $str = 'FALSE'; // force certain number/date formats to be imported as strings if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) { $str = "'$str"; } // escape fields that include double quotes if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; } ?>

The section that prevents values being scrambled does so by inserting an apostrophe at the start of the cell. When you open the resuling file in Excel you may see the apostrophe, but editing the field will make it disappear while retaining the string format. Excel is strange that way.

The types of values being escape this way are: values starting with a zero; values starting with an optional + and at least 8 consecutive digits (phone numbers); and values starting with numbers in YYYY-MM-DD format (timestamps). The relevant regular expressions have been highlighted in the code above.

Formatting - colours, numbers, dates

This script generates and triggers the download of a text file with an Excel Mime-Type. We are not building an actual Excel spreadsheet file.

Defining styles, colours, column widths, etc, is not possible using this technique. You may be able to generate an HTML table with some formatted data that Excel will recognise, otherwise you need a much more complicated script.

Exporting to CSV format

Newer versions of Excel are becoming fussy about opening files with a .xls extension that are not Excel binary files, making CSV format with a .csv extension a better option.

The tab-delimited text options describe above may be a bit limiting if your data contains newlines or tab breaks that you want to preserve when opened in Excel or another spreadsheet application.

A better format then is comma-separated variables (CSV) which can be generated as follows:

<?PHP function cleanData(&$str) { if($str == 't') $str = 'TRUE'; if($str == 'f') $str = 'FALSE'; if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) { $str = "'$str"; } if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; } // filename for download $filename = "website_data_" . date('Ymd') . ".csv"; header("Content-Disposition: attachment; filename=\"$filename\""); header("Content-Type: text/csv;"); $out = fopen("php://output", 'w'); $flag = false; $result = pg_query("SELECT * FROM table ORDER BY field") or die('Query failed!'); while(false !== ($row = pg_fetch_assoc($result))) { if(!$flag) { // display field/column names as first row fputcsv($out, array_keys($row), ',', '"'); $flag = true; } array_walk($row, 'cleanData'); fputcsv($out, array_values($row), ',', '"'); } fclose($out); exit; ?>

Normally the fputcsv command is used to write data in CSV format to a separate file. In this script we're tricking it into writing directly to the page by telling it to write to php://output instead of a regular file. A nice trick.

As an aside, to export directly to CSV format from the command line interface in PostgreSQL you can use simply:

postgres=# COPY (SELECT * FROM table ORDER BY field) TO '/tmp/table.csv' WITH CSV HEADER;

and for MySQL, somehing like the following:

SELECT * FROM table ORDER BY field INTO OUTFILE '/tmp/table.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

Exporting to CSV with Unicode intact

If like us your data contains UTF-8 characters you will notice that Excel doesn't handle them very well. Other applications can open UTF-8 content without problems, but for some reason Microsoft wants to keep you in the dark ages.

Fortunately, there is a trick you can use. Below you can see how we modify the script to convert everything from UTF-8 to UTF-16 Lower Endian (UTF-16LE) format which Excel, at least on Windows, will recognise.

When opening this file in Excel you might find all the data bunched into the first column. This should be fixable using the "Text to Columns..." command under the Data menu.

<?PHP function cleanData(&$str) { if($str == 't') $str = 'TRUE'; if($str == 'f') $str = 'FALSE'; if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) { $str = "'$str"; } if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; $str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8'); } // filename for download $filename = "website_data_" . date('Ymd') . ".csv"; header("Content-Disposition: attachment; filename=\"$filename\""); header("Content-Type: text/csv; charset=UTF-16LE"); $out = fopen("php://output", 'w'); $flag = false; $result = pg_query("SELECT * FROM table ORDER BY field") or die('Query failed!'); while(false !== ($row = pg_fetch_assoc($result))) { if(!$flag) { // display field/column names as first row fputcsv($out, array_keys($row), ',', '"'); $flag = true; } array_walk($row, 'cleanData'); fputcsv($out, array_values($row), ',', '"'); } fclose($out); exit; ?>

This script may not work for all versions of Excel. Please let us know using the Feedback form below if you encounter problems or come up with a better solution.

Specifying column headings

The above database download examples all use the database field names for the first row of the exported file which may not be what you want. If you want to specify your own more user-friendly headings you can modify the code as follow:

<PHP $colnames = array( 'memberno' => "Member No.", 'date_joined' => "Date joined", 'title' => "Title", 'firstname' => "First name", 'lastname' => "Last name", 'address' => "Address", 'postcode' => "Postcode", 'city' => "City", 'country' => "Country", 'phone' => "Telephone", 'mobile' => "Mobile", 'fax' => "Facsimile", 'email' => "Email address", 'notes' => "Notes" ); function map_colnames($input) { global $colnames; return isset($colnames[$input]) ? $colnames[$input] : $input; } // filename for download $filename = "website_data_" . date('Ymd') . ".csv"; ... if(!$flag) { // display field/column names as first row $firstline = array_map("map_colnames", array_keys($row)); fputcsv($outstream, $firstline, ',', '"'); $flag = true; } ... ?>

The values in the first row will be mapped according to the $colnames associative array. If no mapping is provided for a fieldname it will remain unchanged. Of course you will want to provide your own list of suitable headings. They don't have to be in order.

References

< PHP


Like Tweet     Bitcoin

User Comments and Notes

keith 6 January, 2007

Excellent tutorial - thanks alot, it helped me a hell of a lot. Keep up the excellent work.

Open Source 30 August, 2007

Very useful to know this. Appreciate it.

kathy 10 October, 2007

Its really useful and have implemented. But my client wants it to be true excel and not text (tab limited).

Most versions of Excel use a proprietary file format called BIFF which is difficult to reproduce. Newer versions use Office Open XML which has so far failed to get approval as an open standard so also a problem to reproduce. You should tell your client that TAB and CSV formats are many times more efficient and can easily be converted to true Excel using a macro.

Dragan 4 July, 2008

Excel can "read" simple HTML just fine, so you might as well create an HTML-table, with the required xls headers of course.

Sure, if you think HTML is a sensible format for transmitting data then go for it. I'll be sticking with tab-delimited text (or CSV or XML) as it's compatible with more programs, and much smaller

blight 7 November, 2008

Here is a simple article which describes how to create xls spreadsheets with php.

You might find it interesting.

ammupon 11 June, 2009

Thank you soooooo much for your wonderful Post!!!

James 14 June, 2009

Why do i get a blank spreadsheet after exporting the data?

If you get a blank spreadsheet then you haven't exported any data. Try first with a text/plain header to see what's happening and then when it's working change the headers to trigger the Excel download.

swapnil 7 December, 2009

i find Amazing and Excellent information, especially tht u dont need to create file for a download. Thanks for the awesome code.

aSeptik 11 December, 2009

Use this header both in CVS and HTML format:

header( "Content-type: application/vnd.ms-excel; charset=UTF-16LE" );

Halfling Rogue 23 February, 2010

Excellent article; easy to understand and very useful.

Only problem is that the cleanData function doesn't seem to actually get rid of newlines. I found that was easily fixed by using the custom nl2br function on the php.net site: ca2.php.net/manual/en/function.nl2br.php#91828

Any numbers longer than 15 digits get converted by the spreadsheet program into scientific notation, but that can be fixed by throwing quotes around the number in the while loop so that it's parsed as text.

Mike Carrun 24 December, 2010

The article is pleasantly simple to understand. Excellent work.

One note though; Dates exported into Excel appear as ######## in the cells, though they are displayed correctly in the formula bar. Anyone know how to fix this?

Doesn't that mean you just need to make the column wider?

Sarah 12 January, 2011

Thanks for this code - brilliant. I used it for getting data from a msql database. Had some problems to begin with where all the data was being put into 1 cell in the spreadsheet. It was because I had put doctype, head, body etc in the code. I took all that out so it was just the code you gave and then it worked - thanks!

Tim 13 January, 2011

Man this is good, I love it. been looking for something as simple as this, and now I found it. Perfect.

My only thing is probebly simple, but my colums get put in double, once with just a number and once with the colomn name, all the values are double too. I get it from sql database.

You need to use the fetch_assoc function when extracting data rather than fetch_array. The latter will sometimes return both an enumerated and associative array leading to duplicates in the output.

Quicksilver 4 February, 2011

/t will not work as a tab delimiter. Don't believe people when they say HTML doesn't have a tab delimiter, use &#9; to put values into a new column.

Haider Abbas 28 February, 2011

This is one of the best tutorials I have read so far. You made a very difficult task so easy. I give you 10 out of 10. And tons of thanks!

Scott Fennell 12 March, 2011

FYI -- I am trying to get this to work with MySQL and one thing that triggered an error was that mysqli_query takes an additional argument for the database link. PG apparently does not and I though some folks might want to know that right off the bat.

simon mills 6 April, 2011

Love it thanks - Im using the direct from sql method.
Is the a way to export two or more queries onto the same excel page - perhaps with a gap between both.

Of course, just repeat the code that comes after the header() commands with the next query. And if you want to include a blank line, just output a "\n" in between the two code blocks.

Adam Johnson 20 April, 2011

Really nice tutorial

One thing I did have to change for the tab-delimited version, was to output a new line as \r\n instead of \n.

So the following lines:
echo implode("\t", array_keys($row)) . "\n";
and
echo implode("\t", array_values($row)) . "\n";

had to change to:
echo implode("\t", array_keys($row)) . "\r\n";
and
echo implode("\t", array_values($row)) . "\r\n";

Without that change Excel didn't know what a newline was and just showed a blank sheet.

This is something I don't like about Excel, the year is 2011 and it still doesn't understand that a file might be using LF and not CRLF lol.

Mike 24 August, 2011

Thank you so much for this. You just made my night! (well, my early morning).

MySQL users, make sure to switch to mysqli_query and mysqli_fetch_assoc.

Also, remove one of the "=" from "!==" or your file will not stop downloading.

sueirna 7 September, 2011

How do i set font type, size, color and set the width of row and column table?

P-Hector 10 September, 2011

Thank you so much for this. You just gave me an edge at my place of work. MySQL users, make sure to switch to mysql_query and mysql_fetch_array.

My only problem is that after downloading, i have duplicate columns and then i'd need to go through deleting all the 2nd columns. How do i modify the code to fix duplicating each of my columns... Thanks...!!!

If you use mysql_fetch_array you should pass MYSQL_ASSOC as the second parameter, or just use mysql_fetch_assoc instead.

Chalise 14 September, 2011

My issue is similar to P-Hector's. I am getting duplicate columns - one is the key number, other the key name, and the same value in each column. I am using SQLite. How can I prevent the duplicate columns from occurring?

Similar to MySQL, for the SQLite fetchArray function you need to pass a second parameter as SQLITE3_ASSOC which will return just the named columns. See the documentation for details

Anup Vaze 16 September, 2011

This is one of the best post i have come across. Cool. It really gives good step by step guidance towards the task. It helped me a lot. Thanks a lot.

John 18 September, 2011

I think I am having a UTF-8 issue. I have Excel 2010 on my system and am exporting Japanese characters. I used your cleandata function, but it's being displayed as gibberish. Also, the text to columns won't work on multiple rows, so it looks like I'll have to do it row-by-row...ouch! Have you found any fixes?

Sylwia 17 March, 2012

Hello,
Thank you for this solution! But I have one problem: when I have too much data in my table, data is no longer saved into file, but a new webpage with my data is opened (I counted characters number in Word and it's working for 3640 but no for 4000).
Is there somewhere some limit?

Youyouk 31 May, 2012

Hello, thank you fo this very interesting Tutorial. However I've a problem. I tried to implement it in a MVC structure but the generated file include whole the HTML source code of the page.

I'm not an expert, but I think it's missing something like "session_start()" in my code. Thanks to anyone who could help me

Normally we would put the download script in its own file, but if you include it on an HTML page you need to make sure that it ends with exit; so as not to continue rendering the page.

totoylubacon 1 June, 2012

Very nice post. my problem of exporting sql queries to excel was solved. Thank you very much for your unselfish post.

icl1c 6 June, 2012

Hi, a useful information : if your first cell begins by "ID", you will have another warning message. You have to remove this string.

prem 8 June, 2012

I m able to export the excel but i m getting an error stating "A file is in a different file format than its extension indicates" can any one pls help me out

The file will be in a text (CSV or tab-delimited) format and the extension is XLS (for Excel) so there should be a conflict, but it can still be opened using Excel or other spreadsheet programs.

Garrett 16 June, 2012

Excellent tutorial, exactly all the information I needed. Thanks for organizing it so well and explaining everything clearly. Cheers!

Steve 10 July, 2012

Has anyone told you about Excel's behavior with these downloads in Microsoft Office 2010 on Windows 7? In previous versions of Windows, the file downloaded and opened in Excel fine. But in the 2010 scenario in Win 7, for the same files, a dialog pops up with, "The document is corrupt and cannot be opened. To try and repair it, use the Open and Repair command in the Open dialog box and select Extract Data when prompted." Following these directions fails silently. Is there an update we should be aware of for Excel 2010 in Win 7?

Yes, it seems that Excel will no longer open files with the .xls extension if they are formatted as TAB-delimited text or CSV. If you change the extension to .txt they should open using "Extract Data" in Excel instead of just double-clicking. The .xls extension files generated by this script will open in Google Docs.

Nathan 10 July, 2012

Great examples here, worked like a charm.

If you declare the file as csv as per example 7 it opens fine in Excel 2010 without complaining about it being in a different format.

CoolArrow 14 September, 2012

Thanks you for this great and easy to follow example. Cheers! opens up with win7 excel2010 even with the popups about file maybe corrupt and excel can't read SYLK files.

NLO 17 January, 2013

How more awesome could this tutorial ever be?
I have a slight question, though.
How would you do, to define the first row titles, instead of using the db columns?

Good question. I've added a new section for this with example code.

Melissa 20 February, 2013

has anyone seen the header row and some of the data rows wrap around 70 columns? I can't see why it would wrap. The header row wraps at column 72, and some of the data rows wrap at the same place...

Mohammad 25 April, 2013

Since 2007, and still this tutorial RULES the web as the best article regarding PHP xls download

I raise the hat for you

sajid 3 May, 2013

Hi, i am using this code for exporting data from database into excel format. My table has 150000 rows but after exporting i am getting only apprx 5000 records. I dont know what the heck happening?

Jeff 14 June, 2013

this is a cool tutorial! but im having a problem regarding Chinese characters.

劉仁杰 -> this is the data on the database
劉仁杰 -> and this the data after I download it.

does anybody know how solve this? thanks!

venkatesh 23 July, 2013

Hi, I implemented the excel sheet as you specified above but my problem is Excel shows an extensionHardening (Using Excel 2013) Please help me how to resolve

Which code are you using? The "output to CSV" option with a .csv extension should open without problems in Excel.

Kishlay 15 April, 2014

@Jeff : Dude you are getting thses strange characters as the font for Chineese or Japaneese (sorry I don't know what language you have used for data) is not installed in your excel

Send Feedback

Use this form to send a message to The Art of Web:


used only for us to reply, and to display your gravatar.

CAPTCHA refresh

<- copy the digits from the image into this box

press Esc or click outside this box to close

Load Feedback Form

top