PHPExcel Memory Issues when just reading (not yet writing) an xls document.

Topics: Developer Forum, Project Management Forum, User Forum
May 29, 2014 at 3:15 PM
Edited Jun 7, 2014 at 2:39 AM
Hi there,

I am the PHP/Javascript/AJAX developer for http://performatix.co
I have recently started working with PHPExcel as it is a requirement to import attribute "ancestrees", visually depicted in an xls file as the one uploaded to https://www.codeplex.com/Download/AttachmentDownload.ashx?ProjectName=phpexcel&WorkItemId=10749&FileAttachmentId=8503 (05.New_Bus.Phone System_Performatix_Ancestree_20May14.xls)

how is it recommended to write PHP code to process the following file?
XLS document, for Business Phone Systems Service - containing the sheets 2 tier and 1 tier which we specifically focus on, in our Performatix attributes manager's ancestree import feature.
the PHP code i have sofar for doing this is:
        getrecords(); // retrieve attribute records from performatix database
        //$tabledata = explode(";,;",$_POST["list_tables"]);
        //$table = $tabledata[0];
        //$file = $table."_".date("YmdHis",time()).".xls";
        set_time_limit(7200);
        $updateorreplace = $_POST["radio_updateorreplace"];
        $uploaddir = '../temp/';
        $uploadfile = $uploaddir . basename($_FILES['file_importfile']['name']);
        $firstname = $uploadfile;
        $i=0;
        if (file_exists($firstname)) {
            $uploadfile = str_replace(".xls"," (".$i.").xls",$firstname);
            while (file_exists($uploadfile)) {
                $i++;
                $uploadfile = str_replace(".xls"," (".$i.").xls",$firstname);
            }
        }
        if (move_uploaded_file($_FILES['file_importfile']['tmp_name'], $uploadfile)) {
            // auto detect ancestree dimentions
            echo "  <result>Successfully uploaded \"".$uploadfile."\".</result>\n";
            ob_flush();
            if ($updateorreplace=="0") {
                // Update Existing Record Ancestree's
                /**  Identify the type of $uploadfile  **/
                $inputFileType = PHPExcel_IOFactory::identify($uploadfile);
                /**  Create a new Reader of the type that has been identified  **/
                $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                try {
                    /** Load $uploadfile to a PHPExcel Object  **/
                    $objPHPExcel = PHPExcel_IOFactory::load($uploadfile);
                    $worksheetData = $objReader->listWorksheetInfo($uploadfile);
                } catch(PHPExcel_Reader_Exception $e) {
                    die('   <result>Error loading file: '.$e->getMessage()).'</result>\n';
                }
                echo "  <workbook file=\"".$uploadfile."\" format=\"".$inputFileType."\">/n";
                $wsi = 0;
                $xCol = "A";
                $xRow = "1";
                $yCol = "A";
                $yRow = "1";
                $keys = array();
                $_SESSION["keysvalues"] = breakdown($_REQUEST["menu_services"]);
                foreach ($worksheetData as $worksheet) {
                    if (stristr($worksheet['worksheetName'],"tier") !== false) {
                        $worksheetname = $worksheet['worksheetName'];
                        $alpha = "BCDEFGHIJKLMNOPQRSTUVWXYZ";
                        $num = "123456789";
                        $objWorksheet = $objPHPExcel->setActiveSheetIndex($wsi);
                        $i = 0;
                        $char = substr($alpha,$i,$i+1);
                        for ($n = 1; $n < strlen($num); $i++) {
                            $digit = substr($num,$n,$n+1);
                            $value = $objWorksheet->getCellByColumnAndRow($char, $digit)->getValue();
                            if ($value !== "" && strlen($value) < 24) {
                                $xCol = $char;
                                $xRow = $digit;
                                array_push($keys, $value);
                                while (stristr($value, ";")==false && stristr($value, ",")==false && !is_numeric($value) || array_key_exists($value,$_SESSION["keysvalues"]) || $value !== "") {
                                    $char = substr($alpha,$i++,$i+1);
                                    $yCol = $char;
                                    $value = $objWorksheet->getCellByColumnAndRow($char, $digit)->getValue();
                                    array_push($keys, $value);
                                }
                                goto label0;
                            }
                        }
                        label0:
                        for ($a = 0; $a < strlen($alpha); $a++) {
                            $char = substr($alpha,$i,$i+1);
                            $value = $objWorksheet->getCellByColumnAndRow($char, $worksheet['totalRows']-1)->getValue();
                            if ($value !== "" && (stristr($value,";")==false && stristr($value,",")==false)) {
                                $yRow = $worksheet['totalRows'];
                                goto label1;
                            }
                        }
                        $objPHPExcel->disconnectWorksheets();
                        unset($objPHPExcel);
                        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                        $worksheetData = $objReader->listWorksheetInfo($uploadfile);
                        label1:
                        echo "      <spreadsheet tab=\"".xmlentities($worksheetname)."\" dimensions=\"".$xCol.";".$xRow.";".$yCol.";".$yRow."\" keys=\"".$keys."\" />\n";
                    }
                    $wsi++;
                }
                echo "  </workbook>\n";
                echo "  <result>File is valid, and was successfully uploaded as ".$uploadfile.".</result>\n";
            } elseif ($updateorreplace=="1") {
                // Replace Existing Attribute Record's
                /**  Identify the type of $uploadfile  **/
                $inputFileType = PHPExcel_IOFactory::identify($uploadfile);
                /**  Create a new Reader of the type that has been identified  **/
                $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                $worksheetData = $objReader->listWorksheetInfo($uploadfile);
                try {
                    /** Load $uploadfile to a PHPExcel Object  **/
                    $objPHPExcel = PHPExcel_IOFactory::load($uploadfile);
                } catch(PHPExcel_Reader_Exception $e) {
                    die('   <result>Error loading file: '.$e->getMessage()).'</result>\n';
                }
                $worksheetData = $objReader->listWorksheetInfo($uploadfile);
                echo "  <workbook file=\"".$uploadfile."\" format=\"".$inputFileType."\">/n";
                echo "  </workbook>\n";
            }
        } else {
            echo "  <result>File upload failed!</result>\n";
        }
        //echo '    <result>Debugging info:\n';
        //foreach ($_FILES as $file) {
        //  echo implode("  ",$file)."\n";
        //}
        //echo '</result>';
