Excel Spreadsheets and PHP [General]

Prev: Max file size in fopen()
Next: PHP, PHPMailer and SMTP Server

From: Bastien Koert on 19 Feb 2010 19:37

You can also create an htnl table and excel will happily handle that as well.

The real trick is to get IE to accept the stream as a file download. I
find that I need to save the file first and the push the file down.

On 2/19/10, Hansen, Mike <Mike.Hansen(a)atmel.com> wrote:
>> -----Original Message-----
>> From: Ian Robertson [mailto:irobertson(a)americantextile.com]
>> Sent: Friday, February 19, 2010 1:28 PM
>> To: php-general(a)lists.php.net
>> 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
>
>
> Mike
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--
Sent from my mobile device

Bastien

Cat, the other other white meat

From: "Daevid Vincent" on 19 Feb 2010 20:53

> >> -----Original Message-----
> >> From: Ian Robertson [mailto:irobertson(a)americantextile.com]
> >> Sent: Friday, February 19, 2010 1:28 PM
> >> To: php-general(a)lists.php.net
> >> 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, ' ') + 1;
$height = $lines * 14;
$row++;
$value =
html_entity_decode(trim(strip_tags(str_replace(' ', "\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(' ', "\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, ' ') + 1;
$height = $lines * 14;
$row++;
$value =
html_entity_decode(trim(strip_tags(str_replace(' ', "\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(' ',"\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
"'.$table_name.'" to Excel</a> ';
}

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 [daevid.vincent(a)panasonic.aero]
* @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', 'It is not
adviseable to sort these '.number_format($num_rows).' rows using the column
headers (as this may lock-up some browsers). Please narrow your results
to less than 1000.');
if ($description) echo ''.$description.'';
if ($table_name)
{
echo get_download_to_excel_link_html($download_variable,
$table_name);

From: "aschwin on 20 Feb 2010 07:05

On 19-2-2010 21:28, Ian Robertson wrote:
> Hello, everyone.
>
> Just a quick question.
>
> What are you using, if anything, to create Excel spreadsheets with PHP?
>
> Thank you in advance.
>
>
Hello Ian,

You can use the PHPExcel class. It can read, write and convert between
Excel versions without the need of the COM. Depending on the version of
the file needs to be, it uses XML or bitbashing.

Or, like others said, you can use XML for writing Excel files in the new
xlsx format. This is actually a ZIP-file with a lot of XML-files in it.
This makes it possible to use other XML techniques as well like Schema,
XSL, XPath and DTD.

I prefer to make a XML based template and use XSL to transform and parse
the data. With PHP you can pass parameters to the XSL file and fill in
the blanks where needed.

Excel is quite complex due to the cells, the styles, the formulas and
the worksheets.

Kind regards,

Aschwin Wesselius

From: Nathan Rixham on 20 Feb 2010 11:15

Bastien Koert wrote:
> You can also create an htnl table and excel will happily handle that as well.
>
> The real trick is to get IE to accept the stream as a file download. I
> find that I need to save the file first and the push the file down.
>

+1 this approach; Excel is HTTP aware and you can simply plumb in the
URL of an HTML table and excel will do the rest; it works v well; saves
tonnes of work and means you can do nice little things like importing
SPARQL over HTTP queries straight in to excel - and then make nice pivot
[1] views of the data, all in a couple of minutes.

[1] http://www.getpivot.com/

From: "Jay Blanchard" on 21 Feb 2010 21:43

[snip]
What are you using, if anything, to create Excel spreadsheets with PHP?
[/snip]

PHP

http://www.evolt.org/node/26896

First | Prev | Next | Last
Pages: 1 2 3
Prev: Max file size in fopen()
Next: PHP, PHPMailer and SMTP Server