Prev: MDB2 and MSSQL
Next: Quickform: how to check two fields in one function using registerRule()
From: Markus Ernst on 26 Feb 2007 04:49
Hello!

I use Structures_DataGrid for exporting database records to Excel.
Anyway, all I get is an empty file which neither Excel nor OpenOffice
can read, while the same works without problems with the CSV renderer.
Here is my code with some test data:

<?php

// select output format
$suffix = 'xls';

// Initiate and fill datagrid
require_once 'Structures/DataGrid.php';
$datagrid =& new Structures_DataGrid();
$data = array(
array(2 => 'Spears', 3 => 'Britney'),
array(2 => 'Williams', 3 => 'Robbie'),
array(2 => 'Jagger', 3 => 'Mick')
);
$datagrid->bind($data);

// Set columns
$datagrid->addColumn(new Structures_DataGrid_Column('First Name', 3));
$datagrid->addColumn(new Structures_DataGrid_Column('Last Name', 2));

// Set options
$fileformat = strtoupper($suffix);
$filename = 'userlist_'.date('Y-m-d_H-i');
switch ($suffix) {
case 'xls':
$options = array(
'filename' => $filename.'.xls'
);
$content_type = 'application/vnd.ms-excel';
break;
case 'csv':
$options = array(
'delimiter' => ';'
);
$content_type = 'text/csv';
break;
}
$options['encoding'] = 'utf-8';

