References to other sheets

[Adanteh]

Hi, maybe i'm misunderstanding but is it a limitation that it does not know how to handle references on other sheets? I'm guessing it lacks the context for this, but didn't see it in not Supported.

Example:
Sheet1!A1 = Hi
Sheet1!A2 = =Sheet2!A2
Sheet2!A1 = Hello
Sheet2!A2 = =A1

Now i'd expect this to mean that Sheet1A2 would resolve into "Hello" on Sheet2 as it does in excel, but instead it's resolving into the Hi that's on Sheet1.

[bradbase]

This is not intended behavior. Your expectation is how I would expect it to behave.

It seems we may not be capturing the context of the sheet adequately. In the model everything requires a sheet name. The default is Sheet1.

A workaround is to have the formula in Sheet2!A2 to be '=Sheet2!A1'.

I'm strapped for time today but will take a look at it in the coming couple of days if anyone else hasn't had an attempt.

[Adanteh]

Thanks for quick response. I might be able to take a look today to see if I could autopass proper sheet context. Outside of those references to formulas on other sheets everything seems to work a-okay for my usage and it's a lot faster than Formulas package for me.

[bradbase]

@Adanteh, thanks for reporting the bug. 😄

This is a feature that I thought was already supported so obviously there's a hole in testing. With my recollection of the parser I would not expect fixing it to be a terribly big job. (... that said, I have been wrong before)

In any case if you can make the time, give it a go.

It's great to hear xlcalculator is doing a good job for you. There are many aspects to performance (loading, parsing, translating, executing and others). Each library is likely to have it's own strengths. There are also considerations beyond performance, eg; if the result is close to what Excel provides (if that's what you need).

If you are open to the idea, I would really like to have a conversation with you about your use of the library. There aren't many people who report bugs or step forward to have a chat about how the library is helping them. And if you've got any performance tests you are able to share for comparison with other libraries I'd love to see them.

Cheers

[Adanteh]

Hey, I just messed about with it a bit yesterday so there's not that much to say.

The basic usecase comes down to:

• Have a template XLSX file gets filled in with data from a database (Django webservice) based on Named Ranges.
• When you have an admin account it gives you the filled in template using data from some db queries. (But still a lot of formulas present using that data)
• New wanted feature is for non-admin to export with Values only instead of the original formulas.

I could just rewrite all formulas in plain python, but the template excel files aren't maintained by me and it'll be far easier for the other person to only need to make changes to the excel template files. So far I've been using openpyxl and all good, but obviously no calculations in there.

Right now my approach using Formulas is saving the WIP state with my filled in data, opening that saved file in Formulas, calculating the entire thing, then iterating over cells in ranges in my WIP state and whenever there's a formula in the cell, replacing that cell value with the cell.value at same coordinate in the Calculated workbook.

Using this the only thing I've actually done is a couple very basic smoketests to see if it could handle the formulas used and noticed it would break as soon as there was a reference to a formula on another sheet. Note the formulas approach does actually work for this specific thing, although there are some quirks when you start using Named ranges with multiple destinations. I havent tried that with this package yet.

[bradbase]

@Adanteh, thanks for the background. You have almost exactly described the gap I had found in other libraries and the needs which I need met for my own use.

And thanks for the PR. I can see there's been a review already.

xlcalculator is intended to replicate, as close as possible, the features of Excel which makes it possible to evaluate ("re-calc") the functions in a workbook and present the results, as close as possible, to what Excel would present. The heritage on other projects isn't quite so generalized as they have been built to serve a particular end -- which is quite understandable.

I expect named ranges to work. Supporting them has been a key requirement for my use of xlcalculator. That said, xlcalculator is less than 12 months old and although there have been around 1000 downloads per month for six months or so we have not had all that many bug reports (probably owing to the exceptional effort and experience of @strichter). The library has very good test coverage so that might also contribute toward the lack of bug reports. Interestingly, we have not had reports to support specific functions either so am less than convinced many of those 1000 downloads are actually using the library for much heavy lifting.

Again, thanks for the feedback and contribution. It's really appreciated.

Keep it coming 😄

[kmonson]

@bradbase Is this issue resolved?

[bradbase]

@kmonson the input from @Adanteh appears to have addressed it to a degree. There is further discussion about a more comprehensive solution in #37 which we would all like but nobody has yet to code up. eg; ensuring support for named ranges.

I'd say this is partially supported which is why I've not closed the issue.

[bradbase]

Aaaah.. I've not yet release this yet.

[strichter]

This is released. @kmonson Can we close this?