Excel to table in layout

[rcmcdougle]

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

[KeithR]

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

[rcmcdougle]

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

[KeithR]

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

Adding layout from 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

[rcmcdougle]

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

[Gijs (Gijs de Zwart)]

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

[rcmcdougle]

Thanks Gijs,

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

Robb

[Gijs (Gijs de Zwart)]

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?

[rcmcdougle]

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

[Gijs (Gijs de Zwart)]

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?

[Rickson]

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)

Import CSV and make table in rhino

Hello , I made a script to place table in rhino. It’s simple but maybe can help. Work with Excel , OpenOffice , Google Sheets [02] [01] I made some few modifications of the script , i had problems with Excels csv file : Import-csv-r5_v2.py (3.7 KB)

[Rickson]

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()

[rcmcdougle]

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.

[David_Arthurs]

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.

[Gijs (Gijs de Zwart)]

@rcmcdougle

Once published, this layout table data does not change.

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

Excel Table in Layout

My bad, it did not read numeric cells and I tested it with a text-only table so I couln’t see it. This version is fixed, only for Rhino6: https://drive.google.com/open?id=1ZGhHXWfJ3RmFf_VjEaJhdumxKgNO544Y Let me know if its works. Regards

[littlearch]

Not sure if this will help anyone but I have been trying to streamline and automate Rhino drafting and layouts in a template with excel spreadsheets. The formatting is a bit of a problem in the pdf to Rhino, but if you use the save as pdf in excel the table formatting will transfer to Rhino and you can update the linked pdf ln excel and repeat as required.

[littlearch]

Actually the export as Pdf from Excel is a better work around see attached screen shot Sheet Index is linked and updates

[RBL]

Nice, can you provide a little more detail please

[Japhy]

excel export to PDF

Then Attach PDF (@littlearch what is your method for controlling text scaling/location?)

resaving the excel to pdf export and then update your worksession.

My first impression is that you’ll want to create a specific Annotation Style to suit.

[littlearch]

H Japhy and RBL.
I am not doing any formatting or scaling. I tried printing pdfs from excel with different pdf printers but always got formatting issues in Rhino with it not tabulating the text correctly.

I think the native excel pdf locks the formatting in. I have changed and updated the blocks and they are working and formatted correctly as a table. I will do all my scheduled with excel and I a keynote system.