Exporting data from database in php,and the file f

问题:

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

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

回答2:

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

回答3:

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