Looking for screen capture to pdf/excel recommendation

[svar]

Consider the following: A user selects an option, which result in a query getting run on the server database and the results displayed on the browser.
I am looking for a recommendation to
export these query results in a pdf or spreadsheet. For instance in perl I can use the spreadsheet::writeexcel module to do that. In php?

 

[jpadie]

I have written an engine to do this.

It uses pear/spreadsheet and wkhtmltopdf to render the pdfs.
if multiple reports/whatevers are specified, it can render into multipage pdfs or multi-tab spreadsheets

it also relies on a couple of templates and on the jQuery library dataTables being available.

this is the renderer. the entry point is addReport. $reportdata will be a flat array.

<?php 	if (!defined("SITE")) die(-1);?>
<?php

class jaRender{
	
	public $reports = array();
	public $oF = array();
	
	public function __construct(){
		
	}
	public function __destruct(){
		foreach ($this->oF as $file){
			if (is_file($file)) :
				@unlink($file);
			endif;
		}
	}
	public function addReport($reportTitle, $reportData, $reportFields, $reportShortTitle, $reportType, $js, $uid){
		$this->reports[] = array('title'=>$reportTitle, 'data'=>$reportData, 'fields'=>$reportFields, 'shortTitle'=>$reportShortTitle, 'type'=>$reportType, 'js'=>$js, 'uid'=>$uid);
	}
	
	public function render($type='view'){
		switch (strtolower($type)):
			case 'view':
				
				if ($this->numReports() > 1){
					$reports =& $this->reports;
					require TEMPLATES . DIRECTORY_SEPARATOR . 'multiple.php';
				} else {
					$report = $this->reports[0];
					require TEMPLATES . DIRECTORY_SEPARATOR . 'single.php';
				}
			break;
			case 'zip':
				ob_start();
				$content = array();
				$this->oF = array();
				$arg = array();
				$title = array();
				$zipFile = sys_get_temp_dir() . DIRECTORY_SEPARATOR . uniqid('temp_zip', true) .'.zip';
				foreach ($this->reports as $report):
					ob_start();
					require TEMPLATES . DIRECTORY_SEPARATOR . 'single.php';
					$content = ob_get_contents();
					ob_clean();
					
					//write to a file
					$input = sys_get_temp_dir() . DIRECTORY_SEPARATOR . uniqid('temp_html', true) .'.html';
					file_put_contents($input, $content);
					//echo $input ."<br/>";
					
					//convert to pdf
					$title = preg_replace('/(\d{2}\/\d{2}\/\d{4})/e', "dTransform('\\1')", $report['title']);
					$output = sys_get_temp_dir() . DIRECTORY_SEPARATOR . $title .'.pdf';
					$cmd = 'wkhtmltopdf --orientation Landscape --footer-center "Page [page] of [toPage]" ' . escapeshellarg($input) . ' '. escapeshellarg($output);
					//echo $cmd . '<br/>';
					exec($cmd);
					
					//delete html file
					unlink($input);
					
					//add to zip
					$option = file_exists($zipFile) ? '-g': '';
					$cmd = "zip -D $option " . escapeshellarg($zipFile) ." " . escapeshellarg($output);
					//echo $cmd . '<br/>';
					exec($cmd);
					
					//delete the pdf file
					unlink($output);
				endforeach;
				if (is_file($zipFile)):
					header ('Content-Type: application/zip');
					header('Content-Length: ' . filesize($zipFile));
					header('Content-Disposition: attachment; filename="RocketRoute Reports.zip"');
					@readfile($zipFile);
				endif;
				unlink($zipFile);
				exit;
				break;
			case 'pdf':
				ob_start();
				$content = array();
				$this->oF = array();
				$arg = array();
				foreach ($this->reports as $report):
					require TEMPLATES . DIRECTORY_SEPARATOR . 'single.php';
					$content[] = ob_get_contents();
					ob_clean();
				endforeach;
				ob_end_clean();
				foreach ($content as $c):
					$this->oF[] = sys_get_temp_dir() . DIRECTORY_SEPARATOR . uniqid('temp_html', true) .'.html';
					$arg[] = escapeshellarg(end($this->oF));
					file_put_contents(end($this->oF), $c);
				endforeach;
				$output = sys_get_temp_dir() . DIRECTORY_SEPARATOR . uniqid('temp_pdf', true) .'.pdf';
				$cmd = '	 --orientation Landscape --footer-center "Page [page] of [toPage]" ' . implode (' ', $arg) . ' '. escapeshellarg($output);
				$this->oF[] = $output;
				exec($cmd, $outputString, $return);
				if (is_file($output)):
					//echo 'ok';
					header ('Content-Type: application/pdf');
					header('Content-Length: ' . filesize($output));
					header('Content-Disposition: attachment; filename="RocketRoute Reports.pdf"');
					@readfile($output);
				endif;
				break;
			case 'xls':
			case 'excel':
				ob_end_clean();
				require_once 'Spreadsheet/Excel/Writer.php';
				$workbook = new Spreadsheet_Excel_Writer();
				foreach($this->reports as $report):
				
					// Creating a worksheet
					$worksheet = $workbook->addWorksheet($report['shortTitle']);
					if($worksheet instanceof PEAR_error):
						die ($worksheet->getMessage());
					endif;
					$rowCount=1;
					$worksheet->write(0,0,$report['title']);
					foreach(array_keys($report['fields']) as $key=>$field):
					  $worksheet->write($rowCount, $key, $field );
					endforeach;
					foreach($report['data'] as $row):
						$rowCount++;
						foreach(array_values($row) as $key=>$value):
					    	$worksheet->write($rowCount, $key, $value);
						endforeach;
					endforeach;
				endforeach;
				$workbook->send('RocketRouteReports.xls');
				$workbook->close();
		endswitch;
	}
	
