> >> -----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);
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
