PancakeSpreadsheet α: Read / write xls(x) files without Excel

Grasshopper
1

This plugin makes use of the NPOI library which allows you to manipulate xls(x) files without Excel installed and it is faster. It should work both on Windows and macos but macos isn’t tested.

If you need to write Excel files / modify opened spreadsheet, please use flow control components (e.g. Wait Until) to make sure they are sequentially ordered otherwise the result is unreliable.

This plugin is still in the alpha stage, and may contain unknown bugs.

Backup files especially when you are writing xls(x) files. Don’t hesitate to contact me or leave a reply here if you run into something. Most of writing components aren’t thoroughly tested. Advice is also welcome.

It would have a few more features if Pancake is installed but it will work without it.

image
image994×359 27 KB

5 Likes
2

Can you provide a example file for this?

1
11411×533 55.6 KB

1 Like
3

File needs to be a file path instead of a directory, e.g. D:\test\a.xlsx.
And it’s probably not gonna be saved if there’s no sheet in the workbook.

image
image1360×648 59.6 KB

image

simple_write_xlsx.gh (8.6 KB)

1 Like
4

Thank you. It works

5

Released on food4rhino.

6

Hi what needs to be the inputs for the “set cell style” component. Imnot sure what should be in the “value” input. thanks

7

Generally it’s not quite supported and it is a future feature to be implemented.

The possible style names includes:

alignment
borderBottom
borderLeft
borderRight
borderTop
bottomBorderColor
dataFormat
fillBackgroundColor
fillBackgroundColorColor
fillForegroundColor
fillForegroundColorColor
fillPattern
font
hidden
indention
leftBorderColor
locked
quotePrefixed
rightBorderColor
rotation
shrinkToFit
topBorderColor
verticalAlignment
wrapText

You can search for examples on CellUtil.SetCellStyleProperties for manipulating cell styles.

8

Hi @gankeyu,

Thanks for the plugin! This is the best Excel plugin I found in my trials. I am currently using this setup to read Excel files, in which I use Human’s item selector to reference the sheet name.

afbeelding
afbeelding2618×411 67.1 KB

I use (very light) Excel tables do drive my script to generate geometry and would like to request an update toggle/ node input (to refresh the spreadsheet coming in to Grasshopper). I prefer this to be at the spreadsheet level, to prevent the item selector from updating as well (and defaulting back to another sheet).

9

I’m afraid a spreadsheet must be reloaded entirely, due to limitations of the underlying mechanism.

I would propose a workaround of using two Open Spreadsheet components, one for sheet name and one for actual read, and there could be two reloading toggles, one “bigger” one would also trigger the other one.

1 Like
10

That’s a good point, works well.

I’m trying to apply data from a sheet based on attributes. For instance, I have curves that have the attribute ‘Tram’ which I would like use to set the width of the street, similarly, I could have a sheet called ‘Alley’ which i would use to generate tiny streets with.

I have been trying to read all sheets within the file by grafting the spreadsheet input for the sheet identifier in the Simple read XLS component, but the component does not maintain data order whatsoever when I do this. Is that something you could support in the future? Meaning, you could create a tree branch for each spreadhseet and have a component with a spreadsheet index which then returns the specific spreadsheet branch.

11

May you upload an example file?

12

@gankeyu Yes, of course.

I would like to avoid these kinds of spaghetti wiring shenanigans:

afbeelding
afbeelding867×704 89.2 KB

The procedure I am using is in this file below:
XLS sheets to tree branches.gh (17.6 KB)
Added the XLS as zip:
Sample table.zip (11.9 KB)

13

my apolgoies i’ve been busy recently.

I need sometime to diagnose with the “formatted table” issue on GitHub and come up with a workaround to tackle the issue here.

1 Like