Export data to Excel using PHP Excel

Asked

Viewed 4,279 times

2

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

2 answers

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

1

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

Browser other questions tagged

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