Get Excel sheets names

Developers
, , ,
7

Hello,
using COM objects is more difficult with PythonNet, here some solutions

image
image1520×458 70 KB

  • without Excel Interrop using OpenXml (Dynamo has integrated this lib since 2.10, need to be confirm)
import sys
import clr
import System
from System import Array
from System.Collections.Generic import List, IList, Dictionary

clr.AddReference('DocumentFormat.OpenXml')
import DocumentFormat
from DocumentFormat.OpenXml import *
from DocumentFormat.OpenXml.Packaging import *
from DocumentFormat.OpenXml.Spreadsheet import *

filepath = IN[0]

spreadsheetDoc = SpreadsheetDocument.Open(filepath, False)
workbookPart = spreadsheetDoc.WorkbookPart
sheetsName = [s.Name.Value for s in spreadsheetDoc.WorkbookPart.Workbook.Sheets]
spreadsheetDoc.Close()

OUT = sheetsName
  • using Excel Interrop and Net Reflection
import sys
import clr
from System import Environment 
try:
    from System.Reflection import BindingFlags
except:
    clr.AddReference("System.Reflection")
    from System.Reflection import BindingFlags

clr.AddReference('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal

filepath = IN[0]

emptyargs = ()
app = Excel.ApplicationClass()
app.Visible = False
app.DisplayAlerts = False
workBooks = app.Workbooks
wb = workBooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, None, workBooks, (filepath, ))
ws = wb.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, None, wb, None)
wsNames = [w.GetType().InvokeMember("Name", BindingFlags.GetProperty, None, w, None) for w in ws]
wb.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, None, wb, emptyargs)
app.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, None, app, emptyargs)
if wb is not None:
    Marshal.ReleaseComObject(wb)
if app is not None:
    Marshal.ReleaseComObject(app)
wb = None
app = None

OUT = wsNames
  • using Excel Interrop casting each object to the correct Interface
import sys
import clr
from System import Environment 

clr.AddReference('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal

filepath = IN[0]

ex = Excel.ApplicationClass()
ex.Visible = False 
workbook = Excel.Workbooks(ex.Workbooks).Open(filepath)
all_Sheets = Excel.Sheets(workbook.Worksheets)
all_sheets_name = [Excel.Worksheet(ws).get_Name() for ws in all_Sheets]
#
allworkbooks = Excel.Workbooks(ex.Workbooks)
allworkbooks.Close()
ex.Quit()
if workbook is not None:
    Marshal.ReleaseComObject(workbook)
if ex is not None:
    Marshal.ReleaseComObject(ex)
workbook = None
ex = None
OUT = all_sheets_name
11 Likes