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

Asked

Viewed 1,439 times

0

Good afternoon guys. I’m 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 idea?

mysql

  • you are using some programming language?

  • I published a code in PHP, if you need to export something, as you have not put information about the language you are using, I hope to be useful.

  • 1

    In your program when you will open the file, you should set the type of separator that if using in csv, most of the time is ;, then on Excel or Libre offce, you must define that you want to separate by ;.

2 answers

2


0

For the sake of better performance, and use of the PHP language, I recommend using the PHP Excel library: https://github.com/PHPOffice/PHPExcel

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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.