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