Spreadsheets
Create calculations based on spreadsheets
Last updated
Create calculations based on spreadsheets
Last updated
CalcTree provides spreadsheets as a calculation option. You can add any spreadsheet that follows the Office Open XML standard to a CalcTree page as a source.
Spreadsheets with .xlsx file extensions are based on Office Open XML (OOXML) standard and hence can be used as a source in CalcTree
On this page you will learn to create calculations based on spreadsheets. To learn more about creating parametric content based on these calculations, continue on Parametric content
From the right-hand panel, click on Spreadsheet. A dialogue appears that allows you to select an .xlsx spreadsheet file to upload. Use the below spreadsheet as a sample. The spreadsheet gets uploaded and added as a source to current page. The spreadsheet viewer will slide in from the bottom of the view, showing the spreadsheet source:
The spreadsheet viewer is a read-only viewer that allows you to navigate between sheets inside the spreadsheet and define parameters and inputs for the spreadsheet source.
Use mouse scrolling wheel while holding the ctrl key (control key on Mac) to zoom in and out the spreadsheet.
After adding the sample spreadsheet as a source, click on cell C5 to select it. A green circle with a plus sign appears inside the cell. Click on the circle to define a parameter calculated by that cell's formula. A parameter named sheet1_c5 will appear on the right-hand panel which gets the calculated value of cell C5's formula:
That was a single-value parameter. You can also create a table parameter by selecting a range and then clicking on the plus button. As an example, select the B2:C3 range and then click on the plus circle. A table parameter called ct_table_1 will appear on the right-hand panel which gets calculated by the cells in the B2:C3 range:
Unlike Python sources, you can't edit the spreadsheet source inside CalcTree. However, you can make edits to a spreadsheet file offline, and re-upload it. As an example, after adding the sample spreadsheet source, you can download it from under the Uploaded Files section on the right-hand-side panel, by clicking on Download command from its three-dot menu:
Open the spreadsheet and edit cell C2 from 3 to 4. Now click on the + Add button on the right hand-side panel and select Spreadsheet. Browse to the edited spreadsheet on your computer and select it for upload - make sure the file has the same name as before so that it will replace the existing source. Note that the calculated value for the spreadsheet parameters get updated on the right-hand-side.
If you upload a spreadsheet with the same file name as an existing spreadsheet source, it will replace the existing spreadsheet. The parameter definitions will remain pointing to the same addresses.
You won't need to re-upload an edited spreadsheet for inputting different values of cells. By defining inputs for the spreadsheet, you can provide input values to spreadsheet sources, without opening the source.
In order to use the spreadsheet as a calculation source, it will be convenient to provide input values to it without editing it. To achieve that you can define spreadsheet inputs. As an example, after adding the sample spreadsheet as a source, select cell C2 and click on the green plus icon. A spreadsheet input called sheet1_c2 will appear on the right-hand-side. This input initially has the current value from cell C2, but you can enter a new value for it, which will be sent to the spreadsheet as the new value for cell C2.
If a spreadsheet cell has no formula in it (such as numeric value like 2), clicking on the plus icon will create a spreadsheet input. On the other hand, if the spreadsheet cell has a formula (such as = C2 + C3 ) clicking on the plus icon will create a spreadsheet parameter.
Clicking on the plus icon when a range of cells is selected, will always create a tabular parameter, not an input.
Defining a spreadsheet grid of inputs and outputs are among next items on our roadmap. Please follow our product roadmap items and status here and let us know what you think!
If the spreadsheet includes charts, CalcTree will extract them as dynamic figures under the spreadsheet source. As an example, add the below sample spreadsheet with charts to a CalcTree page and create an input from cell B3 . Now change the input value shet_b3 and inspect how the figure dynamically updates under the source on the right-hand-side:
You can use spreadsheet charts as parametric elements on the document. To learn more continue on Parametric content
Linking spreadsheets to other sources is among next items on our roadmap. We plan to enable it as explained below. Please follow our product roadmap items and status here and let us know what you think!
You can link multiple sources to create more complex calculations. That means a source picks up a result from another source and does more calculations on it.
In order to link a spreadsheet to other calculations, it needs to refer to the value of another source's parameters. That is achievable by typing the other source's parameter name inside a spreadsheet input. For example, if you have two parameters called width, and height which are defined by other sources, you can refer to them in inputs sheet1_c2 and sheet1_c5 :
