Re: [PHP] Excel Spreadsheets and PHP
On Fri, Feb 26, 2010 at 3:47 PM, Ian Robertson irobert...@americantextile.com wrote: Thank you all very much for your replies. I learned about a few new approaches. I didn't see it come up yet, so I'll post the URL of what I have been using. php_writeexcel - http://www.bettina-attack.de/jonny/view.php/projects/php_writeexcel/ I've been able to pull off quite a bit with this class and actually have referenced this Perl page for documentation since this PHP class was ported from a Perl class - http://cpansearch.perl.org/src/JMCNAMARA/Spreadsheet-WriteExcel-0.37/WriteExcel/doc/WriteExcel.html Another option just occurred to me. You can attach to and read/write to Excel sheets using an ODBC client. As long as you are building a sheet/workbook with strictly tabular data, that should allow you to issue SQL commands. I'm not sure whether you can create an Excel file from scratch that way, but if not it would be trivial to have an empty Excel file that you can use as a template. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Spreadsheets and PHP
Thank you all very much for your replies. I learned about a few new approaches. I didn't see it come up yet, so I'll post the URL of what I have been using. php_writeexcel - http://www.bettina-attack.de/jonny/view.php/projects/php_writeexcel/ I've been able to pull off quite a bit with this class and actually have referenced this Perl page for documentation since this PHP class was ported from a Perl class - http://cpansearch.perl.org/src/JMCNAMARA/Spreadsheet-WriteExcel-0.37/WriteExcel/doc/WriteExcel.html So, many thanks for your replies and also thanks to Johann and his 3 Bettinas, wherever you are, haha. -Original Message- From: Jay Blanchard [mailto:jblanch...@pocket.com] Sent: Sunday, February 21, 2010 9:44 PM To: Ian Robertson; php-general@lists.php.net Subject: RE: [PHP] Excel Spreadsheets and PHP [snip] What are you using, if anything, to create Excel spreadsheets with PHP? [/snip] PHP http://www.evolt.org/node/26896 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Excel Spreadsheets and PHP
[snip] What are you using, if anything, to create Excel spreadsheets with PHP? [/snip] PHP http://www.evolt.org/node/26896 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Spreadsheets and PHP
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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Excel Spreadsheets and PHP
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/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[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.
RE: [PHP] Excel Spreadsheets and PHP
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
RE: [PHP] Excel Spreadsheets and PHP
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
Re: [PHP] Excel Spreadsheets and PHP
On Fri, Feb 19, 2010 at 3:36 PM, Bob McConnell r...@cbord.com 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: [PHP] Excel Spreadsheets and PHP
-Original Message- From: Ian Robertson [mailto:irobert...@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
Re: [PHP] 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 mike.han...@atmel.com wrote: -Original Message- From: Ian Robertson [mailto:irobert...@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: [PHP] Excel Spreadsheets and PHP
-Original Message-
From: Ian Robertson [mailto:irobert...@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.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
