Excel | Do Know Evil - Tyler Mulligan's Tips and Tricks

May 12, 2007 No comments

Using PHP to Make Excel Easier

Recently, I was presented with the task of counting the number times filenames repeated in an excel document. Being a 16,000+ row file, with well over 500 unique filenames, there was no simple solution in excel.

Having some experience exporting excel documents using PHP, I knew importing them wouldn’t be too hard. A few google searches later and I came across Excel Reader which fit the bill perfectly.

After sorting my excel document by filename, I created a loop in PHP that would read the first filename, count the number of times it repeated until it changed, then append that data to a string that would be exported to a new excel document once it was done reading the data.

// Starting at 2 to skip the headers | +1 so it compares the last row
for ($i = 2; $i <= $data->sheets[0]['numRows']+1; $i++) {
	if ($data->sheets[0]['cells'][$i][3]==$curFileName) {
		$curFileCount++;
	} else {
		// Output the data line:
"Device","User","Filename","Filecount"
		$stringData =
"\"".$data->sheets[0]['cells'][$i-1][1]."\",\"".$data->sheets[0]['cells'][$i
-1][2]."\",\"".$data->sheets[0]['cells'][$i-1][3]."\",\"".$curFileCount."\"\
n";
		fwrite($fh, $stringData);
 
		// Reset the count and update the curFileName
		$curFileName = $data->sheets[0]['cells'][$i][3];
		$curFileCount=1;
	}
	echo "Row: ".$i."\n";
}

Of course… I didn’t have just one of these files but 6 and manually entering each filename would be a waste of my time. My software solution for this was to create an “Input” and “Output” folder and have PHP cycle through the “Input” folder.

// For every file in the dir "Input", generate a report
if ($handle = opendir('Input')) {
	while (false !== ($orgFileName = readdir($handle))) {
		if($orgFileName != '..' && $orgFileName != '.') {
 
			// Echo the file we're reading, then read that file
			echo $orgFileName."\n";
			$data->read("Input/".$orgFileName);

Eventually it all came together as such:

<?php
 
// Don't change any of this
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
 
// For every file in the dir "Input", generate a report
if ($handle = opendir('Input')) {
	while (false !== ($orgFileName = readdir($handle))) {
		if($orgFileName != '..' && $orgFileName != '.') {
 
			// Echo the file we're reading
			echo $orgFileName."\n";
			$data->read("Input/".$orgFileName);
 
			// Set the first filename to compare
			$curFileName =
$data->sheets[0]['cells'][2][3]==$curFileName;
			$curFileCount = 0;
 
			// Create a file and open it for writing
			$myFile =
"Output/File_Count-".str_replace(".xls",".csv",$orgFileName);
			$fh = fopen($myFile, 'w') or die("can't open file");
 
			// Starting at 2 to skip the headers | +1 so it
compares the last row
			for ($i = 2; $i <= $data->sheets[0]['numRows']+1;
$i++) {
				if
($data->sheets[0]['cells'][$i][3]==$curFileName) {
					$curFileCount++;
				} else {
					// Output the data line:
"Device","User","Filename","Filecount"
					$stringData =
"\"".$data->sheets[0]['cells'][$i-1][1]."\",\"".$data->sheets[0]['cells'][$i
-1][2]."\",\"".$data->sheets[0]['cells'][$i-1][3]."\",\"".$curFileCount."\"\
n";
					fwrite($fh, $stringData);
 
					// Reset the count and update the
curFileName
					$curFileName =
$data->sheets[0]['cells'][$i][3];
					$curFileCount=1;
				}
				echo "Row: ".$i."\n";
			}
			// Close file
			fclose($fh);
		}
	}
}
?>