Keep information in date format with PHP?

Question

I have the following information in a file .xls 12/12/2017 10:34:03 but when I show in the browser with PHP appears like this 43081.4403125. has some way of keeping in date format?

I’m using the Phpexcel library to pick up the following information lines that picks up:

utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $z)->getValue());

$i = linha
$z = coluna

---

<?php
error_reporting(E_ALL);

date_default_timezone_set('America/Sao_Paulo');

require_once 'PHPExcel.php';
require_once 'Marketplace.php';

class MarketplaceDAO extends Marketplace
{
    public $msg = "";

    private $totais = array("Recebidos","Recebidos (Cliente)","Atendidos","Atendidos (Cliente)","Backlog","Backlog (Cliente)","Tempo Médio de Tratativa","Contato Mais Antigo");

    function __construct()
    {
        $this->setBacklog($this->carregarBacklogCliente());
        $this->setAtendidos($this->carregarAtendidosCliente());
        $this->setTmt($this->carregarTMTCliente());
        $this->setContatoMaisAntigos($this->carregarContatoMaisAntigoCliente());
    }

    public function pegarDado($dado)
    {
        switch ($dado) {
            case "backlog":
                return $this->getBacklog();
                break;
            case "contato_mais_antigo":
                return $this->getContatoMaisAntigos();
                break;
            case "atendidos":
                return $this->getAtendidos();
                break;
            case "tmt":
                return $this->getTmt();
                break;
        }
    }

    private function abrirAquivo()
    {
        $base = "MKTPLACE";
        $dia = date('d');
        $mes = date('m');
        $ano = date('Y');

        //$arquivo = $dia.".xls";
        //$filename = utf8_decode("\\\\fsb2w02\Planejamento_SacOsasco\Base Kista\E-mail Kista\Consolidado\MKTPLACE\\2016\\12\\16.xls");
        $filename = $this->localizarArquivo($dia, $mes, $ano);

        if(!file_exists($filename)){
            $dia = date("d", mktime($dia-1));
            $filename = $this->localizarArquivo($dia, $mes, $ano);
            $data = date("d/m/Y h:i:s", fileatime($filename));
            $this->msg = utf8_encode("ULTIMA ATUALIZAÇÃO DO ARQUIVO - ".$data);
        } else {
            $data = date("d/m/Y h:i:s", fileatime($filename));
            $this->msg = utf8_encode("ULTIMA ATUALIZAÇÃO DO ARQUIVO - ".$data);
        }

        return $filename;
    }

    private function localizarArquivo($dia, $mes, $ano){
        return utf8_decode("\\\\fsb2w02\Planejamento_SacOsasco\Base Kista\E-mail Kista\Consolidado\MKTPLACE\\".$ano."\\".$mes."\\".$dia.".xls");
    }

    public function getMensagem(){
        return $this->msg;
    }

    private function lerArquivo()
    {
        $objReader = new PHPExcel_Reader_Excel5();
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($this->abrirAquivo());
        $objPHPExcel->setActiveSheetIndex(0);
        return $objPHPExcel;
    }

    private function carregarTMTCliente()
    {
        $objPHPExcel = $this->lerArquivo();
        return utf8_decode($objPHPExcel->getActiveSheet()
            ->getCellByColumnAndRow(9, 2)
            ->getValue());
    }

    private function carregarBacklogCliente()
    {
        $objPHPExcel = $this->lerArquivo();
        return utf8_decode($objPHPExcel->getActiveSheet()
            ->getCellByColumnAndRow(8, 2)
            ->getValue());
    }

    private function carregarAtendidosCliente()
    {
        $objPHPExcel = $this->lerArquivo();
        return utf8_decode($objPHPExcel->getActiveSheet()
            ->getCellByColumnAndRow(4, 2)
            ->getValue());
    }

    private function carregarContatoMaisAntigoCliente()
    {
        $objPHPExcel = $this->lerArquivo();
        return $objPHPExcel->getActiveSheet()
        ->getCellByColumnAndRow(10, 2)
        ->getValue();
    }

