Using sheet.js to load excel sheets into TW?

Developers
#1

hey,
i am not very experienced with JS but found this and have been trying a bit to get it to work in TW. it is a library(?) that can take xlsx or other spreadsheet files and display them as html tables:
SheetJS - Home
using a single-file TW, i was hoping it would be possible to read spreadsheets stored in the same folder and display/parse data from them for processing in TW.

i was digging through the docs and playing around with @tobibeer 's enable-js plugin to try and get sheetjs to load a file to no avail. i think i am a bit over my head.

has anyone tried to use this software with TW? if not, do you have any pointers on where to start or what is/is not possible (automatically reading files in the browser? running an import as a macro like <<sheetjs "C:/path/to/file">> ? importing rows/columns as tiddlers? live updating results when the source file is edited??)

best
scribs

#2

After a rough look, it should be feasible to write a widget plugin for sheetjs.

2 Likes
#3

This would be a very handy plugin, maybe if a tiddler is set to type: text/spreadsheet it renders the spreadsheet, and store the data in the tiddler text field as json?

Just my thoughts :grin:

#4

agreed! i work with excel a lot to store reference data and it would be incredible to be able to access that (changing) data live in TW and be able to combine excel functions/logic with filter syntax and sorting. not to mention being able to integrate live data into my other notes. the possibilities are almost endless!

1 Like
#5

I just want to refloat this post.

TW that loads excels would be a super nice feature.

Shouldn’t sheetsjs library be installed too?
I don’t know if the pluggin aproach would be enough or it might require also some server modifications, ¿ @poc2go probably a job for NodeRed-TW?

Edit: probably is comparable to Echarts, where just a pluggin installation is enough to allow new visualization and interaction capabilities with js

1 Like
#6

Manually loading excel sheets into TiddlyWiki without sheet.js

I just want to point out there already exists Intergrations with Google sheets, excel can be saved as CSV and imported, such CSV’s can be imported and converted to tiddlers with the JSON Mangler plugin and there is the core plugin" XLSX Utils: XLSX spreadsheet utilities" (this already contains some JS macros It is based on the library js-xlsx.

  • I have personally found Excel to CSV, then using CSV or JSON Mangler to generate a Data Plugin very useful and sometimes even write my own parser to extract from CSV the columns I need, if there are no issues with delimiters, such as text containing commas, thus quoted.
  • The first thing I do is process a row at a time with splitregexp[\n] then in a nested list parse each row into columns with a split[,]
    • Tools such as set multiple variables or action multiple fields using filters in the the names/values parameter pairs would be useful.
  • I have found the core plugin fiddly, but once set up for a specific import it is easy to repeat many times, where the other methods are manual every time, and better for ad hoc or one off imports.

[Edited]

Note it looks like the core plugin was based on an earlier version of sheets.js anyway https://github.com/SheetJS/js-xlsx goes to;

So maybe it is just a matter of an upgrade?

1 Like
#7

There are indeed some libraries that support previewing tables xlsx or even docx, but the written plug-ins are too big. At the same time, I don’t see any special advantages of tiddlywiki loading tables.

#8

Just loading an excel tables is not that impressive,

What I wish for is some special tiddlers type that renders an interactive excel sheet, where I could do calculations, graphs, dinamic tables, etc and have the possibility to use the standard excel formulas I already know.

Is there a defined max size up to wich plugin dev is not an option?

#9

while it might not be as impressive (by some metric - it would be impressive to me!), it would certainly be very very useful to be able to use excel references as variables in wikitext! like a plugin that enables loading a file and then using it like:

Weekly sales $ <<excel "MyNamedRangeInExcel">>
Top product: <<excel "B12">>

Weekly sales $ 12,345
Top product: Widget A™

with an option to reload excel data as a toolbar button or similar.

this way we have access to both excel data manipulation/aggregation and compatibility with tw filter syntax to enable a very powerful workflow for both collecting and organizing/summarizing the data!