Import worksheet in text format using PHPExcel

Navigation
3

How to import an excel file as the 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();
    }
}

The way the dates in the format 11/12/2014 //MÊS/DIA/ANO looks like this: 41984.69211805556

Thank you in advance for the help.

    
asked by anonymous 13.11.2014 / 16:25

5 answers

4

Use: 

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

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

Then I check if the variable is 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 planet date 

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

It is because when it receives the date by getValue() , it returns a float.

Knife: 

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

By the way which version of PHPExcel are you using?

    
13.11.2014 / 17:09
0

Try this: 

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

I was with this same problem but with the answers given I did some tests which solved my problem, follow below as an example if it has not yet 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
    
03.06.2015 / 19:02
ASP.NET vNext in Apache Minimize + SytemTray