Export data to Excel using PHP Excel

To generate your spreadsheets in the Brazilian standard, use the codes below

Use for date

<?   $format = 'Y-m-d';                          
     $dateValue = DateTime::createFromFormat($format, '2009-02-15');
     $objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
?>

Out of date

<?php $objPHPExcel->getActiveSheet()->setCellValue('A1', $dateValue->format('d/m/Y')); ?>

Use for real numbers

$objPHPExcel->getActiveSheet()->getStyle('B1')->getNumberFormat()->setFormatCode("#,##0.00");

Exit value in R $

$objPHPExcel->getActiveSheet()->setCellValue('B1', '100000.09');

I use it in my plans this way and it works correctly. Numbers should return as float and PHPExcel formats. (E.g., 10000.00 = 10,000.00); The date can be in the same American standard. (Ex. 2009-02-15 = 02/15/2009).

I've created a class template to do the whole export process using PHPExcel, I believe it can also serve its purpose:

  require_once("./PHPExcel/Classes/PHPExcel.php");

    class ExportData
    {
        private $data;
        private $filename;
        private $sheet;

        public function __construct(array $data, array $sheet = array('Column A','Column B','Column C'), $fileName='nome_do_arquivo')
        {
            $this->data = $data;
            $this->sheet = $sheet;
            $this->filename = $fileName;  
        }

        private function getLetters()
        {
            $letters = range('A', '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;
        }

        public function exportData()
        {
             $letters = $this->getLetters();
             $export = new PHPExcel();
             $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
             $cacheSettings = array(' memoryCacheSize ' => '20MB');
             PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
             $sheet = $this->utf8_converter($this->sheet);
             $export->setActiveSheetIndex(0)
                    ->fromArray($sheet, null, 'A1');
            $numColumn = 2;
            if (!empty($this->data)) {
               foreach ($this->data as $key => $value) {
                  $keys = array_keys($value);
                  for ($i=0; $i < count($sheet); $i++) {
                      $export->setActiveSheetIndex(0)->setCellValue($letters[$i] . $numColumn, $value[$keys[$i]]);
                  }
              $numColumn++;
              }
            }
            $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='" . $this->filename . ".xls'");
            header("Content-Transfer-Encoding: binary");
            $xmlWriter->save("php://output");
            exit;
        } 
    }

    $titulos = array('ID','Título','Mensagem', 'Data de publicação');
    $dados = array( 
                0 => array(
                    'id'=>1,
                    'titulo'=>'teste 2',
                    'texto'=>'olá',
                    'data_banco'=>'01/05/2015 15:30:10'
                ),
                1 => array(
                    'id'=>2,
                    'titulo'=>'teste 3',
                    'texto'=>'olá2',
                    'data_banco'=>'08/10/2015 11:20:10'
                ),
                2 => array(
                    'id'=>3,
                    'titulo'=>'teste 4',
                    'texto'=>'olá4',
                    'data_banco'=>'02/11/2015 10:20:30'
                ),
                3 => array(
                    'id'=>4,
                    'titulo'=>'teste 5',
                    'texto'=>'olá6',
                    'data_banco'=>'10/12/2015 15:20:30'
                ),
                4 => array(
                    'id'=>5,
                    'titulo'=>'teste 6',
                    'texto'=>'olá3',
                    'data_banco'=>'12/11/2015 08:20:30'
                )
              );
    $exportData = new ExportData($dados, $titulos, 'exemplo_de_dados');
    $exportData->exportData();