RE: [PHP] Excel Spreadsheets and PHP

> >> -----Original Message-----
> >> From: Ian Robertson [mailto:[email protected]]
> >> Sent: Friday, February 19, 2010 1:28 PM
> >> To: [email protected]
> >> Subject: [PHP] Excel Spreadsheets and PHP
> >>
> >> Hello, everyone.
> >>
> >> Just a quick question.
> >>
> >> What are you using, if anything, to create Excel spreadsheets
> >> with PHP?
> >>
> >> Thank you in advance.
> >>
> >
> > Pear Spreadsheet Excel Writer.
> >
> > http://pear.php.net/package/Spreadsheet_Excel_Writer

Related, here is a routine we use. Assuming you already have your data in a
multi-array.


/**
 * Outputs an Excel .xls file
 * Note: a row that starts with "---" will be considered a separator row
and output any text following the "---" as such.
 *
 * @param string $title_text The name of the title in the Excel .xls
document (gmdate('Y-m-d H:i') is auto appended)
 * @param array $header_array an array of headers for each column
 * @param array $data_array the data for each column and row
 * @param string $file_name the name of the .xls file to save as
(gmdate('Y-m-d H:i') is auto appended), defaults to $title_text
 * @author Daevid Vincent
 * @date   10/29/2009
 */
function download_table_to_excel($title_text, &$header_array, &$data_array,
$file_name=null)
{
	//require_once './includes/gui/gui_setup.inc.php';

	if (!$file_name) $file_name = $title_text;
	$file_name = str_replace( array('[', ']'), array('(',')'),
$file_name);

	add_user_log('Action', 'Download "'.$file_name.'" Excel file');

	
set_include_path(get_include_path().PATH_SEPARATOR.ROOTPATH.'/includes/pear
');
	require_once
ROOTPATH.'/includes/pear/Spreadsheet/Excel/Writer.php';

 	$excel_control_characters = array('@', '=');

	$exceldoc = new Spreadsheet_Excel_Writer();

	// Set version to 8 (BIFF8) so strings are not truncated to 255
chars
	//$exceldoc->setVersion(8);
	
//http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-write
r.spreadsheet-excel-writer-workbook.setversion.php
	//http://forum.openx.org/index.php?showtopic=503418353
	//http://pear.php.net/bugs/bug.php?id=3384

 	$worksheet =& $exceldoc->addWorksheet('Sheet 1'); //sheet name can
only be < 31 chars, but we only use one sheet, so hard-code it

	$format_data =& $exceldoc->addFormat();
	$format_data->setTextWrap();

 	// Create an array to track the value length per column, the
default width is 8.11
	$max_column = count($header_array) - 1;
 	$max_len_by_column = array();
 	for ($col = 0; $col <= $max_column; $col++)
$max_len_by_column[$col] = 8.11;

 	$row = -1;
 	// Optionally write table title
	if ($title_text)
	{
		$format_title =& $exceldoc->addFormat();
		$format_title->setAlign('center');
		$format_title->setAlign('vcenter');
		$format_title->setBold();
		$format_title->setTextWrap();

		$title_text .= ' (created on '.gmdate('Y-m-d @ H:i').'
UTC)';
		// adjust the row height from the number of lines in the
table title
		$lines = substr_count($title_text, '<br>') + 1;
		$height = $lines * 14;
		$row++;
		$value =
html_entity_decode(trim(strip_tags(str_replace('<br>', "\n",
$title_text))));
		if (is_string($value) && in_array(substr($value,0,1),
$excel_control_characters)) $value = ' '.$value; // Add a space before
Excel control characters
 		$worksheet->write($row, 0, $value, $format_title);
	 	$worksheet->setRow($row, $height);
	 	$worksheet->mergeCells($row, 0, $row, $max_column);
	}

	// Write column headers
	$format_header =& $exceldoc->addFormat();
	$format_header->setBold();
	$format_header->setTextWrap();

 	$row++;
 	foreach ($header_array as $col => $header)
 	{
 		// remove html tags from values
 		$value =
html_entity_decode(trim(strip_tags(str_replace('<br>', "\n",
is_array($header) ? $header[0] : $header))));
		if (is_string($value) and in_array(substr($value,0,1),
$excel_control_characters)) $value = " ".$value; // Add a space before
Excel control characters
 		$worksheet->write($row, $col, $value, $format_header);
 		if (is_array($header)) $worksheet->writeNote($row, $col,
$header[1]);
 	}

 	foreach ($data_array as $i => $data)
 	{
 		$row++;
 		$col = 0;

 		//check for magic separator rows
	  	if ( substr($data,0,3) == '---' )
	  	{
			$separator_row = substr($data,3);
			// adjust the row height from the number of lines
in the table title
			$lines = substr_count($separator_row, '<br>') + 1;
			$height = $lines * 14;
			$row++;
			$value =
html_entity_decode(trim(strip_tags(str_replace('<br>', "\n",
$separator_row))));
			if (is_string($value) &&
in_array(substr($value,0,1), $excel_control_characters)) $value = '
'.$value; // Add a space before Excel control characters
	 		$worksheet->write($row, 0, $value, $format_title);
		 	$worksheet->setRow($row, $height);
		 	$worksheet->mergeCells($row, 0, $row, $max_column);

	  		continue;
	  	}

 		foreach ($data as $key => $value)
 		{
 			$value =
html_entity_decode(trim(strip_tags(str_replace(array('<br>',"\t"),
array("\n",''), $value))));
			if (is_string($value) &&
in_array(substr($value,0,1), $excel_control_characters)) $value = "
".$value; // Add a space before Excel control characters

 			$worksheet->write($row, $col, $value,
$format_data);

 			// find the maximum value len (up to 40) so an
appropriate column width can be set
 			$lines = explode("\n", $value);
 			foreach ($lines as $line)
 			{
 				$len = min(40, strlen($line) * 1.20);
//[dv] this 1.20 seems to be a fudge factor with no real basis AFAICT?
 				if ($len > $max_len_by_column[$col])
$max_len_by_column[$col] = $len;
 			}

 			$col++;
 		}
 	}

 	// Adjust column width based on column values
 	foreach ($max_len_by_column as $col => $len)
$worksheet->setColumn($col, $col, $len);

	// Send the worksheet
 	$exceldoc_name = $file_name.' ('.gmdate('Y-m-d H:i').').xls';
	$exceldoc_name = str_replace( array('[', ']',':'), array('(',
')','-'), $exceldoc_name); //IE6 chokes on some characters in filename
 	$exceldoc->send($exceldoc_name);
	$exceldoc->close();

	unset($header_array, $data_array);
} 