// Output
header('Content-type: '.$content_type.'; charset=utf-8');
header('Content-Disposition: attachment;
filename="'.$filename.'.'.$suffix.'"');
$renderer =&
$datagrid->setRenderer(constant('DATAGRID_RENDER_'.$fileformat), $options);
$datagrid->render($renderer, $options);
exit;

?>

If I change $suffix to 'csv' I get a proper CSV file, so the problem
must be somewhere in the rendering part. Also, the problem is not in the
duplicate headers; the XLS file remains empty also when I remove them.

(Sidenote: The CSV could actually do the job, except for non-ASCII
characters. Excel just opens it as Latin-1, so the user would need to
open it in OpenOffice, choose the correct delimiter and utf-8 encoding
and save it to Excel format, which is too complicated, as the
application is intended to be used by non-techies.)

Does anybody see the mistake I made in my code? Or is there some special
trick needed to make the XLS renderer work?

Thanks for any hint!
Markus
From: michael.mauch on 26 Feb 2007 14:57
Markus Ernst wrote:

> I use Structures_DataGrid for exporting database records to Excel.
> Anyway, all I get is an empty file which neither Excel nor OpenOffice
> can read, while the same works without problems with the CSV renderer.
> Here is my code with some test data:

Your code nearly works for me, if I remove the linebreak here:

> header('Content-Disposition: attachment;
> filename="'.$filename.'.'.$suffix.'"');

Maybe that was only a screwup of your newsreader. Open Office happily
opens the resulting file. So probably the culprit is not your code.
Perhaps you one of your PEAR packages is too old? Here I have:

Structures_DataGrid 0.8.1 beta
Structures_DataGrid_DataSource_Array 0.1.2 beta
Structures_DataGrid_Renderer_XLS 0.1.1 beta

Regards...
Michael
From: Markus Ernst on 27 Feb 2007 11:10
(Michael Mauch) schrieb:
> Markus Ernst wrote:
>
>> I use Structures_DataGrid for exporting database records to Excel.
>> Anyway, all I get is an empty file which neither Excel nor OpenOffice
>> can read, while the same works without problems with the CSV renderer.
>> Here is my code with some test data:
>
> Your code nearly works for me, if I remove the linebreak here:
>
>> header('Content-Disposition: attachment;
>> filename="'.$filename.'.'.$suffix.'"');
>
> Maybe that was only a screwup of your newsreader. Open Office happily
> opens the resulting file. So probably the culprit is not your code.
> Perhaps you one of your PEAR packages is too old? Here I have:
>
> Structures_DataGrid 0.8.1 beta
> Structures_DataGrid_DataSource_Array 0.1.2 beta
> Structures_DataGrid_Renderer_XLS 0.1.1 beta

Thank you for your input! I updated all kinds of PEAR packages that
could have any impact, with no success. Anyway as you wrote it worked
for you, I tried to understand what happens in those Excel and OLE
classes and finally found the reason: A Safe Mode restriction prevented
the OLE File class from creating a temporary file!

So here's the Safe Mode proof version of my code (csv part removed):

<?php

// Initiate and fill datagrid
require_once 'Structures/DataGrid.php';
$datagrid =& new Structures_DataGrid();
$data = array(
array(2 => 'Speàrs', 3 => 'Britñey'),
array(2 => 'Wilłiams', 3 => 'Robbie'),
array(2 => 'Jagger', 3 => 'Mick')
);
$datagrid->bind($data);

// Set columns
$datagrid->addColumn(new Structures_DataGrid_Column('First Name', 3));
$datagrid->addColumn(new Structures_DataGrid_Column('Last Name', 2));

// Attach XLS renderer
$filename = 'userlist_'.date('Y-m-d_H-i');
$options = array(
'filename' => $filename.'.xls'
);
$options['encoding'] = 'utf-8';
$renderer =& $datagrid->setRenderer(DATAGRID_RENDER_XLS, $options);

// Now we have to get the workbook object and set the temp directory
// Of course this directory must be created manually and chmoded
// with writing permission for PHP
$workbook =& $renderer->getContainer();
$workbook->setTempDir($_SERVER['DOCUMENT_ROOT'].'/temp/');

// Output file
$datagrid->render($renderer, $options);
exit;

?>
From: Mark Wiesemann on 27 Feb 2007 11:37
Hi Markus,

Markus Ernst wrote:
> Thank you for your input! I updated all kinds of PEAR packages that
> could have any impact, with no success. Anyway as you wrote it worked
> for you, I tried to understand what happens in those Excel and OLE
> classes and finally found the reason: A Safe Mode restriction prevented
> the OLE File class from creating a temporary file!
>
> So here's the Safe Mode proof version of my code (csv part removed):
[...]
> $workbook =& $renderer->getContainer();
> $workbook->setTempDir($_SERVER['DOCUMENT_ROOT'].'/temp/');
[...]

I now remember that I had the same problem sometime last year. Can you
please write a feature request? I think it would be a good idea to add a
new option for the temporary directory and to document the need to set
this option when safe_mode is enabled.

Regards,
Mark

--
http://www.markwiesemann.eu
From: Markus Ernst on 27 Feb 2007 12:48
Mark Wiesemann schrieb:
> Hi Markus,
>
> Markus Ernst wrote:
>> Thank you for your input! I updated all kinds of PEAR packages that
>> could have any impact, with no success. Anyway as you wrote it worked
>> for you, I tried to understand what happens in those Excel and OLE
>> classes and finally found the reason: A Safe Mode restriction prevented
>> the OLE File class from creating a temporary file!
>>
>> So here's the Safe Mode proof version of my code (csv part removed):
> [...]
>> $workbook =& $renderer->getContainer();
>> $workbook->setTempDir($_SERVER['DOCUMENT_ROOT'].'/temp/');
> [...]
>
> I now remember that I had the same problem sometime last year. Can you
> please write a feature request? I think it would be a good idea to add a
> new option for the temporary directory and to document the need to set
> this option when safe_mode is enabled.

Ok I have done this.

Now with my real world data I still have the *** ERROR IN SST ***
problem that is already described in a bug for Spreadsheet_Excel_Writer,
but that should (according to a comment in the bug report) be solved in
the actual version when setting setVersion(8). Anyway the problem seems
to persist.

Is there a known solution to this now? Or do I just forget something?
Here's the code, that works for small data, but breaks with bigger
amounts (5-column person list, breaks from line 89):

<?php
$options = array('encoding' => 'utf-8', 'filename' => 'test.xls');
$renderer =& $datagrid->setRenderer(DATAGRID_RENDER_XLS, $options);
$workbook =& $renderer->getContainer();
$workbook->setTempDir('path/to/dir/');
$workbook->setVersion(8);
$datagrid->render($renderer);
?>
 |  Next  |  Last
Pages: 1 2
Prev: MDB2 and MSSQL
Next: Quickform: how to check two fields in one function using registerRule()