Looping through Multiple XLSX Files

Topics: Developer Forum, User Forum
May 23, 2014 at 8:04 PM
Edited May 23, 2014 at 8:04 PM
Hi guys,

I am trying to write a little PHP program that will loop through all the Excel spreadsheets in a certain folder and open them and read them (and eventually import their data into a database).

I think I have everything set up correctly, PHPExcel 1.8 seems to be installed correctly, and the server is running PHP 5.3.

What's happening is that it opens and reads the the first *.xlsx file just fine and prints out some cell values, but then when it gets to the second spreadsheet it seems like the reader dies and the PHPExcel object just bombs out.

This seems to have something to do with the XLSX format. If all my spreadsheets are XLS format, it seems to work correctly...But there's got to be a way to get it to work with XLSX, right? XLSX support is one of PHPExcel's defining charms...

I couldn't find anything that caught my attention in the logs, so I was wondering if anyone had any tips to get this working?

Thanks!

Here's my current code:
<?php

/** Error reporting */
error_reporting(E_ALL);
ini_set('display.errors', 1);

/** Include PHPExcel */
require dirname(__FILE__) . '/Classes/PHPExcel.php';
require dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';  

$directory = dirname(dirname(__FILE__));    
$files = scandir($directory);

foreach($files as $file)
{
    //echo 'Checking file: "' . $file . '"...<br>';

    if(endsWith($file, ".xls") || endsWith($file, ".xlsx"))
    {
        try
        {
            processSpreadSheet($directory, $file);
        }
        catch(Exception $e)
        {
            echo $e;
            die($e);
        }
    }
}   

function endsWith($haystack, $needle)
{
    return $needle === "" || substr($haystack, -strlen($needle)) === $needle;
}

function processSpreadSheet($directory, $file)
{
    echo 'Now importing "' . $file . '"...<br>';            
    $fullFilePath = $directory . '/' . $file;   
    echo $fullFilePath . "<br>";
    y
    $inputFileType = PHPExcel_IOFactory::identify($fullFilePath);
    echo 'File Type :  ' . $inputFileType . '<br>';

    //Seems to blow up here on the second pass through
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objReader->setReadDataOnly(true);
    $objPHPExcel = $objReader->load($fullFilePath);
    $objPHPExcel->setActiveSheetIndex(0);

    $worksheet = $objPHPExcel->getActiveSheet();

    $highestRow         = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $nrColumns = ord($highestColumn) - 64;

    echo "The worksheet has ";
    echo $nrColumns . ' columns, A-' . $highestColumn . ', ';
    echo ' and ' . $highestRow . ' rows.<br>';

    echo $worksheet->getCell('A1')->getValue() . '<br>';
    echo $worksheet->getCell('A2')->getValue() . '<br>';

    PHPExcel_Calculation::getInstance()->clearCalculationCache();
    $objPHPExcel->disconnectWorksheets();
    unset($objPHPExcel);
}
?>