Exporting data from database in php,and the file format for excel export should be as windows 97-2003 workbook

Question

Below is my code to export data from mysql database. It works properly, but while exporting excel file, file saved as in tab delimeter format. I just need to save it in windows 97-2003 workbook. I keep changing on header's, but it doesn't work.. while open those file it popups some alert such as "the file you are trying to open,'filename.xls', is in different format than specified by the file extension.." I need to rid on it. Can any one give suggestion.. thanks in advance :)

HTML coding:

<input type="submit" name="frmDownload" id="frmDownload" value="CSV" title="Export" class="frmDownloadButton" />
<input type="submit" name="frmDownload" id="frmDownload" value="Excel" title="Export" class="frmDownloadButton" />
<input type="submit" name="frmDownload" id="frmDownload" value="TEXT" title="Export" class="frmDownloadButton" />
<input type="submit" name="frmDownload" id="frmDownload" value="XML" title="Export" class="frmDownloadButton" />

PHP routine:

if ($_POST["frmDownload"]) { 
    $output = "";
    $line_terminated="\r\n";
    $strDatas = array();
    $field_terminated = doGetFieldDelimeter($_POST);
    $export_schema = "Name".$field_terminated."Code".$field_terminated."Email".$field_terminated."Designation".$field_terminated."Number".$field_terminated."Salary".$field_terminated."Age";
    $strDataQuery = doSelectRecords();
    $strDatas = $strDataQuery;
    $output.= doGetExportSchema($_POST,$export_schema);
    $delimeter = doGetDelimeterForTextFile($_POST);
    $output.= doExportData($_POST, $strDatas, $field_terminated, $line_terminated, $delimeter);
    $output.= doGetXmlTitle($_POST);
    doGetHeader($_POST,$output,$objPHPExcel);
    echo $output;
    exit;
}

functions:

function doSelectRecords()
{
    $strSql = "SELECT * FROM tbl_employee";
    $strResult = SelectQry($strSql);
    return $strResult;
}


function SelectQry($Qry) {
    $result = mysql_query($Qry) or die ("QUERY Error:".$Qry."<br>".mysql_error());      
    $numrows = mysql_num_rows($result); 
    if ($numrows == 0) {            
        return;
    } else {
       $row = array(); 
       $record = array();
       while ($row = mysql_fetch_array($result)) { 
            $record[] = $row; 
       }        
    }   
    return MakeStripSlashes($record);
}

function doGetExportSchema($objArray,$export_schema)
{

    if ($objArray["frmDownload"] =="XML") {
        $output.= '';
        $output.= '<employee>';
    } else {
        $output.= $export_schema;
    }
    return $output;
}

function doGetDelimeterForTextFile($objArray)
{
    if($objArray["frmDownload"] =="TEXT") {
        $delimeter = '\t';
    } else {
        $delimeter = '';
    }
    return $delimeter;
}

function doExportData($objArray,$strDatas,$field_terminated,$line_terminated,$delimeter = NULL)
{

    for ($k=0; $k<count($strDatas); $k++) {
        $strData = $strDatas[$k];
        if ($objArray["frmDownload"] == "XML") {
            $output.= $line_terminated;
            $output.= '<row>';
            $output.= $line_terminated;
            $output.= '<name>'.$strData['1'].'</name>'.$field_terminated;
            $output.= '<code>'.$strData['2'].'</code>'.$field_terminated;
            $output.= '<email>'.$strData['3'].'</email>'.$field_terminated;
            $output.= '<designation>'.$strData['4'].'</designation>'.$field_terminated;
            $output.= '<number>'.$strData['5'].'</number>'.$field_terminated;
            $output.= '<salary>'.$strData['6'].'</salary>'.$field_terminated;
            $output.= '<age>'.$strData['7'].'</age>'.$field_terminated;
            $output.= '</row>'.$field_terminated;
        } else {
            $output.= $line_terminated;
            $output.= $strData['1'].$field_terminated;
            $output.= $strData['2'].$field_terminated;
            $output.= $strData['3'].$field_terminated;
            $output.= $strData['4'].$field_terminated;
            $output.= $strData['5'].$field_terminated;
            $output.= $strData['6'].$field_terminated;
            $output.= $strData['7'].$delimeter;
        }
    }
    return $output;
}


