Problems exporting from PHP to CSV

Question

Problems exporting from PHP to CSV

Navigation

#1 by (3 votes)

3

I'm having trouble viewing a csv file. My intention is that I can use the Excel command "data to column" and stay line by line, but the file has skipped line making it impossible. The code was made based on another example here even posted by an adm.

     date_default_timezone_set('America/Sao_Paulo');

ini_set('memory_limit', '12000M');
 require_once($_SERVER['DOCUMENT_ROOT']."/administrator/lib/connection.php");

//echo "<script type=\"text/javascript\"> window.open(\"http://www.tometoo.com.br/generateCSV2.php\", \"_blank\")</script>";

        $PDO = Database::Connect();

        $SQL = "SELECT idpost, idcomment, cm_id_author, pg_name, ps_date, cm_date, ps_message, cm_name_author, cm_message, pss_likes, pss_shares, pss_comments, cms_like, cms_reply 
        from fb_post
        INNER JOIN fb_pssummary ON pss_idpost = idpost
        INNER JOIN fb_page ON ps_idpage = idpage
        INNER JOIN fb_comment ON cm_idpost = idpost
        INNER JOIN fb_cmsummary ON cms_idcomment = idcomment 
        where ps_date between '2016-09-01 00:00' and '2016-09-01 23:59:59'
        ORDER BY ps_date ASC";

        $SQL = $PDO->prepare($SQL);

        $SQL->execute();

        $result = $SQL->fetchAll(PDO::FETCH_ASSOC);


        function array_para_csv(array &$array)
        {
           if (count($array) == 0) {
             return null;
           }
           ob_start();
           $df = fopen("php://output", 'w');
           fputcsv($df, array_keys(reset($array)));
           foreach ($array as $row) {
              fputcsv($df, $row);
           }
           fclose($df);
           return ob_get_clean();
        }

        function cabecalho_download_csv($filename) {
    // desabilitar cache
    $now = gmdate("D, d M Y H:i:s");
    header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
    header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
    header("Last-Modified: {$now} GMT");

    // forçar download  
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");

    // disposição do texto / codificação
    header("Content-Disposition: attachment;filename={$filename}");
    header("Content-Transfer-Encoding: binary");
}


cabecalho_download_csv("setembro_1_1M_1_parte" . date("Y-m-d") . ".csv");
echo array_para_csv($result);

the output has been this way in excel 229151370439618_1206843959337016,1206843959337016_1206850609336351,1063115217101837, CartaCapital, "2016-09-01 00:15:31", "2016-09-01 00:27:42", "POLICE VIOLENCE The center of Sao Paulo became a veritable war-chamber tonight, from the moment that Military Police decided to disperse the demonstration against the coup from the middle of it. The effect was the dispersion of protesters to various corners and a real hunt promoted by police based bombs and rubber bullets. According to information, a young woman is currently in the Clinic Hospital with the strong possibility of having lost sight of an eye and a photographer had her work equipment broken and was detained. At the moment, not even the photographer's lawyers were informed about the reason for the arrest Images by Tadeu Amaral

Coup #Impeachment, "" Marco Antonio Camelo "," Capital letter defends bandits. ", 418,76,263,4,0

Please ignore facebook content that has nothing to do with my opinion. As you can see beyond the accent has the problem of the lines.

php

asked by anonymous 18.10.2016 / 18:44

1 answer

Error !!! You did not select the file to upload. Codeigniter Connect PostgreSQL to Delphi 7 via ZeosLib

score 3 · Answer 1

One solution to avoid this kind of problem is through a well-developed library PHP Excel :

1) After downloading the library PHP Excel

2) Just use it as follows from your code:

date_default_timezone_set('America/Sao_Paulo');
ini_set('display_errors', 1);
error_reporting(-1); //mostra todos os erros (-1 ou E_ALL)
ini_set('memory_limit', '12000M');
require_once($_SERVER['DOCUMENT_ROOT']."/administrator/lib/connection.php");
require_once($_SERVER['DOCUMENT_ROOT']."/administrator/lib/PHPEXcel/Writer/PHPExcel.php");
require_once($_SERVER['DOCUMENT_ROOT']."/administrator/lib/PHPEXcel/Writer/Excel2007.php");

$PDO = Database::Connect();

$SQL = "SELECT idpost, idcomment, cm_id_author, pg_name, ps_date, cm_date, ps_message, cm_name_author, cm_message, pss_likes, pss_shares, pss_comments, cms_like, cms_reply 
        from fb_post
        INNER JOIN fb_pssummary ON pss_idpost = idpost
        INNER JOIN fb_page ON ps_idpage = idpage
        INNER JOIN fb_comment ON cm_idpost = idpost
        INNER JOIN fb_cmsummary ON cms_idcomment = idcomment 
        where ps_date between :initial and :final
        ORDER BY ps_date ASC";

$SQL = $PDO->prepare($SQL);
$SQL->execute([':initial'=>'2016-09-01 00:00', ':final'=>'2016-09-01 23:59:59']);

$result = $SQL->fetchAll(PDO::FETCH_ASSOC);

exportExcel("nome_do_arquivo", $result);

function exportExcel($filename, $result)
{ 
    $result = addHeader($result);
    $export = new PHPExcel();
    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
    $cacheSettings = array(' memoryCacheSize ' => '8MB');
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

    $export->setActiveSheetIndex(0)
            ->fromArray($result, null, 'A1');

    $xmlWriter = new PHPExcel_Writer_Excel2007($export);

    header("Pragma: protected"); // required
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Cache-Control: public", false); // required for certain browsers
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8');
    header("Content-Disposition: attachment;filename='{$filename}.xlsx'");
    header("Content-Transfer-Encoding: binary");
    $xmlWriter->save("php://output");
    exit();
}

function addHeader($value)
{
    $return = null;
    if (is_array($value)) {
        $header = array_keys($value[0]);
        $return[0] = $header;
        foreach ($value as $key => $row) {
            foreach ($row as $columnKey => $column) {
                $currentfindedKey = array_search($columnKey, $header);
                $return[$key][$currentfindedKey] = $column;
            }
        }
        array_unshift($return, $header);
    }
   return $return;
}