phpexcel reads long numbers as boolean

Question

I'm using Symfony2.3.4, PHP5.6.3 and PHPExcel 1.8.0.

When I tried to read an excel file it works OK for almost all cells.

If the cell contains a very large number, when I read it and show the value in an html view it outputs false.

I tried to use a custom value binder like Mark Baker instructed here but I couldn't make it work, it just comes as a boolean right from the beginning.

IMPORTANT:

The excels I'm trying to load in the html are downloaded(generated) from another site and I noticed when you try to open them with Microsoft Excel, it first prompts you with a warning window telling the user that the FILE EXTENSION AND THE FILE FORMAT DO NOT MATCH, although if you choose to open it anyway, it opens fine.

I think that's what's causing the problem, I'm almost sure(I can't contact the guys who implemented the other site's download function) they did something like this:

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $ext == 'xlsx' ?
 'Excel5' : 'Excel2007');

when they should have done something like this:

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $ext == 'xls' ?
 'Excel5' : 'Excel2007');

making the EXTENSION and the FORMAT match, as instructed in the PHPExcel's docs.

If you need any specific clarification please ask.

My code to load the file into the html:

public function uploadAction() {
    $request = $this->getRequest();
    $form = $this->createFormBuilder()
        ->add('file', 'file')
        ->getForm();
if ($request->getMethod() == 'POST'){

    $form->submit($request);

    $file = $form['file'];
    $file->getData()->move(
                'uploads', $form['file']->getData()->getClientOriginalName());

    $ext = pathinfo($file->getData()->getClientOriginalName(), PATHINFO_EXTENSION);
    $name = pathinfo($file->getData()->getClientOriginalName(), PATHINFO_BASENAME);

    //$objReader = \PHPExcel_IOFactory::createReader('xlsx' == $ext ? 'Excel2007' : 'Excel5');
    $objReader = \PHPExcel_IOFactory::createReaderForFile('uploads/' . $name);
    $objReader->setReadDataOnly(true);

    $objPHPExcel = $objReader->load('uploads/' . $name);
    $activeSheet = $objPHPExcel->getActiveSheet();

    $rowIter = $activeSheet->getRowIterator();
    foreach ($rowIter as $key => $row) {
        $columns = array();
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false);
        foreach ($cellIterator as $cell)
            $columns[] = $cell->getCalculatedValue();
    }
}
}

NOTE: I really don't know the difference between:

$objReader = \PHPExcel_IOFactory::createReader('xlsx' == $ext ? 'Excel2007' : 'Excel5');

and

$objReader = \PHPExcel_IOFactory::createReaderForFile('uploads/' . $name);

I DO know I can't use the first because of the problem I described above about the files being ill-generated and so. If I try to use it, the browser goes:

The filename uploads/<name>.xls is not recognised as an OLE file.

Can anyone point me to a workaround, because it's now me on the hook and I'm supposed to make it work somehow. Maybe there's nothing wrong with the files and it's me doing something wrong. Please help, this is causing me problems with dates too but one step at a time.

EDIT:

This is but the read function in OLERead.php.

I was browsing it and var_dump-ing all I could get my hands on.

As you can see there are two var_dumps in the code below, those output:

string '<div>    
    ' (length=8)

string '��ࡱ�' (length=8)

Which doesn't happen when I try it with a regular .xls file created manually:

string '��ࡱ�' (length=8)

string '��ࡱ�' (length=8)

I guessed you could use this better than me if it helps at all. Thanks again.

