Excel Spreadsheets and PHP

--_000_00F751D90F8D7B4D94E6F6EB88B0BAA80227436B97TalkingHead sa_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hello, everyone.

Just a quick question.

What are you using, if anything, to create Excel spreadsheets with PHP?

Thank you in advance.



--_000_00F751D90F8D7B4D94E6F6EB88B0BAA80227436B97TalkingHead sa_--

RE: Excel Spreadsheets and PHP

From: Ian Robertson

> What are you using, if anything, to create Excel spreadsheets with
PHP?
>=20

Output CSV files with the correct MIME type. MS-Windows will open them
in Excel by default in both IE and Firefox.

Unfortunately, this happens even if you have Open Office installed and
would prefer to use that.

Bob McConnell

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Excel Spreadsheets and PHP

--=-Ng4yLIOBXv3e43Y+UR4S
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Fri, 2010-02-19 at 15:36 -0500, Bob McConnell wrote:

> From: Ian Robertson
>
> > What are you using, if anything, to create Excel spreadsheets with
> PHP?
> >
>
> Output CSV files with the correct MIME type. MS-Windows will open them
> in Excel by default in both IE and Firefox.
>
> Unfortunately, this happens even if you have Open Office installed and
> would prefer to use that.
>
> Bob McConnell
>


I believe there are pear classes to output these files, and the new xlsx
format is XML-based so shouldnt be too difficult to output something
simple. Also, the ods format is well documented, and again should be
very easy to output to.

Thanks,
Ash
http://www.ashleysheridan.co.uk



--=-Ng4yLIOBXv3e43Y+UR4S--

Re: Excel Spreadsheets and PHP

On Fri, Feb 19, 2010 at 3:36 PM, Bob McConnell wrote:
> From: Ian Robertson
>
>> What are you using, if anything, to create Excel spreadsheets with
> PHP?
>>
>
> Output CSV files with the correct MIME type. MS-Windows will open them
> in Excel by default in both IE and Firefox.
>
> Unfortunately, this happens even if you have Open Office installed and
> would prefer to use that.
>
> Bob McConnell
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

If you need more control over formatting, formulas, etc., there is an
XML format that Microsoft supports.

http://msdn.microsoft.com/en-us/library/aa140066%28office.10 %29.aspx

Then you can use DOM in PHP to build what you need.

Andrew

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Excel Spreadsheets and PHP

> -----Original Message-----
> From: Ian Robertson [mailto:irobertson@americantextile.com]=20
> Sent: Friday, February 19, 2010 1:28 PM
> To: php-general@lists.php.net
> Subject: [PHP] Excel Spreadsheets and PHP
>=20
> Hello, everyone.
>=20
> Just a quick question.
>=20
> What are you using, if anything, to create Excel spreadsheets=20
> with PHP?
>=20
> Thank you in advance.
>=20

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

Re: Excel Spreadsheets and PHP

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 wrote:
>> -----Original Message-----
>> From: Ian Robertson [mailto:irobertson@americantextile.com]
>> Sent: Friday, February 19, 2010 1:28 PM
>> To: php-general@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

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Excel Spreadsheets and PHP

> >> -----Original Message-----
> >> From: Ian Robertson [mailto:irobertson@americantextile.com]
> >> Sent: Friday, February 19, 2010 1:28 PM
> >> To: php-general@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.spreadsh eet-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 ' href="'.$_SERVER['PHP_SELF'].'?'.$params.'">Download table
"'.$table_name.'" to Excel

';
}


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

'.$description.'

';
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