Export to Excel Image

Hi,
I was lookging at this post:

And used the Script to get my Families(name) and the images exported to excel. Does anyone know a way just to get the families in the project. Because the script is creating me images for families that exist but havent been placed in the project.

Any suggestions welcome.

Clockwork’s “all family types of category” will get you what you need.

hello @666Cachopo_Cultist66

try this

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]		
fecSymb = toList(UnwrapElement(IN[0]))	
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
4 Likes

Thanks c.poupin It works like charm!

I tried with some family types, but they look radiactive objects in green outlines and transparent :alien: :space_invader:, I do not know if this happens because most of these families are inside assembly because it looks like when you see the preview of elements while editing the assembly in Revit.

Do you know what is the image size limit for this? I tried until 400 pixels side and worked but no much more because it does not work or it appears very pixelled.
image

try this way


import clr
import System
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


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

from System.Runtime.InteropServices import Marshal


class ExcelUtils():
	def __init__(self, expSettings, filepath, sizebitmap):
		self.expSettings = expSettings
		self.filepath = filepath
		self.sizebitmap = sizebitmap
		
	def exportXls(self):
		
		ex = System.Activator.CreateInstance(System.Type.GetTypeFromProgID("Excel.Application", True))
		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()
			
assembly = UnwrapElement(IN[0])

lstElem = [doc.GetElement(xId) for xId in assembly.GetMemberIds()]
lstElemType = [doc.GetElement(e.GetTypeId()) for e in lstElem]
outdata = []
imgSize = Size(100, 100 )
for symb in lstElemType:
	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
3 Likes

Just for the exercise, a solution with openpyxl and PIL

I’ve tried openxml, but it’s a real pain to work with images with DocumentFormat.OpenXml


import clr
import System

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

clr.AddReference('RevitServices')
import RevitServices
from RevitServices.Persistence import DocumentManager
doc = DocumentManager.Instance.CurrentDBDocument

clr.AddReference('System.Drawing')
from System.Drawing.Imaging import ImageFormat
from System.Drawing import Size

clr.AddReference('Python.Included')
import Python.Included as pyInc
path_py3_lib = pyInc.Installer.EmbeddedPythonHome
sys.path.append(path_py3_lib + r'\Lib\site-packages')
from PIL import Image
import openpyxl
from openpyxl.worksheet.dimensions import ColumnDimension, DimensionHolder
from openpyxl.utils import get_column_letter
from openpyxl import Workbook
import io

def bitmap_to_PILimage(bmp):
    ms = System.IO.MemoryStream()
    bmp.Save(ms, ImageFormat.Png)
    rgbValues = ms.ToArray()
    ms.Close()
    pythonbytes = bytes(rgbValues)
    image = Image.open(io.BytesIO(pythonbytes))
    return image
    
assembly = UnwrapElement(IN[0])
myDocFolder = System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments)

lstElem = [doc.GetElement(xId) for xId in assembly.GetMemberIds()]
lstElemType = [doc.GetElement(e.GetTypeId()) for e in lstElem]
outdata = []

imgSize = Size(100, 100 )
for symb in lstElemType:
    bitm = symb.GetPreviewImage(imgSize) 
    famName = symb.Family.Name
    symbName = symb.get_Name()
    pil_img = bitmap_to_PILimage(bitm)
    outdata.append([pil_img, famName, symbName])

wb = Workbook()
ws = wb.active
for i, (pil_img, famName, symbName) in enumerate(outdata):
    ws.cell(row=i+1,column=2).value = famName
    ws.cell(row=i+1,column=3).value = symbName
    img = openpyxl.drawing.image.Image(pil_img)
    ws.add_image(img, 'A' + str(i+1))
    # set the height of the row 
    ws.row_dimensions[i + 1].height = 100
# set columns dimension
ws.column_dimensions['A'].width = 15
dim_holder = DimensionHolder(worksheet = ws)
for col in range(1 , ws.max_column + 1):
    dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=20)
ws.column_dimensions = dim_holder
# save the worksheet
wb.save(myDocFolder + '\\test57.xlsx')
wb.close()

OUT =  outdata
1 Like