Creating Spreadsheet file
You can use an excel with all your formulas of your calculator and to convert it to a working form on your site, you only need to configure a working spreadsheet that returns the right values when inputs are typed in, for example, in order to create a simple form with the sum of 2 numbers, you would create a sheet like this one:
You can freely use any of the implemented excel functions. You can check the whole list of functions available here:
https://phpspreadsheet.readthedocs.io/en/latest/references/function-list-by-category/
Main
Next step would be to upload this file to your calculator configuration, using the 'Spreadsheet' option. Valid formats are .xls, .xlsx and .odt
Some extra configuration fields:
- Use Spreadsheet File Cache: use this to improve performance only when you don't need to refresh your excel upload for a while. File will be cached so we suggest to activate only when you finish testing your excel sheet file.
- Spreadsheet calculation launches: You can also execute custom php code before and/or after the excel is launched. Use the option to select when you prefer to launch the calculations. You can also choose to execute the excel 'inside' your php code, you only need to write
//[EXCEL]inside your code in order to launch excel calculations at that specific point. This can be used to perform initial handling of your data before sending them to excel and/or after results are recovered. - Locale: Depending on your excel version you may need to set your computer language in order to ensure the names of the functions are properly parsed by the excel library.
Input Mapping
This section configures the link between the fields of your form (user input), and the cells of the excel. Add as many fields as you need, and set the variable name of your field, and the number of sheet and cell where the field should be sent to. (The sheet numbers begin with the number 0 for the first one.)
For our simple example you would need to set the input 1 and input 2 field numbers to the cells B2 and B3 of your excel file (sheet 0)
Output Mapping
Finally, the output mapping will define the information you will like to extract from your excel file, usually the result of the operations.
You will need to specify the sheet and cell you want to recover, and the variable where you want to store the value.
For our sum example, a single output is needed, mapping the result cell B5 to a new variable 'result'. This variable name may be printed at your output, emails, pdf...etc using the ##result## syntax.
You're done. Now your input form sends the inputs to the excel file, that will perform all calculations and return back the results needed.