PHP database Excel export

Question

I am using a PHP script to export data from MySQL into Excel. The first row of the Excel sheet is a column heading. I want them to appear in bold. How do I do this? I am using the following code:

$con = mysql_connect("localhost","admin","password");
if (!$con)
{
    die('Could not connect to DB: \n' . mysql_error());
}

mysql_select_db("ALNMSI", $con);

$result = mysql_query("SELECT * FROM survey1");

$filename = "alnmsi_" . date('d-m-Y') . ".xls";

header("Content-Disposition: attachment; filename=\"$filename\"");

header("Content-Type: application/vnd.ms-excel"); $flag = false;

$flag = false;

while($row = mysql_fetch_array($result))
{
    if(!$flag)
    {
        data_keys();
        $flag = true;
    }
    array_walk($row, 'cleanData');
    data_array($row);
}

no excel does accept raw text, the excel file is being created properly now i want to introduce a bit of formatting in the excel file. i need the first row to be in bold — user434885, Jan 10 '11 at 06:59
i am not using a library, the headers i am using informs the browser that instead of the output being displayed on the web page it shgould be downloaded into and .xls file — user434885, Jan 10 '11 at 07:06
Show us your `data_array()` function, since that is where you are having problems. If you are outputting some column formatted file or CSV data, then I'm afraid we cannot help making any text bold. — mario, Jan 10 '11 at 07:12
If you're not using a library, then the probability is that you're not actually generating an Excel file, just a text file (CSV probably, though the code you've posted doesn't show any file generation at all) with an extension of .xls. Simply setting headers is pretty meaningless. If I echoed text containing the dialogue from Star Wars and set the headings to pretend it was a .mov file, I wouldn't expect the "film" I'd created to be playable in Movie Player — Mark Baker, Jan 10 '11 at 09:24

score 4 · Answer 1 · answered Jan 10 '11 at 08:06

4

You should use a library to generate Excel files.

PHPExcel : http://phpexcel.codeplex.com/
Spreadsheet_Excel_Writer : http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.intro.php

answered Jan 10 '11 at 08:06

Twister

749
4
9

the pear excel writer appears to only work upto excel5.0 not 7.0. – user434885 Jan 11 '11 at 07:37
also i dont want to introduce such completixity into my code so as to use PHPExcel classes. Is there no other way i can use the html bold and underline tags to do this ? – user434885 Jan 11 '11 at 07:38
I think you will add more complexity trying to avoid such librairies. If you are building new files I do not see the problem. Excel 7 is able to open excel 5 files. – Twister Jan 12 '11 at 08:09

score 3 · Accepted Answer · edited May 23 '17 at 12:26

I'm assuming you are doing it wrong. Whatever you output it's probably not a valid Excel file, since you didn't mention BIFF (this is the binary format which Excel files would be made of).

If you output TAB or COMMA separated values, then Excel only accidentially opens it correctly. And there is no way to style headers.

PHP database Excel export

2 Answers2