dongnao2582
dongnao2582
2014-03-22 17:12

mysql来优化格式化问题

已采纳

Found the code of coverting mysql to excel, all works perfect, but the problem is that the data with dot is saved with dot and not comma, so when its converted to excel its end up as date: mar.45. So the question is how to replace dot with comma before its converted to excel - PH, Chlorine and Temperature ? (NOT FOR DATE)

Table in database: enter image description here

Excel file:

enter image description here

Code:

<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "root"; //MySQL Username     
$DB_Password = "";             //MySQL Password     
$DB_DBName = "chart";         //MySQL Database Name  
$DB_TBLName = "googlechart"; //MySQL Table Name   
$filename = "excelfilename";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("
");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/
|
|
|/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "
";
    }   
?>
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dpjjmo3079 dpjjmo3079 7年前

    You need to check which fields are numeric and then format the output. Here you have some changes to the sample:

    <?php
    /*******EDIT LINES 3-8*******/
    $DB_Server = "localhost"; //MySQL Server    
    $DB_Username = "root"; //MySQL Username     
    $DB_Password = "";             //MySQL Password     
    $DB_DBName = "chart";         //MySQL Database Name  
    $DB_TBLName = "googlechart"; //MySQL Table Name   
    $filename = "excelfilename";         //File Name
    /*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
    //create MySQL connection   
    $sql = "Select * from $DB_TBLName";
    $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
    //select database   
    $Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
    //execute query 
    $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
    $file_ending = "xls";
    $reals=array();
    //header info for browser
    header("Content-Type: application/xls");    
    header("Content-Disposition: attachment; filename=$filename.xls");  
    header("Pragma: no-cache"); 
    header("Expires: 0");
    /*******Start of Formatting for Excel*******/   
    //define separator (defines columns in excel & tabs in word)
    $sep = "\t"; //tabbed character
    //start of printing column names as names of MySQL fields
    for ($i = 0; $i < mysql_num_fields($result); $i++) {
        $type = mysql_field_type($result,$i);
        echo mysql_field_name($result,$i) . "\t";
        if ($type == "real")
        {
            $reals[] = $i;
        }
    }
    echo "<pre>";
    print("
    ");    
    //end of printing column names  
    //start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != ""){
                if (in_array($j, $reals)){
                    $schema_insert .= str_replace(".",",","$row[$j]").$sep;
                } else {
                    $schema_insert .= "$row[$j]".$sep;
                }
            }
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/
    |
    |
    |/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "
    ";
    }   
    echo "</pre>";
    ?>
    

    Note: mysql is deprecated and will be removed from PHP on version 5.5

    点赞 评论 复制链接分享

为你推荐