Spreadsheet_Excel_Writer data output is damaged

I use Spreadsheet_Excel_Writer to generate .xls file and it works fine until I have to deal with a large amount of data. On certain stage it just writes some nonsense chars and quits filling certain columns. However some columns are field up to the end (generally numeric data)

I'm not quite sure how the xls document is formed: row by row, or col by col... Also it is obviously not an error in a string, because when i cut out some data, the error appears a little bit fur开发者_JAVA百科ther.

I think there is no need in all of my code

here are some essentials

$filename = 'file.xls';
$workbook = & new Spreadsheet_Excel_Writer(); 
$workbook->setVersion(8); 
$contents =& $workbook->addWorksheet('Logistics');
$contents->setInputEncoding('UTF-8');

$workbook->send($filename);

//here is the part where I write data down
$contents->write(0, 0, 'Field A');
$contents->write(0, 1, 'Field B');
$contents->write(0, 2, 'Field C');

$ROW=1;
foreach($ordersArr as $key=>$val){
  $contents->write($ROW, 0, $val['a']);
  $contents->write($ROW, 1, $val['b']);
  $contents->write($ROW, 2, $val['c']);

  $ROW++;
}
$workbook->close(); 

I had the same problem, I found this solution that works for me:

http://pear.php.net/bugs/bug.php?id=19284&edit=3

[2012-08-08 17:12 UTC] identit (Vincent Dubourg)

The solution is change in Root.php \ line 623 :

fwrite($FILE, pack("V", 1));

to

fwrite($FILE, pack("V", $num_sb_blocks));

the file is pear/OLE/PPS/Root.php in package OLE 1.0.0RC2 (beta)

I know it's an old post but I had the same problem and solved it reverting to Spreadsheet_Excel_Writer 0.9.2 and OLE-1.0.0RC1.

pear uninstall Spreadsheet_Excel_Writer-0.9.3
pear uninstall OLE-1.0.0RC2

pear install OLE-1.0.0RC1
pear install Spreadsheet_Excel_Writer-0.9.2

Hope this helps someone in the future.

Ok! I've found what was the problem. The thing I didn't mention is that i had to set encoding to UTF-8 and output russian text that has Cyrillic charset. So for me these lines were necessary

  $workbook->setVersion(8);
  ...
  $contents->setInputEncoding('UTF-8');

but S_E_W with setVersion(8) generated bad BIFF8 file, that messed up all my xls if the output exceeded a certain amount of bytes. It could not be opened in MS Office, and opened with damaged data in Oo...

The possible solution I've found in the web is changing the following lines

<...>\Spreadsheet\Excel\Writer\Workbook.php

$this->_codepage = 0x04E4

change value to 0x04E3 (code page for Windows-1251)

<...>\Spreadsheet\Excel\Writer\Format.php

$this->_font_charset = 0

change value на 0xCC (chrset ANSI Cyrillic).

That should do the trick for those who use Cyrillic letters. I'm about to try this out.

And, yes, this library is SO outdated. I'll be transferring to http://phpexcel.codeplex.com/ Thanks for advice

UPD: The solution above does not seem to work =\ And I havent found anywhere on the web patch or solution that does the trick, and the latest version (which is 0.9.2) does not solve the problem. So i presume this is still a BUG, that would never be fixed...

Spreadsheet_Excel_Writer is close to being deprecated by PEAR. I'd suggest you try using phpexcel - http://phpexcel.codeplex.com/ - instead.