apache crashes on simple file?

Feb 22, 2011 at 1:52 PM

Hello,

For my masters thesis I need to process a huge dataset of stock information. This data is being output in excel and I need to prepare this data for usage in a statistical tool. I want to use a php script to prepare my dataset but unfortunately apache crashes on my fairly simple script...

<?php

// thesis: verwerking dataset

/** Error reporting */
error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

/** PHPExcel */
require_once './Classes/PHPExcel.php';


echo '<form action="'.$_SERVER['PHP_SELF'].'" method="POST" enctype="multipart/form-data">
Excel document: <input type="file" name="dataset"><br /><input type="submit" value="Verwerk"></form><hr>';

if(!empty($_FILES['dataset']['tmp_name']))
{
	// read original dataset
	$objReader = new PHPExcel_Reader_Excel2007();
	$objReader->setReadDataOnly(true);
	$input = $objReader->load($_FILES['dataset']['tmp_name']);

	// Create new PHPExcel object
	$output = new PHPExcel();	
	$output->setActiveSheetIndex(0);
	$output->getProperties()->setCreator("Jerome Kleinen")
							 ->setLastModifiedBy("Jerome Kleinen")
							 ->setTitle("Thesis Commonality in Liquidity")
							 ->setSubject("dataset")
							 ->setDescription("Dataset for thesis")
							 ->setKeywords("thesis dataset liquidity")
							 ->setCategory("dataset");

	$highestRow = $input->getHighestRow();
	$highestColumn = $input->getHighestColumn();
	$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
	
	$newcolumn = 1;
	
	for($i = 1; $i <= $highestColumnIndex; $i++)
	{
		if(stripos($input->getActiveSheet()->getCellByColumnAndRow($i, 2)->getValue(), "(P)") === FALSE) // not a price column
		{
			for($j = 1; $j <= $highestRow; $j++)
			{
				$output->getActiveSheet()->setCellValueByColumnAndRow($newcolumn, $j, $input->getActiveSheet()->getCellByColumnAndRow($i, $j)->getValue());
			}
			$newcolumn++;
		}
		else
		{
			for($j = 1; $j <= $highestRow; $j++)
			{
				$output->getActiveSheet()->setCellValueByColumnAndRow($newcolumn, $j, $input->getActiveSheet()->getCellByColumnAndRow($i, $j)->getValue());
			}			
			
			$newcolumn++;
			
			$output->getActiveSheet()->setCellValueByColumnAndRow($newcolumn, 1, $input->getActiveSheet()->getCellByColumnAndRow($i, 1)->getValue()); // copy name of stock
			$output->getActiveSheet()->setCellValueByColumnAndRow($newcolumn, 2, "return");
			
			for($j = 3; $j < $highestRow; $j++)
			{
				
				$pt1 = $input->getActiveSheet()->getCellByColumnAndRow($i, $j)->getValue();
				$pt2 = $input->getActiveSheet()->getCellByColumnAndRow($i, $j + 1)->getValue();
				if(!empty($pt1) && is_numeric($pt1) && !empty($pt2) && is_numeric($pt2))
				{
					$return = ($pt2 - $pt1) / $pt1;
				}
				else
				{
					$return = "NA";
				}
				
				$output->getActiveSheet()->setCellValueByColumnAndRow($newcolumn, $j, $return);
			}

			$newcolumn++;
		}
	}

	// write to outfile file
	$objWriter = PHPExcel_IOFactory::createWriter($output, 'Excel2007');
	$objWriter->save('php://output');
}


?>
This is just a test file with 1 calculation of the daily return if the column is a price column. I have uploaded a piece of my dataset here: http://www.2shared.com/file/3sHq0DYC/BEL20.html so you guys can see the structure of the file. basically, for every stock there are 4 columns, one being the price of the stock (P). For each price column it should copy the price column and add the daily return right after it (I should put this in the same for loop, this is just a test file), for other columns it should just copy the original data. I am using apache as webserver in a winxp virtualbox install using xampp. I run win7 64bit on my system (if that matters). Does anyone see what's going wrong???

Coordinator
Feb 22, 2011 at 8:15 PM

Are any errors being generated, either by PHP (you should have PHP error logging enabled when developing) or in the web server logs?

You're also echoing html markup before outputting the Excel file directly to the browser (which will corrupt the output), and you're not setting any headers to tell the browser that you're going to be sending it an Excel file.

Feb 23, 2011 at 12:32 AM

No PHP errors. I will check the apache error log tomorrow on my laptop. I was afraid that the saving of the file would fuck it up but it also crashes when I commented it out so that can't be the primary issue. Tomorrow I'll rewrite the script and try to debug it 1 more time, else I'll be forced to look for a different solution to my problem (but I would really like to use php cause that's the language I am most familiar with). I am kinda guessing my for loops are causing apache to run out of memory. I noticed that phpexcel has built in functions for iterations, but not for columns?

Feb 23, 2011 at 12:49 PM

Ok, I solved it. I forgot to use GetActiveSheet on the GetHighestRow() and GetHighestColumn and thats why my loops ran forever exhausting the memory. Strange that it didnt throw any php errors but ok, I solved it...

Coordinator
Feb 24, 2011 at 9:22 PM

I'm surprised that PHP didn't throw any "method doesn't exist" errors myself, but glad you were able to resolve the problem.