How to export CSV query with each column in a cell? [closed]

0

Good afternoon, guys. I am already able to export the query to CSV, but I need the value of each column to be in a different cell. EX: Id in cell A, Name in cell B ...

Any ideas?

    
asked by anonymous 02.09.2015 / 19:03

2 answers

1

This code should do the magic:

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/arquivo.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

I researched this link: link

    
02.09.2015 / 19:34
0

For the sake of better performance, and use of the PHP language, I recommend using the Excel PHP library: link

Now an example of how to implement the method with PHP Excel:

<?php
error_reporting(E_ALL && ~E_DEPRECATED);
ini_set('display_errors', TRUE);
date_default_timezone_set('America/Sao_Paulo');
set_time_limit(0);
ini_set('memory_limit','2500M');

if (PHP_SAPI == 'cli')
    die('Somente através de um navegador');

require_once("PHPExcel.php");

class ExportData
{

    public function __construct($header, $data)
    {

     $letters = $this->getLetters();
     $export = new PHPExcel();

     $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
     $cacheSettings = array(' memoryCacheSize ' => '20MB');
     PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

      $header = $this->utf8_converter($header);
      $export->setActiveSheetIndex(0)
             ->fromArray($header, null, 'A1');
      $line = 2;

      foreach ($data as $key => $item) {
          $letter_key = 0;
          foreach (array_keys($data) as $key => $value) {
              //você pode passar a $key no lugar da letter_key, caso sua coleção comece com 0.
              $export->setActiveSheetIndex(0)->setCellValue($letters[$letter_key] . $line, $item[$value]);
          }
          $line++;
          $letter_key++;
      }

       $fileName = 'Dados_da_Planilha_'.date('d_m_Y');

        $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: private", false); // required for certain browsers
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8');
        header("Content-Disposition: attachment;filename='" .$fileName . ".xls'");
        header("Content-Transfer-Encoding: binary");
        $xmlWriter->save("php://output");
        return;
    }

    private function getLetters()
    {
        $letters = array(
            0 => 'A',
            1 => 'B',
            2 => 'C',
            3 => 'D',
            4 => 'E',
            5 => 'F',
            6 => 'G',
            7 => 'H',
            8 => 'I',
            9 => 'J',
            10 => 'K',
            11 => 'L',
            12 => 'M',
            13 => 'N',
            14 => 'O',
            15 => 'P',
            16 => 'Q',
            17 => 'R',
            18 => 'S',
            19 => 'T',
            20 => 'U',
            21 => 'V',
            22 => 'W',
            23 => 'X',
            24 => 'Y',
            25 => 'Z'
        );
        return $letters;
    }

    private function utf8_converter($array)
    {
        array_walk_recursive($array, function(&$item, $key){
            if(!mb_detect_encoding($item, 'utf-8', true)){
                $item = utf8_encode($item);
            }
        });

        return $array;
    }
} 

$header = array(
                "Título 1",
                "Título 2",
                "Título 3",
               );

$data = array(
        0 => array(
                 'texto1'=>'lorem inpsum dolor one',
                 'texto2'=>'lorem inpsum dolor two',
                 'texto3'=>'lorem inpsum dolor three',
        ),
        1 => array(
                 'texto1'=>'lorem inpsum dolor four',
                 'texto2'=>'lorem inpsum dolor five',
                 'texto3'=>'lorem inpsum dolor six',
        ),
        2 => array(
                 'texto1'=>'lorem inpsum dolor seven',
                 'texto2'=>'lorem inpsum dolor height',
                 'texto3'=>'lorem inpsum dolor nine',
        )
);
$export = new ExportData($header, $data);
    
02.09.2015 / 19:49