the code was modified however, as follows, with some help from someone who has worked with PHPExcel before...
/* Error reporting */
error_reporting(E_ALL);
/* Set PHP Memory Limit */
ini_set('memory_limit', '8192M');
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
date_default_timezone_set('Africa/Johannesburg');
the code previously, (especially within the "if ($updateorreplace) {" statement) attempts to get the ancestree dimensions within the worksheets - some errors were noted in the coding practice, and rectified.
however - the RAM STILL runs out very quickly and we JUST upgraded our Ubuntu server to 1GB ram. is there perhaps a better method to get these data dimensions calculated? Does PHPExcel require a longer timeout for calculating the data dimensions? Or does it just require even MORE memory to be made available, within the code (for reading a 60KB sized XLS file!)??
the next step after getting the dimensions, would be to get the attribute id's from mysql (the column Ancestree's headered in RED) according to the xls document, and building a two tier array to update the ancestrees with for the last column's attribute values. (on right top two rows, horizontally also titled in RED - these are to be set into the database then as the last step for each of the attributes to be displayed via an ajax script in joomla).
all columns titled as attribute values in RED are to be populated via PHPExcel and MySQL eventually (the following steps, to finally get the attribute id's into arrays and populate on the attributes via MySQL)...
sincerely,
Pierre "Greywacke" du Toit.
May 30, 2014 at 1:49 PM
Edited Jun 7, 2014 at 2:40 AM
ok the real issue here seems to be that the script keeps timing out! why does PHPExcel need to take so long to return values? 1GB seems to be enough ram for the server to be able to load and read a 60KB spreadsheet... 0o
  • Pierre "Greywacke" du Toit.
Coordinator
May 31, 2014 at 11:58 AM
So is it actually running out of memory, or is it timing out? I'm not completely certain what your problem is.... and how many database queries are actually being executed?
Jun 1, 2014 at 3:22 AM
Edited Jun 5, 2014 at 6:51 AM
include("../includes/content/performatix.php");
include("../public/PHPExcel_1.8.0/Classes/PHPExcel.php");   // import PHPExcel 1.8.0 classes
error_reporting(E_ALL);
ini_set('memory_limit', '8192M');
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
date_default_timezone_set('Africa/Johannesburg');
$q = intval($_GET["q"]);
if (is_numeric($q)) {
    if ($q > 8 || $q < 0) {
        $q = 0;
    }
} else {
    $q = 0;
}
$sid = $_GET["sid"];
if (is_numeric($sid)) {
    if ($sid > 17 || $sid < 1) {
        $sid = 1;
    }
} else {
    $sid = 1;
}
function getrecords() {
    global $conn, $sid;
    $sql = "SELECT * FROM 2_servicescatalogue ORDER BY text_ServiceDescription ASC;";
    $result = mysql_query_errors($sql , $conn , __FILE__ , __LINE__);;
    if ($result) {
        while ($row = mysql_fetch_array($result)) {
            echo "  <service sid=\"" . $row["bigint_ServiceID"] . "\" desc=\"" . xmlentities($row["text_ServiceDescription"]) . "\" selected=\"".(($row["bigint_ServiceID"]==$sid)?"selected\"":"\"")." >\n";
            $sql1 = "SELECT * FROM 3_serviceattributes WHERE bigint_AttributeServiceID = ".$row["bigint_ServiceID"]." ORDER BY text_AttributeDescription ASC, smallint_ValueOrder ASC;";
            $result1 = mysql_query_errors($sql1 , $conn , __FILE__ , __LINE__);;
            if ($result1) {
                while ($row1 = mysql_fetch_array($result1)) {
                    echo "      <attribute id=\"".$row1["bigint_AttributeID"]."\" desc=\"".xmlentities($row1["text_AttributeDescription"])."\" val=\"".xmlentities($row1["text_AttributeValue"])."\" ord=\"".$row1["smallint_ValueOrder"]."\" ptr=\"".$row1["bigint_AttributeParentID"]."\" />\n";
                }
            }
            echo "  </service>\n";
        }
        mysql_free_result($result);
    }
}
function getarraykeys($sid, $cnt, $att) {
    global $conn;
    $att = explode(",",$att);
    foreach ($att as $key->$value) {
        if (stristr($value,";")!==FALSE) {
            $i = 0;
            $value = explode(";",$value);
            foreach ($value as $val) {
                if ($i == 0) {
                    $att[$key] = "(SA1.bigint_AttributeID = ".$val;
                } else {
                    $att[$key] .= " OR SA1.bigint_AttributeID = ".$val;
                }
                $i++;
            }
            $att[$key] .= ")";
        } else {
            $att[$key] = "SA1.bigint_AttributeID = ".$value;
        }
    }
    $tsql = "SELECT 
                 DISTINCT(SA0.text_AttributeDescription), 
                 SA0.bigint_AttributeID, 
                 (SA1.text_AttributeDescription LIKE SA0.text_AttributeDescription) AS selected 
             FROM 
                 3_serviceattributes SA0 
             JOIN 3_serviceattributes SA1 ON (".implode(" OR ",$att).") 
             WHERE 
                 SA0.bigint_AttributeServiceID = ".$sid." AND 
                 SA0.text_AttributeDescription != UPPER(SA0.text_AttributeDescription) 
             GROUP BY SA0.text_AttributeDescription 
             ORDER BY SA0.text_AttributeDescription ASC, SA0.bigint_AttributeID ASC";
    $result = mysql_query_errors($tsql , $conn , __FILE__ , __LINE__);
    if ($result) {
        $i = 0;
        echo "  <attributekeys cnt=\"".$cnt."\" ids=\"";
        while ($row = mysql_fetch_array($result)) {
            echo $row["bigint_AttributeID"];
            if ($i < mysql_affected_rows($conn)-1) {
                echo ";";
                $i++;
            } else {
                echo "\"";
                $i++;
            }
        }
        $i = 0;
        mysql_data_seek($result, 0);
        echo " keys=\"";
        while ($row = mysql_fetch_array($result)) {
            echo $row["text_AttributeDescription"];
            if ($i < mysql_affected_rows($conn)-1) {
                echo ";";
                $i++;
            } else {
                echo "\"";
                $i++;
            }
        }
        echo " />\r\n";
        mysql_free_result($result);
    }
}
function getarrayvals($sid = 0, $aid = 0, $cnt = 0) {
    global $conn;
    $aid = explode(";", $aid, $cnt);
    $tsql = "SELECT 
                 SA0.text_AttributeDescription, 
                 SA0.text_AttributeValue, 
                 SA0.bigint_AttributeID 
             FROM 
                 3_serviceattributes SA0 
             WHERE (";
             for ($i = 0; $i < count($aid); $i++) {
                 $tsql .= " 
                 ".(($i==0)?"":"OR ")."SA0.bigint_AttributeID = ".$aid[$i];
            }
    $tsql .= " 
             ) AND SA0.bigint_AttributeServiceID = ".$sid." 
             GROUP BY SA0.text_AttributeDescription, SA0.text_AttributeValue 
             ORDER BY SA0.text_AttributeDescription ASC, SA0.text_AttributeValue ASC;";
    $result = mysql_query_errors($tsql , $conn , __FILE__ , __LINE__);
    if ($cnt == 0) $cnt = mysql_affected_rows($result);
    if ($result) {
        $i = 0;
        echo "  <attributevals cnt=\"".$cnt."\" ids=\"";
        while ($row = mysql_fetch_array($result)) {
            echo $row["bigint_AttributeID"];
            if ($i < mysql_affected_rows($conn)-1) {
                echo ";";
                $i++;
            } else {
                echo "\"";
                $i++;
            }
        }
        $i = 0;
        mysql_data_seek($result, 0);
        echo " keys=\"";
        while ($row = mysql_fetch_array($result)) {
            echo $row["text_AttributeDescription"];
            if ($i < mysql_affected_rows($conn)-1) {
                echo ";";
                $i++;
            } else {
                echo "\"";
                $i++;
            }
        }
        $i = 0;
        mysql_data_seek($result, 0);
        echo " vals=\"";
        while ($row = mysql_fetch_array($result)) {
            echo $row["text_AttributeValue"];
            if ($i < mysql_affected_rows($conn)-1) {
                echo ";";
                $i++;
            } else {
                echo "\"";
                $i++;
            }
        }
        echo " />\r\n";
        mysql_free_result($result);
    }
}
function importancestrees($uploadfile="") {
    
}
function replaceattributes($uploadfile="") {
    
}
function breakdown($string = "") {
    $array1 = explode("|;|",$string);
    $array = array();
    for ($i = 2; $i < count($array1); $i++) {
        $array2 = explode(";;;",$array1[$i]);
        $array[$array2[1]] = array();
        $array[$array2[1]][0] = $array2[0];
        $array[$array2[1]][1] = $array2[2];
    }
    return $array;
}
Jun 1, 2014 at 3:35 AM
Edited Jun 5, 2014 at 4:37 PM
hi Mark Baker! :D ok - meditated a bit, and i've realised that it's unfortunately TERRIBLY easy to fall into the MEMORY traps with PHPExcel! :o i've had a relook at my code whilst commenting it - and it is pasted below before i continue to test. just hope i avoided all the memory traps available on a [quad core Rackspace server with 40GB HDD and 1GB RAM] (http://31.222.157.140/mySpecs.html) ;) here's the updated code now. ;)
    case 6: // upload xls document for reading ancestree(s) from
        //getrecords(); // retrieve service's attribute records from performatix database
        //$tabledata = explode(";,;",$_POST["list_tables"]);
        //$table = $tabledata[0];
        //$file = $table."_".date("YmdHis",time()).".xls";
        set_time_limit(7200);                                                           // increase time limit for php switch ($q) execusion
        $updateorreplace = $_POST["radio_updateorreplace"];                             // decide which code procedure to follow
        $uploaddir = '../temp/';                                                        // set relative directory for uploads
        $uploadfile = $uploaddir . basename($_FILES['file_importfile']['name']);        // get the relative file path of the upload
        $firstname = $uploadfile;                                                       // set the initial name of the upload
        $i=0;                                                                           // initiate the counter for similar filenames
        if (file_exists($firstname)) {                                                  // if file already exists
            $uploadfile = str_replace(".xls"," (".$i.").xls",$firstname);               // append counter to end of filename within brackets
            while (file_exists($uploadfile)) {                                          // loop while file exists
                $i++;                                                                   // iterate counter
                $uploadfile = str_replace(".xls"," (".$i.").xls",$firstname);           // change uploadfile filename
            }                                                                           // end while file exists loop
        }                                                                               // end if file exists
        if (move_uploaded_file($_FILES['file_importfile']['tmp_name'], $uploadfile)) {  // if uploaded file successfully moved to
            echo "  <result>Successfully uploaded \"".$uploadfile."\".</result>\n";     // write upload status
            ob_flush();                                                                 // ensure send of upload and move xml status
            if ($updateorreplace=="0") {                                                // if Update Existing Record Ancestree's
                $inputFileType = PHPExcel_IOFactory::identify($uploadfile);             // Identify the type of $uploadfile
                $objReader = PHPExcel_IOFactory::createReader($inputFileType);          // Create a new Reader of the type that has been identified
                $objReader->setReadDataOnly(true);                                      // set this if you don't need to write (yet)
                try {                                                                   // try clause
                    $objPHPExcel = PHPExcel_IOFactory::load($uploadfile);               // Load $uploadfile to a PHPExcel Object
                    $worksheetData = $objReader->listWorksheetInfo($uploadfile);        // list the worksheetinfo as $worksheetData
                } catch(PHPExcel_Reader_Exception $e) {                                 // catch exceptions
                    die('   <result>Error loading file: '.$e->getMessage()).'</result>\n'; // report error
                }                                                                       // end try / catch clause
                echo "  <workbook file=\"".$uploadfile."\" format=\"".$inputFileType."\">/n";   // export relative workbook location/filename and format
                $wsi = 0;                                                               // instantiate worksheet counter
                $xCol = "A";                                                            // instantiate left column
                $xRow = "1";                                                            // intantiate top row
                $yCol = "A";                                                            // instantaite right column
                $yRow = "1";                                                            // instantiate bottom row
                $keys = array();                                                        // instantiate the keys array
                $_SESSION["keysvalues"] = breakdown($_REQUEST["menu_services"]);        // split service attribute key/value pairs as array data
                foreach ($worksheetData as $worksheet) {                                // step through worksheets
                    if (stristr($worksheet['worksheetName'],"tier") !== false) {        // if worksheet contains biometric data
                        $worksheetname = $worksheet['worksheetName'];                   // get the worksheetname
                        $alpha = "BCDEFGHIJKLMNOPQRSTUVWXYZ";                           // instantiate the alphabetical characters
                        $num = "123456789";                                             // instantiate the numerical characters
                        $objWorksheet = $objPHPExcel->setActiveSheetIndex($wsi);        // load the worksheet into memory for viewing
                        $i = 0;                                                         // instantiate the horizontal index iterator
                        $char = substr($alpha,$i,$i+1);                                 // instantiate the first horizontal character to find the top left dimention
                        for ($n = 1; $n < strlen($num); $i++) {                         // loop through the columns vertically to find the starting and ending numbers
                            $digit = substr($num,$n,$n+1);                              // instantiate the first vertical digit
                            $value = $objWorksheet->getCellByColumnAndRow($char, $digit)->getValue();   // get the value of the cell
                            if ($value !== "" && strlen($value) < 24) {                 // if the value is not empty and the length is less than 24 (most form fields submitted are) also check if blue, has a border and is bold?
                                $xCol = $char;                                          // set the left character
                                array_push($keys, $value);                              // add to the collected keys array
                                while (stristr($value, ";")==false &&                   // loop horizontally and check that there are no ; in column values
                                       stristr($value, ",")==false &&                   // check that there are no comma's in values
                                       !is_numeric($value) ||                           // check that there are no single numeric values on values
                                       array_key_exists($value,$_SESSION["keysvalues"]) // check wether the current value exists in the $keysvalues array
                                       || $value !== "") {                              // check wether the values are not empty
                                    $char = substr($alpha,$i++,$i+1);                   // get next horizontal character
                                    $xRow = $digit;                                     // set the top digit
                                    $yCol = $char;                                      // get latest horizontal character
                                    $value = $objWorksheet->getCellByColumnAndRow($char,// get next value
                                                                   $digit)->getValue();
                                    array_push($keys, $value);                          // save next "header" value to array
                                }                                                       // end horizontal loop
                                goto label0;                                            // jump to vertical loop exit point (label0)
                            }                                                           // end if
                        }                                                               // end vertical loop
                        label0:                                                         // label0 (exit point)
                        $yRow = $worksheet['totalRows'];                                // set the bottom row
                        $objPHPExcel->disconnectWorksheets();                           // disconnect worksheets
                        unset($objPHPExcel);                                            // unset objPHPExcel
                        $objReader = PHPExcel_IOFactory::createReader($inputFileType);  // reset objReader
                        $worksheetData = $objReader->listWorksheetInfo($uploadfile);    // reload worksheets
                        echo "      <spreadsheet tab=\"".xmlentities($worksheetname).   // write spreadsheet dimentions to xml
                                    "\" dimensions=\"".$xCol.                           // top constraint
                                    ";".$xRow.                                          // left constraint
                                    ";".$yCol.                                          // bottom constraint
                                    ";".$yRow.                                          // right constraint
                                    "\" keys=\"".$keys."\" />\n";                       // keys collected to build ancestree
                    }                                                                   // end if worksheet contains biometric data 
                    $wsi++;                                                             // increment worksheet index
                }                                                                       // end worksheet loop
                echo "  </workbook>\n";                                                 // end workbook data
                echo "  <result>File is valid, and was successfully uploaded as ".      // export upload result status
                     $uploadfile.".</result>\n";                                        // export location/filename
            } elseif ($updateorreplace=="0") {                                          // else if Replace Existing Attribute Record's
                $inputFileType = PHPExcel_IOFactory::identify($uploadfile);             // (all work below is still to be resumed unrelated to current forum post)
                $objReader = PHPExcel_IOFactory::createReader($inputFileType);          // Create a new Reader of the type that has been identified
                $worksheetData = $objReader->listWorksheetInfo($uploadfile);            // list the worksheetinfo as $worksheetData
                try {                                                                   // try clause
                    $objPHPExcel = PHPExcel_IOFactory::load($uploadfile);               // Load $uploadfile to a PHPExcel Object
                    $worksheetData = $objReader->listWorksheetInfo($uploadfile);        // list the worksheetinfo as $worksheetData
                } catch(PHPExcel_Reader_Exception $e) {                                 // catch exceptions
                    die('   <result>Error loading file: '.$e->getMessage()).'</result>\n'; // report error
                }                                                                       // end try / catch clause
                echo "  <workbook file=\"".$uploadfile."\" format=\"".$inputFileType."\">/n";   // export relative workbook location/filename and format
                echo "  </workbook>\n";                                                 // end workbook data// end workbook data// end workbook data// end workbook data
            }                                                                           // end if $inputFileType clause
        } else {                                                                        // id uploaded file not successfully moved to
            echo "  <result>File upload failed!</result>\n";                            // export upload failure status
        }                                                                               // end if upload successful clause
        //echo '    <result>Debugging info:\n';
        //foreach ($_FILES as $file) {
        //  echo implode("  ",$file)."\n";
        //}
        //echo '</result>';
        break;
and here is the breakdown function, with the specific purpose of taking a string as argument and breaking it down into an array to match attribute keys and return id's for processing in the next step. ;)
(had to replace previous post due to max post length "impositions" - i also included the other functions which have mysql calls - but i am keeping them on different instances of the file https://blah/scripts/ajax_attributes.php?q=6 (sofar is the only one which uses PHPExcel and 0 MySQL queries.))