Get Excel sheets names

Developers
, , ,
#1

Hi, how to get Excel sheets names?
What is best solution for you?
Is it possible with Python 3 in new versions?
Thank you

#2

Hi @Drbohlav ,

The Archilab package has a node to extract this information, perhaps you could look into that code.
Also, Python has a package called pandas which also has this functionality, but that isn’t OOTB for python so that is a bit of a hassle to install, especially when rolling out your script(s).

2 Likes
#3

Hi @Daan, at first I’m looking for solution without external Python libraries as Pandas or Openpyxl. It was possible in earlier versions with IronPython. I’m not sure if this is possible in version with Python 3.

1 Like
#4

Since we can’t see what you were doing before we can find a solution which mirrors the previous behavior. Best to post a functional code sample in the previous code base so we can help you more directly.

1 Like
#5

Hi @Drbohlav
maybe try the @GavinCrump method:

Check out the Sparrow package which has a node in it that can read worksheets and is in Python. It uses the microsoft interop utilities which are available as long as you have Excel installed I think.

Cheers

1 Like
#6

Hi @JacobSmall,

With IronPython2 this code worked fine:

import clr

clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')

from Microsoft.Office.Interop import Excel

#--------------------------------------------

app = Excel.ApplicationClass()
app.Visible = False
app.DisplayAlerts = False
wb = app.Workbooks.Open(IN[0])
ws = wb.Worksheets
wsNames = [w.Name for w in ws]
wb.Close()
app.Quit()
OUT = wsNames

After switch to CPython3 there is this Warning:

image
image872Ă—906 49.5 KB

image
image1866Ă—744 150 KB

#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
3 Likes