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

Erik Kaplun
  • 37,128
  • 15
  • 99
  • 111
Jack
  • 153
  • 2
  • 13
  • 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? – Erik Kaplun Oct 01 '13 at 09: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. – Jack Oct 01 '13 at 09:54

2 Answers2

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/.

Community
  • 1
  • 1
Erik Kaplun
  • 37,128
  • 15
  • 99
  • 111
  • 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. – Jack Oct 01 '13 at 09:51
  • 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. – Erik Kaplun Oct 01 '13 at 09:55
-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!

SirJohnFranklin
  • 57
  • 4
  • 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. – Jack Oct 01 '13 at 09:53
  • This is not answering the specific question that was asked – Ben May 29 '19 at 12:01