public function read($sFileName) {
        // Check if file exists and is readable
        if (!is_readable($sFileName)) {
            throw new PHPExcel_Reader_Exception("Could not open " . $sFileName . " for reading! File does not exist, or it is not readable.");
        }

        // Get the file identifier
        // Don't bother reading the whole file until we know it's a valid OLE file


        $this->data = file_get_contents($sFileName, FALSE, NULL, 0, 8);

         ////VAR_DUMPSSSSSSSSSSSS
         var_dump($this->data);
         var_dump(self::IDENTIFIER_OLE);
         die();
        // Check OLE identifier
        if ($this->data != self::IDENTIFIER_OLE) {
            throw new PHPExcel_Reader_Exception('The filename ' . $sFileName . ' is not recognised as an OLE file');
        }

        // Get the file data
        $this->data = file_get_contents($sFileName);

        // Total number of sectors used for the SAT
        $this->numBigBlockDepotBlocks = self::_GetInt4d($this->data, self::NUM_BIG_BLOCK_DEPOT_BLOCKS_POS);

        // SecID of the first sector of the directory stream
        $this->rootStartBlock = self::_GetInt4d($this->data, self::ROOT_START_BLOCK_POS);

        // SecID of the first sector of the SSAT (or -2 if not extant)
        $this->sbdStartBlock = self::_GetInt4d($this->data, self::SMALL_BLOCK_DEPOT_BLOCK_POS);

        // SecID of the first sector of the MSAT (or -2 if no additional sectors are used)
        $this->extensionBlock = self::_GetInt4d($this->data, self::EXTENSION_BLOCK_POS);

        // Total number of sectors used by MSAT
        $this->numExtensionBlocks = self::_GetInt4d($this->data, self::NUM_EXTENSION_BLOCK_POS);

        $bigBlockDepotBlocks = array();
        $pos = self::BIG_BLOCK_DEPOT_BLOCKS_POS;

        $bbdBlocks = $this->numBigBlockDepotBlocks;

        if ($this->numExtensionBlocks != 0) {
            $bbdBlocks = (self::BIG_BLOCK_SIZE - self::BIG_BLOCK_DEPOT_BLOCKS_POS) / 4;
        }

        for ($i = 0; $i < $bbdBlocks; ++$i) {
            $bigBlockDepotBlocks[$i] = self::_GetInt4d($this->data, $pos);
            $pos += 4;
        }

        for ($j = 0; $j < $this->numExtensionBlocks; ++$j) {
            $pos = ($this->extensionBlock + 1) * self::BIG_BLOCK_SIZE;
            $blocksToRead = min($this->numBigBlockDepotBlocks - $bbdBlocks, self::BIG_BLOCK_SIZE / 4 - 1);

            for ($i = $bbdBlocks; $i < $bbdBlocks + $blocksToRead; ++$i) {
                $bigBlockDepotBlocks[$i] = self::_GetInt4d($this->data, $pos);
                $pos += 4;
            }

            $bbdBlocks += $blocksToRead;
            if ($bbdBlocks < $this->numBigBlockDepotBlocks) {
                $this->extensionBlock = self::_GetInt4d($this->data, $pos);
            }
        }

        $pos = 0;
        $this->bigBlockChain = '';
        $bbs = self::BIG_BLOCK_SIZE / 4;
        for ($i = 0; $i < $this->numBigBlockDepotBlocks; ++$i) {
            $pos = ($bigBlockDepotBlocks[$i] + 1) * self::BIG_BLOCK_SIZE;

            $this->bigBlockChain .= substr($this->data, $pos, 4 * $bbs);
            $pos += 4 * $bbs;
        }

        $pos = 0;
        $sbdBlock = $this->sbdStartBlock;
        $this->smallBlockChain = '';
        while ($sbdBlock != -2) {
            $pos = ($sbdBlock + 1) * self::BIG_BLOCK_SIZE;

            $this->smallBlockChain .= substr($this->data, $pos, 4 * $bbs);
            $pos += 4 * $bbs;

            $sbdBlock = self::_GetInt4d($this->bigBlockChain, $sbdBlock * 4);
        }

        // read the directory stream
        $block = $this->rootStartBlock;
        $this->entry = $this->_readData($block);

        $this->_readPropertySets();
    }

Answer 1

The difference between

$objReader = \PHPExcel_IOFactory::createReader('xlsx' == $ext ? 'Excel2007' : 'Excel5');

and

$objReader = \PHPExcel_IOFactory::createReaderForFile('uploads/' . $name);

The first is trusting that the extension is correct for the actual format of the file, that a file with an extension of .xlsx really is an OfficeOpenXML-format file or an extension of .xls really is a BIFF-format file, and then telling PHPExcel to use the appropriate reader.

This isn't normally a problem unless it isn't (for example) just HTML markup in a file with an .xls or .xlsx extension.... then you're selecting the wrong Reader for the actual format of the file; and this is what MS Excel itself is telling you with its message that "FILE EXTENSION AND THE FILE FORMAT DO NOT MATCH"

The second is using PHPExcel's identify() method to work out what format the file really is (irrespective of what it claims to be based on a false extension), and then selecting the appropriate Reader for that format.

EDIT

Unsure exactly how large your large numbers are, but I'll take a look at the HTML Reader and see if I can identify why it should be giving a boolean false instead of an actual numeric value