Comments (5)
cc @vinci1it2000 Any light on this issue? still there I think
I tried passing in data in many ways, but it seems that the calculate function adds the custom input in a weird way to the solution data so that the write function can't interpret it.
from formulas.
The problem is the input data names. The convention is that they have to be UPPERCASE.
I replicated your problem using a sample excel named book.xlsx.
The following code is the solution:
>>> import formulas
>>> xl = formulas.ExcelModel()
>>> xl.load('book.xlsx')
>>> list(xl.dsp.data_nodes)
["'[BOOK.XLSX]INPUTS'!A2",
"'[BOOK.XLSX]INPUTS'!A3",
"'[BOOK.XLSX]INPUTS'!B3",
"'[BOOK.XLSX]INPUTS'!A4",
"'[BOOK.XLSX]INPUTS'!B4",
"'[BOOK.XLSX]DATA'!A3",
"'[BOOK.XLSX]DATA'!B3",
"'[BOOK.XLSX]OUTPUTS'!A3",
"'[BOOK.XLSX]OUTPUTS'!B3"]
>>> sol = xl.calculate({"'[BOOK.XLSX]INPUTS'!B3": 6, "'[BOOK.XLSX]INPUTS'!B4": 8, "'[BOOK.XLSX]DATA'!B3": 4})
>>> xl.write(dirpath='./outputs')
{'BOOK.XLSX': {Book: <openpyxl.workbook.workbook.Workbook at 0x7f9ce81a6670>}}
The resulting excel output file (i.e., ./outputs/BOOK.XLSX) has the cell "'[BOOK.XLSX]OUTPUTS'!B3"
equal to 18, i.e. the sum of the provided inputs. You can also verify the correctness of your calculation, without saving the excel file, using the object sol
as follow:
>>> sol["'[BOOK.XLSX]OUTPUTS'!B3"]
<Ranges>('[BOOK.XLSX]OUTPUTS'!B3)=[[18.0]]
I hope that this explanation can be useful for your development by using formulas.
from formulas.
So, I think the uppercase thing is an issue, but it's not the real issue in the example. I have everything uppercase and it still doesn't work. However, I think it's due to the fact that I don't have all the cells pre-defined in my excel. Some of them are empty so they are not initialized. Your example assumes that whatever inputs you give are also in the initial XLS file (book.xls in your example). Somehow the cells should be created if you provide an input that's not already in the initial input file. Does this make sense?
from formulas.
I tested the previous code cleaning the prefilled cells and it is working. Probably you have some range with empty values in your formulas, so in this case, the excel file is compiled differently. I made a change in the library that can solve your problem. You can temporarily use the commit 5a4f132 (I will release soon a new official release).
I made a simple test case using a new sample excel partially pre-filled named new_book.xlsx.
The following code shows how to use the library:
>>> import formulas
>>> xl = formulas.ExcelModel()
>>> xl.load('new_book.xlsx')
>>> xl.finish()
>>> list(xl.dsp.data_nodes)
["'[NEW_BOOK.XLSX]INPUTS'!A2",
"'[NEW_BOOK.XLSX]INPUTS'!A3",
"'[NEW_BOOK.XLSX]INPUTS'!B3",
"'[NEW_BOOK.XLSX]INPUTS'!A4",
"'[NEW_BOOK.XLSX]DATA'!A3",
"'[NEW_BOOK.XLSX]OUTPUTS'!A3",
"'[NEW_BOOK.XLSX]OUTPUTS'!B3",
"'[NEW_BOOK.XLSX]DATA'!B3",
"'[NEW_BOOK.XLSX]INPUTS'!B3:B4",
"'[NEW_BOOK.XLSX]INPUTS'!B4"]
>>> sol = xl.calculate({
... "'[BOOK.XLSX]INPUTS'!B2": 3, # Extra data to be saved.
... "'[BOOK.XLSX]INPUTS'!B3": 3, # Overwritten value.
... "'[BOOK.XLSX]INPUTS'!B4": 3, # New value.
... "'[BOOK.XLSX]DATA'!B3": 1, # New value.
... })
>>> xl.write(dirpath='./outputs')
{'BOOK.XLSX': {Book: <openpyxl.workbook.workbook.Workbook at 0x7f9ce81a6670>}}
from formulas.
Close with the new release v1.0.0.
from formulas.
Related Issues (20)
- by the way the TRUNC function is missing or broken
- Error found during using formula reference in range
- How to create a custom function that calls .xll file?
- Combination with IFERROR and VLOOKUP from another sheet does not return a correct value HOT 1
- How to define custom variable placeholder HOT 3
- Are tables implemented? HOT 7
- Not a valid formula for decimal numbers missing any digits before the decimal HOT 1
- Question: How to ignore invalid formula HOT 2
- Possible to implement TEXT() and VALUE() functions? HOT 1
- Issue with parenthesis and logical operators HOT 1
- Workbook calculate modifies filename, sheet names to uppercase HOT 1
- Example results #VALUE! HOT 1
- Can it handle macro contained in excel cell HOT 1
- Calculated ranges to be used inside another function HOT 1
- Interface for implementing functions in formulas
- Save the model HOT 1
- Question - is it possible to import a openpyxl workbook? HOT 1
- Range Assembly construction infinite loop HOT 1
- Range Assembly inefficient HOT 4
- Help needed on using Formulas in parsing SUMPRODUCT formula HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from formulas.