Export data to Excel using PHP Excel

Question

I’m using the library Phpexcel to export some data from the database.

My problem is in relation to some types of data that it puts a simple quotes at the beginning of the number and/or dates '.

In case of date coming from the database with the following format (YY-MM-DDDD):

• if I try to format to Brazilian format and only display, then export with the ' in front;
• if I put in the format that comes from the bank and try to use the functions to format properly it is not working;

Code example to format date line

$objPHPExcel->getActiveSheet()
    ->getStyle('A1')->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);

And in cases of numbers with decimal places also quote '

For example if I set the format #.##0,00000 it returns me the value '0,12345 but I didn’t want you to call me back with that quote, assuming I’m passing the value 0,12345

$objPHPExcel->getActiveSheet()
    ->getStyle('B1')
    ->getNumberFormat()
    ->setFormatCode('#.##0,00000');

Answer 1

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

Date exit

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

Use for real numbers

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

Output of value in R$

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

I use in my spreadsheets this way and it works correctly. Numbers should return as float and Phpexcel format. (Ex.: 10000.00 = 10,000.00) ; The date may be in the same American standard. (Ex. 2009-02-15 = 15/02/2009).

Answer 2

I created a class model to do the entire export process using Phpexcel, I believe it can also serve your 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();