I am trying to display values fetched from a MySQL database as an excel spreadsheet. Now, I have been successful to create a csv file using the code below;

<?php
error_reporting(0);
    include("includes/connect.php"); // This is the connection string to the database

    //Select the required records from the database
    $result = mysql_query("SELECT * FROM cart_products"); 
    if (!$result) die('Couldn\'t fetch records');
    $num_fields = mysql_num_fields($result);
    $headers = array();

    for ($i = 0; $i < $num_fields; $i++) 
    {
        $headers[] = mysql_field_name($result , $i);
    }

    $fp = fopen('php://output', 'w');
    if ($fp && $result)
    {
            header('Content-Type: text/csv');
            header('Content-Disposition: attachment; filename="export.csv"');
            header('Pragma: no-cache');
            header('Expires: 0');
            fputcsv($fp, $headers);

            while ($row = mysql_fetch_row($result)) 
            {
                 fputcsv($fp, array_values($row));
            }
            die;
     }
?>

However, when I try to replace the parameters for an excel spreadsheet, I get a distorted spreadsheet, with records not in their corresponding cells. The code for the excel spreadsheet is as below;

<?php
error_reporting(0);
    include("includes/connect.php"); // This is the connection string to the database

    //Select the required records from the database
    $result = mysql_query("SELECT * FROM cart_products"); 
    if (!$result) die('Couldn\'t fetch records');
    $num_fields = mysql_num_fields($result);
    $headers = array();

    for ($i = 0; $i < $num_fields; $i++) 
    {
        $headers[] = mysql_field_name($result , $i);
    }

    $fp = fopen('php://output', 'w');
    if ($fp && $result)
    {
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment; filename="export.xls"');
            header('Pragma: no-cache');
            header('Expires: 0');
            fputcsv($fp, $headers);

            while ($row = mysql_fetch_row($result)) 
            {
                 fputcsv($fp, array_values($row));
            }
            die;
     }
?>

Any help?
Thanx in advance.

Recommended Answers

All 3 Replies

The Excel format is not equal to the CSV format, hence the distortion. There are tools available to create a real Excel file if that's what you need, for example PHPExcel.

Ok, Lemme try that!! Thanx

@Pritaeas, Thanx alot. Now I understand why it was behaving that way.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.