3.2. Excel workbook
An example how to load, calculate, and write an Excel workbook is the following:
>>> import formulas
>>> fpath , dir_output = 'excel.xlsx' , 'output'
>>> xl_model = formulas . ExcelModel () . loads ( fpath ) . finish ()
>>> xl_model . calculate ()
Solution(...)
>>> xl_model . write ( dirpath = dir_output )
{'EXCEL.XLSX': {Book: <openpyxl.workbook.workbook.Workbook ...>}}
Tip
If you have or could have circular references , add circular=True
to finish method.
To plot the dependency graph that depict relationships between Excel cells:
>>> dsp = xl_model . dsp
>>> dsp . plot ( view = False ) # Set view=True to plot in the default browser.
SiteMap([(ExcelModel, SiteMap())])
digraph dmap {
node [style=filled]
label = <dmap>
splines = ortho
style = filled
129 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!A1</TD></TR><TR><TD align="RIGHT" border="1">default</TD><TD align="LEFT" border="1">inputs</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!A1\""]
130 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!A2</TD></TR><TR><TD align="RIGHT" border="1">default</TD><TD align="LEFT" border="1">2</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-0.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!A2\""]
131 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!A3</TD></TR><TR><TD align="RIGHT" border="1">default</TD><TD align="LEFT" border="1">6</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-1.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!A3\""]
132 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!A3:A4</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-2.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!A3:A4\""]
133 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!A4</TD></TR><TR><TD align="RIGHT" border="1">default</TD><TD align="LEFT" border="1">5</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-3.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!A4\""]
134 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!B1</TD></TR><TR><TD align="RIGHT" border="1">default</TD><TD align="LEFT" border="1">Intermediate</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-4.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!B1\""]
135 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!B2</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-5.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!B2\""]
136 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!B3</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-6.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!B3\""]
137 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!B4</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-7.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!B4\""]
138 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!C1</TD></TR><TR><TD align="RIGHT" border="1">default</TD><TD align="LEFT" border="1">outputs</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-8.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!C1\""]
139 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!C2</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-9.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!C2\""]
140 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!C3</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-10.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!C3\""]
141 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!C4</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/format_output-11.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!C4\""]
142 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/EXCEL.XLSXDATAA3A4.html">='[EXCEL.XLSX]DATA'!A3:A4</TD></TR></TABLE>> fillcolor=springgreen shape=box tooltip="\"='[EXCEL.XLSX]DATA'!A3:A4\""]
143 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/(EXCEL.XLSXDATAA2__EXCEL.XLSXDATAA3).html">=('[EXCEL.XLSX]DATA'!A2 + '[EXCEL.XLSX]DATA'!A3)</TD></TR></TABLE>> fillcolor=springgreen shape=box tooltip="\"=('[EXCEL.XLSX]DATA'!A2 + '[EXCEL.XLSX]DATA'!A3)\""]
144 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/(EXCEL.XLSXDATAB2_-_EXCEL.XLSXDATAA3).html">=('[EXCEL.XLSX]DATA'!B2 - '[EXCEL.XLSX]DATA'!A3)</TD></TR></TABLE>> fillcolor=springgreen shape=box tooltip="\"=('[EXCEL.XLSX]DATA'!B2 - '[EXCEL.XLSX]DATA'!A3)\""]
145 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/(EXCEL.XLSXDATAB2__EXCEL.XLSXDATAB3).html">=('[EXCEL.XLSX]DATA'!B2 / '[EXCEL.XLSX]DATA'!B3)</TD></TR></TABLE>> fillcolor=springgreen shape=box tooltip="\"=('[EXCEL.XLSX]DATA'!B2 / '[EXCEL.XLSX]DATA'!B3)\""]
146 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/(EXCEL.XLSXDATAB3__EXCEL.XLSXDATAC2).html">=('[EXCEL.XLSX]DATA'!B3 ^ '[EXCEL.XLSX]DATA'!C2)</TD></TR></TABLE>> fillcolor=springgreen shape=box tooltip="\"=('[EXCEL.XLSX]DATA'!B3 ^ '[EXCEL.XLSX]DATA'!C2)\""]
147 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/(EXCEL.XLSXDATAC2__EXCEL.XLSXDATAA2).html">=('[EXCEL.XLSX]DATA'!C2 * '[EXCEL.XLSX]DATA'!A2)</TD></TR></TABLE>> fillcolor=springgreen shape=box tooltip="\"=('[EXCEL.XLSX]DATA'!C2 * '[EXCEL.XLSX]DATA'!A2)\""]
148 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2" href="./dispatcher-1e7a39569993e15b62fe4e88d7e268b36d0320ce/MAX(EXCEL.XLSXDATAA3A4_EXCEL.XLSXDATAB2).html">=MAX('[EXCEL.XLSX]DATA'!A3:A4, '[EXCEL.XLSX]DATA'!B2)</TD></TR></TABLE>> fillcolor=springgreen shape=box tooltip="\"=MAX('[EXCEL.XLSX]DATA'!A3:A4, '[EXCEL.XLSX]DATA'!B2)\""]
130 -> 143
130 -> 147
135 -> 145
135 -> 144
135 -> 148
131 -> 143
131 -> 144
131 -> 142
143 -> 135
139 -> 147
139 -> 146
136 -> 145
136 -> 146
145 -> 139
144 -> 136
147 -> 140
133 -> 142
132 -> 148
148 -> 137
146 -> 141
142 -> 132
}
To overwrite the default inputs that are defined by the excel file or to impose
some value to a specific cell:
>>> xl_model . calculate (
... inputs = {
... "'[EXCEL.XLSX]DATA'!A2" : 3 , # To overwrite the default value.
... "'[EXCEL.XLSX]DATA'!B3" : 1 # To impose a value to B3 cell.
... },
... outputs = [
... "'[EXCEL.XLSX]DATA'!C2" , "'[EXCEL.XLSX]DATA'!C4"
... ] # To define the outputs that you want to calculate.
... )
Solution([("'[EXCEL.XLSX]DATA'!A2", <Ranges>('[EXCEL.XLSX]DATA'!A2)=[[3]]),
("'[EXCEL.XLSX]DATA'!A3", <Ranges>('[EXCEL.XLSX]DATA'!A3)=[[6]]),
("'[EXCEL.XLSX]DATA'!B3", <Ranges>('[EXCEL.XLSX]DATA'!B3)=[[1]]),
("'[EXCEL.XLSX]DATA'!B2", <Ranges>('[EXCEL.XLSX]DATA'!B2)=[[9.0]]),
("'[EXCEL.XLSX]DATA'!C2", <Ranges>('[EXCEL.XLSX]DATA'!C2)=[[9.0]]),
("'[EXCEL.XLSX]DATA'!C4", <Ranges>('[EXCEL.XLSX]DATA'!C4)=[[1.0]])])
To build a single function out of an excel model with fixed inputs and outputs,
you can use the compile method of the ExcelModel that returns a
DispatchPipe . This is a function where the inputs and outputs are defined by
the data node ids (i.e., cell references).
>>> func = xl_model . compile (
... inputs = [
... "'[EXCEL.XLSX]DATA'!A2" , # First argument of the function.
... "'[EXCEL.XLSX]DATA'!B3" # Second argument of the function.
... ], # To define function inputs.
... outputs = [
... "'[EXCEL.XLSX]DATA'!C2" , "'[EXCEL.XLSX]DATA'!C4"
... ] # To define function outputs.
... )
>>> func
<schedula.utils.dsp.DispatchPipe object at ...>
>>> [ v . value [ 0 , 0 ] for v in func ( 3 , 1 )] # To retrieve the data.
[9.0, 1.0]
>>> func . plot ( view = False ) # Set view=True to plot in the default browser.
SiteMap([(ExcelModel, SiteMap())])
digraph dmap {
node [style=filled]
label = <dmap>
splines = ortho
style = filled
235 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!A2</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-72dffdc1038a02a1e11afcc7e747361c034602a9/format_output.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!A2\""]
236 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!A3</TD></TR><TR><TD align="RIGHT" border="1">default</TD><TD align="LEFT" border="1">6</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-72dffdc1038a02a1e11afcc7e747361c034602a9/format_output-0.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!A3\""]
237 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!B2</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-72dffdc1038a02a1e11afcc7e747361c034602a9/format_output-1.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!B2\""]
238 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!B3</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-72dffdc1038a02a1e11afcc7e747361c034602a9/format_output-2.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!B3\""]
239 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!C2</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-72dffdc1038a02a1e11afcc7e747361c034602a9/format_output-3.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!C2\""]
240 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!C4</TD></TR><TR><TD align="RIGHT" border="1">filter 0</TD><TD align="LEFT" border="1" href="./dispatcher-72dffdc1038a02a1e11afcc7e747361c034602a9/format_output-4.html">format_output</TD></TR></TABLE>> fillcolor=cyan shape=box style="rounded,filled" tooltip="\"'[EXCEL.XLSX]DATA'!C4\""]
241 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2" href="./dispatcher-72dffdc1038a02a1e11afcc7e747361c034602a9/(EXCEL.XLSXDATAA2__EXCEL.XLSXDATAA3).html">=('[EXCEL.XLSX]DATA'!A2 + '[EXCEL.XLSX]DATA'!A3)</TD></TR></TABLE>> fillcolor=springgreen shape=box tooltip="\"=('[EXCEL.XLSX]DATA'!A2 + '[EXCEL.XLSX]DATA'!A3)\""]
242 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2" href="./dispatcher-72dffdc1038a02a1e11afcc7e747361c034602a9/(EXCEL.XLSXDATAB2__EXCEL.XLSXDATAB3).html">=('[EXCEL.XLSX]DATA'!B2 / '[EXCEL.XLSX]DATA'!B3)</TD></TR></TABLE>> fillcolor=springgreen shape=box tooltip="\"=('[EXCEL.XLSX]DATA'!B2 / '[EXCEL.XLSX]DATA'!B3)\""]
243 [label=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2" href="./dispatcher-72dffdc1038a02a1e11afcc7e747361c034602a9/(EXCEL.XLSXDATAB3__EXCEL.XLSXDATAC2).html">=('[EXCEL.XLSX]DATA'!B3 ^ '[EXCEL.XLSX]DATA'!C2)</TD></TR></TABLE>> fillcolor=springgreen shape=box tooltip="\"=('[EXCEL.XLSX]DATA'!B3 ^ '[EXCEL.XLSX]DATA'!C2)\""]
244 [label=end fillcolor=blue shape=egg]
245 [label=start fillcolor=red shape=egg]
239 -> 243
242 -> 239
243 -> 240
237 -> 242
238 -> 242
238 -> 243
241 -> 237
235 -> 241
236 -> 241
245 -> 235 [xlabel=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">start --> '[EXCEL.XLSX]DATA'!A2</TD></TR><TR><TD align="RIGHT" border="1">inp_id</TD><TD align="LEFT" border="1">0</TD></TR></TABLE>>]
245 -> 238 [xlabel=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">start --> '[EXCEL.XLSX]DATA'!B3</TD></TR><TR><TD align="RIGHT" border="1">inp_id</TD><TD align="LEFT" border="1">1</TD></TR></TABLE>>]
239 -> 244 [xlabel=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!C2 --> end</TD></TR><TR><TD align="RIGHT" border="1">out_id</TD><TD align="LEFT" border="1">0</TD></TR></TABLE>>]
240 -> 244 [xlabel=<<TABLE border="0" cellspacing="0"><TR><TD border="0" colspan="2">'[EXCEL.XLSX]DATA'!C4 --> end</TD></TR><TR><TD align="RIGHT" border="1">out_id</TD><TD align="LEFT" border="1">1</TD></TR></TABLE>>]
}