|
Posted by Paul Lautman on July 18, 2008, 3:13 pm
Please log in for more thread options
Geoff Cox wrote:
> Hello
>
> The code below is from a php file which can obtain data from a mysql
> database in Excel format.
>
> Does any part of it explain why the time part if the Date/Time field
> does not have the seconds value?
>
> Cheers
>
> Geoff
>
>
> $link = mysql_connect ($Host, $User, $Password) or die('Could not
> connect: ' . mysql_error());
> mysql_select_db($DBName) or die('Could not select database');
>
> $select = "SELECT * FROM `".$TableName."` ORDER by DateTime";
> $export = mysql_query($select);
> $fields = mysql_num_fields($export);
>
> for ($i = 0; $i < $fields; $i++) {
> $csv_output .= mysql_field_name($export, $i) . "\t";
> }
>
> while($row = mysql_fetch_row($export)) {
> $line = '';
> foreach($row as $value) {
> if ((!isset($value)) OR ($value == "")) {
> $value = "\t";
> } else {
> $value = str_replace('"', '""', $value);
> $value = '"' . $value . '"' . "\t";
> }
> $line .= $value;
> }
> $data .= trim($line)."\n";
> }
> $data = str_replace("\r","",$data);
>
> header("Content-Type: application/vnd.ms-excel");
> header("Content-Disposition: attachment; filename=database_dump.xls");
> header("Pragma: no-cache");
> header("Expires: 0");
> print $csv_output."\n".$data;
> exit;
This code does NOT obtain data from MySQL in Excel format.
This code obtains data from MySQL in the standard format that MySQL delivers
its data.
The code does NOT even write the data out in Excel format. It writes it out
(very badly code wise) in tab delimited format.
Excel then attempts to guess what type of data is between each delimiter.
If you view the outputted data in a text editor, you will see the seconds
value. Try reformatting the cell in Excel.
My advice is to output this data in Excel's XML format, then you can specify
precisely what the format should be for each cell.
See: http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx
| 
XML Sitemap