Hi, how to get Excel sheets names?
What is best solution for you?
Is it possible with Python 3 in new versions?
Thank you
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
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
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
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
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:
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
10 Likes
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
2 Likes
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.
Edit 22/05/2024
you can also test with the DynamoIronPython3 package, but there is currently a bug with COM interfaces (bug only in Dynamo)
it’s fixed now
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.
1 Like
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
3 Likes
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.
2 Likes