function doGetFieldDelimeter($objArray) 
{

    switch ($objArray["frmDownload"]) {
        case "CSV":
            echo $field_terminated= ",";
            break;
        case "Excel":
            echo $field_terminated="\t";
            break;
        case "TEXT":
            echo $field_terminated="|";
            break;
        case "XML":
            echo $field_terminated="\r\n";
            break;
    }
    return $field_terminated;
}

function doGetXmlTitle($objArray)
{
    if ($objArray["frmDownload"] == "XML") {
        $output.= '</employee>';
    }
    return $output;
}

function doGetHeader($objArray,$output,$objPHPExcel = NULL)
{
    header("Content-Description: File Transfer");
    switch ($objArray["frmDownload"]) {
        case "CSV":
            header("Content-Type: application/csv");
            header("Content-Disposition: attachment; filename=employee_details.csv");
            break;
        case "Excel":
            header("Pragma: public");
            header("Expires: 0");
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Content-Type: application/force-download");
            header("Content-Type: application/octet-stream");
            header("Content-Type: application/download");;
            header("Content-Disposition: attachment;filename=Report.xls");
            header("Content-Transfer-Encoding: binary ");
            break;
        case "TEXT":
            header("Content-Type: application/txt");
            header("Content-Disposition: attachment; filename=employee_details.txt");
            break;
        case "XML":
            header("Content-Type: application/xml");
            header("Content-Disposition: attachment; filename=employee_details.xml");
            break;
    }
    header("Content-Transfer-Encoding: binary");
    header("Expires: 0");
    header("Cache-Control: must-revalidate");
    header("Pragma: public");
    header("Content-Length: ".strlen($output));
    ob_clean();
    flush();
}

