exporting database to xls with price formatted with dollar sign and commas

Im exporting my mysql database to excel, and everything is working, but I want the price field to display with a dollar sign and commas within the excel spreadsheet.

Here is my code:

$pubtable = $_GET["publication"];
$addate = $_GET["adDateHidden"];

$export = mysql_query("SELECT * FROM $pubtable WHERE addate = '$addate' ORDER BY price DESC") or die ("Sql error : " . mysql_error());

$fields = mysql_num_fields($export);

for($i = 0; $i < $fields; $i++){
    $header .= mysql_field_name($export , $i). "\t";
}

while($row = mysql_fetch_row($export)){
    $line = ''; 
    foreach($row as $value)    {   
        if(!isset($value) || trim($value) == "")        {
            $value = "\t";
        } else {
            $value = str_replace('"' , '""' , $value);
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= tr开发者_如何学编程im($line). "\n";
}
$data = str_replace("\r" , "" , $data);

if(trim($data) == ""){
    $data = "\n(0)Records Found!\n";                        
}

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=".$pubtable."_".$addate.".xls");
header("Pragma: no-cache");
header("Expires: 0");
header ('Content-Transfer-Encoding: binary');
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT');
header ('Cache-Control: cache, must-revalidate');
print "$header\n$data";

I tried doing this

$export = mysql_query("SELECT  CONCAT('$', FORMAT(price, 2)) as fieldalias * FROM $pubtable WHERE addate = '$addate' ORDER BY fieldalias DESC") or die ("Sql error : " . mysql_error());

this formats it correctly but it only outputs the price field and nothing else.

---

Technically, you're not producing an Excel spreadsheet. You're producing a CSV file with a .xls extension. CSV has no mechanism for adding formatting, because it's just plain text. You can have MySQL and/or PHP format a number into what looks like a nice currency value, but then you're destroying its existence as a number. It'll be a string-that-used-to-be-a-number.

You should use PHPExcel to produce an ACTUAL Excel file, into which you can add all the usual goodies that Excel supports, including colors and formulae.

---

Your code is generating a text file (CSV formated) with a XLS extension. Then, under Windows with an Excel installed, it may be automatically opened by Excel despite the content is only text file. This method cannot produce any data with style.

I suggest that you use OpenTBS to easily build your Excel file. OpenTBS is a PHP class which can build real XLSX, DOCX, ODT, ODS and more... using templates. You just design the template with Excel and then merge it with the data under PHP and you have a new XSLS directly for download, or as a files saved on the server.