	public function numReports(){
		return count ($this->reports);
	}
	
}
?>

an example template is here

<?php 	if (!defined("SITE")) die(-1);?>
<?php getHeader();?>
<div class="ui-widget-header ui-corner-all reportTitle"><?php echo $report['title'];?></div>
<?php if ($report['type'] == 'report'):?>
<div class="table">
	<table id="<?php echo $report['uid'];?>">
	<thead>
		<tr>
			<?php foreach ($report['fields'] as $heading=>$align): ?>
			<th class="<?php echo $align;?>"><?php echo wordwrap($heading, 50, '<br />');?></th>
			<?php endforeach; ?>
		</tr>
	</thead>
	<tbody>
		<?php foreach ($report['data'] as $row): ?>
			<tr>
			<?php foreach ($row as $field=>$value): ?>
				<td class="<?php echo $report['fields'][$field];?>"><?php echo ($value);?></td>
			<?php endforeach; ?>
			</tr>
		<?php endforeach;?>
	</tbody>
	</table>
</div>
<?php 
?>
<?php echo $report['js'];?>
	
</script>
<?php else:?>
<?php echo $report['js'];?>
<?php endif;?>
<?php if (!isAcrobat()): ?>
<script type="text/javascript">
	$('document').ready(function(){
		addjIcon();
	});
</script>

<?php endif; ?>
<?php getFooter();?>

this in turn relies on a footer and header to provide the framework

<?php
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
?>
<!DOCTYPE HTML>
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
		<title>jaReporting</title>
		<style type="text/css">
			@import url('<?php echo SITE;?>/css/demo_page.css');
			@import url('<?php echo SITE;?>/css/demo_table_jui.css');
			@import url('<?php echo SITE;?>/css/demo_table.css');
			@import url('<?php echo SITE;?>/css/general.css');
		</style>
		
		
		<!-- <link type="text/css" href="<?php echo SITE;?>/css/redmond/jquery-ui-1.8.6.custom.css" rel="stylesheet" />
		-->
		<link rel="stylesheet" href="[URL unfurl="true"]http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css"[/URL] />
  		<script src="[URL unfurl="true"]http://code.jquery.com/jquery-1.9.1.js"></script>[/URL]
  		<script src="[URL unfurl="true"]http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>[/URL]
 
		<script type="text/javascript" src="<?php echo SITE;?>/js/jquery.dataTables.min.js"></script>
		<script type="text/javascript">
	
		
			<?php if (!isAcrobat()): ?>

			$(document).ready(function(){
				$('#tabs___').tabs();
			});
			
			function addjIcon(){
				
					
				var	elem = $('.reportTitle');
				$(elem).after('<div class="buttons" style="float:right; width:200px;margin-top:-18px; margin-bottom: 2px;padding-top:0;text-align:right"><button id="home">Home</button><button id="pdf">pdf</button><button id="xls">Excel</button><button id="zip">Zipped PDFs</button></div>');
				
				$('.buttons').css('overflow-y','hidden').find('button').bind('click', function(){
												var url = "<?php echo SITE;?>/index.php"; //parent.document.URL;
												if(url.indexOf('?') != -1 ){
													url = url + '&iframe=1';
												} else{
													url = url + '?iframe=1';
												}
												switch ($(this).attr('id')){
													case 'home':
														window.location = "<?php echo SITE;?>/index.php";
													break;
													case 'pdf':
														
														var src = url + '&type=pdf';
														window.location = src;
													break;
													case 'xls':
														var src = url + '&type=xls';
														window.location = src;
													break;
													case 'zip':
														var src = url + '&type=zip';
														window.location = src;
													break;

												}
											}).button();
				
				
			}
			<?php endif; ?>
		</script>
	</head>
	<body>
		<div id="content">

	</div><!-- end content div -->
	</body>
	<?php echo $js;?>
</html>

the report['js'] and $js variables are kludges for when I needed to inject code to change things around but leave the html datatables sortable etc. they should not be necessary for most applications.