Import spreadsheet with text format using Phpexcel

Question

How to import an excel file as cells in TEXT format

code

$file_tmp = $_FILES['arquivo']['tmp_name'];

try {
    $FileType = PHPExcel_IOFactory::identify( $file_tmp ); 
    $objReader = PHPExcel_IOFactory::createReader( $FileType ); 
    $objReader->setReadDataOnly( true );
    $objPHPExcel = $objReader->load( $file_tmp );   
}catch( Exception $e ){
    die( $e->getMessage() );
}

$objWorksheet = $objPHPExcel->getActiveSheet();
$fimColuna = $objWorksheet->getHighestColumn();
$numero_de_linhas = $objWorksheet->getHighestRow();
$numero_de_colunas = PHPExcel_Cell::columnIndexFromString( $fimColuna );

for( $row = 0; $row <= $numero_de_linhas; $row++ ){
    $data = array();
    for( $col = 0; $col < $numero_de_colunas; $col++ ){     
        $data[] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
    }
}

As the dates are in format 11/12/2014 //MÊS/DIA/ANO get like this: 41984.69211805556

Thanks in advance for the help.

Answer 1

Utilize:

echo PHPExcel_Style_NumberFormat::toFormattedString($objWorksheet->getCellByColumnAndRow($col, $row)->getValue(), 'YYYY-MM-DD hh:mm:ss');

Answer 2

The dates only came this way 41984.69211805556 when it was 11/12/2014 // MÊS/DIA/ANO if it were 31/10/2014 // DIA/MÊS/ANO normal vineyard.

Then I check if the variable has the size of a normal date

function isDate( $date ){
     return strlen(preg_replace('/[^0-9]/', '', current(explode(' ', trim($date))))) == 8 ?  true : false;
}

And I add a Phpexcel function that treats the date of the spreadsheet

date('d/m/Y', PHPExcel_Shared_Date::ExcelToPHP( $data ) ); // 41984.69211805556 -> 11/12/2014

Answer 3

I had this same problem but with the answers I did some tests which solved my problem, follow below as an example if you have not solved.

$data=$objPHPExcel->getActiveSheet()->getCellByColumnAndRow($coluna, $linha)->getValue(); //como é retornado 41121
echo date('d/m/Y', PHPExcel_Shared_Date::ExcelToPHP( $data ) ); //como fica depois de formatado 30/07/2012

Answer 4

Try this:

$data = trim( $objWorksheet->getCellByColumnAndRow(9, $i)->getValue()); 
$data = date("Y-m-d", strtotime("01/01/1900 + $data_fatura days - 2 days"));

Answer 5

It’s because when you get the date by getValue(), it returns a float.

Knife:

$objWorksheet->getCellByColumnAndRow($col, $row)->getNumberFormat()->getFormatCode();

By the way, which version of Phpexcel is using?