/**
 * Used as a supporting function for print_table() and the key to
download_table_to_excel()
 * Returns an HTML anchor tag
 *
 * @param string $download_variable $_GET parameters that are parsed to
re-create the table in Excel rather than HTML
 * @param string $table_name unique name of this table (useful for when
multiple tables are on the same page)
 * @return string
 * @author Daevid Vincent
 * @date   2010-02-02
 */
function get_download_to_excel_link_html($download_variable, $table_name)
{
	if ($_SESSION['mobile']) return;

	$params = "{$download_variable}={$table_name}";

	//append existing $_GET parameters automatically to the URL string
	foreach ($_GET as $variable=>$value)
		if (is_array($value))
			foreach ($value as $array_value)
				$params .= "&{$variable}[]={$array_value}";
		else
			$params .= "&{$variable}={$value}";

	return '<a class="excel"
href="'.$_SERVER['PHP_SELF'].'?'.$params.'">Download table
"<b>'.$table_name.'</b>" to Excel</a><br/><br/>';
}


And here's a partial of the related function to output a table from an
array of data

/**
 * A generic routine for displaying an HTML table
 * Note: a row that starts with "---" will be considered a separator row
and output any text following the "---" as such.
 *
 * @access public
 * @return an HTML formatted <table>
 * @param 	string $title_text the title of the table
 * @param 	array $header_array the column headers, ex:
array(array('Header 1 Title', 'Header 1 Description/Tip', 'nosort'), ...);
OR array('Header 1', 'Header 2', ...);
 * @param 	array $data_array the data of the table
 * @param 	array $td_attribute_array CSS attributes for the
$data_array values [do not count the detail column as an index] for
example, $attributes[1] = 'align="center"'; will center the second
$data_array column to the right
 * @param 	string $table_name put a 'download to excel' link (huh?)
 * @param	boolean $portlet (true) toggle if you want this to be a
minimizeable portlet or not
 * @param	string $table_class any CSS class information for the table
tag (default is 'sortable')
 * @param	string $portlet_class any CSS class information for the
portlet tag (default is 'portlet')
 * @param  	string $description a blob of text to display just above
the table
 * @see		print_array_table()
 * @author Daevid Vincent [[email protected]]
 * @date	2009-01-14
 */
function print_table($title_text, $header_array, $data_array,
$td_attribute_array=NULL, $table_name=NULL, $portlet=true,
$table_class='sortable', $portlet_class='portlet', $description=null)
{
	$download_variable = 'download_to_excel';

	$num_rows = @intval(count($data_array));

<?php
if ($num_rows)
{
	$header = array_shift($header_array);
	if ($num_rows > 1000) notification_table('info', '<i>It is not
adviseable to sort these '.number_format($num_rows).' rows using the column
headers (as this may lock-up some browsers).<br/>Please narrow your results
to less than 1000.</i>');
	if ($description) echo '<p>'.$description.'<p>';
	if ($table_name)
	{
		echo get_download_to_excel_link_html($download_variable,
$table_name);