Are tables implemented?

[deanm0000]

I got this error:

FormulaError: ('Not a valid formula:\n%s', '=Table6[[#This Row],[MWh]]/Table6[[#This Row],[MW]]')

I'm guessing Tables aren't implemented and that's why this doesn't work since it's just division. Is that right?

On that note, is there a way to exclude tabs when loading a file via formulas.ExcelModel().loads(fpath).finish() or to warn for formula errors instead of erroring out?

[vinci1it2000]

Yes tables are not implemented. Can you share the reference and definition of tables? So it can be in the pipeline of futures development

[deanm0000]

Thanks for the follow up.

Is this what you mean by reference?
https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

Also, I'm still wondering if there's a way to exclude tabs or warn about errors instead of raising an exception.

[FynnFreyer]

Hi,

I'm working on a project that needs this functionality as well, and my manager told me that long term it would be much cleaner to use this library, instead of using our current way of libreoffice --headless --convert-to csv [...]. So, when I have capacity, I would like to work on implementing tables if you'd be willing to accept a PR regarding this @vinci1it2000.

Anyway, I'm putting some loose thoughts into this issue, in case this might be helpful down the line:

An easily digestible overview of the syntax of structured references (e.g. =<table_name>[[<column_1>],[<column_2>]]) can be found here in the "Structured reference syntax rules" section and following.

I had a (very) quick (and cursory) glance around the code base, so I don't understand it that well, but it seems that adding another token filter to the Parser class would be the way to do this. There could be more Operand classes for tables, column specifiers, item specifiers, and table specifiers and another Operator for referencing with brackets. Are Operators designed with something other than the typical infix notation in mind? If not, it might make sense to make this a special kind of Function instead? As I said, it was a very cursory glance, and these thoughts are preliminary.

Is there any documentation available on what exactly those ast and process methods need to do? (I mean, ast probably builds up an abstract syntax tree, and process might calculate things (?), but that's really just an initial guess, because I don't want to read the code and figure it out atm.)

Fortunately, openpyxls Worksheets already give easy access to a dictionary of tables.

This is probably a case of YAGNI, but it might make sense to consider references to other worksheets (section "Create a cell reference to another worksheet", e.g. =<sheet_name>!A1:B1), or named ranges (accessible in openpyxl via defined_names, maybe somewhat related to #114) when implementing structured references for tables.
Maybe formulas can already do those, then nvm.

Also, because Excel-developers seem to be masochists (or sadists), you can actually reference data from different workbooks, but I would think that's super out of scope.

Besides, the people who use this "feature" deserve the hell they are wading through. I mean modularization is nice and everything, but this is just sick. /s

[vinci1it2000]

Hi @FynnFreyer thanks for your interest and collaboration. We can have a talk so we can see how to proceed. You can contact me by email.

[dberardo-com]

any update on this issue? would it be possible to define tables as a custom formula or would that be to hard ?

@FynnFreyer ?

[FynnFreyer]

Hi @vinci1it2000 I wrote you an email

[FynnFreyer]

@dberardo-com, thanks for pinging me, because I totally missed the first mention. I have not been working on this as yet, and if you need something soon, I'd suggest you take a look at exporting to csv via LibreOffice. Maybe that helps you for your use case (it's the ugly hack I'm using in another project rn)

Probably not a good idea to hold your breath and wait for the solution here atm, since I can't give any sort of eta as to when I get around to working on this ^^'