Create a family library document (export Excel with PreviewImage)

[simon_murphy1]

Hi I am trying to create to a document like the image below. It has a preview image of the family in the first column. The type name in the second column. And the file path of the family document in the third column.

I have managed to get all the pieces required to build the family library document. The difficult part is exporting this information to a suitable place. The image below uses a Bumblebee package workflow to embed the images in the Excel document but it is not stable and the images if made larger overlap so large or small not useful.

I was hoping GoogleSheets might work but BIMPlus Googlesheet package can’t embed images just image URLs and I can’t automate uploading lots of images. So I think that is also not a solution.

So I am thinking of creating a drafting view and importing images and creating text notes. But I would prefer an external document to avoid opening a Revit project. Does anyone know of a way of getting this information from Dynamo into a document for easy viewing?

Any suggestions welcome.

Writing Preview Images to Excel.dyn (93.9 KB)

Script contents: Python get a list of furniture family types. Python get family folder location. Python get Preview image of Family. Export Images. Create Excel. Bumblebee Embed images.

Edit: Would it be possible to use IronPython Microsoft Word Interop to Embed images in word. I have limited python skills also on my work computer I can not install extra Python modules.

[architectcoding]

I think the excel approach is good enough, no need to jump into word. Check this question in stackoverflow and resizing images in excel:

Resize image after insert in excel sheet

Or if you just want to give a try to word:

https://stackoverflow.com/questions/38227689/office-interop-word-how-to-add-a-picture-to-document-without-getting-compressed#:~:text=The%20common%20way%20to%20insert,Add()%3B%20Range%20docRange%20%3D%20wordDoc.

[jacob.small]

You can already do larger images… why not make the excel lines larger so they don’t overlap?

[simon_murphy1]

When you adjust the row height it stretches and distorts the image. It doesn’t remove the overlapping either. But I could possibly do something about that with an Excel macro I could buy except the images keep disappearing. So I am going to investigate exporting to Word. I will post here if I get something useful.

[c.poupin]

Hello,
an example using Clipboard and Paste

# coding: utf-8 
import clr
clr.AddReference('ProtoGeometry')
from Autodesk.DesignScript.Geometry import *

clr.AddReference('RevitAPI')
import Autodesk
from Autodesk.Revit.DB import *

clr.AddReference('RevitServices')
import RevitServices
from RevitServices.Persistence import DocumentManager

doc = DocumentManager.Instance.CurrentDBDocument
uiapp = DocumentManager.Instance.CurrentUIApplication
app = uiapp.Application
uidoc = uiapp.ActiveUIDocument


import System
from System import Environment
from System import Array
from System.Collections.Generic import *

clr.AddReference('System.Drawing')
import System.Drawing
from System.Drawing import *
clr.AddReference('System.Windows.Forms')
from System.Windows.Forms import Clipboard


