Page 1 of 1
Accessing Microsoft Excel
Posted: Thu Aug 17, 2023 11:27 pm
by Cey42
I am trying to use the scripting to read data from Excel. My problem is I cannot even get basic access to Excel. Here is what I have tried
Code: Select all
import clr
clr.AddReference("Microsoft.Office.Interop.Excel")
That fails with "Could not add reference to assembly Microsoft.Office.Interop.Excel"
Then I tried using openpyxl
Code: Select all
import sys
sys.path.append('C:\\Program Files (x86)\\Microsoft Visual Studio\\Shared\Python37_64\\Lib\\site-packages')
import openpyxl
workbook = openpyxl.Workbook()
That gives the following error "AttributeError: 'ColorDescriptor' object has no attribute 'to_tree'"
Any ideas of what I am doing wrong or other things to try?
Thanks,
Cey
Re: Accessing Microsoft Excel
Posted: Fri Aug 18, 2023 12:32 pm
by admin
Hi,
not something I have tried before from IronPython...
My first thought for the first issue is that clr.AddReference() may be expecting the assembly to already be loaded, so may fail if the interop assembly is not loaded. Maybe you need to point it to the dll on disk? There is some info here :
https://ironpython.net/documentation/dotnet/dotnet.html
cherees,
Robin
Re: Accessing Microsoft Excel
Posted: Thu Aug 24, 2023 10:46 pm
by Cey42
I have made some progress on this but now have run into another issue. I am trying to connect to the current running instance of Excel. I do not want to create a new instance. Here is the current code
Code: Select all
import sys
import clr
clr.AddReferenceToFileAndPath(r"C:\Program Files (x86)\Microsoft Visual Studio\Shared\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Excel.dll")
import Microsoft.Office.Interop.Excel as Excel
from System.Runtime.InteropServices import Marshal
Excel = Marshal.GetActiveObject("Excel.Application")
I now get the error: AttributeError: 'type' object has no attribute 'GetActiveObject'
From searching around, this seems to be because the IronPython SharpCap is using .NetCore vs .NetFramework which has GetActiveObject. I have tried other solutions but run into the same issue.
Not sure if the above info gives any ideas of what else I can try.
Re: Accessing Microsoft Excel
Posted: Fri Aug 25, 2023 12:16 pm
by admin
Hmm,
how about something like this :
https://stackoverflow.com/a/65496277/653430
The solution there may not be possible easily in pure IronPython, but could easily be put into a small C# DLL that is then used from IronPython.
cheers,
Robin
Re: Accessing Microsoft Excel
Posted: Sun Aug 27, 2023 11:08 pm
by Cey42
After more searching, I finally found a way to do this. Here is the code for reference
Code: Select all
import clr
clr.AddReferenceToFileAndPath(r"C:\Program Files (x86)\Microsoft Visual Studio\Shared\Visual Studio Tools for Office\PIA\Office15\Microsoft.Office.Interop.Excel.dll")
import Microsoft.Office.Interop.Excel as Excel
from Microsoft.Office.Interop.Excel import Application
from System.Runtime.InteropServices import Marshal
excel_app = Marshal.BindToMoniker("C:\\temp\\test.xlsx")
wb = excel_app.Application.ActiveWorkbook
sh = wb.ActiveSheet
...
excel_app = None