从php中导出数据，Excel导出的文件格式应为windows 97-2003工作簿 [英] Exporting data from database in php,and the file format for excel export should be as windows 97-2003 workbook

问题描述

下面是我从mysql数据库导出数据的代码。它正常工作，但导出Excel文件时，文件保存为制表符格式。我只需要保存在Windows 97-2003工作簿中。我继续改变标题，但它不工作..打开这些文件时，弹出一些警报，如您尝试打开的文件filename.xls'与文件扩展名指定的格式不同。 我需要摆脱它。可以任何人提出建议..提前感谢:)

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编码：

<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例程：

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

功能：

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 - 使用 phpExcel库。它有几个很好的功能生成excel / csv 本机格式使用php 。

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

2-使用此代码根据点击的提交按钮下载excel或csv

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

这篇关于从php中导出数据，Excel导出的文件格式应为windows 97-2003工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！