I'm working on getting data from a database into a spreadsheet format for a customer. I've decided to use the PEAR package "Spreadsheet_Excel_Writer" since it seems it's just what I need. Unfortunately, if I run my code, I get a valid XML file, it's just empty contents. Nothing makes it in there. If I dump the object, I get what's expected; however, no data.

Anyway, it seems as though there's something wrong (I'm not sure what) with how the XLS file is created. The sheet name should be the date (according to my code and the object dump), the workbook is the petition name. When I download it, the filename is perfectly fine, but the petition name is the worksheet name, and my data is invisible.

Not sure what's going on, but here's my code. Can anyone see what's going wrong with this? I'm at my wits end.

<?php

include('../../includes/functions.php');

checkLoginStatus($access['admin']);

$rowHeaders = array('id', 'Last Name', 'First Name', 'Email', 'Comments');

$filename = $_SESSION['petition_sigs_dl']['name'];

if($_SESSION['petition_sigs_dl']['format'] == 'xls')
{
	$filename .= '.xls';
    require_once('Spreadsheet/Excel/Writer.php');

// Create a new workbook
$xls = new Spreadsheet_Excel_Writer();

// Send proper headers to the browser
$xls->send($filename);

$bold = $xls->addFormat();
$bold->setBold();

// Create a worksheet
$sheet = $xls->addWorksheet(date('M j, Y'));

for($c=0; $c<count($rowHeaders); $c++)
{
	$sheet->write(0, $c, $rowHeaders[$c], $bold);
}

$i=1;
foreach($_SESSION['petition_sigs_dl']['signatures'] as $sig)
{
	$sheet->write($i, 0, $sig['id']);
	$sheet->write($i, 1, $sig['lname']);
	$sheet->write($i, 2, $sig['fname']);
	$sheet->write($i, 3, $sig['email']);
	$sheet->write($i, 4, $sig['comments']);

	$i++;
}

// Send the file to the browser
$xls->close();
}
elseif($_SESSION['petition_sigs_dl']['format'] == 'csv')
{
	$filename .= '.csv';

$file = '"' . implode('","', $rowHeaders) . '"' . "\r\n";

foreach($_SESSION['petition_sigs_dl']['signatures'] as $sig)
{
    $file .= '"' . $sig['id'] . '", "' 
			  . $sig['lname'] . '", "' 
			  . $sig['fname'] . '", "' 
			  . $sig['email'] . '", "' 
			  . $sig['comments'] . '"' . "\r\n";
}

header('Content-Type: text/csv');
header('Pragma: no-cache');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Pragma: no-cache');
header('Expires: 0');
header('Cache-Control: no-store, no-cache, must-revalidate');
header('Cache-Control: pre-check=0, post-check=0, max-age=0');
header('Content-Description: File Transfer');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Content-Length: ' . filesize('./tmp/' . $filename));
header('Content-Transfer-Encoding: none');

echo $file;
}

Thanks for any help anyone can give me.

The CSV section works perfectly fine. The data is printed, and I can import into excel. It would be nice to get the excel part working though.

    According to the documentation, you must assign by reference when instantiating a worksheet:

    $sheet =[color=red]&[/color] $xls->addWorksheet(date('M j, Y'));

    Hope that's all it is...?

      Except I'm using php5.... so it shouldn't matter.. and even if I do assign by reference, it still doesn't work.

      I may just have to run through it and update it for php 5.... this could be a fun day 🙁

        Yeah, I couldn't figure out why that should matter in PHP5 (admittedly I did not dig all that deep), but I figured since the documentation claims to be recent, maybe there was a reason. Good luck....

          Gave up on it for now. Can't see a good reason for using it when I can just use a CSV file. I think I'll email the maintainer and get him to update it for Office 7 (XP) & 8 (2007) and php 5.

            a month later

            bpat - just saw your post-
            I have the pear excel spreadsheet writer working fine with php5

            do you want me to dig it up and see if I did anything different ?

            (the simplest gotcha is that it wont overwrite or create the new file if you have the previous 'same name' file open in excel)

              It looks like you are sending the file to the browser before you populate it with data

              move the following

              	// Send proper headers to the browser
	$xls->send($filename);

              so it shows just before the

              	// Send the file to the browser
	$xls->close();

              That should start it working.

                No, $xls->send() just sends headers, not any data.

                The issue was that the server I was trying to do this on had open_basedir in effect and so I couldn't write to /tmp on the system, so I had to create my own "temporary" folder to write to. Once I did that, it worked perfectly fine.

                  20 days later

                  I figure that the library is not able to cache the spreadsheet to the hard drive. I have tried several different methods and folders to have "Write" access but nothing works.

                  I create a temp folder in wwwroot and one in the root c:\ directory; neither worked. I used the setTempFile and that didn't work.

                  How did you get yours to work?

                  Thanks,
                  Steve

                    I created a folder where I had the script that was using spreadsheet excel writer. I then made sure it had read/write permissions set (777). I then used $workbook->setTempDir() to set the temporary directory to use the newly created folder.

                      Write a Reply...