I was wondering if instead of going and learning VBA which I dont really like, can I do something similar with python? For windows there is DataNitro which looks like what I am referring to, but I have a Mac, is there any other way? Is datanitro the only option? I'm not looking to start a debate, just some option if any.
3 Answers
http://www.ironpython.info/index.php?title=Interacting_with_Excel
Many people use the IronPython tool, which is .NET compatible or something like that.
Excel (which exposes a COM automation interface) is accessible to .NET languages like IronPython through the .NET COM interop facilities.
import clr
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0,
Culture=neutral, PublicKeyToken=71e9bce111e9429c') from Microsoft.Office.
Interop import Excel
ex = Excel.ApplicationClass()
ex.Visible = True
ex.DisplayAlerts = False
workbook = ex.Workbooks.Open('foo.xls')
ws = workbook.Worksheets[1]
print ws.Rows[1].Value2[0,0]
Comments
You can find a lot of Python modules to manipulate Excel file here : https://pypi.python.org/pypi?%3Aaction=search&term=excel&submit=search
In particular, xlrd (read Excel files) and xlwt (write Excel files).
Comments
This is possibly not the most elegant way and may raise issues when opening the data again in excel, but you could save it as a csv and then:
import csv
Documentation: https://docs.python.org/2/library/csv.html
can I do something similar with python?Yes