PHP and Spreadsheets Part 2

The two headers tell the user’s browser that it will be sending a file called file.sxc and the browser will prompt the user to open or save the file. Then a normal HTML table is created from the data and sent to the browser. The spreadsheet program (OpenOffice.org’c Calc in this case) will interpret the HTML table into a normal spreadsheet, along with any formatting you’ve included. If you’re operating in a Microsoft environment, you may want to modify the Content-disposition header to send a file name of file.xls so that it can be recognized by Excel (although Excel will open the file correctly even if it’s called file.sxc, you will have to tell it to do so). The benefits of the HTML table method are that it’s still simple to implement and it gives you more

control over the layout of the data in the user’s spreadsheet program. However, you’re still missing out on some of the advanced features that most spreadsheet programs implement. If you want to get into the advanced features of the spreadsheets, you’re left to discover how to use COM or figure out the file formats for everything. Luckily, though, other ingenious programmers

have already figured this out for you and make their code available to you online. Isn’t open source software great? One spreadsheet writer class that was recently recommended to readers of the php-general mailing list is the PEAR package Spreadsheet_Excel_Writer

(SEW), which can be found at :

http://pear.php.net/package/Spreadsheet_Excel_Writer

While learning the API of this class and implementing it may be more difficult at first than the other methods, it offers you interfaces to a lot more spreadsheet features than any

other approach we have seen so far. Listing 3 shows a simple example from the SEW documentation of how to create a spreadsheet. The class also provides method to

implement spreadsheet features such as text decoration and alignment, formulas, multiple worksheets, page properties, notes,images, zoom and many other things. You can send the file directly to the user, as the earlier examples to, or save it to the server.

OpenOffice.org doesn’t have any trouble opening Excel files, either, so you’re not locked into using Microsoft Excel as your spreadsheet program. Depending on your needs and how fancy of a spreadsheet you need to deliver to your users, either the CSV method, HTML table method or the Excel_Writer class can hopefully solve your problems. If you have a better solution than

the ones presented here or any other PHP tips to solve your troubles and get them published here. One other quick tip that’s been mentioned before but is related to the above has to deal with Internet

1 <?php

2 require_once ‘Spreadsheet/Excel/Writer.php’;

3

4 // Creating a workbook

5 $workbook = new Spreadsheet_Excel_Writer();

6

7 // sending HTTP headers

8 $workbook->send(‘test.xls’);

9

10 // Creating a worksheet

11 $worksheet =& $workbook->addWorksheet(‘My first worksheet’);

12

13 // The actual data

14 $worksheet->write(0, 0, ‘Name’);

15 $worksheet->write(0, 1, ‘Age’);

16 $worksheet->write(1, 0, ‘John Smith’);

17 $worksheet->write(1, 1, 30);

18 $worksheet->write(2, 0, ‘Johann Schmidt’);

19 $worksheet->write(2, 1, 31);

20 $worksheet->write(3, 0, ‘Juan Herrera’);

21 $worksheet->write(3, 1, 32);

22

23 // Let’s send the file

24 $workbook->close();

25 ?>

Listing 3

1 <?php

2

3 $data = array(array(1,2,3),array(‘one’,’two’,’three’));

4

5 header(‘Content-type: application/octet-stream’);

6 header(‘Content-disposition: attachment; filename=file.sxc’);

7

8 $count = 1;

9 $row_count = count($data);

10 echo “<table>\n”;

11 foreach($data as $row) {

12 echo “<tr>\n”;

13 echo ‘<td>’.implode(“</td>\n<td>”,$row) . “</td>\n”;

14 echo “</tr>\n”;

15 }

16 echo ‘</table>’;

17 ?>

Explorer downloading file in specific instances. If you are using SSL and sessions, Internet Explorer will report that it can’t find the file to download when you use any of the

above methods. The problem is because the default session handler sends a Cache-limiter header of nocache, which causes Internet Explorer to not download the file.

The fix to this is to either modify the session.cache-limiter setting in php.ini to public or place

session_cache_limiter(‘public’); in your code before you call

session_start().