[PHP] 導出CSV

edited 十月 2013 in PHP新手區
正在嘗試使用PHP直接導出CSV 或EXCEL的檔案 (不儲在伺服器上)

已經使用了
http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.intro.php

每當我使用ECHO時, 也會自動在下載回來的檔出現, 這也不是問題, 問題是使用EXCEL開啟時, 會出現錯誤提示, 大意是說內容包含CSS的格式,

請問如何解決?

原始討論: http://twpug.net/x/modules/newbb/viewtopic.php?topic_id=4533

評論

  • edited 九月 2009
    官網看起來沒什麼問題?
    那問題應該就出在你的程式了
  • edited 九月 2009
    與mysql 有關嗎???
    除了mysql 的select , insert 等database command 外,

    我只有while 及 if 的command
  • edited 九月 2009
    @thomas85hk, 貼出程式碼應該會比較快
  • edited 九月 2009
    <?
    ob_start();
    session_start();
    include ("../dbconnect.php");
    include ("../candlestick/function.php");
    include ("../prostick/function.php");
    include ("../component/constant.php");

    //echo "date = ".$_POST."-".$_POST."-15<br>";

    if($_POST != ""){

    //$d=$_POST-0;
    $month=$_POST-0;
    $year=$_POST-0;
    $p=$_POST-0;
    $pType=$_POST-0;

    $date=$year."-".$month."-1";

    //$_SESSION["day"]=$_POST["day"];
    $_SESSION["month"]=$_POST["month"];
    $_SESSION["year"]=$_POST["year"];
    $_SESSION["p"]=$_POST["p"];
    $_SESSION["pType"]=$_POST["pType"];

    //if ($x==12) print " x=1";
    //if ($x<12) print " x=".++$x;
    //if ($_POST==12) echo $_POST++; else echo $_POST;
    }

    ?>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=big5" />
    <title>Simulation Result - Average Dollar</title>
    <style type="text/css">
    <!--
    .style6 { font-size: 24px;
    color: #6633FF;
    font-family: Arial, Helvetica, sans-serif;
    }
    -->
    </style>
    </head>


    <?
    //chnage it
    $startYear = 1990;
    $startMonth = 1;
    $currYear = $startYear;
    $currMonth = $startMonth;
    $endYear = 2004;
    $endMonth = 1;
    $noOfMonth = 60;
    $pType = 2;
    //finish

    $printout = "no";

    echo "Simulation Result - DAC AD<BR>";
    echo "Start: $currYear-$currMonth, Period: $noOfMonth, pType: $pType<BR><BR>";
    echo "<table border=1><tr><td>Date</td><td>Profit</td><td>Annual Profit</td></tr>";

    while ($currYear != $endYear or $currMonth != $endMonth){
    //echo "Curr:$currYear-$currMonth<BR>";
    //echo "End:$endYear-$endMonth<BR>";

    $finishYear = $currYear;
    $finishMonth = $currMonth;
    for($i = 0; $i < $noOfMonth-1; $i++){
    if($finishMonth == 12){
    $finishMonth = 1;
    $finishYear++;
    }
    else{$finishMonth++;}
    }

    $query = "select * from sim_result_ad where date = '$currYear-$currMonth-01' and period ='$noOfMonth' and pType ='$pType'";
    $sqlResult = mysql_query($query);
    $num = mysql_num_rows($sqlResult);

    if($num > 1){
    echo "<BR>ATTENTION: There is more than one data (Date:$currYear-$currMonth-01, Period:$noOfMonth, pType:$pType<BR>";
    } else
    if($num ==1){
    //echo "Very good, I can find a data from database.<BR>";
    }if($num ==0){
    //echo "num of row:$num<BR>";
    $query = "select date, close from hsi where date between '".$currYear."-".$currMonth."-1' and '".$finishYear. "-".$finishMonth."-31'";
    //echo "query =".$query."<br>";
    $sqlResult = mysql_query($query);

    $num2 = mysql_num_rows($sqlResult);
    //check time to action
    $action = ceil($num2 / $noOfMonth);// can set how frequently do transaction
    //echo "action = ".$action."<br>";

    if ($pType == 1) {
    $moneyInHand = $INIT_MONEY;
    $eachTimeBuy = $INIT_MONEY;
    }
    else if ($pType == 2) {
    $moneyInHand = $INIT_MONEY;
    $eachTimeBuy = $INIT_MONEY / $noOfMonth;
    }
    else if ($pType == 3) {
    $moneyInHand = $INIT_MONEY/2;
    $eachTimeBuy = ($INIT_MONEY/2) / $noOfMonth;
    }

    $noOfStock = 0;

    //to keep check the number of transaction we have
    $cnt = 0;

    //Buy same amount of stock in a same number of in the same interval of time
    while($row = mysql_fetch_array($sqlResult)){
    $today = $row["close"];
    if($cnt % $action == 0){
    if($moneyInHand >= $eachTimeBuy){
    $noOfStock += (int)($eachTimeBuy / $today);
    $moneyInHand -= $eachTimeBuy;
    }
    }

    $last = $today;
    $cnt++;

    }
    $moneyInHand += $noOfStock * $last;
    $moneyInHand -= $INIT_MONEY;
    $profit = $moneyInHand / $INIT_MONEY * 100;

    include ("../component/annualProfit.php");

    //add profit and annual profit to database
    $today = date("$currYear-$currMonth-01");
    $query = "insert into sim_result_ad values ('$today', '$noOfMonth', '$pType', '$profit', '$annualProfit')";
    mysql_query($query);
    }

    if($currMonth == 12){
    $currMonth = 1;
    $currYear++;
    }
    else{$currMonth++;}

    //Print out the summary
    $row = mysql_fetch_array($sqlResult);
    echo "<tr><td>$row[0]</td><td>$row[3]</td><td>$row[4]</td><tr>";
    }
    echo "</table>";

    include ("sim_data.php");
    ?>

    除了最後一個include (sim_data.php) 外, 全部include的php也是一些function或變數
  • edited 九月 2009
    sim_data.php 是

    <?php
    require_once 'Spreadsheet/Excel/Writer.php';

    // Creating a workbook
    $workbook = new Spreadsheet_Excel_Writer();

    // sending HTTP headers
    $workbook->send('test.xls');

    // Creating a worksheet
    $worksheet =& $workbook->addWorksheet('My first worksheet');

    // The actual data
    $worksheet->write(0, 0, 'Name');
    $worksheet->write(0, 1, 'Age');
    $worksheet->write(1, 0, 'John Smith');
    $worksheet->write(1, 1, 30);
    $worksheet->write(2, 0, 'Johann Schmidt');
    $worksheet->write(2, 1, 31);
    $worksheet->write(3, 0, 'Juan Herrera');
    $worksheet->write(3, 1, 32);

    // Let's send the file
    $workbook->close();
    ?>
  • edited 九月 2009
    @thomas85hk, 這些資料看起來是在網頁上顯示,並未看到使用 Spreadsheet_Excel_Writer 或是輸出 CSV 的部份?
  • edited 九月 2009
    對的, 輸出部份在 sim_data.php
    已經在第一段程式碼加入了 sim_data.php

    輸出部份在第五個留言
  • edited 九月 2009
    Spreadsheet/Excel/Writer.php的send是利用header傳送
        function send($filename)
        {
            header("Content-type: application/vnd.ms-excel");
            header("Content-Disposition: attachment; filename=\"$filename\"");
            header("Expires: 0");
            header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
            header("Pragma: public");
        }
    
    你把sim_data.php include在一堆html之後當然會失敗
    因為已經違反了header的規則
  • edited 九月 2009
    恩,回的太快,沒看到第 2 篇

    簡單的說,把 sim_data.php 做的事情放在一個獨立的程式中,不要跟原本程式放在一起就可以了。
  • edited 九月 2009
    謝謝, 已經解決了問題
Sign In or Register to comment.