Bug 47294 – PEAR-Spreadsheet_Excel_Writer produces multiple worksheet files that Excel 2000 can't open.

• Status: RESOLVED UPSTREAM
• Product: Gentoo Linux

[Description Dean 2004-04-08 18:08:14 UTC]

When using PEAR-Spreadsheet_Excel_Writer to create a multiple worksheet Excel file, the resultant file doesn't open under Microsoft Excel 2000.  However, the same file DOES open fine under KDE kspread and OpenOffice Calc.

Reproducible: Always
Steps to Reproduce:
1. Address the Bug 47293 issue by replacing the array_push with array_pop
2. Save the following program as example.php and run: php example.php

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

// We give the path to our file here
$workbook = new Spreadsheet_Excel_Writer('test.xls');
// Setup to use Excel 97 format
$workbook->setVersion(8);

// Create a long string.  Make sure it is greater than 255 characters...
//
$astring = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
$mystring = $astring.$astring.$astring.$astring.$astring.$string.$astring.$astring;

//Prepare the first sheet...
$mworksheet =& $workbook->addWorksheet('My Example');
$mworksheet->writeString(0, 0, 'Header 1');
$mworksheet->writeString(0, 1, 'Header 2');
$mworksheet->writeString(0, 2, 'Header 3');
$mworksheet->WriteString(0, 3, 'Header 4');
$mworksheet->setColumn(0,0, 50);
$mworksheet->setColumn(1,1, 20);
$mworksheet->setColumn(3,3, 50);

//Write to the spreadsheet
for ($row = 1; $row < 100; $row++)
{
	$mworksheet->writeString($row,0,$mystring);
	$mworksheet->writeURL($row,3,"http://www.dummy.org/".$mystring);
	$first = substr($mystring,0,1);
	$rest = substr($mystring,1);
	$mystring = $rest.$first;
}

//
// If I only do one spreadsheet, I am fine, but if I include the following, 
// the spreadsheet doesn't open under Microsoft Excel 2000.
//

//Prepare the second sheet...
$m2worksheet =& $workbook->addWorksheet('My Example 2');
$m2worksheet->writeString(0, 0, 'Header 1');
$mworksheet->writeString(0, 1, 'Header 2');
$mworksheet->writeString(0, 2, 'Header 3');
$mworksheet->WriteString(0, 3, 'Header 4');
$m2worksheet->setColumn(0,0, 50);
$m2worksheet->setColumn(1,1, 20);
$m2worksheet->setColumn(3,3, 50);

//Write to the spreadsheet
for ($row = 1; $row < 100; $row++)
{
	$m2worksheet->writeString($row,0,$mystring);
	$m2worksheet->writeString($row,3,$mystring);
	$first = substr($mystring,0,1);
	$rest = substr($mystring,1);
	$mystring = $rest.$first;

}
// We still need to explicitly close the workbook
$workbook->close();
?>


Actual Results:  
Program appears to run, and the resultant file opened up fine under OpenOffice
Calc.  However, when you open it up under Microsoft Excel 2000, Microsoft fails
to open it - and reports no error.

Expected Results:  
Excel 2000 should have been able to open the file.

While I have Excel 97 selected, the same problem exists under the Excel 95
format.  (If you commment out the setVersion statement, you get Excel 95 format.)

[Comment 1 Tal Peer (RETIRED) 2004-04-18 06:25:43 UTC]

Is this fixed in 0.7?

[Comment 2 Dean 2004-04-19 16:51:26 UTC]

No, the problem continues to happen even after upgrading to 0.7

[Comment 3 Tal Peer (RETIRED) 2004-04-20 03:35:49 UTC]

Upstream bug.
Please report it to http://pear.php.net/bugs/report.php?package=Spreadsheet_Excel_Writer

T