How to work with embedded spreadsheets
Embedded spreadsheets can be used to display data as an Excel table or to get Excel to perform calculations that can then be written into attributes of business objects and stored in the database. This can be especially useful if you already have existing Excel spreadsheets that you want to re-use to perform some calculations.
To use embedded spreadsheets in Aware IM you need to do the following:
- Define an attribute of the Document type in some object
- Open this attribute for editing, select the “Allowed Extensions” property and then choose “Embedded Spreadsheet” from the drop down list of allowed extensions.
- If you will be storing results of spreadsheet calculations in the database define attributes that will store these calculations (the attributes must belong to the same object that owns the Document attribute)
When the system shows the form of this object in the browser Aware IM will generate a button that will allow users to create and edit spreadsheets. When the user presses this button Aware IM will display an Excel-like spreadsheet. You can create your own spreadsheet or use an existing one by importing an Excel file or using Copy and Paste to paste cells from Excel into the Aware IM spreadsheet.
If you want Aware IM to populate an existing spreadsheet with data from the database you need to specify the contents of the cell as “tag expression” that refers to objects in Context, for example <<Customer.Name>>. The Context will contain the instance of the object that owns the Document attribute with the spreadsheet. When the user presses the “Read Data” button at the top of the spreadsheet Aware IM will replace contents of the cell that have tag expressions with data from the database.
Once the data has been populated Excel will obviously re-calculate everything based on the values read from the database. You can then save results of the calculation in attributes of the object(s) in Context. To do this you need to do the following:
- Click on the “Mapping” button at the top of the spreadsheet to define cells that will be written into attributes of the object(s) in Context. You need to define which cell to write and then provide a tag expression that defines where this cell will be written into. Note that the mapping is saved with the spreadsheet, so you only need to define mapping once
- Click on the “Save Data” button to get Aware IM to save the cells defined in “mapping” into the database