Prev: MDB2 and MSSQL
Next: Quickform: how to check two fields in one function using registerRule()
From: michael.mauch on 27 Feb 2007 19:42
Markus Ernst wrote:

> 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.

Do you have an URL for that bug?

> 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);
> ?>

Could you write a complete example so we can try it? 5 columns and 89
lines seems to be still tiny.

Regards...
Michael
From: Markus Ernst on 28 Feb 2007 07:00
(Michael Mauch) schrieb:
> Markus Ernst wrote:
>
>> 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.
>
> Do you have an URL for that bug?

http://pear.php.net/bugs/bug.php?id=1572

> Could you write a complete example so we can try it? 5 columns and 89
> lines seems to be still tiny.

<?php

// Initiate datagrid
ini_set('include_path',
$_SERVER['DOCUMENT_ROOT'].'/tools/PEAR'.':'.ini_get('include_path'));
require_once 'Structures/DataGrid.php';
$datagrid =& new Structures_DataGrid();

// Create data with 200 records, 5 columns, unique strings
$data = array();
for ($i=0; $i<200; $i++) {
$data[] = array($i.'hello there', $i.'this', $i.'is', $i.'a', $i.'test');
}
$datagrid->bind($data);

// Set columns
$datagrid->addColumn(new Structures_DataGrid_Column('Column 1', 0));
$datagrid->addColumn(new Structures_DataGrid_Column('Column 2', 1));
$datagrid->addColumn(new Structures_DataGrid_Column('Column 3', 2));
$datagrid->addColumn(new Structures_DataGrid_Column('Column 4', 3));
$datagrid->addColumn(new Structures_DataGrid_Column('Column 5', 4));

// Attach XLS renderer
$options = array(
'filename' => 'errorInSSTTest.xls',
'encoding' => 'utf-8'
);
$renderer =& $datagrid->setRenderer(DATAGRID_RENDER_XLS, $options);

// Get the workbook object and set the temp directory and version
// Of course temp directory must be created manually and chmoded
// with writing permission for PHP
$workbook =& $renderer->getContainer();
$workbook->setTempDir($_SERVER['DOCUMENT_ROOT'].'/temp/');
$workbook->setVersion(8);

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

?>

When opening the resulting file,
- Excel shows a broken file warning and displays until row 105, Col 2
- OpenOffice starts displaying distorted characters at the same column,
and *** ERROR IN SST *** a few rows later.

Some more facts:
- It happens with utf-8 encoding, but not with default encoding
(iso-8859-1).
- As the example shows, it also happens if no special characters are in
the data.
- There seems to be a relationship between the total amount of text in
unique field contents: If you change the second column contents from
$i.'this' to 'this', the error starts in row 127; now change the third
column contents from $i.'is' to $i.'iss', then the error starts in row 123.

Do you think I should file a bug with this info? If yes, does it belong
to Structures_DataGrid_Renderer_XLS or rather to Spreadsheet_Excel_Writer?
From: michael.mauch on 28 Feb 2007 13:43
Markus Ernst wrote:

> When opening the resulting file,
> - Excel shows a broken file warning and displays until row 105, Col 2
> - OpenOffice starts displaying distorted characters at the same column,
> and *** ERROR IN SST *** a few rows later.

I can see garbage characters in OpenOffice, but no "ERROR IN SST". And
the lines after the garbage are missing.

> Some more facts:
> - It happens with utf-8 encoding, but not with default encoding
> (iso-8859-1).

I tried with many encodings, but the only working one was

'encoding' => ''

> - As the example shows, it also happens if no special characters are in
> the data.

Yes.

> - There seems to be a relationship between the total amount of text in
> unique field contents: If you change the second column contents from
> $i.'this' to 'this', the error starts in row 127; now change the third
> column contents from $i.'is' to $i.'iss', then the error starts in row 123.
>
> Do you think I should file a bug with this info? If yes, does it belong
> to Structures_DataGrid_Renderer_XLS or rather to Spreadsheet_Excel_Writer?

Yes, I think you should file a bug report, but I'm not sure for which
one. I see the same problem with Spreadsheet_Excel_Writer alone:

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$workbook->setVersion(8);

$worksheet =& $workbook->addWorksheet('My first worksheet');

// $worksheet->setInputEncoding('UTF-16LE');
// $worksheet->setInputEncoding('UTF-8LE');
// $worksheet->setInputEncoding('ISO-8859-7');
$worksheet->setInputEncoding('UTF-8');
// $worksheet->setInputEncoding('ISO-8859-1');
// $worksheet->setInputEncoding('windows-1251');

$data = array();
for ($i=0; $i<500; $i++) {
$data[] = array($i.'hello there', $i.'this', $i.'is', $i.'a', $i.'test');
}
for ($i=0; $i<500; $i++) {
for($j=0; $j<count($data[$i]); $j++) {
$worksheet->write($i, $j, $data[$i][$j]);
}
}
$workbook->close();
?>

There the problem is at line 198.

If I omit that $workbook->setVersion(8), it works. Alas, setVersion() is
not documented on
http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.php
and I can see in Spreadsheet/Excel/Writer/Workbook.php:

/**
* Sets the BIFF version.
* This method exists just to access experimental functionality
* from BIFF8. It will be deprecated !
* Only possible value is 8 (Excel 97/2000).
* For any other value it fails silently.
*
* @access public
* @param integer $version The BIFF version
*/
function setVersion($version)

On the other hand, Structures/DataGrid/Renderer/XLS.php has a line

$this->_workbook->setVersion(8);

And there's apparently no way to turn that off. Probably you really want
setVersion(8), because you wrote it in your example.

Regards...
Michael
From: Markus Ernst on 12 Mar 2007 09:16
(Michael Mauch) schrieb:
> Markus Ernst wrote:
>> Do you think I should file a bug with this info? If yes, does it belong
>> to Structures_DataGrid_Renderer_XLS or rather to Spreadsheet_Excel_Writer?
>
> Yes, I think you should file a bug report, but I'm not sure for which
> one. I see the same problem with Spreadsheet_Excel_Writer alone:

[code snipped]

> There the problem is at line 198.

Thank you, I used your code for the bug report. Anyway I found several
bugs on this same topic, so I did not file a new one, but added it to
bug #2010.

> If I omit that $workbook->setVersion(8), it works. Alas, setVersion() is
> not documented on
> http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.php
> and I can see in Spreadsheet/Excel/Writer/Workbook.php:
>
> /**
> * Sets the BIFF version.
> * This method exists just to access experimental functionality
> * from BIFF8. It will be deprecated !
> * Only possible value is 8 (Excel 97/2000).
> * For any other value it fails silently.
> *
> * @access public
> * @param integer $version The BIFF version
> */
> function setVersion($version)
>
> On the other hand, Structures/DataGrid/Renderer/XLS.php has a line
>
> $this->_workbook->setVersion(8);
>
> And there's apparently no way to turn that off. Probably you really want
> setVersion(8), because you wrote it in your example.

setVersion(8) seems to be necessary to make multibyte encodings work, if
I omit it, the XLS file does not break (as you mention above), but
non-ASCII characters are not properly encoded. It looks like so far
Spreadsheet_Excel_Writer (and along with it,
Structures_DataGrid_Renderer_XLS) is not applicable in UTF-8
environments, let's hope they get it fixed soon!
First  |  Prev  | 
Pages: 1 2
Prev: MDB2 and MSSQL
Next: Quickform: how to check two fields in one function using registerRule()