PHP and Excel

Today's adventure in PHP coding has been to read an Excel spreadsheet as data!

I've been doing PHP for just 10 years now. I've written all of my sites in my signature below by hand, in PHP and JavaScript.

Most of us can gather our Affiliate statistics from AEBN, or HotMovies or XTube and put them in .csv files. And we can read those .csv files with PHP scripts, and do arithmetic, or make charts and graphs.

But .csv files are when we take an Excel file and Save As .csv. And, we better not have any extra commas in text fields!!

Save As .csv is an extra step! So I took 2 hours today and made reading the Excel file itself work, the same way I read .csv files.

While that may or may not be that big of a deal with simple stats data, it is a big deal for other Excel spreadsheets I have, e.g. budgets, or projections, as those involve calculations. Having Cells calculate or do arithmetic on a number of other Cells is the basic purpose of an Excel spreadsheet in the first place.

This also gets me one step closer to stopping using MySQL! Already for the past 2 years, I have been keeping simple data tables saved in my working directory (as the Arrays themselves, not just .csv). Now, I will be able to keep more complex data tables, as local files, not in the fussy MySQL tables. And, I can download and save, and I can use between multiple Domains, when those Domains are on different Hosts.

I'm a bit amazed by the simplicity of 3 of my sites:

http://Porno-Mall.com
http://10kapps.com/film
http://10kapps.com/theatre

All 3 have about 50 lines of html. The rest is a small number of lines of PHP that read one or more .csv files of data, and row by row, prints text or Images.

Nothing hardcoded in the PHP script itself. The Excel spreadsheet is where all the data is, and it is much easier to maintain an Excel spreadsheet than it is to constantly edit hard-coded data in a PHP script.

Now, I can spend more time on graphics, than on coding, which is what I have been doing on the film and theatre pages, but need to do more of on Porno-Mall.

I'm not fully qualified to know about this, but using a spreadsheet of any size for any large volume of traffic may have a huge impact on performance. Unlike MySQL, using a spreadsheet as an input source by php may require that it is read in its entirety each time a browser calls up that page. There may be ways to use PHP to keep the spreadsheet resident in memory but that is probably not the default behavior. For a low traffic website, a seldom used web page or a small spreadsheet this can be a quick way to implement a solution.

Mine are definitely small. An array is easier to update and save (with the previous file saved as backup with a date added to its filename) and even download and carry in one's pocket... than MySQL

I've never lost a MySQL table, but I'm sure it's painful.

Just tackled reading .xls files with multiple worksheets! This saves time by not having to Save As .csv, and ensuring that fields with commas don't mess up the comma separated action, but also increases organization by having many sets of data in one file with multiple worksheets.