PHP: Exporting Data to Excel

Tweet 36 Shares 0 Tweets 63 Comments

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.

What we're actually doing here is creating a text (TAB or CSV) file containing the data which can then be opened by Excel or any other spreadsheet. Before you ask, that means NO formatting, NO colours and NO formulae - just the actual data.

For maximum compatibility with recent version of Excel, Numbers and other applications you should use the CSV download format rather than tab-delimted text.

Preparing the data

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

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

Further down this page you will find examples for creating a downloadable file using data sourced 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) {
    array_walk($row, __NAMESPACE__ . '\cleanData');
    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;

The __NAMESPACE__ reference and \ are required to be compatible with PHP Namespaces, and should be included whether or not you are currently using namespaces for your code to be future-compatible.

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) {
    array_walk($row, __NAMESPACE__ . '\cleanData');
    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;

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.

source

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_20241222.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 vnd.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/cell formatting, column widths, etc, using this method. 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.chirpinternet.eu
  // 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))) {
    array_walk($row, __NAMESPACE__ . '\cleanData');
    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;

This is the entire script required to query the database, clean the data, and trigger a file download.

The database functions need to match the database you're using. MySQL users for example will need to use mysqli_query and either mysqli_fetch_assoc or mysqli_fetch_assoc in place of the PostgreSQL functions. Or better, PDO::query().

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.chirpinternet.eu
  // 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.

Exporting to CSV format

As newer versions of Excel are becoming fussy about opening files with a .xls extension that are not actual Excel binary files, making CSV format with a .csv extension is now 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
  // Original PHP code by Chirp Internet: www.chirpinternet.eu
  // Please acknowledge use of this code by including this header.

  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))) {
    array_walk($row, __NAMESPACE__ . '\cleanData');
    if(!$flag) {
      // display field/column names as first row
      fputcsv($out, array_keys($row), ',', '"');
      $flag = TRUE;
    }
    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.

source

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, something 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 Microsoft apparently still occupies 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
  // Original PHP code by Chirp Internet: www.chirpinternet.eu
  // Please acknowledge use of this code by including this header.

  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))) {
    array_walk($row, __NAMESPACE__ . '\cleanData');
    if(!$flag) {
      // display field/column names as first row
      fputcsv($out, array_keys($row), ',', '"');
      $flag = TRUE;
    }
    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.

Changing 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 = [
    '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(__NAMESPACE__ . '\map_colnames', array_keys($row));
      fputcsv($out, $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.

source

References

< PHP

User Comments

Most recent 20 of 63 comments:

Post your comment or question

quyle92 18 December, 2020

for me to make utf-8 work, you have to put utf-8 BOM like this $out = fopen("php://output", 'w+'); fwrite($out, "\xEF\xBB\xBF");

and remove this $str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');

tahir aleem 26 July, 2020

I have a column which has arabic written values and it's not displaying it correctly instead it looks like this "Ø¬Ø§�™...Ø¹Û Ø¯Ø§Ø±Ø§�™„Ø¹�™„�™ˆ�™... �™†Ø§�™†Ú©�™ˆØ§Ú". Any Solution??

Roger 12 May, 2018

Hi, got 2 questions.
First, I can't figure out why you have (&$str) in your function for cleaning.
Second. My exports are working fine, it's just that it seems to not include the first row. It will export header row and then the last 7 of 8 rows. I'm pulling hair trying figure this out. Using PHP7 and mysql on a mac. Any ideas?
Thanks!

Firstly, the '&' indicates Passing by Reference meaning that our function can directly affect the original variable.

For your second question, you may be using a loop starting at 1 instead of 0 meaning the first row is skipped.

Shazwan 22 March, 2018

my string got '<' character which cause the excel to only output the text before the '<' character. how to clean this without removing this '<' character

Kaloy San Pedro 23 December, 2017

Hi, I tried using the csv code and it shows a lot of warning that the array_walk is expecting an array but it returns null instead. I echoed the value of $row inside the if statement and I was getting an array. I don't know what part of the code was it being null. I'm new to PHP. Hoping you can help thanks

SzFJ 3 May, 2017

Please rename this article to Export data to csv...

XLS has a binary format, NOT comma- or tab-separated, quoted values!

Purple Lady 12 April, 2017

I am using the CSV version and it works great! The only issue I am having is that when I open the downloaded csv file in Excel it contains extra whitespace in each cell. How can I trim this before downloading the file?

Does your data contain extra whitespace? If so the answer is to add:

$str = trim($str);

At the top of the cleanData function.

Thomas 27 February, 2017

Is way to change font in csv or xls? I thinking about bolding font or change color.

Kwesi Chamba 11 February, 2017

I get this in my csv. I get this error when I open the csv file...
Fatal error: Uncaught Error: Call to undefined function mysql_query()

mysql_query() has been deprecated in favour of mysqli_query() or
PDO::query() in the latest PHP:

ref: php.net/mysql-query

Solly Makofane 5 January, 2017

Wow, the code so amazing, My work is much easy now. Thanks fam. But one more problem I have is that when the xls file is downloaded, It duplicates the data. One with heading and one with just a number but same data. How do I solve this? e.g Names: Solly; 1: Solly; Surname: Makofane; 2: Makofane. Please help

You need to use *_fetch_assoc instead of *_fetch_row which will contain both numbered and named indices.

Alex 6 September, 2016

How to pass link href to the excel file? Thank you very much for this tutorial, very helpful

Dominic Matthew Teo 4 September, 2016

"/t" for new tab, "/n" for new row, ? for new spreadsheet?

Is it even possible?

No, not possible. We're not creating actual Excel workbook files.

Sajjad 18 May, 2016

Great tutorial,
Now I can export my website's user activity in excel format.
Thankyou.

Jeffrey Osborn 18 February, 2016

This is really helpful, but I would changed the output file's extension to TAB because it is a TAB format. Also I think it's common that TAB and CSV files are opened in MS Excel so the behavior should stay the same.

Also just in case someone is interested you could generate a real XLS file by using MS Excel as shown here.

Or you could use some alternative approach for example like this one.

I hope this helps someone.

hanumanth 26 October, 2015

this code was very nice
but,my problem is , how to give colors for excel sheet.

Jordan 10 December, 2014

Hello,

I'm looking to use this script for a weekly cronjob to store an excel file on my server. I'm not the most experienced in php so I'm wondering how I would modify it to make it to where it doesn't trigger a download, it instead stores the file at some location (say, public_html on my server).

I'll be using the SQL method

It's the "header()" commands that trigger the download. To write to a local file you need to instead use fopen/fwrite/fputcsv or similar functions, to create and populate the file.

Kevin Espiritu 17 November, 2014

Why is it that when i tried this code, all of the 'html tags' above it is included in the .csv file being downloaded?

The download script has to go at the top of the page so it can trigger the download and exit before any HTML is output.

Michael 10 November, 2014

I have found this tutorial very helpful, amazing and mostly works.

I only want to know if there is way to remove auto format of excel, im having problems exporting fractions it automatically becomes dates.

I am using the CSV format already and it still changes, im i doing something wrong?

The problem is entirely with Excel, but sometimes adding an apostrophe to the start of the field serves to prevent AutoFormat. You would need to add a fraction-detecting regex to the line marked force certain number/date formats to be imported as strings.

okey 7 October, 2014

What if you what the excel file generated to be a read only file. How do you go about it?

Patrick 15 July, 2014

Thank you. Your clean data function saved me a lot. Fantastic!!