    public function carregaCabecalhoTabela()
    {
        $objPHPExcel = $this->lerArquivo();
        //Para acrescentar a coluna de "Contato mais antigo muda de 10 para 11"
        for ($i = 0; $i < 10; $i ++) {
            if ($i == 3 || $i == 1 || $i == 5 || $i == 7) {
                continue;
            }
            echo "<th>" . $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, 1)->getValue() . "</th>";
        }
    }

    private function carregarValoresTotais($coluna){
        switch ($coluna) {
            case "Recebidos (Cliente)":
                break;
        }
    }

    public function carregaCorpoTabela()
    {
        $objPHPExcel = $this->lerArquivo();

        //$z = coluna
        //$i = linha

        for ($z = 2; $z < 14; $z++) {
            echo "<tr>";
            if($z == 3 || $z == 5 || $z == 6){
                continue;
            }
            //Para acrescentar a coluna de "Contato mais antigo muda de 10 para 11"
            for ($i = 0; $i < 11; $i++) {
                if ($i == 3 || $i == 1 || $i == 5 || $i == 7) {
                    // Pula colunas desnecessarias no corpo da tabela
                    continue;
                } else if($i == 0 && $z != 2){
                    //coloca a cor azul claro na coluna skill com exceção da linha de total
                    echo "<td class='estilo-coluna-skill'>" . utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $z)->getValue()) . "</td>";
                }else if($z == 2){
                    if($i != 0){
                        //Centraliza os valores numericos da linha de total
                        if($i != 10 && $i != 9){
                            //Formata o numero das colunas com exceção das ultimas colunas
                            echo "<td class='estilo-colunas-valor-numerico estilo-linha-total'>" . utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $z)->getValue()) . "</td>";
                        }else if($i == 10){
                            echo "<td class='estilo-colunas-valor-numerico estilo-linha-total'>".utf8_encode('MANUTENÇÂO')."</td>";
                        }else{
                            echo "<td class='estilo-colunas-valor-numerico estilo-linha-total'>" . utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $z)->getValue()) . "</td>";
                        }
                    } else {
                        //Mantem alinhado a esquerda a primeira coluna da linha de total
                        echo "<td class='estilo-linha-total'>" . utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $z)->getValue()) . "</td>";
                    }
                }else if($i != 10 && $i != 9){
                    //Formata o numero das colunas com exceção da linha total
                    echo "<td class='estilo-colunas-valor-numerico'>" . utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $z)->getValue()) . "</td>";
                }else if($i == 10){
                    echo "<td class='estilo-colunas-valor-numerico'>".utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $z)->getValue())."</td>";
                }else{
                    echo "<td class='estilo-colunas-valor-numerico'>" . utf8_decode($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, $z)->getValue()) . "</td>";
                }
            }
            echo "</tr>";
        }
    }
}

---

To first option appears this error:

Fatal error: Uncaught Error: Call to a member function getNumberFormat() on float in >C:\xampp\htdocs\painel-cajamar\class\MarketplaceDAO.php:160 Stack trace: #0 C:\xampp\htdocs\painel-cajamar\class\MarketplaceView.php(28): MarketplaceDAO->carregaCorpoTabela() #1 C:\xampp\htdocs\painel-cajamar\indexMarketplace.php(41): MarketplaceView->carregarTabelaMarketplace('corpo') #2 {main} thrown in C:\xampp\htdocs\painel-cajamar\class\MarketplaceDAO.php

To second that:

Recoverable fatal error

Object of class PHPExcel_Style_NumberFormat could not be converted to string in C:\xampp\htdocs\painel-cajamar\class\MarketplaceDAO.php on line 160

Answer 1

Try the following code:

$objPHPExcel->getActiveSheet()
            ->getCellByColumnAndRow($i, $z)
            ->getValue()
            ->setFormatCode('dd-mmm-yyyy')

Or as follows

$objPHPExcel->getActiveSheet()
            ->getStyle($i,$z)
            ->getNumberFormat()
            ->setFormatCode('dd-mmm-yyyy');