PhpSpreadsheet - Get raw string value for d/m/Y dates instead of being converted to floats

Question

I am trying to read an excel file that has 03/05/2008 kind of format, but when I read using PhpSpreadsheet, it returns me 2008.0.

Is there a way to get the raw string format of columns instead of converting to float?

try {
    $inputFileType = IOFactory::identify($path);

    try {
        $reader = IOFactory::createReader($inputFileType);
        $reader->setReadDataOnly(true);
        $valuesSpreadsheet = $reader->load($path);

        try {
           $spreadsheetArr = $valuesSpreadsheet->getActiveSheet()->toArray();
           dd($spreadsheetArr);
        }
     }
}

Edit: I don't want to get a specific cell and convert it to timestamp like the comments below. I want to get as array ->toArray() but getting all raw string formats.

https://stackoverflow.com/questions/44304795/how-to-retrieve-date-from-table-cell-using-phpspreadsheet — Tim Williams, Mar 19 '19 at 22:56
Possible duplicate of [How to retrieve date from table cell using PhpSpreadsheet?](https://stackoverflow.com/questions/44304795/how-to-retrieve-date-from-table-cell-using-phpspreadsheet) — miken32, Mar 20 '19 at 01:01
Well, I dont want 1 cell only, I want to get all data as array. — senty, Mar 20 '19 at 09:19
@Learner Seems like something went wrong with your bounty message, so I removed the bounty. You can try again with a proper, more readable message if you want. — Baum mit Augen, Jun 13 '19 at 21:20

score 2 · Answer 1 · answered Mar 22 '19 at 03:29

Take out the $reader->setReadDataOnly(true) line prior to loading the data and the values should be displayed properly. If not you can also try the following code.

$path = 'yourPath';

try {
    $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($path);

    try {
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);       
        $valuesSpreadsheet = $reader->load($path);

        try {
           $spreadsheetArr = $valuesSpreadsheet->getActiveSheet()->toArray(null, null, true, true);
           print '<pre>' . print_r($spreadsheetArr, 1) . '</pre>';
        } catch (Exception $e) {
            echo $e . PHP_EOL;
        }
     } catch (Exception $e) {
        echo 'Unable to load file ' . $path . PHP_EOL;
        echo $e . PHP_EOL;
     }
} catch (Exception $e) {
    echo 'Unable to locate file ' . $path . PHP_EOL;
    echo $e . PHP_EOL;
}

score 2 · Answer 2 · answered Jun 13 '19 at 21:08

toArray() has a parameter to return the cell values formatted as they are in the spreadsheet. Try calling it like this:

$spreadsheetArr = $valuesSpreadsheet->getActiveSheet()->toArray(null, true, true, true);

About 80% of the way down this page is documentation for the toArray() function.

In short, toArray() can accept 4 parameters:

whatever value you want empty cells to return
(boolean) do formulas need to be calculated?
(boolean) does cell formatting matter?
(boolean) do you want the array indexed by the spreadsheet's column and row?

Zafahix · Answer 3 · 2019-06-13T12:40:45.983

You should use getRowIterator() and getCellIterator() functions to loop through all cells. In the code below, all cells will be returned as raw values.

try {
    $inputFileType = IOFactory::identify($path);
    try {
        $reader = IOFactory::createReader($inputFileType);
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($path);
        $worksheet = $spreadsheet->getActiveSheet();\
        foreach ($worksheet->getRowIterator() as $index => $row) {
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(FALSE); //This loops through all cells
            $cells = [];
            foreach ($cellIterator as $cell) {
                $cells[] = $cell->getValue();
            }
            $rows[] = $cells;
            print_r($rows);
        }
    }
}

PhpSpreadsheet - Get raw string value for d/m/Y dates instead of being converted to floats

3 Answers3