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
– Syed Ibrahim, Commented Aug 26, 2014 at 5: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
– RN Kushwaha, Commented Aug 31, 2014 at 5:43

Sverri M. Olsen · Accepted Answer · 2014-08-26 05:37:21Z

1

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);
}

edited Aug 26, 2014 at 5:37

answered Aug 26, 2014 at 5:30

Sverri M. Olsen

13.2k3 gold badges38 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alex.pulver · Accepted Answer · 2014-08-27 07:19:15Z

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

RN Kushwaha · Accepted Answer · 2014-09-27 20:42:36Z

1

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;
    }
     ?>

edited Sep 27, 2014 at 20:42

answered Aug 26, 2014 at 5:31

RN Kushwaha

2,1343 gold badges30 silver badges41 bronze badges

9 Comments

RN Kushwaha Over a year ago

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 Over a year ago

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 Over a year ago

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

Syed Ibrahim Over a year ago

brother how to export excel file as .xls format?? your code make is save as in tab delimited...

RN Kushwaha Over a year ago

What's going on if you open this xls sheet? I mean any warning or just a single line and tab delimited values?

|

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related