Hi all,
is there a way to omit importing hidden rows from an excel file?
Thank you.
Not offhand. Programmatically the data is still there after all - hiding things is just a modification of the display (like hiding Revit elements). This means you’ll need to rely more heavily on the COM interop to attempt this which breaks more often than anyone likes.
So before progressing, can you outline ‘why’ and give some background?
It may be as simple as adding a range to what you return.
What i have are some notes in excel that I will import into Revit and I would like to be able to hide/unhide notes when importing. I don’t want to delete them since we might want to add them in the future.
I might try and move them beyond my export range and bring them back after.
Instead of hiding them in excel, add another column for ‘include’ and just store a ‘yes’ or ‘no’ value in it. Setting up a data validation can help immensely here.
Then use that to filter the list in Excel - once marked no it won’t show up, but you can always edit the filters or even sort by ‘yes/no’ status to get to a particular note to toggle.
Then in Dynamo read everything and filter by the ‘yes’ value to get just the notes you want, and use the ‘in’ list to generate your content.
yeah, that’s what i’m doing now. will try and filter them by index.
1 Like
I’d use a boolean mask instead.
Depending on how your data is set up it could be as simple as List.FilterByBoolMask(notes, include==“yes”)[“in”];.
For some reason i’m getting this error and my filter by bool is not working.
I can see it working, very strange
null isn’t valid for boolean comparison. You’ll need to replace the nulls or perhaps try an equality check with the == node (not the one with a tolerance, but the exact equality check).
A solution using openpyxl
- Dynamo 2.12 : python package need to be installed
- Dynamo 2.16+ : python package is installed by default
import sys
import clr
from openpyxl import load_workbook
xls_path = IN[0]
sheet_name = IN[1]
wb = load_workbook(xls_path)
ws = wb[sheet_name]
data = []
for row in ws:
if ws.row_dimensions[row[0].row].hidden == False:
data.append([cell.value for cell in row])
OUT = data
Another solution if you are comfortable with OpenXml
import clr
import sys
import System
from System import Array
from System.Collections.Generic import *
clr.AddReference('DocumentFormat.OpenXml')
import DocumentFormat
from DocumentFormat.OpenXml import *
from DocumentFormat.OpenXml.Packaging import *
from DocumentFormat.OpenXml.Spreadsheet import *
def ReadExcel(filepath, sheetName="", read_hidden_rows=True):
lst_rows = []
fileStream = System.IO.FileStream(filepath, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite)
spreadsheetDoc = SpreadsheetDocument.Open(fileStream, False)
try:
workbookPart = spreadsheetDoc.WorkbookPart
if System.String.IsNullOrEmpty(sheetName):
sheet = workbookPart.Workbook.Sheets.GetFirstChild[Sheet]()
else:
sheet = next((s for s in workbookPart.Workbook.Sheets if s.Name.Value == sheetName), None)
print(sheet)
print([s for s in spreadsheetDoc.WorkbookPart.Workbook.Sheets])
# get all sheet Name
sheetsName = [s.Name.Value for s in spreadsheetDoc.WorkbookPart.Workbook.Sheets]
print(sheetsName)
worksheet = spreadsheetDoc.WorkbookPart.GetPartById(sheet.Id.Value).Worksheet
#
rows = worksheet.GetFirstChild[SheetData]().Elements[Row]()
if read_hidden_rows :
lst_rows = [row for row in rows ]
else:
lst_rows = [row for row in rows if not (row.Hidden is not None and row.Hidden.Value) ]
except Exception as ex:
print(ex)
lst_rows = []
finally:
spreadsheetDoc.Close()
fileStream.Dispose()
# REST of code to be completed to read cell values
# TODO
# TODO
return [x.GetType().ToString() for x in lst_rows]
xls_file_path = IN[0]
sheet_name = IN[1]
OUT = ReadExcel(xls_file_path, sheet_name, read_hidden_rows=False)
4 Likes
Thank you both. I went with “==” since I want other users to understand what’s going on in the script as well 
2 Likes