HOME - ABOUT

Archive for October, 2007

Export HTML Data to MS Excel: a PHP/JavaScript Solution

Tuesday, October 30th, 2007

Google up “export HTML to Excel” and most of your matches will provide methods using ASP/VBScript by either declaring the Response.ContentType = “application/vnd.ms-excel” (exhibit A) or building a new ActiveXObject and fuddling around with that (exhibit B).

Well, neither of these helped me at all since I was using PHP for this particular project, so I had to come up with my own solution.

First things first — PHP has the ability to generate an Excel file “on the fly” simply by setting the Content-Type in your page header.
Put the following lines of code (inside your PHP tags, of course) at the top of an HTML document and force the browser to open the page in Excel:

<?php
	header('Content-Type: application/vnd.ms-excel'); //IE and Opera
	header('Content-Type: application/x-msexcel'); // Other browsers
	header('Expires: 0');
	header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
?>

This is all well and good if you simply want to direct a specific page to always open in Excel. I, however, had some additional requirements. Because the page I was working on works a little bit like a query portal, it would have been extremely obnoxious to re-code every single query page to simultaneously write a PHP/Excel file at the same time the data was being displayed. Obviously, this would also significantly increase the load on the server and slow down browsing. This was not something I was willing to sacrifice when the PHP/Excel page might only be viewed 10 or 5 or 1 percent of the time a query was run! So, I determined that I wanted to export:

  • only upon request by the user (or, on demand — like when an “Export” button is clicked.)
  • only part of a page to Excel (in this case, the contents of a DIV, which contained table(s) of data.)

Using some JQuery AJAX functions and a couple PHP scripts as middlemen, here’s how I accomplished this.

Initially, I created a simple PHP file called export.php that contains HTML headers, a CSS link and an HTML body with one PHP statement included. Here is the full text of export.php:

<html>
	<head>
		<link href='./css/styles.css' rel='stylesheet' type='text/css'></link>
	</head>
	<body>
	<?php
		echo $_POST['text'];
	?>
	</body>
</html>

What we’ll do with this, is send the entire contents of the desired container (in my case, the DIV) as a POST. (I just use the name “text” in this example.) This script will generate a temporary HTML source for everything I want to send to Excel. However, we still need to add the Content-Type declaration — we just can’t do it here or else the PHP POST won’t execute because the file would’ve been already send to Excel! (Annoying, yes.)

So, we have to create a second script that basically contains everything in this one, plus the PHP headers. I made another file called export2.php which creates yet a third file (excel.php) and writes the PHP headers, the contents of export.php (including POST data) and does any text manipulation that may be needed or desired. export2.php looks something like this:

<?php
	$writeData = "<php\n";
	$writeData .= "header('Content-Type: application/vnd.ms-excel');\n";
	$writeData .= "header('Content-Type: application/x-msexcel');\n";
	$writeData .= "header('Expires: 0');\n";
	$writeData .= "header('Cache-Control: must-revalidate, post-check=0, pre-check=0');\n";
	$writeData .= "?>\n";

// the following is useful for removing tags you don't want to export (INPUT, IMG, etc.)
	$allowedTags = '<html><head><body><link><div><table><th><tr><td><thead><tbody><br><b><i><span>';
	$_POST['content'] = strip_tags($_POST['content'], $allowedTags);

// the following may be necessary if there are quotes in your POST data
	$writeData .= stripslashes(stripslashes($_POST['content']));
	$exportFile = "./excel.php"; 

        if (!$handle = fopen($exportFile, 'w+')) {
           echo "Cannot open file ($exportFile)";
	     exit;
	}
	if (is_writable($exportFile)) {
	    if (fwrite($handle, $writeData) === FALSE) {
               echo "Cannot write to file $exportFile";
         	   exit;
	    }
          fclose($handle);
	} else {
	    echo "The file $exportFile is not writable";
	}
?>

Now that our data handling scripts are in place, we need to tell our initial page how to process them. First, I created a button to trigger the event which initiates the AJAX requests.

<input id="expexcel" type="button" value="Export to Excel"></input>

Next, add the jQuery to handle the click event of this button, the AJAX POST requests and the redirect to the newly created excel.php file.

$(document).ready(function() {
	$("#expexcel").click(function() {
		$.post("./tmp/export.php", {text: $("#container").html()}, function(data) {
			$.post("./tmp/export2.php", {content: data}, function() {
				window.location = "./tmp/excel.php";
			});
		});
	});
});

This small bit of code produces two AJAX requests and one redirect every time the Export to Excel button is clicked.
Line 03 makes the first POST to export.php. It sends the entire HTML content of the “#container” DIV as the POST data. The result of this POST is returned in jQuery as “data”, which is used in line 04. This second POST (to export2.php) sends the result of the first POST as “content”. Once export2.php processes this content and writes it to the file excel.php, the AJAX request is successful and the page is redirected to excel.php in line 05.

 Posted in PHP, Programming | 5 Comments »

World of Warcraft: Mac Video Capture Option

Sunday, October 21st, 2007

Option ScreenBlizzard has a added new video capture feature to the Mac version of World of Warcraft. Initially, I tried it on the highest resolution setting of 1446×904.Mac OptionsWhen I hit the capture button, the game slowed to a painful crawl and after a few seconds, I received an error that the setting was too high and the capture stopped. When I watched the few seconds long video that was captured, it was as jerky and rough as the game play. I worked my way down the settings, getting the same error, until I got to 640×400. At that setting, it works perfectly, without affecting performance, and not giving me an error. After the capture is complete, it compresses the video while you play. This process didn’t affect the performance of the game either.

Here’s a video of a dancing Ogre in Negrand. He and his friends were killed by me shortly after this video was captured. There is no option for 480×300 which is what this video is. I had to capture at 640×400 and convert using iMovie.

I posted this video using DreamHost’s Free Flash Media Tools. They are very easy to use and work great. To simplify the process of getting the video into the article, I installed the AFC FLV Player plugin. It has one dependency, the AFC Plug System. You have to create an account on their site, which I wasn’t crazy about.

 Posted in WoW | 2 Comments »


← Back to Home

© wkm. Powered by WordPress using the wkm Theme.