0

I know there is other ways or modules to read/write xls with python. I have seen Autodesk Dynamo (which use IronPython) can open xls with Microsoft.Office.Interop.Excel related codes. Hence I tried to do similar thing in PyCharm for testing purpose. However, some error pops.

I pick a computer that installed KMSpico for the Office. That computer installed Python 3.7.x I have installed pythonnet

I've tried the following code in PyCharm:

import clr
# clr.AddReference("Microsoft.Office.Interop.Excel") # System.IO.FileNotFoundException: Unable to find assembly 'Microsoft.Office.Interop.Excel'.
# I find a dll in C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c
clr.AddReference("Microsoft.Office.Interop.Excel, Culture=neutral, Version=15.0.0.0, PublicKeyToken=71e9bce111e9429c")
import Microsoft.Office.Interop.Excel as Excel
excel = Excel.ApplicationClass()
print(excel.Workbooks) # System.__ComObject
print(dir(excel.Workbooks)) # ['CreateObjRef', 'Equals', 'Finalize', 'GetHashCode', 'GetLifetimeService', 'GetType', 'InitializeLifetimeService', 'MemberwiseClone', 'Overloads', 'ReferenceEquals', 'ToString', '__call__', '__class__', '__delattr__', '__delitem__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__getitem__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__overloads__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__setitem__', '__sizeof__', '__str__', '__subclasshook__']
wb = excel.Workbooks.Open(r'C:\Users\user123\Desktop\source\text.xlsx') # AttributeError: '__ComObject' object has no attribute 'Open'

I am not sure why the dll has no Open function

Raii
  • 157
  • 6

1 Answers1

1

while looking for a solution I encountered your unanswered question. I needed to fill some gaps with try and error solutions. Anyway it worked ... --somehow--, and the reason was the return types of the generic type <class 'System.__ComObject'>. I think some of mapping-magic inside pythonnet doesn't go well here. I could not access the dlls in C:\Windows\assembly\GAC_MSIL. According to Import of DLL with pythonnet I used the following to load the dll:

import System
from System import Reflection
exceldll = "C:/Program Files (x86)/Microsoft Office/root/Office.../Microsoft.Office.Interop.Excel.dll"
Reflection.Assembly.LoadFile(exceldll)

After that the following imports work fine:

from Microsoft.Office.Interop import Excel
from Microsoft.Office.Interop.Excel import Workbooks, Sheets, Range, Worksheet

After starting the actual Exel-Apllication excelApp = Excel.Application(), I needed to force the object types from System.__ComObject to whatever needed

wbs = Workbooks(excelApp.Application.Workbooks)
wb = wbs.get_Item(1) # .Item() is not mapped, at least .get_Item() return correct type
wss = Sheets(wb.Sheets) # wb.Sheets woulb be generic type System.__ComObject
ws = Worksheet(wss.get_Item(1)) # leftmost worksheet 
cells = Range(ws.Cells)
singleCell = Range(cells.get_Item(3,5)) # even get_Item() returns System.__ComObject here
singleValue = singleCell.get_Value()

Also https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.applicationclass?view=excel-pia sais that this is reserved for internal use only. Thus, maybe instantiating your excel object via excel = Excel.Application() is safer.

Axel
  • 13
  • 2