Updating Excel data with Project Siena
This post describes a method of using Project Siena with Excel data that can be updated later without having to change the app. [Yes Its been a while since I posted anything, thought it was about time so here is my first post, using the cool product I am now working on]
Siena can import Excel data in all its glory, but only while you are creating your app. The data is literally inserted into the app, so cannot be updated once your app has been published. (Before being published you can update your data by tapping the Refresh icon on the data sources page). This post describes a way of making your app consume updated data, after the app is done. All you need is to be able to write a .txt file to a location on your intranet or the internet.
TL;DR version
- Save as CSV
- Convert to JSON as .txt file
- Put .txt file on intranet/internet somewhere
- Create WADL file that points to this .txt file
- Import WADL file into Siena
Here is the rather more detailed version
Firstly lets make a sample spreadsheet called Movies.xlsx which contains a simple table of the best movies out there, and call the table itself Movies.
In Excel do File / Save As a CSV file to create Movies.csv. You will likely get a warning about CSV files losing some information, but that’s ok, remember to keep the original xlsx file safe just in case. I’m sure there any many ways to convert Excel data to JSON, but the first one I found, that worked for me, was https://www.convertcsv.com/csv-to-json.htm . (This should not be taken as any kind of Microsoft endorsement of this site). Load your CSV file then click Convert CSJ to JSON, and from there click Save to Disk. This will create a .json file, I recommend renaming it to .txt as some web hosts (eg mine) are not happy delivering .json files over the internet (due to mime type configuration I think).
Next you need to put this movies.txt file on the intranet/internet using whatever method is available to you, such as FTP. Lets pretend I put this file on https://www.contoso.com/data/movies.txt (I did also try putting this on a corporate Sharepoint site, but the authentication got in the way).
Next install the WADL Generator https://social.technet.microsoft.com/Forums/en-US/82c7b717-5100-423c-aa44-effed28caf74/wadl-generator-for-project-siena?forum=projectsiena and fire it up.
Give the service a name (lets call it MovieDB), click Next. There is no authentication, so Next on that screen. We get to the functions screen, in the Name field put GetAll, and in the url field put the path to the .txt file. Click “Try It” and you should see the JSON in the window. Click Save and Next a few times, then name this file MoviesWADL.xml.
OK lets make a simple Siena app that consumes the Excel file directly, then we’ll update it to use the online version. Start up Siena, go to App Data / Data Sources / Excel and import the original Movies.xlsx file, selecting the table called Movies, then Import Data. The screen should show our data, so hit the back button to get to the canvas. Click + and add a new Text Gallery, then set its Items source to Movies, and instantly you’ll see the data in a scrollable list.
Save this Siena file, though of course in the real world you would create a fantastic app using this Movies data source. OK, lets switch the app over to the online version: back to Data Sources and delete the Movies.xlsx data. Instead click on REST and select the MoviesWADL.xml file we made earlier. To make sure it works, click the Try it button to see the data. Click Back to get to the canvas again, select the gallery we made earlier (which will be showing error triangles at this point), and change its Items property to MoviesDB!GetAll(). As if by magic the data will show up just as before, but now it is getting it live, well when the app starts up at least.
If you have multiple Excel tables you need to expose like this, create additional functions in the WADL Generator pointing to different .txt files, and give them other names. When your Excel data changes, you just have to repeat steps 1-3 above – no change in the WADL file or your app required. This method works well for data that doesn’t change very often, but if updates are required too often then you should consider creating a proper web service connected to a database, and expose a real REST API, instead of this manual conversion process.