Automation Excel from Python

问题:

In my company, we use Linux in development and production environment. But we have a machine running Windows and Excel because we use a third party application excel addin to get financial market data to the machine. The add-in provides some functions (just like Excel function) for getting these datas into the local machine and then sending back to a MySql Database. We've also developed some VBA script to automation the task but still not satisfy with the result. I'm considering using Python to do all these jobs, but before jumping in, i need to find a python package that can do

1. Use python to manipulate Excel (with its add-ins) and use its functions without opening Excel?
2. If I need to open Excel, I need to automate that task of executing the script every day, or in specific moment of the day (the market data need to be feed a specific time)

Thanks for suggestion

回答1:

You'll need the Python Win32 extensions - http://sourceforge.net/projects/pywin32/

Then you can use COM.

from win32com.client import Dispatch
excel = Dispatch('Excel.Application')
wb = excel.Workbooks.Open(r'c:\path\to\file.xlsx')
ws = wb.Sheets('My Sheet')
# do other stuff, just like VBA
wb.Close()
excel.Quit()

You can put your script on Windows Task Scheduler to run for the times you need.

回答2:

As an alternative, you might consider openpyxl.

import openpyxl
wb= openpyxl.Workbook()
ws = wb.get_active_sheet()
ws.title = 'My Title'
wb.save('C:\\Development\\Python\\alpha.xlsx')

Here's a chapter from the book I'm working through.

https://automatetheboringstuff.com/chapter12/

Luck