• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

PHP to create Excel spreadsheet

What is the best way to create an Excel Spreadsheet via PHP from a MySQL Database?

Is there a template for this or a good place to get instructions?

I don't want to save a CSV file, I actually want to create a document.
1 Solution

Take a look at http://phpexcel.codeplex.com/ I have been using it lot and it works fine.
Julian HansenCommented:
If you don't want to go that approach - one that I use all the time is to export data in Excel XML format.

This allows you to use simple output statments to produce Excel docs.

What I do is save an Excel document in XML form - cut out the header and footer bits and put them in include files and then create the rows with a loop and output.
Ray PaseurCommented:
In my experience, there is no disadvantage to creating a CSV document, and it is very easy to do.  The CSV file suffix is almost universally associated with Excel.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Julian HansenCommented:
In my experience, there is no disadvantage to creating a CSV document

I completely disagree with this there is a huge disadvantage to using CSV the main one being Excel's propensity to drop leading zero's

The XML approach gives you the option not only to get around this but you can format your result with headings, colours, background colours etc (not critical but makes it more presentable and saves having to first import to excel and then fixing and saving).

If the recipients of the doc are not used to CSV files there may be issues there.

Personally - CSV has its uses but it is not a replacement for something like an XML dump
rgranlundAuthor Commented:

I want to create a function on my website, that when I click a button, it will create an Excel document from my MySQL DB, that I have formatted and allow me to save it.  I want it to be an Excel Document, not CSV.
Julian HansenCommented:
The XML process I described will be an Excel document - it will have an XML extension but Excel will read it.

If you want an XLS then you need to look at the link posted by ventiro - the XML was posted as a quick and easy alternative to importing a new code library
rgranlundAuthor Commented:
I have the following that I found.  However, I am having trouble at line 35:
require_once ('../includes/config.inc.php');
require_once (MYSQL);

//your MySQL Database Name of which database to use this
$tablename = "orders"; //your MySQL Table Name which one you have to create excel file
// your mysql query here , we can edit this for your requirement
$q = "Select * from $tablename ";
//create  code for connecting to mysql
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

//  Define a filename of excelde fine separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
$fp = fopen('database.xls', "w");
$schema_insert = "";
$schema_insert_rows = "";
//start of printing column names as names of MySQL fields


//  Set the column name of excel file start of adding column names as names of MySQL fields
for ($i = 1; $i < mysqli_num_fields($r); $i++)
$schema_insert_rows.= mysql_field_name($r,$i) . "\t";
echo $schema_insert_rows;
fwrite($fp, $schema_insert_rows);
//end of adding column names


//Getting data from database and adding to the excel filestart while loop to get data
while($row = mysqli_fetch_row($r))
//set_time_limit(60); //
$schema_insert = "";
for($j=1; $j<mysqli_num_fields($r);$j++)
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= strip_tags("$row[$j]").$sep;
$schema_insert .= "".$sep;
$schema_insert = str_replace($sep."$", "", $schema_insert);

//this corrects output in excel when table fields contain \n or \r
//these two characters are now replaced with a space

$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\n";
//$schema_insert = (trim($schema_insert));
//print $schema_insert .= "\n";
//print "\n";

//  5) After adding data to excel file is completed then close the file
fwrite($fp, $schema_insert);

Open in new window

Julian HansenCommented:
This is not an Excel file - this is a CSV using tab characters as delimiters.

And line 35 is blank ....
rgranlundAuthor Commented:
I meant line 27 sorry.
Julian HansenCommented:
What problems are you having? Error or Understanding?

Also just to confirm that what this script is doing is producing a tab delimited text file (same as a CSV) - not a native Excel file. The fact that is being named .xls does not mean it is a native Excel file.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now