I use python to handle excel spreadsheets (the only limitation is .xls , you can't use .xlsx (i didn't really check).
python module: xlrd
description: A Python module for extracting data from MS Excel ™ spreadsheet files.
if you're on a shared server you can't install the module into the same root folder that python will be looking for it in, so you need to append the path and put your files into www/py/scripts and www/py/xlrd where www is the web root:
from the shell:
cat > XLS2HTML.py
sys.path.append( "/root_1/root_2/www/py/scripts" )
sys.path.append( "/root_1/root_2/www/py/xlrd" )
# where root_1 and root_2 are directories from the server root (it needs to be an absolute path I think)
from datetime import date,datetime,time
from xlrd import open_workbook,xldate_as_tuple
book = open_workbook('spreadhseets/weekly_specials.xls')
def w2html (str1ng):
#end function list
with open("outputhtml.html") as fyle:
for name in book.sheet_names():
print "<h1>" + name + "</h1>"
for row_index in range(0,book.sheet_by_name(name).nrows):
print "<tr> \n"
for col_n in range(0,book.sheet_by_name(name).ncols): #i might have switched columns and rows around, this might make your sheet transformed 90 degrees lol)
print "</table> \n"
this will print all of the cells for each sheet into a separate html table (i think, didn't actually run it but if I don't get it first the right time.
if you want it to write the code into the html file, change all of the print statements like this
print "" will now become w2html("")
hope this helps some, if you have dates inside of your xls file you need to convert them to a tuple first, like this
sheet = book.sheet_by_name(name)
cell = sheet.cell(row_index,col_n)
date_value = xldate_as_tuple(sheet.cell(row_index,col_n).value,book.datemode)
otherwise it will throw you a type error, if you want to ignore the dates, wrap the nested for loops inside a
all the for loops here.
I like it, if you're using enourmous xls file (like a gig or more) you should look into using on demand sheet calling. It's much much faster, but for my purposes using the above as a skeleton works.
if you want to pass a value to the python script it will get stored in sys.argv (like a sheet name?)
so calling python XLS2HTML.py week_8_menu.xls
will put the value "week_8_menu.xls" in sys.argv (maybe 1, i forget)
that way you don't need to edit the python script using pico or nano :p
php also has a way to let you open processes from a php script, you can read the process stream and it will echo all of the print lines in python or you can open the html table file that this script writes and echo the entire file (outputhtml.html). I actually use both, the process stream to show my users the error generated, and the htmlfile to show missing values inside the excel sheet. It lets them find errors in 30,000 rows of excel data very very fast, and having the data in an html table structure allows you to change it into an sql query with little effort.
There is a module to write to excel sheets (xlwd i think), but why would you want to.
good luck with the boss
Hope this helps.