Do Know Evil – Tyler Mulligan’s Tips and Tricks

I post a lot of zip files on forums to trade information with my fellow gamers, programmers, internetards and what have you. As such, I like to tell them what the zip contains. Now the title of this post is a bit of a misnomer as I haven’t really created the zip yet… but I figured it would be just as helpful. The only extra step you would have in doing an actual zip file would be to extract the files to a folder but enough of the intro, lets dive in.

Step one: Have a folder with the files you want to list

(Whats with your image icons d00d? My images are associated with irfanview)

Step two: Open the Command prompt.

Do this by going to start -> Run… and typing in “cmd”

Step three: Navigate to your folder with “cd” (change directory) and list the files without any attributes using “dir /b”

Step four: Right click anywhere in the black and choose, “Mark“, this allows you to highlight the outputted text.

Highlight your filelist

When you right click again, the text is copied to your clipboard and you can paste it anywhere you’d like

I ask the national members of ISO to vote “NO” in the ballot of ISO DIS 29500 (Office OpenXML or OOXML format) for the following reasons:

1. There is already a standard ISO26300 named Open Document Format (ODF): a dual standard adds costs, uncertainty and confusion to industry, government and citizens;
2. There is no provable implementation of the OOXML specification: Microsoft Office 2007 produces a special version of OOXML, not a file format which complies with the OOXML specification;
3. There is information missing from the specification document, for example how to do a autoSpaceLikeWord95 or useWord97LineBreakRules;
4. More than 10% of the examples mentioned in the proposed standard do not validate as XML;
5. There is no guarantee that anybody can write software that fully or partially implements the OOXML specification without being liable to patent lawsuits or patent license fees by Microsoft;
6. This format conflicts with existing ISO standards, such as ISO 8601 (Representation of dates and times), ISO 639 (Codes for the Representation of Names and Languages) or ISO/IEC 10118-3 (cryptographic hash);
7. There is a bug in the spreadsheet file format which forbids any date before the year 1900: such bugs affect the OOXML specification as well as software applications like Microsoft Excel 2000, XP, 2003 and 2007.
8. This standard proposal was not created by bringing together the experience and expertise of all interested parties (such as the producers, sellers, buyers, users and regulators), but by Microsoft alone.

Sign the petition here

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);
		}
	}
}
?>

My apologies for the less than perfect code, I was on a deadline and perfect code wasn’t a requirement .

Here are some screenshots of the script in action.