Keep information in date format with PHP?

3

I have the following information in a file .xls 12/12/2017 10:34:03 but when I show in the browser with PHP it appears like 43081.4403125 . do you have any way to keep in date format ?

I'm using the PHPExcel library to get the info I'm following 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>";
        }
    }
}

The 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

A second this:

  

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

    
asked by anonymous 15.12.2017 / 20:05

1 answer

2

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');
    
15.12.2017 / 20:17