3

I have a working program which integrates python and Excel (thanks to xlwt). I am reading from a csv file and copying into an excel. While I do this, I also have a few formulae that I include using the formula module in xlwt. To be specific, the code is 

ws_xlwt.write(3,3, Formula("MMULT(MINVERSE(C11:D12),"+col_char+"2:"+col_char+"3)"), style)

col_char is an ASCII code generated in the loop. The array (C11:D12) contains data generated using the code:

ws_xlwt.write(10,2, Formula("SUMPRODUCT($C$36:$C$161,$C$36:$C$161)"), style)

When the program runs, it puts the formula in the particular cell correctly. But the cell displays the error "#VALUE" instead of the result. The interesting point is when I open the excel file, double click on this particular cell to edit it and hit enter without changing anything, the result is displayed. I am not sure if this is a python integration issue or if its an Excel problem. 

OS - Windows 7 64-bit
Python - 2.7 32-bit
Improve this question
0

1 Answer 1

Reset to default
1

My best guess is that it is due to the evaluation of the formula. I don't believe that xlwt evaluates the formulas as they are inserted, meaning that you get a value error because the results of the SUMPRODUCT do not exist when the MMULT function is evaluated. The reason it works when you double-click and hit enter is because Excel is now recalculating everything, giving you a valid result.

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

That's exactly what I thought too. So, I included wb_xlwt.save(os.path.splitext(file_name)[0] +".xls") in between the sumproduct and the mmult.. ws_xlwt.write(10,2, Formula("SUMPRODUCT("+col_char+"36:"+col_char+"161,$O$36:$O$161)"), style) wb_xlwt.save(os.path.splitext(file_name)[0] +".xls") ws_xlwt.write(3,3, Formula("MMULT(MINVERSE(C11:D12),"+col_char+"2:"+col_char+"3)"), style) This should effectively save the sheet and the book and then write to it again (I think) therefore eliminating the error. It still hasn't resolved the issue. so, in brief it now reads..
@groovyrv I don't think saving it will do the trick - you need to open it after you write the original formula in order to force Excel to calculate it (Excel needs to be involved because xlwt does not do any calculations itself).
@groovyrv Did a little more research, and it seems it is the case that you will need to open/close Excel in order for it to work. See this post (stackoverflow.com/questions/4198365/…), where the author indicates that is the issue. You will either need to split the script into pieces, opening Excel as necessary; or programmatically open/close it.
Thank you again for the help. I have been able to get it to work using openpyxl for now by directly putting the formula into the cell. Though I did figure out that I have a slightly bigger problem now (array formulae) which are not supported by any of the free excel readers. Maybe this question is for another thread. Thanks again.
@groovyrv Yeah, I would definitely not underestimate Python when it comes to doing calculations. There are some situations (e.g. a COUNTIF on a range of 100K rows) where Excel will take minutes (if it doesn't crash) while Python can do the equivalent in seconds. Plus, if you just care about the resulting data and don't need the underlying formula, you will gain efficiency on the Excel side in the form of the workbook not automatically calculating the formulas you add each time you change something.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.