CODEWITHSUNDEEP
phpphpexcel
Adrianb
Adrianb

Reputation: 35

PHPExcel converting from XLS to CSV destroys data

I want to convert a XLS file to CSV, everything is fine except for 1 column.

Duration (min., sec.)
2:24
4:20
4:20
4:20
3:54

Is outputted as:

Duration(min., sec.)
0.1
0.180555556
0.180555556
0.180555556
0.1625

Here is the function which does the conversion:

function convertXLStoCSV($infile, $outfile) // the function that converts the file
{
    $fileType = PHPExcel_IOFactory::identify($infile);
    $objReader = PHPExcel_IOFactory::createReader($fileType);
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($infile);

    $writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');
    $writer->setDelimiter(",");
    //$writer->setEnclosure("");
    //$writer->save("test123.csv");
    foreach ($objPHPExcel->getWorksheetIterator() as $workSheetIndex => $worksheet) {
        $objPHPExcel->setActiveSheetIndex($workSheetIndex);
        $writer->setSheetIndex($workSheetIndex);
        $writer->save('converted/' . $outfile ."_" . $worksheet->getTitle() . ".csv");
}

What am I doing wrong and how can I get the desired output ? Thank you in advance.

Upvotes: 1

Views: 452

Answers (2)

Mark Baker
Mark Baker

Reputation: 212412

Because you're setting

$objReader->setReadDataOnly(true);

you're teling PHPExcel not to read any of the formatting information from the xls file

That includes the number format mask that is the only thing that differentiates a floating point number from a date/time value in MS Excel files.

If you load the formatting information as well, then PHPExcel can recognise that a cell value is supposed to be a date, and format it accordingly, otherwise it can only display it as a floating point number.

So don't tell PHPExcel to load only data, let it load the format information as well

Upvotes: 2

Rob Anthony
Rob Anthony

Reputation: 1813

The output is not 'wrong' it is just storing the number in a different format.

What it is doing is multiplying to minutes by 60 and adding on the number of seconds then dividing by 1440 (24 hours * 60 minutes). So it is a fraction of a day, rather than the number of seconds, which is often the format.

To convert the decimal back into the time you want, multiply by 1440 then, do an integer division by 60 to get the number of minutes and mod 60 to get the number of seconds.

Upvotes: 2

Related Questions