PHP and Spreadsheets Part 1

HTML gives us a limited ability to present data to our users. Tables are useful, but if you want to give users the ability to delete rows, order columns or adjust the formatting at all, you need to program the PHP/CSS code behind it to do all of this. A simple solution to this is to have PHP present the data to the users in a format their spreadsheet program can understand. Now the users,assuming they’re at least slightly proficient with spreadsheets, can order and reformat the data as they see fit, save a copy and/or email it off to whomever. There are different ways you can accomplish this with PHP, depending, at least in part, on how fancy you want your spreadsheets to be before the users get them. One of the easiest methods, but with the least amount of control over formatting, is to send the users a CSV, or “comma-separated value,” file. A CSV file is just a plaintext file in which data is separated by commas and rows are separated by newline characters. Listing 1 shows a very simple technique you could use to implement CSV files.

The code starts off by sending a couple headers that tell the user’s browser that a plain-text CSV file is being sent to it and then outputs the data. The user’s browser will prompt the user to save or open the file. If *.csv files are associated with a spreadsheet program on the user’s computer, then the file will open in that program, otherwise the user may have to select an application for the purpose. The good thing about this method is that it’s simple to implement, but you have no control over the format of the data within the spreadsheet program. If you want any colours, rows or columns joined, you’re out of luck. A second method, which is often a more attractive to most developers, consists of using HTML tables to give you more control over the format of the data within the spreadsheet program. The HTML table method is similar to the CSV method in that you send some headers designating what type of file is being sent and then display your data in a table. Since you’re using HTML, you can join cells together (using the rowspan and colspan attributes), change font colours, alignment and decoration (bold, italic), colour the table cells, and so on. Any kind of decoration you can do with HTML will be recognized by the spreadsheet program. It should be noted that most spreadsheet programs will not recognize decorations created using CSS (cascading style sheets), though. Thus, if you want coloured or bold text, ou’re stuck using the old <font> and <b> tags instead of the CSS methods. Listing 2 shows a simple method of how a PHP script can send an HTML table to the user’s spreadsheet

program.