excel_writer数据输出被破坏。

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)

我使用Spreadsheet_Excel_Writer生成.xls文件，它可以正常工作，直到我不得不处理大量的数据。在某些阶段，它只是写一些无意义的字句，然后放弃填充某些列。然而，有些列直到最后都是字段(通常是数值数据)

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

我不太确定xls文档是如何形成的:一行一行，或col by col…而且它显然不是字符串中的错误，因为当我删除一些数据时，这个错误会出现得更远一点。

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

4 个解决方案

#1

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)

[2012-08-08 17:12 UTC] identit(文森特·杜布)

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

解是根的变化。php \ line 623:

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

来

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

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

这个文件是梨/ OLE / PPS /根。php包OLE 1.0.0RC2 (beta)

#2

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.

我知道这是一篇老文章，但我遇到了同样的问题，于是我将它恢复到Spreadsheet_Excel_Writer 0.9.2和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.

希望这对未来的人有所帮助。

#3

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

好的!我找到问题所在了。我没有提到的是，我必须将编码设置为UTF-8并输出具有Cyrillic字符集的俄文文本。所以对我来说，这些线是必要的。

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

但是使用setVersion(8)的S_E_W生成了糟糕的BIFF8文件，如果输出超过一定数量的字节，就会把所有的xls都搞砸。无法在MS Office中打开，在Oo中打开数据损坏…

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

我在web上找到的可能的解决方案是更改以下几行

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

<…> \ Excel电子表格\ \ \ Workbook.php作家

$this->_codepage = 0x04E4

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

将值更改为0x04E3 (Windows-1251的代码页)

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

<…> \ Excel电子表格\ \ \ Format.php作家

$this->_font_charset = 0

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

变化值на0 xcc(chrset ANSI斯拉夫字母)。

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

是的，这个图书馆太过时了。我将转到http://phpexcel.codeplex.com/谢谢你的建议

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

UPD:上面的解决方案似乎不起作用=\，我还没有在web补丁或解决方案中找到能起作用的地方，而最新的版本(0.9.2)并不能解决问题。所以我认为这仍然是一个BUG，永远无法修复。

#4

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

Spreadsheet_Excel_Writer几乎被PEAR弃用了。我建议您尝试使用phpexcel—http://phpexcel.codeplex.com/—来代替。

#1