Excel to table in layout

I have been searching around for a while and have not found anything that works for me. I have a GH that creates an item from an engineering provided spreadsheet. I also have a few layout sheets that provide 2d documentation from that 3d model. Each of those layouts contains a table with data in cells that correspond to specific ranges in the engineering spreadsheet. I would like to automate the layouts to include the .xlsx data in that table. If the spreadsheet changes a new file is created so the link is only one way.

I have a matrix in Grasshopper that is exactly what I want into the layout. I just can’t figure how to get that data into the layout appropriately.

Thanks In Advance,
Robb

http://www.rhinopiping.com/table-plugin might be worth a try.

It appears one cannot send grasshopper objects directly to a layout. Is this true?

I have succeeded in setting an array of points for the various text objects in model view then using that in a view on layout. I don’t like cluttering up my model space with that kind of data but it works for now.

Robb

https://www.food4rhino.com/app/fabtools have some layout options available for grasshopper.

This is Florians main website and contact details:
http://blickfeld7.com/architecture/rhino/grasshopper/FabTools

@DavidRutten It would be good to have a table component for Grasshopper.

Regards,
Keith

I had seen that thread previously. Fab Tools does not suit my purpose. Nor do I want to create a proper CSV derivation of the engineering spreadsheet that is driving the core of the model at present. I just need that same engineering data visible in the layout.

Thanks anyway, I appreciate the input.

Robb

Maybe you can make something yourself with a script. I did something similar for making an part list in my page layout tools

Thanks Gijs,

My Python skills are not up to that. Very interesting stuff in your layout tools. Will download and use.

Robb

I might be able to help, if I know exactly what you need. It wasn’t completely clear from your post. Do you want to generate something in gh, or was this your attempted workaround and do you just want to have a table on a layout page that updates with a change of an excel sheet?

Thanks for the offer Gijs.

I have a large engineering spreadsheet within which a range of about 40 cells is used to size the basis of a product. 2 columns and 20 rows. The layout documenting the finished design needs to include the spreadsheet data in a table. Once published, this layout table data does not change. Surface features and designs may change but the core remains per the engineering data. If the engineering data changes, a new model is generated with a new layout set.

I’ve got that working perfectly, via grasshopper in model space, to generate the foundation from the spreadsheet. I can select a tab in the spreadsheet for the different size items. The rows and columns are the same tab to tab. I just need that table in the layout. I was thinking I could do something similar with Grasshopper to get the table to layout.

I don’t care if it is Grasshopper or a script under a button. I was experimenting and learning python / winforms a few years ago. But I have since been working just with Grasshopper. I have a sense that taking the Grasshopper scripts to Python/WinForms is the more robust solution for these issues as they need to move out into the enterprise. Part of what I am tasked with is to create tools to standardize and streamline the design and documentation process.

Robb

I’ve briefly looked into it, but it seems there aren’t many options in ironpython to work with xslx files. I’ve found openpyxl, but as far as I know that does not work with ironpython. Easiest route would be to use .csv
Why is it a problem to use .csv?

I messed with this one a bit and got it working to propagate on layouts or in model space.

The one thing it needs is to integrate Annotation Styles text size vs an override (so that you can leave scale to layouts checked & have one in model, one in paper if need be)


def CSVBom():

    listA = _csv
    #a = listA
    plane=Rhino.Geometry.Plane.WorldXY
    ListTotals = []
    table = _pointFeed
    rs.EnableRedraw(False)
    ListLarge = []
    ListLen = []
    a=0



    for row  in listA:
                row = row.split(";")
                rowA = []
                for str in row:
                    str = str.replace("\n","")
                    rowA.append(str)
                rowB = []
                for aa in rowA:
                    rowB=aa.split(',')
                if len(rowB)<= len(rowA):rowB=rowA
                list=[]
                A=len(rowB)
                ListLen.append(A)
                for txt in rowB:
                    if txt :
                       test = rs.AddText(txt, plane, height=_textSize, font='Arial', font_style=0, justification=None)
                       bb = rs.BoundingBox(test, plane, in_world_coords=True)
                       if bb :
                           large =round(( bb[1].DistanceTo(bb[0])),1)
                           if large < 0: largeur=large*(-1)
                           list.append(large)
                           rs.DeleteObject(test)
                       else : 
                           large=0.0
                           list.append(large)
                    else : 
                           large=0.0
                           list.append(large)
                ListLarge.append(list)

    N = max(ListLen)
    d_max = {i: max(map(itemgetter(i), ListLarge)) for i in range(N)}
    #Largest 
    ListMax = d_max.values()
    #Layers
    rs.AddLayer("Name")
    rs.AddLayer("Detail Lines",parent="Name")
    rs.AddLayer("Texts",parent="Name")

    
    listB = _csv


    Group=rs.AddGroup()
    ListGroup = []
    H = _textSize
    font='Arial'
    count=1


    for row  in listB:
        V=0
        Y=0
        row = row.split(";")
        rowA = []
        for str in row:
            str = str.replace("\n","")
            rowA.append(str)
        rowB = []
        for aa in rowA:
            rowB=aa.split(',')
        if len(rowB)<= len(rowA):rowB=rowA
        for value in rowB:
            #text placement
            placeA= ((-H*2) - (float(count)*(H*2)))
            placeB= ((-H) - (float(count)*(H*2)))
            #rectangle
            LX = (ListMax[Y])+(H*.25+H)
            recA=rs.AddRectangle(plane,LX,H*2)
            VV=V+(LX/2)
            recA=rs.MoveObject(recA,[V,placeA,0.0])
            rs.ObjectLayer(recA,"Detail Lines")
            ListGroup.append(recA)
            #text
            txt=value
            if txt :
                txtA=rs.AddText (txt,plane,H,font,0,2 + 131072)
                txtA=rs.MoveObject(txtA,[VV,placeB,0.0])
                rs.ObjectLayer(txtA,"Texts")
                ListGroup.append(txtA)
            #new text placement
            Y=Y+1
            V=V+LX
            #New Line
        count=count+1
    ListGroup=rs.MoveObjects(ListGroup,table)
#    rs.EnableRedraw(True)
    rs.AddObjectsToGroup(ListGroup,Group)

if x:
    CSVBom()



Going from the engineering XLSX to a few cols of CSV with the proper delimiters is just one more opportunity for things to get sideways. I can be rigorous but a half dozen others speaking different languages can be problematical.

Just to mix things up a bit. Since the data is in your speadsheet, why not use VBA in excel to drive rhino rather than python with intermediate files cluttering up. Ok you still have to format the table data which can be done with the string functions. There is no real need to use Grasshopper as all your design decisions are already done in the spreadshee.

in that case, this might work, as you can directly insert an xlsx as a table: