Facing problem with XLWT and XLRD - Reading and wr

Question

I am facing a problem with xlrd and xlwt. Pasting the sample code below.

from xlwt import Workbook, Formula, XFStyle
import xlrd

book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
myFontStyle = XFStyle()
myFontStyle.num_format_str = '0.00'
sheet1.write(0,0,10, myFontStyle)
sheet1.write(0,1,20, myFontStyle)
sheet1.write(1,0,Formula('AVERAGE(A1:B1)'), myFontStyle)
book.save('formula.xls')

wb = xlrd.open_workbook('formula.xls')
sh = wb.sheet_by_index(0)
for rownum in range(sh.nrows):
    print sh.row_values(rownum)

The idea is to write some values to Excel file, have some excel specific functions like LogNormal, StdDev etc and read the calculated values using XLRD.

By running the above code, I get the following results which are undesirable:-

[10.0, 20.0]
[u'', '']

Ideally I should have got 15 on the second row. It writes the Excel perfectly when I open it but XLRD does not return the results. I am stuck with this for a very critical project. Request you to kindly respond earliest.

Thanks and Regards Tarun Pasrija

Answer 1

Here is the answer I gave to the same question on the python-excel google-group yesterday.

[Background: I'm the author/maintainer of xlrd and the maintainer of xlwt]

Neither xlrd nor xlwt contains a formula evaluation engine. This is in common with other packages which are free (in any sense) and written in an interpreted language. This is documented in the tutorial that you can download via http://www.python-excel.org ... see pages 17 and 36.

If you break up your script into two pieces, execute the first, open the result XLS file with Excel/OOo calc/Gnumeric, [may need to hit F9 here to recalculate], save again, execute the 2nd script piece: xlrd will display the results.

Other possibilities:

(1) Ask on the gnumeric mailing list if it is possible to drive gnumeric programmatically to the extent of: open named XLS file, [re-]calculate all formulas, save as named XLS file (with the recalculated formula results included -- it is necessary to stress this because the last time I asked, the native gnumeric file format did NOT included the calculated formula results).

(2) You could ask the same question about Openoffice.org's calc program, perhaps on news:comp.lang.python and/or www.stackoverflow.com ... it has a set of APIs called "PyUNO"; last time I looked, most folk gave up trying to wade through and understand the humungous quantity of documentation. Any better news would I'm sure be gratefully received in the Python+spreadsheet world.

(3) "LogNormal" (I presume you mean LOGNORMDIST and/or LOGINV) and "StdDev" are scarcely Excel-specific. You could calculate your own results in Python; xlwt would need to be augmented to allow the caller to supply a result value for a Formula cell, instead of plugging in an invariant zero-length string as you noticed.

(4) Tell us your higher-level objectives ... we may be then able to come up with other suggestions.

Is this "very critical project" academic / for a charity / for a commercial enterprise? Have you considered Resolver One (http://www.resolversystems.com/products/resolver-one/) ? AFAIK, they offer discounts and their product is dirt-cheap anyway at about USD 100 per licence.

Answer 2

You need to open it in Excel and resave it before running the second bit. xlwt/xlrd don't actually work out the formula themselves, so you'd need Excel to do that. I've just tested with OpenOffice, and it works after resaving the file.

Also, please tell me you are not using this method just to calculate an average? You can do that in Python very easily:

average = float(sum(mynumbers))/len(mynumbers)

(In Python 3, you can leave out the float())