clr.AddReferenceByName('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


class ExcelUtils():
	def __init__(self, expSettings, filepath, sizebitmap):
		self.expSettings = expSettings
		self.filepath = filepath
		self.sizebitmap = sizebitmap
		print self.filepath
		
		
	def exportXls(self):
		
		ex = Excel.ApplicationClass()
		ex.Visible = True
		ex.DisplayAlerts = False
		workbook = ex.Workbooks.Add()
		workbook.SaveAs(self.filepath)
		ws = workbook.Worksheets[1]	
		nbr_row = len(self.expSettings)
		nbr_colum = len(self.expSettings[0])
		#
		xlrange  = ws.Range[ws.Cells(1, 2), ws.Cells(nbr_row, nbr_colum )]
		xlrange.EntireRow.RowHeight  = self.sizebitmap.Height
		ws.Range("a1").EntireColumn.ColumnWidth = self.sizebitmap.Width / 4
		a = Array.CreateInstance(object, nbr_row, nbr_colum - 1)
		#
		for indexR, row in enumerate(self.expSettings):
			for indexC , value in  enumerate(row):
				if indexC == 0 and value is not None:
					Clipboard.SetDataObject(value)
					rng  = ws.Range[ws.Cells(indexR + 1, 1), ws.Cells(indexR + 1 , 1)]
					ws.Paste(rng, False)
					
				else:
					a[indexR, indexC - 1] = value
				
		#copy Array in range			
		xlrange.Value2 = a		
		used_range = ws.UsedRange	
		for column in used_range.Columns:
			column.AutoFit()
			
toList = lambda x : x if hasattr(x, '__iter__') else [x]		
listcat = toList(UnwrapElement(IN[0]))			
#make filter			
lstbipCat = [System.Enum.ToObject(BuiltInCategory, x.Id.IntegerValue) for x in listcat]			
filtercat = ElementMulticategoryFilter(List[BuiltInCategory](lstbipCat))
#collector
fecSymb = FilteredElementCollector(doc).OfClass(FamilySymbol).WherePasses(filtercat).ToElements()
outdata = []
imgSize = Size( 100, 100 )
for symb in fecSymb:
	bitm = symb.GetPreviewImage(imgSize) 
	famName = symb.Family.Name
	symbName = Element.Name.GetValue(symb)
	outdata.append([bitm, famName, symbName])
	
#define folder to export		
directory = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

objXls = ExcelUtils(outdata, directory + '\\test.xls', imgSize)
objXls.exportXls()

OUT = directory

[uaftab.cem19]

Jacob a small question; can we make excel cells larger using dynamo so that they do not overlap? or we need to do it manually after the export command?

[jacob.small]

Not with the out of the box tools. Bumblebee package may have this available. Or you could preform at the spreadsheet in sheet2 and populate the contents with a formula that is ‘=sheet1cell’, and have Dynamo write data to sheet1. Then just do a save as every time before you write the data.

[simon_murphy1]

Unfortunately I don’t think the Bumblebee will work in this situation.

Are the images family preview images? I think @c.poupin clipboard and paste is only workable solution for getting the images into Excel in a stable way and a useable size.

What are the Revit parameters you want to export, I could try edit his Python script.

[Ahmad_Saad]

Hi Simon,
Would you be able to share your updated script according the copy/paste method?
Thanks in advance

[simon_murphy1]

I only slightly modified the python script in the solution above. Also I never solved the problem of the many pop-ups which make it not very useful. If I get a chance when at work I will copy the python here.

[simon_murphy1]

Creating an Excel Family Container Document_Sorted By Category_AllCats_#17.dyn (24.2 KB)

Edit: I think you have to press OK on every pop up to get it export the images. Is there someone who could help manage the pop-ups automatically?

[BI_2]

Hi Simon. I wanted to quickly hop on here and raise some questions. I noticed that you modified the script so it skips model text… For detail items, since filled regions are under detail items and filled regions actually aren’t families, it actually causes problems when detail items (OST_DetailComponents) is brought in. I am wondering how one would go about to filter out the filled regions, assuming that will have to be done in the script rather than the category.

BTW, I didn’t notice the pop-ups, I did however get errors and the data wouldn’t properly write on the spreadsheet. I later noticed that it only happens if you run the script more than one time, and errors will go away if you restart your pc. I know it has to do with the copy and paste mechanism in Microsoft.

[simon_murphy1]

I think it would be tricky to filter within a category because the way the script is written. But maybe you could do this step with nodes and include this list in the bit that starts the copy paste mechanism of the script which I don’t have the ability to adjust if this bit is causing a problem. Sorry can’t be more help I made this dynamo script a long time back and don’t use it anymore.

[BI_2]

Thank you for your reply Simon. I came across this thread because I am trying to achieve something similar that you were doing, except I need to add detail items to the mix. I tried to feed families directly into the script and didn’t get any errors but it doesn’t look like the families made it through the script.

I am tagging @c.poupin since they are the one who incorporated the copy and paste mechanism. Are you familiar with how one should go about filtering filled regions from detail items category (OST_DetailComponents)? I tried feeding detail item families directly into the script instead of the detail item category (to avoiding getting the error “AttributeError: ‘FilledRegionType’ object has no attribute ‘Family’”. Because initially it seems to me the reason I got the error was because the filled regions don’t have families, they are just types. But even feeding families directly into the script doesn’t produce anything. I didn’t get error this time but those families didn’t make it to the spreadsheet.

[simon_murphy1]

https://www.revitapidocs.com/2021.1/ffba76e9-45c5-6c7c-a409-d63e2f57f43d.htm

Filled regions might have a property that can be used to filter them out in the python script.

Edit: below

Create a new list.

FinalFamiliesList =

For famt in list familyTypes:
If famt.ForegroundPatternColor and famt. BackgroundPatternColor == null:
FinalFamiliesList.append(famt)

I’m sorry this kinda sketchy just doing it on my phone.

[BI_2]

This might work. I am still not sure why directly feeding the script with the detail items families don’t produce anything in the spreadsheet.

for famt in list familyTypes:

Would this line even work? Do you mean just “familyTypes”?
Edit:
So it gives me an error “NameError: name ‘familyTypes’ is not defined”

Edit 2:
So I don’t know if you are still on mobile, I made some changes to your script and I am going to paste part of the script here. I called fecsNew instead of familyTypes. And I got a new error message. “AttributeError: ‘PanelType’ object has no attribute ‘ForegroundPatternColor’”. Seems like panel style also belongs to the detail item category, it isn’t just filled region I need to get rid of.

#collector
fecSymb = FilteredElementCollector(doc).WherePasses
(filtercat).WhereElementIsElementType().ToElements()

fecNew = []

for fec in fecSymb:
    if fec.FamilyName == "Model Text":
        pass
    else:
        fecNew.append(fec)

catName = []
sortedsymbs = []

FinalFamiliesList = []

for famt in fecNew:
    if famt.ForegroundPatternColor and famt.BackgroundPatternColor == 'null':
        FinalFamiliesList.append(famt)

fecNew = FinalFamiliesList

for s in fecNew:
    fam = s.Family
    cat = fam.FamilyCategory
    catName.append(cat.Name)

[simon_murphy1]

Maybe post full python script here easier for people to debug

[BI_2]

Just did. I was in the middle of editing my post, my bad. Please see above.

[c.poupin]

Hello @BI_2
if your goal is to remove ‘FilledTypeRegion’ of the list
you can replace this line
fecSymb = FilteredElementCollector(doc).WherePasses(filtercat).WhereElementIsElementType().ToElements()
by this line
fecSymb = FilteredElementCollector(doc).OfClass(FamilySymbol).WherePasses(filtercat).ToElements()

i updated my previous post

Create a family library document (export Excel with PreviewImage)

Hello, an example using Clipboard and Paste [Screenshot_3] # coding: utf-8 import clr clr.AddReference('ProtoGeometry') from Autodesk.DesignScript.Geometry import * clr.AddReference('RevitAPI') import Autodesk from Autodesk.Revit.DB import * clr.AddReference('RevitServices') import RevitServices from RevitServices.Persistence import DocumentManager doc = DocumentManager.Instance.CurrentDBDocument uiapp = DocumentManager.Instance.CurrentUIApplication app = uiapp.Application uidoc = uiapp.…

[BI_2]

Hi @c.poupin, thank you for the reply. That line did it. Now I am finally getting what I wanted in the spreadsheet. The only thing that isn’t working is the preview image. The detail items preview image is shown as “X---------------X”. All of my detail items are 2D elements so I thought maybe that’s why (the 3D preview images are normal) but I noticed that other generic models that are 3D is also showing “X----------X” as preview image in the spreadsheet. I couldn’t quite pinpoint where it went wrong.