Get Excel sheets names

[Drbohlav]

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

[Daan]

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).

[Drbohlav]

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.

[jacob.small]

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.

[paris]

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.

Get Worksets from Excel

Hi @Konrad_K_Sobon , @c.poupin , @Jonathan.Olesen I have the same problem with version “2021.25.3”, I use an Excel version 2013 and Excel for Microsoft 365 in French language. Any thoughts? Excel files: “Microsoft Excel 2013 MSO 64 bits” ou “Microsoft Excel pour Microsoft 365 MSO 64 bit” ############### Dynamo 2.3.1 Revit 2020 ######### Dynamo 2.6.1 Revit 2021 [image] # Copyright(c) 2016, David Mans, Konrad Sobon # @arch_laboratory, http://archi-lab.net, http://neoarchaic.net impo…

Cheers

[Drbohlav]

Hi @jacob.small,

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:

[c.poupin]

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

• 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

[pyXam]

Thank you so much @c.poupin!!!

You are an absoloute legend.
upping to Cpython3 was also a topic in our workplace today and while investigating myself how to up our current read excel files scripts to stay hip with the cool kids.

As there are not currently any open source Cpython3 packages that i was able to rip from…Then i had to do it myself -_-

So…Obviously this was developed for my specific need but it does build on the above structure and may help someone who stumbles helplessly along this thread like me who is looking for some function similar to what @GavinCrump had developed for the community on IronPython2

So this was made and tested in*
Dynamo Core 2.13.1.3887
Dynamo Revit 2.13.1.5510
Revit 2023

#Made by pyXam
#https://forum.dynamobim.com/u/pyxam

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]
worksheetName = IN[1]

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)

# Check if the worksheet name exists in the Excel spreadsheet
worksheet = None
for w in ws:
    wsName = w.GetType().InvokeMember("Name", BindingFlags.GetProperty, None, w, None)
    if wsName == worksheetName:
        worksheet = w
        break

# Throw an error if the worksheet name was not found
if worksheet is None:
    raise ValueError("No worksheet with the name: {}".format(worksheetName))

# Retrieve data from the specified worksheet
sheet = worksheet.GetType().InvokeMember("UsedRange", BindingFlags.GetProperty, None, worksheet, None)
rows = sheet.GetType().InvokeMember("Rows", BindingFlags.GetProperty, None, sheet, None)
row1 = rows.GetType().InvokeMember("Item", BindingFlags.GetProperty, None, rows, (1,))
cells = row1.GetType().InvokeMember("Cells", BindingFlags.GetProperty, None, row1, None)
numCols = cells.GetType().InvokeMember("Count", BindingFlags.GetProperty, None, cells, None)

xclDataHeaders = []
xclDataListsTransposed = []

headers = []

for i in range(1, numCols + 1):
    cell = cells.GetType().InvokeMember("Item", BindingFlags.GetProperty, None, cells, (i,))
    header = cell.GetType().InvokeMember("Value2", BindingFlags.GetProperty, None, cell, None)
    headers.append(header)

xclDataHeaders.extend(headers)

dataLists = [[] for _ in range(numCols)]

for i in range(1, numCols + 1):
    column = sheet.GetType().InvokeMember("Columns", BindingFlags.GetProperty, None, sheet, (i,))
    columnCells = column.GetType().InvokeMember("Cells", BindingFlags.GetProperty, None, column, None)
    columnValues = [c.GetType().InvokeMember("Value2", BindingFlags.GetProperty, None, c, None) for c in columnCells][1:]  # Exclude the first cell
    dataLists[i - 1].extend(columnValues)

xclDataListsTransposed.extend(dataLists)

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 = xclDataHeaders, xclDataListsTransposed

[c.poupin]

I think using Net Reflection is the best option at the moment (compatible with all Python engines including the next version PythonNet v3.0.x)

you can also test with the DynamoIronPython3 package, but there is currently a bug with COM interfaces (bug only in Dynamo)

In any case, I recommend studying PythonNet 2.5 and PythonNet 3 (compared to IronPython2) before starting conversion.

[GavinCrump]

Slightly off topic, but given you’re amazing Python skills I was curious which resources you find best for studying up on these types of things? I appreciate once you get to a certain point, experimentation is a large part of it, but if there’s any nuggets of wisdom let me know! Always blown away by the depth of your knowledge in this space.

[c.poupin]

Github repos are a good source of information to track developments and issues (Ironpython, PythonNet, pyRevit, DynamoBim, ). the DynamoBim, BuildinCoder, etc. forums are also sources of inspiration.
However, I remain an eternal beginner who tests again and again

[GavinCrump]

If you are a beginner than I am an amoeba haha! Thanks, I have also begun to peruse a lot of githubs and dive into the contents of libraries as well. Keep up the awesome Pythoning, and can always appreciate the trait of humility.