1

I would like to try and make a program which does the following, preferably in Python, but if it needs to be C# or other, that's OK.

  1. Writes data to an excel spreadsheet
  2. Makes Excel recalculate formulas etc. in the modified spreadsheet
  3. Extracts the results back out

I have used things like openpyxl before, but this obviously can't do step 2. Is the a way of recalculating the spreadsheet without opening it in Excel? Any thoughts greatly appreciated.

Thanks,

Jack

Improve this question
2
  • If it needs to be C#, it might as well be IronPython :) Also, do you actually need it to work with MS Excel specifically? Or do you just need to do spreadsheet tasks with Python in general? Commented Oct 1, 2013 at 9:40
  • Hi Erik, I'd like to let someone less familiar with python/coding change how calculations are being performed, so I'd like to keep it to Excel or possibly LibreOffice. Commented Oct 1, 2013 at 9:54

2 Answers 2

Reset to default
1

You need some sort of UI automation with which you can control a UI application such as Excel. Excel probably exposes some COM interface that you should be able to use for what you need. Python has the PyWin32 library which you should install, after which you'll have the win32com module available.

See also:

If you don't necessarily have to work with Excel specifically and just need to do spreadsheet using Python, you might want to look at http://manns.github.io/pyspread/.

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

2 Comments

Thanks, two great suggestions. I would like to keep it to Excel (or possibly LibreOffice) as it is intended to be used by people less familiar with coding. I'll investigate Excel's COM interface.
I've added a link that contains an example automation session of Excel with Python, and it seems that it's exactly what you requested.
-1

you could you pandas for reading in the data, using python to recalculate and then write the new files.

For pandas it's something like:

#Import Excel file
xls = pd.ExcelFile('path_to_file' + '/' + 'file.xlsx')
xls.parse('nyc-condominium-dataset', index_col='property_id', na_values=['NA'])

so not difficult. Here the link to pandas.

Have fun!

Improve this answer

2 Comments

Thanks for the suggestion. Unfortunately the idea is to let someone with less experience with Python be able to change how the calculations are performed in the Excel, so it really needs to recalculate in Excel.
This is not answering the specific question that was asked

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.