if any these symbol { /\\r|\\n|,|"/ } found in details we have make empty.. for this purpose only, i used str_replace.. :) — Syed Ibrahim, Aug 26 '14 at 05:12
[`SELECT ... INTO`](https://dev.mysql.com/doc/refman/5.0/en/select-into.html). [SELECT syntax](https://dev.mysql.com/doc/refman/5.0/en/select.html). [Example](http://stackoverflow.com/questions/125113/php-code-to-convert-a-mysql-query-to-csv#answer-125125). — Sverri M. Olsen, Aug 26 '14 at 05:12
Your original question was about export to csv not excel workbook, at least you should be confident what you are asking. It makes no sense to change the question when they answered and once you accepted that answer. — RN Kushwaha, Aug 31 '14 at 05:43

Sverri M. Olsen · Answer 1 · 2014-08-26T05:37:21.283

You can just tell your MySQL database to dump it as CSV:

$file = 'D:/path/to/website/dump.sql';

// The SELECT...INTO query is unable to overwrite if the
// file already exists, so delete it beforehand:
if (is_file($file))
    unlink($file);

$query = "
    SELECT id, title, created   -- The fields to export
    INTO OUTFILE '{$file}'      -- The file
    FIELDS TERMINATED BY ','    -- Delimiter
    OPTIONALLY ENCLOSED BY '\"' -- Quote if necessary
    LINES TERMINATED BY '\n'    -- End line with LF newline
    FROM tbl_employee           -- Table name
";

$db = new MySQLi('localhost', 'root', '', 'your_database');
$db->query($query);

// Show any errors...
if ($db->error) {
    var_dump($db->error);
}

RN Kushwaha · Answer 2 · 2014-09-27T20:42:36.880

1-Use phpExcel library. It has several nice features for generating excel/csv in native format using php.

2- Use this code to download either excel or csv based on which submit button was clicked

 <input type="submit" name="Download"  value="CSV" title="Export" class="frmDownloadButton" /> 
<input type="submit" name="Download"  value="EXECL" title="Export" class="frmDownloadButton" /> 


    <?php

    if($_POST["download"]){ 
            $output="";
            $line_termineted="\n";

            if( $_POST["download"] =="CSV") $field_termineted=","; else $field_termineted="\t"; 
                $enclosed='"';
                $escaped="\\";

                $export_schema="SR No".$field_termineted."Student ID".$field_termineted."First Name".$field_termineted."Middle Name".$field_termineted."Last Name";
                $dataQuery=mysql_query("select * from sof_student ");
                $output.=$export_schema;
                $p=0;
                while($data=mysql_fetch_array($dataQuery)) {
                 $p++;
                    $output.= $line_termineted.$p.$field_termineted;
                    $output.=$enclosed.$data["id"].$enclosed.$field_termineted;
                    $output.=$enclosed.$data["first_name"].$enclosed.$field_termineted;
                    $output.=$enclosed.$data["middle_name"].$enclosed.$field_termineted;
                    $output.=$enclosed.$data["last_name"].$enclosed.$field_termineted;
                  }

        header("Content-Description: File Transfer");
       if( $_POST["download"] =="CSV"){
            header("Content-Type: application/csv");
            header("Content-Disposition: attachment; filename=report".date("d_m_Y_H_i_s").".csv");
        } else {
            header("Content-Type: application/vnd.ms-excel");
            header("Content-disposition: attachment; filename=report".date("d_m_Y_H_i_s").".xls");
        }

        header("Content-Transfer-Encoding: binary");
        header("Expires: 0");
        header("Cache-Control: must-revalidate");
        header("Pragma: public");
        header("Content-Length: ".strlen($output));
        ob_clean();
        flush();
        echo $output;
        exit;
    }
     ?>

Additionally you can use to download in multiple formats like sql, pdf, html, etc by checking the option user selected in this way. — RN Kushwaha, Aug 26 '14 at 05:32
this is just example code from one of my work. I use mysqli but you can use pdo or mysql. I'm editing my answer for clarity. — RN Kushwaha, Aug 26 '14 at 05:59
You should use $line_termineted="\n"; variable inside while loop like $output.= $line_termineted.$p.$field_termineted; This will make every row to start from a new line — RN Kushwaha, Aug 26 '14 at 06:12
brother how to export excel file as .xls format?? your code make is save as in tab delimited... — Syed Ibrahim, Aug 26 '14 at 13:36
What's going on if you open this xls sheet? I mean any warning or just a single line and tab delimited values? — RN Kushwaha, Aug 26 '14 at 13:40
Wait.. I will post here code to download in all formats like pdf, sql, html,text etc. — RN Kushwaha, Aug 26 '14 at 13:42
the file you are trying to open,'filename.xls', is in different format than specified by the file extension.. — Syed Ibrahim, Aug 26 '14 at 13:44
ya brother thats fine.. but after i import these file it doesn't get imported.. because my import function only accepts with .xls format...but what i export file is in tab delimited format.. — Syed Ibrahim, Aug 26 '14 at 13:46
May be changing this header work for you header("Content-type: application/octet-stream"); — RN Kushwaha, Aug 26 '14 at 13:58

score 1 · Answer 3 · answered Aug 27 '14 at 07:19

Using EasyXLS Excel library you can save real xls files from a mysql query, not csv files:

// Query the database
$query_result = mysql_query( "SELECT * FROM table", $db_conn ) or die( "<strong>ERROR: Query failed</strong>" );

// Create the list used to store the values
$lstRows = new COM("EasyXLS.Util.List");

// Add the header row to the list
$lstHeaderRow = new COM("EasyXLS.Util.List");
$lstHeaderRow->addElement("Column 1");
$lstHeaderRow->addElement("Column 2");
$lstHeaderRow->addElement("Column 2");
$lstRows->addElement($lstHeaderRow);

// Add the values from the database to the list
while ($row=mssql_fetch_array($query_result))
{
     $RowList = new COM("EasyXLS.Util.List");
     $RowList->addElement("" . $row["Column 1"]);
     $RowList->addElement("" . $row["Column 2"]);
     $RowList->addElement("" . $row["Column 3"]);
     $lstRows->addElement($RowList);
}

// Create an instance of the object used to format the cells
$xlsAutoFormat = new COM("EasyXLS.ExcelAutoFormat");
$xlsAutoFormat->InitAs($AUTOFORMAT_EASYXLS1);

// Export list to Excel file
$xls->easy_WriteXLSFile_FromList_2("Query to Excel.xls", $lstRows, $xlsAutoFormat, "MySQL Query values");

See here more details about exporting data from database:

http://www.easyxls.com/manual/basics/export-list-to-excel.html

and here about exporting data to Excel 97-2003 xls workbook:

http://www.easyxls.com/manual/basics/export-to-xls-file-format.html

Exporting data from database in php,and the file format for excel export should be as windows 97-2003 workbook

3 Answers3

Linked