HOME - ABOUT

Archive for the ‘PHP’ Category

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 »


← Back to Home

© wkm. Powered by WordPress using the wkm Theme.